Question About Best Way To Render A Dynamic Dataset

Apr 10, 2008

We have built an analytics solution in Analysis Services with the following setup:

1) FactTable = Customer transactions
2) DimCustomerClassificationTable = For a customer over a trailing twelve month period, we have pre-calculated a series of segmentations such as their revenue segment at a Worldwide Level, Region Level, SubRegion Level, or Country Level, their product classification (e.g., what are they mainly buying) at a Worldwide Level, Region Level, SubRegion Level, or Country Level etc. For each combination of a customer and country over a trailing twelve month period, we have one dimension table record that has their segmentation classifications at each geography level.
3) DimTime = Allows a user to select a trailing twelve month time period
4) DimSalesLocation = the hierarchy of sales organization locations (Worldwide, Region, SubRegion, Country) credited with the sale to the customer

Ideally, we would like to create calculated members on the DimCustomerClassification dimension that would assume the value of the correct segmentation field based on a user's selection (e.g., if they has selected a country, we would have one calculated member called Revenue Segment that assumes the value of Revenue Segment Country, if they selected a SubRegion, it would assume the value of Revenue Segment SubRegion, etc.), but we are not sure if this is possible.

If this isn't possible, what would be the best approach to address this?

Thanks in advance for your help!

View 1 Replies


ADVERTISEMENT

Create A Dataset With .Net Code And Render Report From It. Possible?

Sep 20, 2007



Hi
I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.
My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...
I have tried to find a solution but so far, no luck.
Anyone have any ideas?

/F

View 3 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

View 2 Replies View Related

Dynamic Dataset For Another Dataset!

Dec 3, 2007

Hi experts,

I'm not sure my design is normal or not. Please give me some advice.

I've a dataset and query by a field name 'companyid'.

select * from companyid where companyid = @icompany which @icompany is a input field.


if user select all, i'll send 0 to @icompany then I need to select all records.

question 1. How can I get all records? (i think about this query select * from companyid <> 0)

if user select for example companyid = 1, i'll send 1 to @icompany and the query work fine.

question 2. How can I change the query to adopt this 2 condition?

Thanks a lot,

Jeff

View 8 Replies View Related

Split 1 Dataset Into 3 Without Dynamic SQL

May 16, 2008

I have some data in the following format

Timestamp Value DividendRequirement
01/01/2008 100 100
01/01/2008 200 90
02/01/2008 123 100
02/01/2008 436 90
03/01/2008 399 100
03/01/2008 5046 90
03/01/2008 45 130
04/01/2008 100 100
04/01/2008 233 90
04/01/2008 12 130

What is required is to split data of this format into 3 separate datasets:

1. One dataset for DividendRequirement of 100,
i.e. select * from tableName where DividendRequirement = 100

2. One dataset for DividendRequirement > 100
i.e. select * from tableName where DividendRequirement > 100

3. One dataset for DividendRequirement < 100
i.e. select * from tableName where DividendRequirement < 100

I know that i can do it with 3 separate stored procedures using a different operator ('=', '>' and '<') in each one and that i can combine the 3 stored procedures into 1 using dynamic sql and pass the operator (or some number that maps to a particular identifier) as a parameter to the stored procedure. What i'm after though is a way to avoid dynamic SQL but still keep it as one stored procedure. Possibly some clever use of case statements or something along those lines?

Any ideas?

Thanks

View 5 Replies View Related

How To Get Dynamic Attributes In Dataset

Nov 28, 2007

hi
i have a problem
i am writing a stored procedure which contains "EXECUTE" statment which excutes the query and retrieves the attributes what i want.
by using that procedure it is working fine and i am able to get the result

but i am not able to build the report why because this dataset not listing the attributes.


my procedure is like this :
--------------------------------------------
USE [HOST_BPM_COVLTCP]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROC [dbo].[PB_Report_GetProjectAttributes]
@intProjectId INT
AS
BEGIN

DECLARE @STR_ATTRIBUTES VARCHAR(8000), @SRC_ATTRIBUTE_COLUMNS VARCHAR(8000), @SRC_ATRIBUTE_NAMES VARCHAR(8000)

SELECT
@STR_ATTRIBUTES = COALESCE(@STR_ATTRIBUTES + ',', '') + 'dbo.FindAndReplace(MAX(CASE WHEN ATTRIBUTE_ID = ' + CONVERT(VARCHAR, IDX) + ' THEN ISNULL(ATTRIBUTE_VALUE, '''') ELSE '''' END)) [' + CONVERT(VARCHAR, LABEL) + ']',
@SRC_ATTRIBUTE_COLUMNS = COALESCE(@SRC_ATTRIBUTE_COLUMNS + ',', '') + 'SRC.[' + CONVERT(VARCHAR, LABEL) + ']',
CONVERT(VARCHAR, IDX)
@SRC_ATRIBUTE_NAMES = COALESCE(@SRC_ATRIBUTE_NAMES + ',', '') + '''' + LABEL + ''' ATTR_' + CONVERT(VARCHAR, IDX)
FROM
PB_ATTRIBUTE

