SSRS using multiple datasets
Posted on 2012-03-26
I'm trying to create a report that will display all the employees, their sum(hours/pay) per week (user selected), their sum(hours/pay) per year (current fiscal year), and break down their hours/pay for the week into details (ie date, service code, etc). My boss wants to be able to select/sort/group by employee and/or their team, the date as week range (default) and pick start/end dates from calendar, see employees whether they have submitted hours or not (0 hours/ 0 pay) and totals by employee and team and maybe more later, lol.
He wants this available on one report and while I've gotten most of this working in seperate reports I'm struggling to put it into one report.
1. Can I use multiple datasets and display them within a data region? Like I'd like an employee to list and then the PTD and YTP to display under it in details. I'm not sure if I can get all that detail data and sums in one query.
2. Would this be easier to create in subreports? Where PTD is a subreport under Employee and YTD is a sub report under that?
3. My boss wants the option to choose a week or to choose a start and end date. Is there a way to have 2 parameters with a condition check to see which the user chose from? Is there a to have the @enddate hidden but selecting a value itself based on the value of the @startdate? I.e. default value is @startdate + 6 days? That way if they can pick a startdate/enddate from the calendars or the startdate from the dropdown box
(=DateAdd("d", -7 -(WeekDay(Today(),2))+1, Today())
with last 6 weeks hardcoded this way and
(WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) in the query)?
Is there an easier way than what I'm doing? I want the boss to be able to pick a week or pick 2 dates. Maybe I can do the (=DateAdd("d", -7 -(WeekDay(Today(),2))+1, Today()) in the query?