Showing results for 
Search instead for 
Did you mean: 
Occasional Member - Level 3

Report Studio - Error using date filter expression with 'maximum'

We've got a custom report out of Expense that uses a filter that looks like this '[Created Date] between 2021-08-01 00:00:00.000 and Maximum' and which in the report does exactly what it looks like.  If you double click on the filter in query studio it opens as a 'Filter condition' rather than an expression, and you get to pick a range/individual dates with a prompt screen.


As that was doing exactly what I needed for a new report I replicated the coding but with a different date field:

[Sent for Payment Date] between 'Aug 1, 2022' and 'Maximum' 

The validate button thinks this is perfectly fine, but if I run the report it errors.  If I change the 'maximum' to any date such as 'Dec 25, 2050' it works fine.

It also shows as being a 'detail filter expression' when double clicked into.


Does anyone know what is wrong with it, and how do I change this filter expression into a condition with a selectable date range?  Nothing I've tried brings that screen up.


We are running Cognos 11.1.7



Community Manager
Community Manager

@DavidUOP open up the list of queries you have created and find the one with Created Date, which I believe is the one named EmpExpense. Your screen will look like this:


Double-click the filter on the right under Detail Filters. See screen below. Click the Settings icon (highlighted). 



You will see an option to turn the filter into a prompt which will allow you to select your date range. 



Give that a try and let me know if it works for you. 

Thank you,
Kevin Dorsey
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
Occasional Member - Level 3



It's already working on the reports we have that are using 'created date', which is how I know the option to display the filter in the UI as a range exists.

The problem is having that same option appear when using a different field, which in this case is the 'Sent for Payment Date' field.


This is what it looks like when I double click into the filter:

filter query.JPG


It doesn't need to have a prompt as it's a scheduled report that goes out every 2 weeks and contains data from 01/08/2022 to the date it's run.

Using the range filter that we have showing on our reports (and as seen in your screenshot) it's easy to just set it to have a start from date and then select 'latest date', but at present we only see to have that on reports using created date.  Not having that condition screen with the 'range/individual' etc option screen pop up means I have to just use a date far in the future to get the same result when creating a date range filter using sent for payment.