SQL Server 2008 SSRS one report multiple datasets

Question

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 

Answer 1

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