Author Topic: Filter on Case Statement Output  (Read 289 times)

Offline gats1527

  • Full Member
  • ***
  • Join Date: Sep 2009
  • Posts: 41
  • Forum Citizenship: +0/-0
Filter on Case Statement Output
« on: 10 Aug 2018 12:37:53 pm »
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

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,411
  • Forum Citizenship: +627/-10
  • Cognos Software Muppet
Re: Filter on Case Statement Output
« Reply #1 on: 13 Aug 2018 08:46:47 am »
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.
Meep!

Offline gats1527

  • Full Member
  • ***
  • Join Date: Sep 2009
  • Posts: 41
  • Forum Citizenship: +0/-0
Re: Filter on Case Statement Output
« Reply #2 on: 13 Aug 2018 09:44:42 am »
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
« Last Edit: 14 Aug 2018 03:49:37 am by MFGF »

Offline gats1527

  • Full Member
  • ***
  • Join Date: Sep 2009
  • Posts: 41
  • Forum Citizenship: +0/-0
Filter on Case Statement Output
« Reply #3 on: 13 Aug 2018 09:51:53 am »
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

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,117
  • Forum Citizenship: +84/-0
Re: Filter on Case Statement Output
« Reply #4 on: 13 Aug 2018 02:13:44 pm »
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?

Offline gats1527

  • Full Member
  • ***
  • Join Date: Sep 2009
  • Posts: 41
  • Forum Citizenship: +0/-0
Re: Filter on Case Statement Output
« Reply #5 on: 13 Aug 2018 08:23:22 pm »
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

Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,657
  • Forum Citizenship: +339/-1
Re: Filter on Case Statement Output
« Reply #6 on: 14 Aug 2018 02:11:36 am »
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.

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 379
  • Forum Citizenship: +22/-0
Re: Filter on Case Statement Output
« Reply #7 on: 14 Aug 2018 03:28:19 am »
I'm being a complete nob again, but: Please edit that screenshot. There seem to be real names in there.

Offline gats1527

  • Full Member
  • ***
  • Join Date: Sep 2009
  • Posts: 41
  • Forum Citizenship: +0/-0
Re: Filter on Case Statement Output
« Reply #8 on: 14 Aug 2018 08:39:47 am »
when i remove the case statement and just have the filter as noted by MFGF, the results are still not showing

Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,657
  • Forum Citizenship: +339/-1
Re: Filter on Case Statement Output
« Reply #9 on: 14 Aug 2018 08:59:23 am »
Have you tried filtering the two portions independently to isolate if one or the other might be the problem?


 


       
Twittear