locked
Creating one ssrs report that can use one of multiple stored procedures? RRS feed

  • Question

  • Is there a way to develop a single, generic ssrs report, that can dynamically decide which stored procedure to access?

    I need to create a dozen different admin reports, all of which look exactly the same.  The only difference being the data displayed.  My boss suggested I create a single report, that accepts a parameter that informs the ssrs which report to run.

    So for example, if the url is "MyReports/GenericReport?rptName=OrdersReport", my ssrs will know "OrdersReport" means the "rpt_OrdersReport" stored procedure is what needs to be used.

    Does anyone know how to do this of know of any online tutorials?

    Thanks.

    Tuesday, June 12, 2012 1:56 AM

Answers

  • Hi There

    You will be able to achieve this, please create a wrapper stored procedure for your report and in that wrapper stored procedure you can call multiple stored procedure(as many as you want) based on your parameter value.

    Please make sure that the columns name returned from all these stored procedure, you give them exactly same mask so that you can use one mask for all your values from different stored procedure

    If @Reporttorun=” OrdersReport”
    Exec dbo.GetOrdersReportvalues
    Else if @Reporttorun=” inventoryReport”
    Exec dbo.GetInventoryReportvalues
    else
    Exec dbo.GetStockReportvalues

    I hope this will help.

    If you have any questions please ask.

    Many Thanks

    Syed Qazafi Anjum                                             

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.



    Tuesday, June 12, 2012 2:52 AM

All replies

  • Hi There

    You will be able to achieve this, please create a wrapper stored procedure for your report and in that wrapper stored procedure you can call multiple stored procedure(as many as you want) based on your parameter value.

    Please make sure that the columns name returned from all these stored procedure, you give them exactly same mask so that you can use one mask for all your values from different stored procedure

    If @Reporttorun=” OrdersReport”
    Exec dbo.GetOrdersReportvalues
    Else if @Reporttorun=” inventoryReport”
    Exec dbo.GetInventoryReportvalues
    else
    Exec dbo.GetStockReportvalues

    I hope this will help.

    If you have any questions please ask.

    Many Thanks

    Syed Qazafi Anjum                                             

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.



    Tuesday, June 12, 2012 2:52 AM
  • Could you email me an example of how to develop a report with multiply stored procedures - problem is - i don't want to mask the column names - I want to actually column names returned from the stored procedures. I also want to be able to pass paramaters to the stored procedure the user selects.
    Tuesday, October 1, 2013 5:34 PM
  • Hi, 

    I create a simple prototype of report, using multiple SPs in a single Generic Report. 

    Assumption - Should return same number of columns and same number of parameters ( could be Empty, but would need to have same schema)

    I create 3 SPs with 1 parameter each and 3 output columns, plus 1 wrapper proc to run these SPs. 

    CREATE PROC dbo.spGetCity_test (@City VARCHAR(100) = '0') AS BEGIN SELECT * FROM ( SELECT 'ID' ID, 'City' Name, 1 AS Ord UNION SELECT '1' ID, 'Seattle' Name,2 AS Ord UNION SELECT '2' ID, 'Chicago' Name,2 AS Ord UNION SELECT '3' ID, 'Houston' Name,2 AS Ord UNION SELECT '4' ID, 'Austin' Name,2 AS Ord UNION SELECT '5' ID, 'Dallas' Name,2 AS Ord ) s WHERE ((Name = @City OR Name = 'City') OR @City = '0') ORDER BY Ord END GO CREATE PROC dbo.spGetState_test (@State VARCHAR(100) = '0') AS BEGIN SELECT * FROM ( SELECT 'ID' ID, 'State' Name, 1 AS Ord UNION SELECT '1' ID, 'WA' Name,2 AS Ord UNION SELECT '2' ID, 'OH' Name,2 AS Ord UNION SELECT '3' ID, 'TX' Name,2 AS Ord UNION SELECT '4' ID, 'MI' Name,2 AS Ord UNION SELECT '5' ID, 'VA' Name,2 AS Ord ) s WHERE ((Name = @State OR Name = 'State') OR @State = '0') ORDER BY Ord END GO CREATE PROC dbo.spGetCountry_test (@Country VARCHAR(100) = '0') AS BEGIN SELECT * FROM ( SELECT 'ID' ID, 'Country' Name, 1 AS Ord UNION SELECT '1' ID, 'INDIA' Name,2 AS Ord UNION SELECT '2' ID, 'USA' Name,2 AS Ord UNION SELECT '3' ID, 'CHINA' Name,2 AS Ord UNION SELECT '4' ID, 'JAPAN' Name,2 AS Ord UNION SELECT '5' ID, 'RUSSIA' Name,2 AS Ord ) s WHERE ((Name = @Country OR Name = 'Country') OR (@Country = '0')) ORDER BY Ord END GO CREATE PROC dbo.spGeneric (@ParameterName VARCHAR(100)='', @spName VARCHAR(100) = '') AS BEGIN EXEC @spName @ParameterName; END GO

    EXEC dbo.spGeneric @spName = 'spGetCountry_test', @ParameterName = '0'
    EXEC dbo.spGeneric @spName = 'spGetCountry_test', @ParameterName = 'USA'


    Below is the Result Set. I am trying to return Column header from SP and identifying them by column "Ord=1"

    Once you get these sps created in database, create report with a dataset calling "dbo.spGeneric ".

    You would need 2 parameters 

    1. SP Name ( provide available names - to avoid sql injections) 

    2. Parameter ( I added '0' as an Option for "ALL")

    Add Expression for Row 

    BackGround Color: =IIf(RowNumber(Nothing)= 1, "Gray", "Transparent")

    Font Color: =IIf(RowNumber(Nothing)  = 1, "White", "Black")

    Below is the Screen Shot how I got the report. Same report Works for other procedures. 

    

    Let me know if have any questions. 

    Thanks,

    Lachhi

    • Edited by Lacchi Wednesday, October 2, 2013 12:03 AM
    • Proposed as answer by Lacchi Wednesday, October 2, 2013 12:08 AM
    Wednesday, October 2, 2013 12:02 AM
  • I don't have the same set of columns for each of my stored procedures and the stored procedures are different.

    Do you have an example for this scenario?

    Friday, October 4, 2013 6:07 PM