Hi, having trouble getting a detailed filter to work and need assistance with the syntax.
The filter is on the Period dimension, using a multi select prompt. We have 13 periods in the fiscal, but the database has a 14th period (for adjustments - it is P998). If the user selects P13 on the prompt, I'd like the report to bring back P13 and P998.
For reference, the following works as desired if only P13 is selected, but fails when other periods are also selected (note, even if this ran for multi selected values, I understand it would not return everything we need it to, but just trying to give an idea of what is happening):
IF (?FiscalPeriod? = 13) THEN
([Business View].[Finance Details].[Accounting Period] in ( 13, 998))
ELSE
([Business View].[Finance Details].[Accounting Period] in (?FiscalPeriod?))
The formula need to represet this:
If P13 is one of the periods selected in the prompt, use all periods selected, and also P998.
I have tried various forms of IN, but must be missing something.
Thanks in advance
I ran across this before and I recall having to use extra parens somewhere.... Try to wrap each portion of the IF in a 2nd set of () then also/or wrap the whole thing in (), so like:
(IF ((?FiscalPeriod? = 13)) THEN
(([Business View].[Finance Details].[Accounting Period] in ( 13, 998)))
ELSE
(([Business View].[Finance Details].[Accounting Period] in (?FiscalPeriod?))))
Play with either/both of these and I think you will get it. You may also consider using a CASE statement instead of IF, but similar extra parens may be needed
Thanks for the comment, I will play around with that, but wanted to note that the IF statement I posted will not produce the result desired, it is an example to assist understanding of what I am after.
The posted formula works where only one item is selected in the prompt, it fails when more than one item is selected.
Thanks
A filter is happiest when it simply evaluates to a true or false condition. I don't use conditional "case" or "if" statements in filters.
Try changing it so that it evaluates as intended without the "if"
( ?FiscalPeriod? = 13 and [Business View].[Finance Details].[Accounting Period] in ( 13, 998) )
OR
( [Business View].[Finance Details].[Accounting Period] in (?FiscalPeriod?) )
I am not sure how I can limit the question to a true/false approach if the business rule is as noted above:
"The formula need to represet this:
If P13 is one of the periods selected in the prompt, use all periods selected, and also P998."
Cheers
Perhaps:
( 13 in (?FiscalPeriod?) and [Business View].[Finance Details].[Accounting Period] in ( ?FiscalPeriod?, 998) )
OR
( [Business View].[Finance Details].[Accounting Period] in (?FiscalPeriod?) )
Hi Lynn,
Looks like that did the trick. Still testing it, but it is bringing back expected results now. Thanks very much for your assistance.
Cheers