SELECT
@SRC_ATTRIBUTE_COLUMNS = (CASE WHEN @SRC_ATTRIBUTE_COLUMNS IS NULL THEN '' ELSE ',' + @SRC_ATTRIBUTE_COLUMNS END),
@STR_ATTRIBUTES = ISNULL(@STR_ATTRIBUTES, ''''' DUMMY_COL'),
@SRC_ATRIBUTE_NAMES = ISNULL(@SRC_ATRIBUTE_NAMES, ' '''' WHERE 1 <> 1')

EXEC
(
'
SELECT
DP.IDX PROJECT_ID, dbo.FindAndReplace(DP.CODE) [Project Code], dbo.FindAndReplace(DP.NAME) [Project Name], dbo.FindAndReplace(DP.LABEL) [Project] ' + @SRC_ATTRIBUTE_COLUMNS + ',
ISNULL(DP.CREATED_BY,'''') AS CREATED_BY, ISNULL(DP.MODIFIED_BY,'''') AS MODIFIED_BY,
DP.CREATED_DATE, DP.MODIFIED_DATE
FROM
DIM_PROJECT DP,
(
SELECT
' + @STR_ATTRIBUTES + ', PROJECT_ID
FROM
PB_PROJECT_ATTRIBUTE_VALUE
WHERE
PROJECT_ID = ' + @intProjectId + '
GROUP BY
PROJECT_ID
) SRC
WHERE
DP.IDX = ' + @intProjectId + ' AND
DP.IDX *= SRC.PROJECT_ID
ORDER BY DP.LABEL
'
)
----------
actually this procedure should result the following attributes

PROJECT_ID,
[Project Code],
[Project Name],
[Project],
[Nature of Change],
[Department Priority #],
[Project Start Date],
[Project End Date],
[Project Status],
[Project Justification],
[Project Request Sub-Type],
[Project Request Type],
[Project Request 2nd Sub-Type],
[Requesting Department],
[Capital Fund Number],


but i am not getting the attribute list in the dataset

anyone help me out

View 1 Replies View Related

Dynamic Sql In Dataset Not Working

Mar 6, 2007

I'm trying to have the sql in my dataset dynamic based on report parameters. I expected this to work, but doesn't. Dataset:

="select item from table where " + Parameters!SearchType.Value + " = " + Parameters!Filter.Value + "order by item"

Is this possible or do I need to take a different approach (and what would that approach be)

Thanks,

Derek

View 3 Replies View Related

Dynamic Parameterized MDX Dataset In Report Table

Oct 10, 2007

Hi, I was wondering if there is a way to solve this issue.

I have the following MDX to retrieve specialised time related data from a cube



Code Block

SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[2007 Q1], [Time].[Quarter].[2007 Q1].lag(1), [Time].[Quarter].[2007 Q1].lag(2), [Time].[Quarter].[2007 Q1].lag(3), [Time].[Quarter].[2007 Q1].lag(4), ytd( [Time].[Quarter].[2007 Q1]), ytd( [Time].[Quarter].[2007 Q1].lag(4)),ytd([Time].[Quarter].[2007 Q1].parent.lag(1)),ytd([Time].[Quarter].[2007 Q1].parent.lag(2)) } } } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS

FROM [QMS]

WHERE ( [Overall].[Overall].&[Y] )






I can populate a table with the columns i need from the resulting dataset, however it is not dynamic. i.e.

The dataset field names come up as say,

Measures___Vacancy_Time_All_Time_2007_2007_Q1
Measures___Vacancy_Time_All_Time_2006_2006_Q4

Which i can place in the report...

But if I then want to parameterize [2007 Q1] like as follows




Code Block
="SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[" + Parameters!Quarter.Value + "], [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(1), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(2), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(3), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "]), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(1)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(2)) } } } ON COLUMNS ,NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS FROM [QMS] WHERE ( [Overall].[Overall].&[Y] )"





so that everything is then driven from a single selected Quarter value, the table report no longer gets populated, as it has hardcoded field values such as

=Fields!Measures___Vacancy_Time_All_Time_2007_2007_Q1.Value

and if the Quarter selected is [2006 Q1] for example, this field will not exist in the dataset.

Is there a way to accomplish this? I am using SSRS 2005 against SSAS 2000 cubes

View 5 Replies View Related

SQL Server 2008 :: Dynamic Dataset And Tables In SSRS

Jul 27, 2015

Need to change a Excel report to SSRS.

Excel report has around 15 tables all with different columns.

Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.

Note -- All 15 tables have differnt columns list.

View 3 Replies View Related

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

