| Author |
Message |
miamitourism
Newbie
Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
|
  
Topic: Can I use formulas in Parameters?
Posted: 28 Jan 2014 at 3:58am |
|
Hi Forum!
Is it possible to use a formula in a parameter? Here's my issue:
I need to create a parameter where users can select multiple values for areas of town that they would like to include in my report. For example, they want to be able to choose areas like "Airport", "Downtown" and "Beaches". What they don't know is that those areas are all made up of smaller municipalities that all need to be included in their respective areas. Those municipalities are stored in a field called "cli_clients.ip_region".
How can I define which municipalities are included in each of the Area names, and use those in the parameter? I thought I would be able to create formulas for each area, but I can't seem to pull them into the parameter.
If anyone has any ideas, let's use the "Beaches" area as an example. Municipalities (ip_region) included in this area would be "Miami Beach Art Deco", "Miami Beach", "Sunny Isles", and "Surfside".
I would appreciate any insight :)
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Jan 2014 at 4:52am |
|
if there was a table that related say Beaches to Municipalities then you could use cascading parameters...though depending on the report, that would probably limit you to you one type of area per report run.
If you want the user to zero in on a few different areas for beaches, cascading parameters is probably your best bet. If you just want to show all the beaches but separate them by the area, the reference table would help you group them...
HTH
|
| IP Logged |
|
miamitourism
Newbie
Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
|
Posted: 28 Jan 2014 at 6:37am |
|
Yes, I had thought of Cascading Parameters too, but you're right in that I don't have a table to relate municipalities to areas.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Jan 2014 at 7:07am |
|
You might be able to create a pseudo reference table using a command. Something along the lines of:
select 'Beaches', ip_region from table where ip_region in ('Miami Beach','Sunny Isles')
union
select 'Somthing else', ip_region from table where ip_region in ('','') etc
then you could link the command table to your regular table for cascading parameters or for filtering.
HTH
|
| IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 29 Jan 2014 at 6:04am |
|
Assuming your list of potential places isn't huge, I think this would work.
Create a formula for each potential place, so first a Beaches formula
ip_region in ["Miami Beach Art Deco", "Miami Beach", "Sunny Isles", "Surfside"]
Create your parameter, PlaceSelect
Make it a string
Under values, type out the names of the places you identify in your formulas, one of which will be Beaches
In your selection criteria for the report, use an if/then/else statment
if {?PlaceSelect}="Beaches" then {@Beaches} else
if {?PlaceSelect}="Airport" then {@Airport} else
And so on. This would be pretty cumbersome if you have an exhaustive list of places though.
|
| IP Logged |
|
|
|
