SSRS using multiple datasets

Posted on 2012-03-26
Medium Priority
Last Modified: 2012-03-27
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?
Question by:HSI_guelph

Assisted Solution

rtay earned 400 total points
ID: 37766558
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.

Author Comment

ID: 37766618
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?

Assisted Solution

by:Lynn Huff
Lynn Huff earned 800 total points
ID: 37766621
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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

ID: 37766646
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.

Assisted Solution

by:Lynn Huff
Lynn Huff earned 800 total points
ID: 37766727
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.
LVL 37

Accepted Solution

ValentinoV earned 800 total points
ID: 37770015
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

Author Closing Comment

ID: 37771829
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!!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question