Filter on Case Statement Output

[gats1527]

We have the following case statement "ApproverCheck"

CASE
WHEN [Step] in ('Concur Audit Service100%', 'Concur Audit Service 100%')
THEN
   CASE
   WHEN [Role] = 'Expense Processor(Expense Report Auditing)'
   THEN 'CHECK'
   ELSE 'OK'
   END
ELSE '0'
END

Which we then want to filter the report to only displays those records that match the CHECK criteria as noted above but when we set the filter to be ApproverCheck = 'CHECK' but the report displays no records at all.  Thoughts on what would be causing that?
Tom

[MFGF]

We have the following case statement "ApproverCheck"

CASE
WHEN [Step] in ('Concur Audit Service100%', 'Concur Audit Service 100%')
THEN
   CASE
   WHEN [Role] = 'Expense Processor(Expense Report Auditing)'
   THEN 'CHECK'
   ELSE 'OK'
   END
ELSE '0'
END

Which we then want to filter the report to only displays those records that match the CHECK criteria as noted above but when we set the filter to be ApproverCheck = 'CHECK' but the report displays no records at all.  Thoughts on what would be causing that?
Tom

Hi,

The first thought that comes to mind is that there may be no rows of data matching the criteria you are looking for? If you use the ApproverCheck item in a list object in an unfiltered report, do you see any rows displaying 'CHECK'?

Secondly, what results do you get if you define a filter with the syntax

[Step] in ('Concur Audit Service100%', 'Concur Audit Service 100%') and [Role] = 'Expense Processor(Expense Report Auditing)'

MF.

[gats1527]

Yes when i run the report unfiltered for a particlar time frame there are results.  see attachement.

Also i have added your suggested filter with no results appearing

[gats1527]

We have the following case statement "ApproverCheck"

CASE
WHEN [Step] in ('Concur Audit Service100%', 'Concur Audit Service 100%')
THEN
   CASE
   WHEN [Role] = 'Expense Processor(Expense Report Auditing)'
   THEN 'CHECK'
   ELSE 'OK'
   END
ELSE '0'
END

Which we then want to filter the report to only displays those records that match the CHECK criteria as noted above but when we set the filter to be ApproverCheck = 'CHECK' but the report displays no records at all.  Thoughts on what would be causing that?
Tom

[BigChris]

This could be a complete red herring, but it looks like you've got grouping on your report. Is your filter being applied before or after aggregation?

[gats1527]

i have tried the filter grouping both ways where before aggregration returns no results but after, runs for a very long time with no output or i havent waited that long for it to try and return results.  i have let it sit for 5 minutes

[Lynn]

Have you tried filtering the two portions independently to isolate if one or the other might be the problem?

The statement that MFGF provided should be all you need instead of the case statement.

[hespora]

I'm being a complete nob again, but: Please edit that screenshot. There seem to be real names in there.

[gats1527]

when i remove the case statement and just have the filter as noted by MFGF, the results are still not showing

[Lynn]

Have you tried filtering the two portions independently to isolate if one or the other might be the problem?