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?
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?
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
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?
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
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
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
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.
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.
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.
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
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.
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.
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.
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"?
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"
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.
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??
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?
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#?
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?
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
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.
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
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!
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.
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?
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.
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.
