CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssrs-2008
jhowe
jhowe

Reputation: 10878

SQL Server 2008 SSRS one report multiple datasets

I'm new to SSRS and I have one report that needs to switch between 3 different queries depending on what the user wants to see i.e. Consumables, Service or Total (both combined) What is the best way to do this?

Queries below:

--Consumables 
SELECT COUNT(OrderId)             AS [Consumable_Order_Amount], 
       CONVERT(DATE, DateCreated) AS [Date], 
       CASE 
         WHEN orderheader.webref = '' THEN 'Call Centre' 
         ELSE 'Web' 
       END                        AS [Consumable_Order_Type] 
FROM   OrderHeader 
WHERE  ( CONVERT(DATE, DateCreated) BETWEEN @StartDate AND @EndDate ) 
GROUP  BY CONVERT(DATE, DateCreated), 
          CASE 
            WHEN orderheader.webref = '' THEN 'Call Centre' 
            ELSE 'Web' 
          END 

--Service 
SELECT COUNT(serviceId)           AS [Service_Order_Amount], 
       CONVERT(DATE, DateCreated) AS [Date], 
       CASE 
         WHEN serviceorder.webref = '' THEN 'Call Centre' 
         ELSE 'Web' 
       END                        AS [Service_Order_Type] 
FROM   ServiceOrder 
WHERE  ( CONVERT(DATE, DateCreated) BETWEEN @StartDate AND @EndDate ) 
GROUP  BY CONVERT(DATE, DateCreated), 
          CASE 
            WHEN serviceorder.webref = '' THEN 'Call Centre' 
            ELSE 'Web' 
          END 

--Total           
;

WITH [Total_Order_Amount] 
     AS (SELECT orderid     AS [ID], 
                datecreated AS [Date], 
                webref      AS [WebRef] 
         FROM   orderheader 
         UNION 
         SELECT serviceid   AS [ID], 
                datecreated AS [Date], 
                webref      AS [WebRef] 
         FROM   serviceorder) 
SELECT COUNT(id)                  AS [Service_Order_Amount], 
       CONVERT(DATE, date) AS [Date], 
       CASE 
         WHEN webref = '' THEN 'Call Centre' 
         ELSE 'Web' 
       END                        AS [Service_Order_Type] 
FROM   [Total_Order_Amount] 
WHERE  ( CONVERT(DATE, date) BETWEEN @StartDate AND @EndDate ) 
GROUP  BY CONVERT(DATE, date), 
          CASE 
            WHEN webref = '' THEN 'Call Centre' 
            ELSE 'Web' 
          END

Upvotes: 2

Views: 4490

Answers (3)

jhowe
jhowe

Reputation: 10878

I found the easiest/cleanest solution, pass parameters into a select query depending on type of report, combined with a case statement in a where clause to filter out the different report types in memory. I will mark this as the answer unless there are any objections! Thanks for your help. 

WITH [Total_Order_Amount] 
     AS (SELECT orderid     AS [ID], 
                datecreated AS [Date], 
                webref      AS [WebRef], 
                'Consumable'AS [ReportType] 
         FROM   orderheader 
         UNION 
         SELECT serviceid   AS [ID], 
                datecreated AS [Date], 
                webref      AS [WebRef], 
                'Service'   AS [ReportType] 
         FROM   serviceorder) 
SELECT COUNT(id)           AS [Order_Amount], 
       CONVERT(DATE, DATE) AS [Date], 
       CASE 
         WHEN webref = '' THEN 'Call Centre' 
         ELSE 'Web' 
       END                 AS [Order_Type] 
FROM   [Total_Order_Amount] 
WHERE  ( CONVERT(DATE, DATE) BETWEEN @StartDate AND @EndDate ) 
       AND [ReportType] = CASE @ReportType 
                          WHEN 1 THEN 'Consumable' 
                          WHEN 2 THEN 'Service' 
                          ELSE [ReportType] 
                        END 
GROUP  BY CONVERT(DATE, DATE), 
          CASE 
            WHEN webref = '' THEN 'Call Centre' 
            ELSE 'Web' 
          END

Upvotes: 0

Catch22
Catch22

Reputation: 3351

Why not create a parameter for the report with a list of predefined options that map to each query. This parameter can then be fed to the stored procedure and it can determine which sub-query to return.

For example: create a report parameter called QueryOption with a data type of Integer,
Add Available Values of Consumables, Service and Total with values of 1,2 & 3. Set the Default value to 1, 2 or 3 if you want to automatically select an option for the user.

Modify the stored procedure, to use the QueryOption parameter to determine which sub-query to return. 

IF @QueryOption = 1
BEGIN
    SELECT 'Consumables' -- Add consumables query here
END
IF @QueryOption = 2
BEGIN
    SELECT 'Service'    -- Add Service query here
END
IF @QueryOption = 3
BEGIN
    SELECT 'Total'      -- Add Total query here
END

On the DataSet properties for the stored procedure, ensure that you refresh the fields so that the QueryOption parameter gets automatically added.
As long as the name of the parameter for the sp matches the report parameter name, the parameter value will be fed into the stored procedure.

Upvotes: 0

openshac
openshac

Reputation: 5165

You don't really want to have 3 grids as you would need to maintain these separately, should say a new column be added. However if you are sure the the queries will always have identical columns you could just use a stored proc which decides which query to run. Ok there are separate issues here regarding performance and the execution plans for such a proc (we can go into that later).

Upvotes: 1

Related Questions