I'm relatively new to SSRS, having created a few small reports which reference a simple stored procedure to get data. But I have a whopper assigned to me. It's not necessarily difficult, just huge. The finished report is to be a collection of approx. 20
tables, each with 7 columns and 6 rows. Each cell is a COUNT from a sql query (i.e. SELECT COUNT(*) FROM table).
Surely there must be a more efficient way than creating a separate dataset for each cell in 20 tables. That's hundreds of datasets.
I thought of creating a temp table in my stored procedure housing all of the data, but I'm not sure how to extract it on the SSRS end, and populate each individual cell.
I would greatly appreciate any advice or suggestions you might have.
You're along the right lines. You can create a temporary table in the stored procedure which contains all the data you need. What you can do then is select everything from the temp table, i.e. do something like this:
CREATE PROCEDURE [dbo].[proc1]
AS
BEGIN
-- Create your temp table
SELECT * FROM #tempTable
END
After you have created your stored procedure you can use it in SSRS, it will pick up all the data returned without any further effort.
Not sure if i get the problem correctly but here is a suggestion :
Create a report R1.rdl as general table with columns that you like to show the count. Write the query of dataset as parmeterized like : select count (* ) @col from @table.
You will have the colname and tablename listed in report parameters when done.
Now add another report Main.rdl and add a table to it with single column. Add the previous r1.rdl report as a subreport in this table. You will need to group the subreport row on a dataset field. The dataset can be a list of all tablenames and column names
that you need. This way you will just need a table in your DB which houses all tablenames and colnames required for count. The serialnumber field can be added for helping with group. The subreport will accept a unique tablename and colname as paarmeters for
each group iteration and would generate the required report.
Please let me know if this helps. I assume here that the query governing all is Select count(*) colname from tablename
Member
377 Points
726 Posts
Advice on how to create large report
Aug 19, 2013 09:05 AM|fralo|LINK
Hi all,
I'm relatively new to SSRS, having created a few small reports which reference a simple stored procedure to get data. But I have a whopper assigned to me. It's not necessarily difficult, just huge. The finished report is to be a collection of approx. 20 tables, each with 7 columns and 6 rows. Each cell is a COUNT from a sql query (i.e. SELECT COUNT(*) FROM table).
Surely there must be a more efficient way than creating a separate dataset for each cell in 20 tables. That's hundreds of datasets.
I thought of creating a temp table in my stored procedure housing all of the data, but I'm not sure how to extract it on the SSRS end, and populate each individual cell.
I would greatly appreciate any advice or suggestions you might have.
Thank you.
"They just ARRRGGGHHHH!!!!!"
Contributor
2497 Points
686 Posts
Re: Advice on how to create large report
Aug 19, 2013 09:41 AM|vytautas.ziurlis|LINK
You're along the right lines. You can create a temporary table in the stored procedure which contains all the data you need. What you can do then is select everything from the temp table, i.e. do something like this:
CREATE PROCEDURE [dbo].[proc1] AS BEGIN -- Create your temp table SELECT * FROM #tempTable ENDAfter you have created your stored procedure you can use it in SSRS, it will pick up all the data returned without any further effort.
Member
377 Points
726 Posts
Re: Advice on how to create large report
Aug 19, 2013 10:03 AM|fralo|LINK
Thanks, but I just thought of something. How big can this temp table be? Should it have a separate column for every cell in my report?
There will be hundreds.
"They just ARRRGGGHHHH!!!!!"
Participant
1001 Points
417 Posts
Re: Advice on how to create large report
Aug 19, 2013 10:52 AM|stockcer|LINK
How often does the data in these tables change. Once a day, once a hour, every second, etc. Can the user accept a delay of this report?
James River Webs, Inc.
Member
377 Points
726 Posts
Re: Advice on how to create large report
Aug 19, 2013 10:59 AM|fralo|LINK
It changes several times throughout the work day. As transactions are performed, the data is updated in the database.
I'm not sure what you mean by the user accepting a delay of the report.
"They just ARRRGGGHHHH!!!!!"
Member
220 Points
64 Posts
Re: Advice on how to create large report
Aug 20, 2013 02:34 AM|Abhi.ssrs|LINK
Hi,
Not sure if i get the problem correctly but here is a suggestion :
Create a report R1.rdl as general table with columns that you like to show the count. Write the query of dataset as parmeterized like : select count (* ) @col from @table.
You will have the colname and tablename listed in report parameters when done.
Now add another report Main.rdl and add a table to it with single column. Add the previous r1.rdl report as a subreport in this table. You will need to group the subreport row on a dataset field. The dataset can be a list of all tablenames and column names that you need. This way you will just need a table in your DB which houses all tablenames and colnames required for count. The serialnumber field can be added for helping with group. The subreport will accept a unique tablename and colname as paarmeters for each group iteration and would generate the required report.
Please let me know if this helps. I assume here that the query governing all is Select count(*) colname from tablename
Tahnks,
AB
Member
220 Points
64 Posts
Re: Advice on how to create large report
Aug 20, 2013 02:47 AM|Abhi.ssrs|LINK
sorry... the query assumed is select count(*) from @tablename. So in teh above suggetsed aproach there will be 1 parameter only.