November 22, 2011 at 7:02 am
What is up my SQL friends?! Quick question and I hope it's an easy one....
I'm just starting out with SSRS and I've run into my first roadblock. I'm trying to set up a parameter that kicks out data for a parameter range.
For example, I have several fields in my table including "Community Name" and "Population." I'm trying to set up a parameter that kicks out out data for communities with population size "less than 1,000", "greater than 1000 but less than 2000", and so on and so forth.
Anyone know how to set up a parameter like that? The internet hasn't been tremendously helpful.
Thanks
Mike
November 22, 2011 at 7:03 am
Put 2 parameters.
1 named more than []
1 named less than []
Use that rather than reverse engineering text in the report's query.
Both of then nullable of course.
November 22, 2011 at 7:48 am
Hmmm, I'm not sure I follow. I have a total of 11 ranges I need to set parameters for. I would like to see them all under the same prompt "Population Size" I set up in Report Parameters using multi-values. Am I setting up the parameter in the correct location?
November 22, 2011 at 7:52 am
That would work too.
Just have to list them manually but for 11 that's fine.
November 22, 2011 at 8:06 am
Ok, thanks for your help. I already ahve two additional parameters on this report, and I'm almost positive I have them set up in the correct location. I should be setting up the Population one in the same spot, correct?
November 22, 2011 at 8:10 am
I just don't understand what you're getting at and where you're getting blocked.
Maybe a screenshot would help.
November 22, 2011 at 8:17 am
Sorry, I'm a bit confused myself. I could paste into the text box so I included a screenshot on a word doc. Hopefully it helps.
The screenshot shows where I set up one of my other parameters, not the population one. The parameter in the screenshot is running off a query.
November 22, 2011 at 8:18 am
jpg please.
People are usually frisky about opening a vba activated file made by a programmer 😉
November 22, 2011 at 8:27 am
I wouldnt call myself a programmer just yet haha.
November 22, 2011 at 8:29 am
Maybe you should read those and then come back if you need more help (diy basic ssrs tutorials).
November 22, 2011 at 9:59 am
yes, yes very useful. Thanks for the links. Rather than set ranges for each parameter, I simply used a BETWEEN clause and I'm allowing the user to define their own range.
Woooo woooo!
Lat question and then I'll stop bothering you. If the user elects NOT to use the parameter choice, how can I "turn off" the parameter option?
For example, from the .jpg I sent out earlier, there are several checkboxes in the Report Parameters dialog box including "allow null value" and "allow blank value." I thought checking one of these might do the trick, no luck though.
Thoughts?
November 22, 2011 at 10:25 am
Yes allow null with an or in the where clause for each parameter.
Or not allow null and set impossible ranges (like -1 to 100 000 miles).
November 22, 2011 at 10:35 am
Here's the WHERE clause:
WHERE (A_6.FISCAL_YEAR IN (:Year)) AND (A_6.NAME IN (:Community)) AND (F."Population" BETWEEN (:MinimumPopulation) AND (:MaximumPopulation))
Forget about the :Year and :Community clauses, I'm focused on the Min and Max Population.
Where would you include allowing NULLs in the clause?
Please keep in mind I'm working out of an Orcale db, that's why you're seeing : instead of @ for each parameter name.
November 22, 2011 at 10:41 am
That sums it up pretty good
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
November 22, 2011 at 11:14 am
Awesome, worked like a charm. I ened up switching the WHERE clause to read;
WHERE (A_6.FISCAL_YEAR IN (:Year)) AND (A_6.NAME IN (:Community)) AND ((F."Population" BETWEEN (:MinimumPopulation) AND (:MaximumPopulation)) OR ((:MinimumPopulation IS NULL) AND (:MaximumPopulation IS NULL)))
I also had to set my report parameters to Allow Blank Values. The great part is that the user won't see the NULL checkboxes in the report (not sure why though), so they can either enter a Population Range or not. Hopefully this is a bit more intuitive.
Anywho, thanks for taking the time to show me around today! Much appreciated.
Yeeeeeaaaaa buddy!
Mike
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply