• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

SSRS using multiple datasets

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?
4 Solutions
rtayIT DirectorCommented:
You can have multiple data sources on a SSRS report.  Just make multiple datasets and you can display the data in multiple tables.  Each Dataset can have its own filters, so YTD and MTD would be easy to set up.  Sub reports are also an option for what you are looking for.  I would play around with each to see which works best for your application.
HSI_guelphAuthor Commented:
I read you can't use multiple data sources/datasets in the same data region/tablix. I've tried and gotten errors about having dynamic data in static fields.  Would it be better to use a List and add seperate tables for YTP and PTD?  I played around with this idea but ending up getting repeating data (like instead of one employees hours I'd get all employees and the same for the next employee and the rest after that).  Will passing a parameter like EmployeeID solve this?  What about my query?  Will I get the wrong results if I don't sort/group properly?
Lynn HuffCommented:
I chuckle at your question because I have a boss who also wants to have everything in just ONE report!  

On your first question, I don't think it is possible to have a single tablix pointing to multiple datasets.  I assume you are working in SSRS 2008?  When I run into complex reporting requirements, I will build a stored procedure and then only pass parameters from the report query.  Inside a stored proc you can get as creative as you like with temp tables, variables that change based on a param value, etc.

On your second question, I'm not a fan of subreports, although I do use a lot of drill downs (toggles).  Screen real estate and user confusion slant me against subreports.

On the third question, if you put your query in a stored proc, you can test for null in the second date and pass control of the proc to different queries based on whether or not you sent one date or two to the stored proc.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

HSI_guelphAuthor Commented:
I had been thinking of using stored procedures.  When I worked with JSP I'd write the stored procedure but the Web Master would create it.  My supervisor doesn't have much experience with SQL in reports so I've shyed away but I think I'll see if I can create them myself.  Any advice to keep me from blowing up the database? Lol.
Lynn HuffCommented:
You'll need the appropriate rights to the database of course, and put your procedure in a saved script file that first checks to see of a proc by that name exists, then deletes it if it does, then creates the proc.  I typically start my scripts like this:

USE [DatabaseName]

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ProcedureName' 
			AND user_name(uid) = 'dbo')
	DROP PROCEDURE dbo.ProcedureName

Create Procedure ProcedureName

--Define the parameters we will pass to the procedure.
@BeginDate Date,
@EndDate Date

Open in new window

Pick a procedure name that won't ever be used by a developer, or updates to the application will wipe out your procedure.  Also, remember to grant execute rights on the procedure to the appropriate report user(s) at the end you script that creates the procedure.
ValentinoVBI ConsultantCommented:
If you're new to using stored procedures in your reports, check out the following articles:


On the topic of using multiple datasets in the same data region, as has been pointed out that is not possible.  If possible, try to add your datasets together in one query somehow, possible by using UNION ALL.  If that's not possible and if you've got SQL Server 2008 R2, a possible alternative is using the lookup functions.

See here for more info on those lookup functions: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_3433-Looking-Up-Data-On-Different-Sources.html
HSI_guelphAuthor Commented:
Thank you all for your replies!  I have a working report people can use while I learn more about stored procedures.

TYVM for the links!  I've previously written scripts but haven't created the stored procedures myself and we do have SQL Server 2008 R2 on the report server so I will look into the lookup functions to see if they will help.

TY all so much for your time and input!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now