I had a requirement to display the number of orders each state is making in a specific time period. This report should show only Top 5 states plus one record for all orders from rest of states.
Though there are several ways of doing this, but I did using combined with similar request.
1) Create a request which is having states and orders count. Apply filter on "Orders count" where rank(orders) is greater than 5. Save the request under shared folders in some name.
2) Create another request with necessary columns needed and apply filter on "Orders count" such that it will display only the Top 5 states. Add one dummy column to the report and hard code value ‘1’. I will use this dummy column for sorting purpose later
3) Now select combined with similar request option, create the request like as earlier mentioned and apply filter on state, Using Advanced -> "filter based on result of another request" and point to the first request (item 1) using the Browse. Add one blank column to the report and hard code value ‘2’ for sorting purpose and edit column formula of reporting state as follows;
case when CUSTOMER.STATE = 'X' then 'Y' Else 'OTH' end
(Make sure you really don't have a valid state_cde as 'X' now or in future, otherwise results may go wrong)
4) Sort the report using the dummy column
5) Finally hide the dummy column using column properties -> Column format -> Hide
Though there are several ways of doing this, but I did using combined with similar request.
1) Create a request which is having states and orders count. Apply filter on "Orders count" where rank(orders) is greater than 5. Save the request under shared folders in some name.
2) Create another request with necessary columns needed and apply filter on "Orders count" such that it will display only the Top 5 states. Add one dummy column to the report and hard code value ‘1’. I will use this dummy column for sorting purpose later
3) Now select combined with similar request option, create the request like as earlier mentioned and apply filter on state, Using Advanced -> "filter based on result of another request" and point to the first request (item 1) using the Browse. Add one blank column to the report and hard code value ‘2’ for sorting purpose and edit column formula of reporting state as follows;
case when CUSTOMER.STATE = 'X' then 'Y' Else 'OTH' end
(Make sure you really don't have a valid state_cde as 'X' now or in future, otherwise results may go wrong)
4) Sort the report using the dummy column
5) Finally hide the dummy column using column properties -> Column format -> Hide
No comments:
Post a Comment