SSRS Display week by week historical data

SSAS and SSRS
1

HI SQL gurus - new to the forum.

I recently started teaching myself SSRS and tackling business reports at my office. I have fairly basic SQL query skills, so you may need to bear with me if things get complex.

I have an existing report I inherited from a prior employee that I am trying to enhance.
It displays $$ data based on a weekly parameter set by the user. (@ startdate, @end date)

In my enhancement, I'd like to add a second table that lists historical week by week information but I am not quite sure how to approach this ? I did copy the report and in the duplicate copy (thinking I could link them together) made a hidden parameter with static date range for q1 but that of course didn't quite give me what I wanted, it was 1 large figure instead of a week by week basis. Ideally, a table on the original report would be great, but a linked separate report would also work. I guess this may also pose issues of how many weeks to display in total, etc. But any guidance on how to approach in general would be great.

Report Sample
user sets date range - parameter defaults to last sun-sat

Output:
User A: Target earnings | Total Earnings | %
User B: "
User C: "
Totals:

2

First you should find the query that feeds data to your report. In report designer, you would find the datasets used by the report. Properties of the datasets should tell you what queries it is using. Look at those queries and modify them to return week by week data. Perhaps you may need to group by week. If you need help doing that, post the query and people on the forum should be able to help.

Another alternative is to do the grouping in SSRS itself. My inclination would be to do the grouping in the query rather than SSRS.

3

Yes! That makes sense.
Okay I was going down a very wrong path creating start and end parameters for each historical week I wanted, which was getting cumbersome and lengthy. But the input won't change (I will always want, say the last 12 weeks), so building it into the query makes sense, lightbulb is on and I think I see how I can make this work now.

So....what I just did was replace the @StartDate and @EndDate parameters in the query with my getdate functions and I successfully pulled last weeks' data - so now I just need to figure how to add this to the query, say 12 times to get each weeks worth of date.... Not sure that's the most efficient way but it's starting to make sense to me.

Sample
Old:
(ISNULL(dbo.GetRevenueLine1(dbo.v_Member.Member_ID,@Weekstart,@WeekEnd),0)) AS Client1Revenue,
(ISNULL(dbo.GetRevenueLine2(dbo.v_Member.Member_ID,@Weekstart,@WeekEnd),0)) AS Client2Revenue,
(ISNULL(dbo.GetRevenueLine3(dbo.v_Member.Member_ID,@Weekstart,@WeekEnd),0)) AS Client3Revenue,

New:

(ISNULL(dbo.GetRevenueLine1(dbo.v_Member.Member_ID,DATEADD(DAY, - 13 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)),DATEADD(DAY, - 7- DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))),0)) AS Client1Revenue,
(ISNULL(dbo.GetRevenueLine2(dbo.v_Member.Member_ID,DATEADD(DAY, - 13 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)),DATEADD(DAY, - 7- DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))),0)) AS Client2Revenue,
(ISNULL(dbo.GetRevenueLine3(dbo.v_Member.Member_ID,DATEADD(DAY, - 13 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)),DATEADD(DAY, - 7- DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))),0)) AS Client3Revenue