How to allow that a filter based on a Simple calculated field displays a values list?

Joan Poblet shared this question 3 years ago
Answered

Dear all,


We have created a Simple calculated field in our report doing something like this:


CASE

WHEN Productquantity =0 THEN 'EMPTY'

WHEN Productquantity >0 and Productquantity<=10 THEN 'SMALL'

WHEN Productquantity >10 and Productquantity<=100 THEN 'MEDIUM'

ELSE 'BIG'

END


And we added this field as a filter with Entry style "List selection". The problem is that the values list displayed in the output is always empty and we don't know how to populate it.


Could you please help us?


Thank you very much.


Best Regards,

Joan.

Comments (1)

photo
2

Hi Joan,


As cache filter values are pulled directly from the database, it will be necessary to create a filter from a custom query to achieve this. In the example below I have aggregated my amount by my Group field, and then created a calculated field that returns "good" or "bad" depending on if the value is larger than 163,500.


955ed23dd16c19a7f9e20c15719375d0


As the values for good or bad are dependent on the aggregation of my Group field it was necessary to account for this in the calculated filter. Here is the report's SQL statement.


56faa002c62a74f0d44e6895ecd9a8bb


To create the custom query, I simply removed everything within the SELECT statement, except for the calculated field values:


1d3ecb4125b587bac9d20c478007874c


This returns 2 value ("Good" and "Bad") which I can now filter on:

2c703bf1b6f1445cc6335d348a724926


Please let me know if this is relevant or if you have any trouble with implementation.


Regards,

Nathan

photo
1

Hi Nathan,


Thank you for your detailed explanation. I figured out that the right way was "Custom query" but I didn't know how to use it.


Once we will check it, I'll let you know.


Thank you again.


Best Regards,

Joan.

photo
2

Hi Joan,


Sounds good let me know if there is anything I can do to help in the meantime. As a second thought, including the group by clause should not be necessary as you are simply caching values, rather than their relation to your current data.


Regards,

Nathan

photo
1

Hi Nathan,


We checked it and it worked! Many thanks for your help. You can close the ticket. :)


Best Regards,

Joan.

photo
1

Hi Joan,


Glad to hear it! Let me know if you run into any problems and the case will be re-opened.


Regards,

Nathan


Closed Request Survey

photo