[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
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 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.
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!


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

650 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