Category
Report Builder
Product Version
7.2
How to allow that a filter based on a Simple calculated field displays a values list?
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.
The same question
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.
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.
To create the custom query, I simply removed everything within the SELECT statement, except for the calculated field values:
This returns 2 value ("Good" and "Bad") which I can now filter on:
Please let me know if this is relevant or if you have any trouble with implementation.
Regards,
Nathan
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.
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.
To create the custom query, I simply removed everything within the SELECT statement, except for the calculated field values:
This returns 2 value ("Good" and "Bad") which I can now filter on:
Please let me know if this is relevant or if you have any trouble with implementation.
Regards,
Nathan
Replies have been locked on this page!