I have a small number of rows in a dataset, Table 1.  There is a CLOB on a large dataset, Table 2.  They join on a PK.  I would like to retrieve this CLOB and add it to the data flow for Table1.  In short I want to emulate the following:

Table 1:  Small table without CLOB, 10 rows. 
Table 2: Large table with CLOB, 10,000,000 rows

select CLOB
from table2
where pk = (select pk from table1)

I want this to return the CLOBs for the small number of rows in Table 1.  The PK is indexed obviously so it should be a fast look up.

Table 1 and Table 2 live on different Oracle databases.  How do I perform this operation efficiently in SSIS?  It seems the Lookup and Merge Join wont do this.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.

I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 3 Replies View Related

Listing Datasets In Report (dataset Name, Dataset's Commands)

Oct 12, 2007



Is there any way to display this information in the report?

Thanks

View 3 Replies View Related

Dataset.Tables.Count=0 Where There Are 2 Rows In The Dataset.

May 7, 2008

Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.


ALTER PROCEDURE [dbo].[MyStorProc]

(

@Status smallint,

@RowCount int = NULL,

@FacilityId numeric(10,0) = NULL,

@QueueID numeric (10,0)= NULL,

@VendorId numeric(10, 0) = NULL

)

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF



If @Status = 0

BEGIN

SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END



View 4 Replies View Related

How To Transfer Data From One Dataset To Other Dataset

Apr 11, 2008

i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
 

View 4 Replies View Related

Filter One One Dataset With Values In Another Dataset?

Dec 19, 2006

Hi,

I have two datasets in my report, D1 and D2.

D1 is a list of classes with classid and title

D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.

I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.

Is there any way to do this in RS2005?

View 2 Replies View Related

Cannot Get CSV Or XML To Render

Jun 12, 2007

Hello,



I've just installed SQL Server 2005 Express, and RS (all with SP2). I can run reports fine for HTML, PDF, and Excel, but whenever I specify a format with "CSV" or "XML", I get the following error:



An attempt has been made to use a rendering extension that is not registered for this report serverAn attempt has been made to use a rendering extension that is not registered for this report server



I'd really like these report formats to work. I haven't modified any system configuration files.



Has anyone else seen this error?



Thanks,



Michael

View 2 Replies View Related

Render Data In Asp.net

Apr 14, 2008

I have made a working admin section for my website, now the problem is that I don´t know how to render the data (from sql database) which code should I use to render for instance a field called "projectName"?
 

View 1 Replies View Related

Render To Xml Failed

Aug 15, 2007

Hi.

if I try to export my rdl file to xml from Report Viewer it's work
but if i try to render the rdl from code with format xml if i try to show the xml i get "Cannot have a DOCTYPE declaration outside of a prolog"

this occur just in some rdl's.

somebody help...

sorry about my english

View 1 Replies View Related

CSV Render Engine

Jul 1, 2007

Hi

I have Reporting Services hosted on a server which has Workgroup edition of SQL Server which does not support csv export. I would like to write a csv Renderer.

I am not sure how complex this task is likely to be.
Are there any White Papers or samples that would assist ?
Any advice to point me in the right direction would be most appreciated.

Regards

JohnJames

View 1 Replies View Related

Not Able To Render The Report

Oct 5, 2007

Hello All,
I have a report which throwing this error:

Cannot read the next data row for the data set ALERTSBYSERVERS. (rsErrorReadingNextDataRow) Get Online Help
Arithmetic overflow error converting expression to data type datetime

This report can be viewed on the Visual Studio, but is not rendering it on the Reporting console. Any ideas??

Mannu.

View 4 Replies View Related

Render HTML

Jun 20, 2007

I have a report which uses for one of its columns, a text field that contains HTML formatting. FreeTextBox is used to enter the rich-text data and is bound to a dataset, in this type of format : <BR><B>the title</B><P>Some stuff...</P>. Needless to say, when this prints out it does not look very useful. Is there any way to embed a rich-text type field in a report?

View 1 Replies View Related

Render To PDF File

Nov 12, 2007

Is it possible to render report to PDF using VB.NET, i google it, able to get code using c#, that I am not familliar.
Render to PDF using VB.NET is possible?
Is there anyother way todo it without using C#?


I tried this mehtod

http://myserver/ReportServer/ReportService2005.asmx?%2fReports%2fGroup&rs:Command=Render&rs:Format=PDF


If format is EXECL, it is very quick but PDF is not working, it say downloading..... nothing happen

any idea


thanks
Kal

View 2 Replies View Related

Excel Render Bug?

Apr 30, 2008

I'm using visual studio 2005 with .NET 2.0 SP1.


Under a report server project, I created a table and used it to display a dataset drawn from a database (SQL Server 2005). And I set the "NoRows" property of this table to something like: "= "Customer: " & Parameters!CustomerID.Value & " does not exist in database.". I deployed the report on a report server, run it and exported to Excel. If there were no data returned, the Excel will display the non-exist message as designed. But if I export the same report again without re-run the report, the message will disappear. Looks like a bug?

View 3 Replies View Related

HT Make Spaces Render In Asp.net

Jun 22, 2008

All- I'm using the SQL SP below to drive an asp.net gridview. Note how, after "else", I attempt to send to the browser the HTML code that should create a hard space. However, the characters are simply rendered verbatim by the browser. I've tried putting actual spaces in the quotes, and also the SPACE(x) argument. SP test executes appear to show the spaces in the results, but aps.net isn't rendering the spaces at all. Any thoughts.

(Its really more of an asp.net question, but I thought I'd post this here too for your thoughts. Thx in advance.)




SELECT person_id, male, female, last, CASE WHEN fam_adult = 1 then first else ' ' + first end as first, fam_adult, is_adult, is_kid, is_y_parent_or_kid, is_guest, is_person_type_5, grade, school, is_person_type_6, fam_dad
FROM person

View 1 Replies View Related

Render To Be Viewed On Blackberry

Nov 29, 2007



Hi,
Can someone please help me figure out how to render a report so it can be viewed on a blackberry? I have tried different rendering formats but they didn't work. I am able to view the report when I render as "Web Archive" but in this case all the formatting is messed up.

Please help me out with this. Any help will be appreciated.

Thanks,
-Rohit

View 3 Replies View Related

Show And Render Only One Page

Jul 19, 2007



my report generates most than a page, i want sent the parameters and render only the fist page , the if i need the other when the number page chage the control render the nex page, how i can do it, this because in this moment the control render all the report and the show it , and sometimes i have to wait to long time to see it

someone can help me?

View 3 Replies View Related

How To Render A Subreport Per Page?

Apr 29, 2008

I have a requirement that I'm having a difficult time figuring out.

I have a number of subreports, 27 of them, and I need to put them all on a single report, and have each one render on a separate page. Each of them I can be sure is a single page - how do I force a page break after each subreport?

I've been trying a number of strategies, tables, lists, rectangles, etc, with no luck...

Any thoughts or suggestions would be greatly appreciated!

Thanks,

B

View 3 Replies View Related

Render Report Straight To Pdf

Sep 18, 2007

Hi,

Can anybody tell me if it's possible to set a report to render directly to PDF please. I've found stuff on how to do it via a url but that's not how we want to do it.

Thanks in advance

Steve

View 2 Replies View Related

Report Render To PDF Hangs

Mar 12, 2007

I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.

I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.

Is anyone else finding problems like this? They don't seem to be code related.

Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?

Thanks for any info

View 9 Replies View Related

Render Just The Raw Data From The Recordset

Feb 1, 2008


Is there a way to return just the raw data from a report? I do not mean XML rending which marries the dataset information with the layout information. I just want the raw data from the dataset. If the XML returned was the result of the recordset from the de-parameteraized query only that would be great!

I am looking into writing a Custom Rendering Extension, but I am not fining much outside of the same MS example circulating. It seems to only deal with layout data and not the raw dataset data.

Is there way to get to just the raw data and ignore layout? Is the dataset in the SSRS Temp Dd that you can query after a report has run? If anyone knows of any way to get to the raw data that would be fantastic.



Thanks!

View 3 Replies View Related

ReportExecutionService.Render Question

Jul 18, 2007

Okay, in doing some research about executing a report using code, I found the ReportExecutionService.Render and .LoadReport methods.



If I use this, is the report stored as a snapshot in history on the Report Server or how is the report rendered?



Forgive my ignorance in this and I thank you for your information.

View 8 Replies View Related

Render Lag With Subreports In List

Feb 19, 2008

Hi everyone,

For a project I'm working on right now we issue a bunch of 2-4 page PDF reports (a main page and a subreport) for every entity within a retail hierarchy. Our client would like to have some of those reports rolled up into a single file so that starting in the second level of the hierarchy there is one file for each member and all its ascendants/descendants. In order to do this, I'm putting the existing report into a list on a new template (as a subreport) and creating a dataset for the list to pass the parameters into the subreport. I initially had to do this for 1000+ stores and realized that it was going to take a LONG time (the main page report can take as long as a minute to run as it's kicking off stored procs that retrieve a ton of data from a cube), so I stopped it and restricted my list's dataset to 3 rows to create a 3-report rollup for testing. When I previewed the report, it showed up after a minute or so and it looked fine - everything was where I expected it to be, pages were breaking properly, etc. However, when I tried to export that into PDF it hung forever. I cut the dataset back to 2 records and had the same problem, so then I cut it back to 1 record and it exported just as quickly as it always does. Does anyone have any information on why this is happening and what I can do to get around it? It's going to be a pretty critical component of this project for us.

Thanks in advance,
Adam

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved