Reporting Services - Get the results for a specific period through a longer period

Question

Hello,

I have a table that retrieves data over a 3 day period on an hourly basis, there are several different machines that work on a specific bottle for a period of time, during the three day period there could be several different bottles go on that machine. I want to show the results of the 1st bottle during the time that they were produced, followed by the results of the 2nd bottle during that time e.g.

Rows ------------------------------------  Columns -------------------------------------
Machine  Product      Product  Start       18/01/2010 18/01/2010 18/01/2010 18/01/2010
                      Code     Date        05:00      06:00      07:00      08:00
1        Beer Bottle  10256    17/01/2010  89         89
                               07:00
2        Wine Bottle  10376    18/01/2010                        14         50
                               07:00

But mine is looking like this, it retains the previous Bottles data:

Machine  Product      Product  Start       18/01/2010 18/01/2010 18/01/2010 18/01/2010
                      Code     Date        05:00      06:00      07:00      08:00
1        Beer Bottle  10256    17/01/2010  89         89         14         50
                               07:00
2        Wine Bottle  10376    18/01/2010  89         89         14         50
                               07:00

I have the row group grouped on Product and the column group group on DateTime.

Can anybody please advice how I can get the report to run like the first example.

Regards, AW

Answer 1

Are you aggregating the data in the report or in SQL? If in SQL I would be interested to see the query. It looks to me like a SQL problem instead of report set up. Do you mind posting the query?

Looks like you need to limit when you aggregate based on the product (or product code) as well as the time. Right now you are only doing it base on the time.

HTH

Answer 2

HTH, Hello, thank you for replying, I have attached the query as requested:

SELECT     tblRegion.RegionName, tblSite.SiteName, tblLine.LineName, 
           tblCheck.CheckName, tblCheckResult.DateTime, tblCheckResultDecimal.Result, 
           tblProdRun.PartNo, tblProdRun.PartDesc, tblProdRun.EndDateTime
FROM         tblRegion INNER JOIN
                  tblSite ON tblRegion.RegionID = tblSite.RegionID INNER JOIN
                  tblLine ON tblSite.SiteID = tblLine.SiteID INNER JOIN
                  tblCheckResult ON tblLine.LineID = tblCheckResult.LineID INNER JOIN
                  tblCheck ON tblCheckResult.CheckID = tblCheck.CheckID INNER JOIN
                  tblCheckResultDecimal ON tblCheckResult.CheckResultID =
                  tblCheckResultDecimal.CheckResultID INNER JOIN
                  tblProdRun ON tblLine.LineID = tblProdRun.LineID
WHERE     (tblCheckResult.DateTime >= @DateFrom) AND (tblCheckResult.DateTime <= 
          @DateTo) AND (tblCheck.CheckID = 69) AND (tblSite.SiteID = @SiteID) AND
          (tblProdRun.EndDateTime IS NULL) OR
          (tblCheckResult.DateTime >= @DateFrom) AND (tblCheckResult.DateTime <=
          @DateTo) AND (tblCheck.CheckID = 69) AND (tblSite.SiteID = @SiteID) AND 
          (tblProdRun.EndDateTime BETWEEN @DateFrom AND @DateTo)

ORDER BY tblLine.LineName, tblSite.SiteName, tblCheckResult.DateTime