August 9, 2010 at 8:12 pm
Say I have a dataset based on a reasonably complex combination of joins and where clause. If I want to populate a parameter or parameters based on select distincts of a column or columns of that dataset dataset, how do I do it? I might have a column called geogr_state. It might contain three distinct states and I want people to be able to restrict their report to one of those distinct states in the list. They don't want or need to see all the states.
At the moment the best I can think of is to create one dataset per parameter and each dataset is going to query the database with a variant of the same (complicated) query each time. To me this seems like way too much unnecessary work to be putting on the server.
I thought about sticking the results of one query in a public temp table (i.e. ##temp). It works, but it seems a bit hacky, you know, like I have to go and scrub my hands after I've done it.
If I have not explained this clearly enough please let me know.
GPO
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
August 10, 2010 at 7:18 am
If I understand the first part of your question, it is pretty straight forward. Just make the available values of a paramater based on the results of your query. You can do this in the parameter properties. I find the second paragraph a bit confusing so I am not sure if I am leading you in the right direction.
August 10, 2010 at 12:46 pm
I thought about sticking the results of one query in a public temp table (i.e. ##temp). It works, but it seems a bit hacky, you know, like I have to go and scrub my hands after I've done it.
I'd have to advise against this as what happens when more than 1 user executes your report at the same time? IF both are writing to the same global temp table you will have some interesting data coming out of the reports I'm thinking.
Seems like Dbowlin is putting you on the right track. Make a dataset(s) to drive your parameter(s) and then either pass them to a stored procedure or directly to your query.
-Luke.
August 10, 2010 at 12:47 pm
August 11, 2010 at 6:41 am
Hmm Luke, you're making me rethink something... so if I have a dataset I could potentially take the results of that dataset and pass as a Table-Valued Parameter into a different sp to make a 2nd dataset?
interesting...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 11, 2010 at 7:08 am
I suppose you could do that in 2008, obviously not in 2000 which the OP was asking about. The way I kinda do the same thing in SSRS05 would be like the following example.
I have many employees who work in one branch office, many branches in one company...
I was to see sales figures for a particular set of employees... In SSRS I have an offices parameter and an employees parameter and they are cascading. I use a dataset to drive each one. For offices it would be something like: Select OfficeName, ID from Office; The Office.ID column is the value of the Office Parameter.
For the Employees which would be a multi select parameter I'd do something like Select EmployeeID, EmployeeName from Employee WHERE Office = @office;
To get my data for my report them you need to collect your list of EmployeeIDs and send them to your stored procedure or whatever other means of requesting your data would be. Perhaps in 08 you can use a TVP with the SSRS parameters, but I've never done it as I only have 05 to play with :(. I pass the employee IDs as a ^delimited string which I parse in my stored procedures using the delimited string parsing logic from one of Jeff's Tally table Articles.
I may be way off base on what the OP was looking for, but what I just described seems to be what he was looking for.
-Luke.
August 11, 2010 at 7:36 am
Gotcha, thanks, sorry to muddy the waters in a thread about 2000, wasn't thinking...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 11, 2010 at 7:40 am
Nope, no problems there, I'm actually interested to see if SSRS 08 allows the use of TVP to pass data to and from other datasets. I think it could be an interesting solution to some issues I've seen with my current method, albeit one I won't be able to implement for a while.
-Luke.
August 11, 2010 at 11:03 am
Luke, check this out: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/62731/[/url], and I believe this would be usable in 2000 also, you'd just have to pass it as a string and convert to XMLdata, iirc.
Haven't found anything about TVP being directly implemented in SSRS 2008, but this may solve the problem anyway.
Thanks for the new knowledge!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 11, 2010 at 11:52 am
Yup, That's another valid approach, I guess I just prefer parsing a string with a CTE or Tally table to dealing with XML and custom code in the code window or a library. Seems more straightforward to me. Not that it's a bad solution, in fact it's quite ingenious, its just one I chose not to use since I'm usually only passing a list of values, not an array or set of key value pairs or whatnot.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply