Skip to main content

There is a custom report created by me. It contains two columns as Invoiced Date and Document date. I want to filter the report by using these date fields. First priority goes to Invoiced date. If there is no Invoiced date filtration should be happened with the Document Date. And again If there is both Invoiced date and document dates filtration should be occurred by the invoiced date. Please check the below images.

Start Date, End Date parameters 

 

1st attempt by me
1st attempt result
2nd attempt method

I tried it in several ways in the Filters section of the report designer. But it did not work. In the first method, filtration occurred, but the priority was on the Document date although there was an invoiced date. In the 2nd method, an error message popped up as no Invoice date field found.

So can you please give me guidance to fulfill this requirement? Thank you. 

Try this, using the actual names and conventions in the report designer 

 

(InvoicedDate >= StartDate or

(InvoicedDate is null and

DocDate >= StartDate)) and

(InvoicedDate <= EndDate or

(InvoicedDate is null and

DocDate <= EndDate))

Thank you so much @darylbowman. It worked nicely. 

You're welcome!

Reply


Terms & ConditionsCookie settings