SSRS using multiple datasets

Posted on 2012-03-26
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 100 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 200 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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 200 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 200 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:

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

839 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