Filters don't work when adding comment fields in list report

[megcarroll]

Hi - myself and others at my company have been struggling as it relates to comment fields in list reports.  Whenever we bring in comment fields (header or entry) the filters that we have applied stop working properly.  For example, we set a filter on expense type and it works.  We then bring in the comment fields and the report output shows hundreds of rows of data with blanks in the expense type field even though there is a filter applied to that field.  this has happened with multiple reports - does someone know why/where we are going wrong?

thank you!

[PoojaKumatkar]

Hi @megcarroll ,

Can you please share your report screenshot. I want to see which all columns you have considered in your report and I will try creating similar report in our sandbox and check.

[megcarroll]

hi Pooja - yes, please see below.  and thank you.  the columns that i have filters on are "Status Upon Step Completion" and "Employee Assigned to Step".  as soon as i remove the commenter's name and comment fields from the report, the filters work just fine.  but i have those fields in the report, the "status upon step completion" and "employee assigned to step" filters don't completely work:  they still return only the values that i have set as filters but they also return thousands of blanks in these columns as well and i don't want any blanks in the columns that i have the filters on!

[PoojaKumatkar · Accepted solution]

Hi @megcarroll ,

After doing a lot of trial and error in our sandbox, I finally built something. I hope this is what you are looking for...

Based on description given above in query and your screenshot what I understood is, you are trying to build such report where you are filtering report based on employee name ("Employee Assigned to Step"), Approved/Sent back to employee/any other status ("Status Upon Step Completion") with its associated commenter's name or comments without showing "blank" values/records.

To build this report what I did is, I extracted normal report which doesn't have any filter applied to it cognos report and after that in excel I applied filter of 4 values in "Employee Assigned to Step", filter of 2 values in "Status Upon Step Completion - Approved and Sent Back to Employee) and filter of 4 values in "Commenters name" column to use it in Concur report for testing purpose and easy validation purpose. Refer below screenshot.

1. Filter on " Employee Assigned to Step"

Select column and go to filter's option >> Edit Filters >> Click on '+' botton icon >> Select "Combined" option >> OK >> Select "Employee Assigned to Step" from drop down list of create filter >> Ok >> Search and select any 4 employees one by one by copy pasting values from your excel file into filter search box as shown below >> Click Ok >> click ok >> click ok 

2. Filter on "Status Upon Step Completion"

Select column and go to filter's option >> Edit Filters >> Click on '+' botton icon >> Select "Combined" option >> OK >> Select "Status Upon Step Completion" from drop down list of create filter >> Ok >> Search and select any 2 status one by one by copy pasting values from your excel file into filter search box as shown below (Approved, Send Back to Employee)>> Click Ok >> click ok >> Select option "After auto aggregation" under Application >> click ok 

3. Filter on "Commenters Name"

Select column and go to filter's option >> Edit Filters >> Click on '+' botton icon >> Select "Combined" option >> OK >> Select "Commenter's Name" from drop down list of create filter >> Ok >> Search and select any 4 employee name one by one by copy pasting values from your excel file into filter search box as shown below >> Click Ok >> click ok >> Select option "After auto aggregation" under Application >> click ok

Note - while doing trial n error I might have used different employee name while applying filter, hence, please ignore any difference in comments column values given in my screenshot. 

This time you will get report with non-blank values.

I hope this would work for you. Please give it a try and let me know.

If this answers your query, then please mark solution as accepted.

[megcarroll]

that worked, Pooja!  thank you!!  not sure why it requires that additional filter, though - for instance, what if i didn't want to filter on the commenters name?  in this case i was fine doing so but i could see other instances where that might not be the case!

either way - it worked in this instance and i appreciate your quick response and the time and effort you put into figuring it out!  thank you, again!

[PoojaKumatkar]

Hi @megcarroll ,

For the second scenario which you mentioned about not adding commenters name in filter. There is one more advance filter option available from where we can use 'is not null' filter to exclude blank commenters rows. 

Use below navigation steps: 

To try this, you can either remove existing filter applied on 'Commenter's name' column in your existing report first and then try OR you can create completely new report and try below filter: 

3. Filter on "Commenters Name"

Select column and go to filter's option >> Edit Filters >> Click on '+' botton icon >> Select "Advanced" option >> OK >> Refer below 1st screenshot and follow step1 and step 2 >> Refer below 2st screenshot and follow step1 and step 2 >> Click Ok >> Select option "After auto aggregation" under Application >> click ok

This will give you report excluding blank commenter's name rows. 

Please give it a try and let me know if it works for you.