Filtering one dataset based on another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Filtering one dataset based on another

Author

Topic

kj27
Starting Member

5 Posts

Posted - 2009-09-23 : 12:27:01

Hi Folks,

I've set up two datasets in SSRS 2005. One dataset requires filtering based on two parameters passed from the other dataset. So I've set up two Internal report parameters. Both Default and Available values are set to From Query:

Name: ercv_dc_name
Value and Label Fields: Distribution_Center

Name: ercv_cust_num
Value and Label Fields: Customer__

The parameter values are getting filled correctly, because I can display the values using a textbox (i.e. =Parameters!ercv_dc_name.Value). But I have this in the query for my SELECT statement for the second dataset:

...WHERE (DC.DIST_CTR_NAME = '@Distribution_Center') AND (CL.DIST_CUST_NUM = '@Customer__')

The data from this dataset never gets output. If I hardcode the specific Distribution Center name and Customer # values in that same query, the dataset returns values.

Could someone provide a pointer?

Thanks in advance.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 13:28:21

How are the parameters being selected? From a drop down on report viewer? I think it is because of the defaults believe it or not. Try setting default to null. I have a working version where (to keep real simple) there is a table of TeamLeader, TeamMembers.

User selects first the TeamLeader Parameter which is normal
Select Distinct TeamLeader
From Teams
Order By TeamLeader

Second dataset is
Select TeamMember
From Teams
Where TeamLeader = @TeamLeader
Order By TeamMember

When I did this example I recall NOT being able to have a default because before the TeamLeader selection, the TeamMember was greyed out.

I hope this at least points you in the right direction.

John

"The smoke monster is just the Others doing barbecue"

kj27
Starting Member

5 Posts

Posted - 2009-09-23 : 13:44:34

Hi John,

Thanks for the pointers. One question I had was after you configured your report parameters, did you do anything in your datasources (to map them)?

In my case, the parameters are purely internal. Basically, the first dataset has this query:

SELECT DIST_NAME, DC_NAME, CUST_NUM, SUM(FCADMIN.FC_INVOICE_HEADER.INVOICE_TOTAL)
FROM ...
GROUP BY...
HAVING...

Now I display all these fields, but in the second dataset, I want to select some add'l fields matching DC_NAME, CUST_NUM from the first dataset.

By the way, I did set the parameters to be Multi-Value now.

Thanks

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 13:51:36

I gave up trying to get multi-value to work as well as defaults because we wanted to force the users to make the selection...
In order to keep simple I omitted that my queries are in Stored Procedures. The SQL I posted before does not change but in creating the datasets:
Dataset 1:
exec AAAsp_Get_TeamLeader
(only returns selected TeamLeader)

Dataset 2:
Exec AAAsp_Get_Team_Members @TeamLeader
so yes, I do explicitly use one parameter to return the second. That may be the connection?

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 13:59:17

Actually the first dataset is a single selection but the second dataset does allow multi-selection. I couldn't figure out how to pass the array as a parameter to the second stored procedure

John

"The smoke monster is just the Others doing barbecue"

kj27
Starting Member

5 Posts

Posted - 2009-09-23 : 14:27:29

Hi John,

I wish we could create stored procs, but we can only read from the databases (as in there are two sources of info. hence the two datasets). I tried to follow this:

http://www.duttonsoftware.com/2008/09/25/easy-multi-value-parameters-in-sql-server-reporting-services/

in order to apply a table level filter. But the kicker is that the fields from the seond dataset don't even show up in the Fields drop-down so that I can apply a filter. I just can't imagine why this is designed so poorly. It's like they've made it completely impossible to try to do any joins between two datasets for reporting purposes.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 14:53:06

Interesting. I have always assigned my parameters in the SQL as apposed to doing in table properties but it looks like you did the same in your example. Is the dataset1 query simple enough to post here? I just copied my report to test and changed the stored procedures to SQL and it works the same.

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 14:56:40

I just noticed something. In your example you reference "=Parameters!ercv_dc_name.Value"

But your SQL snippet has parameters labeled
@Distribution_Center and @Customer__

I would have thought your parameter name would be
@ercv_dc_name

John

"The smoke monster is just the Others doing barbecue"

kj27
Starting Member

5 Posts

Posted - 2009-09-23 : 15:14:32

Hey John,

I think I just solved it. Basically, I ditched the table level filter. But still passing in the multi-value arrays as internal parameters, I set a dataset filter and broke up the array like this:

"'" & Join(Parameters!Distribution_Center.Value(), "','") & "'"

This was you just say: Column_Name IN()...and the expression above. It dynamically parses the array and gets you the matching values.

Let me know if you have any questions - I'd love to clarify it.

kj27
Starting Member

5 Posts

Posted - 2009-09-23 : 15:16:09

BTW, you were right about the parameter names. I changed them a little while back (i.e. @DistributorID). Thanks for the catch.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 15:55:57

Neat trick! Glad you were able to solve. I use the Join function when I display multi-selection on report and didn't think to use in SQL.

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources