SQL Reporting Services: DataSets and Parameters

Emi975
Emi975 used Ask the Experts™
on
Hey Gang,
I have not made too many reports so bear with me. What I am trying to do is have the parameters of a dataset select the information for another table.  Example:
DataSet1= 55 calendars to choose from.
DataSet2= 69026 course requests to choose from.
So what my report will do is only show the 55 calendars that are available from the parameters. From that selection, if I choose calendar 54, the records should show all course requests from calendar 54. The problem is, it is only showing one record. How do I connect the two datasets so that the parameters from dataset 1 pull all calendar 54’s entries when selected from the parameters drop down list.

Dataset1.JPG
DataSet2.JPG
Preview.JPG
1-Result.JPG
Parameters.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi

It looks like you did everything almost right.  The only thing that you want to change is instead of using INNER Joins you want to use Left Outer Joins.  That should give you all the records.  

Just right click on the little box symbol in your linking between tables and make sure that just make that the select all records from Request is the only option checked.  Make sure you checked Identity as well as Course.

Hope that works out!

DG

Author

Commented:
Ok, I tried the left outer join and still only got one record. Any suggestions?
Left-Join.JPG
Chris LuttrellSenior Database Architect
Commented:
1. Start with the parameter.  I would rename it to something more descriptive like SelectedCalendarID instead of the default Report_Parameter_0.
2. Change the Value Field to CalendarID.
3. Go to your query and in the Filter column put @SelectedCalendarID.

see if that makes it work.
Chris LuttrellSenior Database Architect

Commented:
sorry, in the Filter column put that on the calendarID row.
Commented:
I think both of the suggestions helped, but I discovered that when I was posting the fields to layout view, I didn't use a table to show all of the records. I appreciate everyone's help.
Must-Add-Table-For-Fields.JPG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial