May 18, 2012 at 3:40 pm
I have an SSRS 2008R2 report with cascading parameters (multi-select) where user selects one or more counties, then one or more cities, then one or more ZIPs from three sequential dropdown boxes.
A text box at bottom of report lists counties, cities and ZIPs chosen so printed report will include identification of locations included in printed data, but the list of cities and ZIPs gets very long when all are selected.
:unsure: Any way to have SSRS sense that Select All has been checked in the dropdown list? If so, how would I modify my JOIN statement (below) to print the phrase All ZIPs if Select All was checked, otherwise list individual ZIPs chosen?
=JOIN(Parameters!ZIP.Value,", ")
Thanks! Carol
May 18, 2012 at 11:35 pm
select * from
database as a
-- join your second db as usual
Inner Join database2 as b on b.fields_names = a.field_names
--- use your parameter in your where clause
where b.zipcode in (@Zip)
I hope this helps. you must use your parameter in your where clause and use in to it doesn't matter if the user only select 1 zip, multiple or select all it will do the job better using IN(Parameter).
May 18, 2012 at 11:46 pm
or you can do it this way. too
select * from
database as a
-- join your second db as usual
Inner Join database2 as b on b.fields_names = a.field_names and b.zipcode in (@Zip)
either way still works for you..:-)
May 19, 2012 at 3:02 am
chornung (5/18/2012)
I have an SSRS 2008R2 report with cascading parameters (multi-select) where user selects one or more counties, then one or more cities, then one or more ZIPs from three sequential dropdown boxes.A text box at bottom of report lists counties, cities and ZIPs chosen so printed report will include identification of locations included in printed data, but the list of cities and ZIPs gets very long when all are selected.
:unsure: Any way to have SSRS sense that Select All has been checked in the dropdown list? If so, how would I modify my JOIN statement (below) to print the phrase All ZIPs if Select All was checked, otherwise list individual ZIPs chosen?
=JOIN(Parameters!ZIP.Value,", ")Thanks! Carol
In the textbox the expression can be set to:
="Zip codes := " & iif(Count(Fields!Zip.Value,"ZipDataset") = Parameters!Zip.Count , "All Zips", join(Parameters!Zip.Value,", "))
This checks the number of available Zip codes against the selected Zip codes. This technique can be used against the cities as well.
Fitz
May 19, 2012 at 8:22 am
This is EXACTLY what I was looking for, Fitz! Kept getting error on false part of iif, but solved that by enclosing first part of iif in parens, and it worked like a charm for both ZIPs and cities. Thanks for making me look good! -Carol:-D
="Zip codes := " & iif((Count(Fields!Zip.Value,"ZipDataset")) = Parameters!Zip.Count , "All Zips", join(Parameters!Zip.Value,", "))
May 19, 2012 at 10:03 am
chornung (5/19/2012)
This is EXACTLY what I was looking for, Fitz! Kept getting error on false part of iif, but solved that by enclosing first part of iif in parens, and it worked like a charm for both ZIPs and cities. Thanks for making me look good! -Carol:-D="Zip codes := " & iif((Count(Fields!Zip.Value,"ZipDataset")) = Parameters!Zip.Count , "All Zips", join(Parameters!Zip.Value,", "))
You don't actually need the extra open and close around the count part. But it works with the extra ones.
Fitz
May 19, 2012 at 10:15 am
Strange. I was getting error pointing to false portion of iif statement, and adding those two parens eliminated the error.
Not getting error now if I delete those two added parens to test, but have since converted placeholder to HTML (was text) and added some HTML code, so not exactly comparing apples to oranges any more. Bottom line: it works, and need to plow on, so am in an "If it ain't broke..." mindset this morning.
Thanks, again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply