November 29, 2011 at 7:59 am
Hello all,
I'm trying to set up a parameter for one dataset from a different dataset.
So, I have the field 'communities' in both datasets, but the two datasets obviously contain different data.
Dataset1 contains budget information, while Dataset2 contains population figures.
I'm trying to create a parameter in Dataset1 using the data in Dataset2.
For example, "I only wanna see budget information for the communities that have a population between 5,000 and 10,000."
Does anyone know how to link the two datasets or what the SQL would look like in Dataset1?
Thanks
November 29, 2011 at 9:10 am
It sounds like all you need is to join two tables. It is hard to say because there are no details in your post. When you say Dataset do you mean something like .net DataSet or something different? If you can explain more clearly what you are trying to do I will be happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 9:30 am
Im working in MS Visual Studio 2005 so I believe I mean .net Datasets. And yes, all I think I need to do is join the two tables. I'm just not sure how that works. Any advice would be very helpful.
November 29, 2011 at 9:34 am
Well you have a couple choices. The first would be to write a query that joins the two tables (that would be my suggestion). However since you are in .Net you can filter your DataTables with the Filter method.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 9:44 am
Maybe this screenshot will help better ID my problem. You can see my two datasets to the left "mike_PRD" and "Population." Does this help provide you with a clearer view to my problem, and hoepfully a clearer solution?
Mike
November 29, 2011 at 9:47 am
Yes it does. You need to create a single query as a new dataset that contains the information you want.
--edit. I didn't notice until this last post that this was under Reporting Services. DOH!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 9:59 am
So in other words, I have to combine the two existing queries into one query?
Please forgive me because I'm definitely a rookie when is comes to SSRS, but there isn't a way to create a parameter for "mike_PRD" based off the data in the "Population" dataset without creating a new query? It seems redundant that I would need to add a new dataset since I already have both under one Report Dataset.
The reason I'm asking is because in addition to Population, I'll have several other parameters I want to add in. And from what I believe you're telling me, I'll need to create one gigantic dataset, then create new datasets for all the other parameters I need. Does that sound right to you?
November 30, 2011 at 6:20 pm
Create one dataset for your report details, join the Population table. (can't see what exactly to join to on the screen shot). This dataset should be filtered by the parameters you want to add. I can't see all of your From, so you'd either want to use an Inner Join for the Population table on the Population.jurisdictioncode and Population between @BeginPop and @EndPop, or use an outer join and apply the filter in the Where clause.
fyi - when you refresh the dataset fields, the parameters will be added to the RDL.
Create a 2nd dataset just for the population parameters if the population counts need to be generated dynamically. A static parameter list might serve you well here if you have set ranges to work with. if you need it to be dynamically drawn from the actual population data, then something like this in the data set:
select distinct population
from population
order by population
edit the parameters, either add the static values to the available values tab, or point to the dataset. Population would be both the label and value.
when you generate the report, you will select the beginning pop and the end pop, the values will be passed through to your mike_prd dataset.
repeat for other parameters. if it makes you feel better, i have many reports that have over a dozen datasets for parameter values. i try to put parameter value queries into stored procedures whenever possible, so they are reusable and easier to maintain.
basically, let sql server do the heavy lifting. all of the data that you need to show on your report in a single data region should be in one query/stored proc, including whichever columns will be used for filtering the data. you can't use SSRS to join disparate data sets into one, but you can use additional data sets for creating parameter selection lists.
hopefully this makes sense. i'm a bit tired.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply