Multiple Reports for a list of Parameters

Question

Multiple Reports for a list of Parameters

rmurraymmi

Mr or Mrs. 500

Points: 541
More actions
July 23, 2008 at 2:06 pm

#189991

I'm just getting started with Report Services. I have build an (Employee) report which includes data from a dozen tables, and it seems to work fine for an individual employee. However, I would like to give it a list of Employee Ids, or the result of a query, to generate multiple reports. How do I go about doing this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Are you using SSRS 2000 or 2005? In 2005 you can create a Multi-Value drop-down list parameter that passes a commas delimited list. In your Report you would set a group (it can be the detail group) so it starts a new page at the end of each employee report.

With more detail about the report we can give a more detailed answer.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rmurraymmi Mr or Mrs. 500 Points: 541 More actions · Answer 2

I am using SSRS 2005.

The report is simply a list of all of the data for a specified employee (Id) collected from about a dozen tables.

Ideally I would like to run a query resulting in a list of Employee Ids and then present that list to the user so that they can choose which report(s) to run. The results of the Id queries could be stored in a table.

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

That is what the multivalued parameter is for. You need 2 datasets in your report. Dataset 1 will return a list of employee names and ids and dataset 2 will return your employee information. Then you create a multivalue parameter using Dataset 1 as the source for the data setting the employee name as the label and employee id as the value. SSRS will automatically add a select all selection and the report user can select however many employees as they want.

If you are using a stored procedure to return the data in dataset 2 then you will need to parse the delimited list of id's. If you are using an ad-hoc query in the report then SSRS will do that for you.

Attached is an example report using the AdventureWorks database.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rmurraymmi Mr or Mrs. 500 Points: 541 More actions · Answer 4

OK that works great. I now get a dropdown list of the employee Ids (which I had put into a table).

Now I want to get a copy of the report for each employee that is selected from the dropdown.

Without changing anything, if I select two employees I only get one report for the first one selected.

I tried putting the entire report into a list. Now I get two reports, but they are both for the first employee.

What's the best way to do this?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Can you attach the rdl file to the thread so I can see how you are trying to do the report? Are you sure your report data source is returning more than 1 employee's information?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rmurraymmi Mr or Mrs. 500 Points: 541 More actions · Answer 6

The SampleIds table contains employee Ids (EmpId) and labels. this is what shows up in the dropdown. There are currently two records in the table. The dataset query is: SELECT * FROM SampleIds.

The other datasets are of the form: SELECT * FROM Prod_Master WHERE EmpId IN (SELECT EmpId FROM SampleIds)

When I run the report with both employees selected I get two copies of the same report. Interestingly the header has the EmpId from one employee and the name from the other.

The rdl is attached.

rmurraymmi Mr or Mrs. 500 Points: 541 More actions · Answer 7

Before you spend too much time on my last post...

I now see that the dataset queries should reference the dropdown list, e.g. @EmployeeIdList in your demo.

I also just had the thought my problem might be that I created a dataset for each table, and my List region can only be associated with one dataset. What I probably should be doing is joining the fields from all the tables into one dataset, and then refer my list to it.

However I'm still not sure how I'll handle the tables that return multiple rows for each employee. I guess I can create a separate dataset for them, since they will be displayed in tables (inside of the list) which can have their own dataset reference.

That's the plan for now.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Sounds like you may be headed the right direction. Glad I could offer a little help.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rmurraymmi Mr or Mrs. 500 Points: 541 More actions · Answer 9

Well, I still haven't got it to work. Here's a recap:

I want to generate employee information reports, with one report for each employee.

I set up a multi-value parameter (SampleIdsList) which uses the SampleIds dataset, which returns EmployeeIds and a label.

I created a 'Master' dataset which joins a number of tables and results in one record per employee based on @SampleIdsList.

I created a List region which specifies the Master dataset and references fields from the Master dataset.

Up to this point everything works fine. If I select multiple employees from the dropdown, I get a report for each one.

My problem is that there are two other tables which yield more than one row per employee. e.g. Employee Stock Purchases (ESPP). I created two other datasets to select the records for an employee from these two tables. I thought that I would be able to put a Table region inside of the List region and have it link to the ESPP dataset. However, even though I set the table's 'Dataset Name' property to the ESPP dataset, it still seems to be linked to the Master dataset, the same as the List. If I go into Edit Expression for one of the cells it shows 'Fields(Master)' as the dataset that I can use values from, and only allows me to use aggregate functions (First/Sum) with the ESPP dataset.

How can I incorporate multiple rows of data from these other two tables into my report?

Note: rdl attached