locked
Advice on how to create large report RRS feed

  • Question

  • User1355545883 posted

    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.

    Monday, August 19, 2013 9:05 AM

Answers

  • User-67082742 posted

    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

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 20, 2013 2:34 AM

All replies

  • User992646781 posted

    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.

    Monday, August 19, 2013 9:41 AM
  • User1355545883 posted

    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.

    Monday, August 19, 2013 10:03 AM
  • User-993404089 posted

    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? 

     

    Monday, August 19, 2013 10:52 AM
  • User1355545883 posted

    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? 

     

    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.

    Monday, August 19, 2013 10:59 AM
  • User-67082742 posted

    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

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 20, 2013 2:34 AM
  • User-67082742 posted

    sorry... the query assumed is select count(*) from @tablename. So in teh above suggetsed aproach there will be 1 parameter only.

    Tuesday, August 20, 2013 2:47 AM