Using If Then Else With Parameters In Filters 2

[Nuffsaid]

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.

 

[goitz]

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.

 

[Draoued]

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?

 

[Nuffsaid]

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.