Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using If Then Else With Parameters In Filters 2

Status
Not open for further replies.

Nuffsaid

Technical User
Aug 3, 2001
374
CA
Hi All,

Using Reportnet and I have a report where I need to prompt the user for a date range and a time zone, then filter based on the responses. The dates in the model are stored in GMT and we created calculations to accomodate various other zones i.e.EST,CST, etc.

I've got 2 problems;

How do I set up a "static" parameter for the time zone options as these values are not stored in the database. Then how do I pass the users selection to the filter?

This is what I have in my filter now but something is way wrong.

if (?Time_Zone?='East')
Then([DWH Production Rogers].[Eastern Standard Time]between ?start_date? and ?end_date?)
else
if (?Time_Zone?='Central')
Then([DWH Production Rogers].[Central Standard Time]between ?start_date? and ?end_date?)
else
.
.
.
ETC

Many Thanks



Nuffsaid.
 
I just had this issue yesterday.

((?Time_Zone?='East') and ([DWH Production Rogers].[Eastern Standard Time]between ?start_date? and ?end_date?))

or

((?Time_Zone?='Central') and
([DWH Production Rogers].[Central Standard Time]between ?start_date? and ?end_date?))

or ...

The thing to keep in mind is that the filter acts like a where clause in SQL, and yes it would be nice that they would document this or provide some examples in thier help pages.
 
this is also working:

( CASE ?Time_Zone?
WHEN 'East' THEN [DWH Production Rogers].[Eastern Standard Time]
WHEN 'Central' THEN [DWH Production Rogers].[Central Standard Time]
ELSE [DWH Production Rogers].[Eastern Standard Time]
END ) between ?start_date? and ?end_date?
 
Hi,

Thanks for both suggestions.

I went with goitz's approach. I was told by support that If Then Else and CASE statements were not supported in filters.

Draoued, seems you're a little more knowledgable (*)



Nuffsaid.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top