I have been tasked with the job of coming up with a SSRS report that will count and sum quantities and costs in weekly increments starting from a specified date and then for the next 26 weeks. Each column will count and sum values for the 7 days that make up each week. So if the date entered is April 6, 2016 the first column is 04/06-04/12, the next is 04/13-04/19, etc. My question is how do I structure the query so that it will automatically sum by week?
I have attached a copy of the actual dataset for 2015. What this report will do is allow the users to forecast what is expected to be received over that six month period. Here is the query that produced this dataset. The PRMDATE field is the date the goods are expected to be received so the columns need to be based on this field value.
,PRMDATE FROM RAD..POP10110 WHERE PRMDATE>='01/01/15' ORDER BY PRMDATE
There is no grouping on this report. In this case these values are what are expected to be received. So the end result for this data would be one row and in each column would be a total count and the total sum of the cost of the items due in each week. Something like:
Week One Week Two Week Three Total
April 6 April 13 April 20
Count Cost Count Cost Count Cost Count Cost
2,000 10,000 3,000 15,000 2,500 12,500 7,500 37,500