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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Resume 5 46
How to lock a table waiting for a store procedure is executed 21 38
denied execute as 13 30
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 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