All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Monday, May 17, 2010

Filter based on the repetition of records in the column

Recently, I just came across a requirement that I think is worth blogging for beginner who's trying to manipulate the report data based on the the repetition of the data. Take a look at the following report:

You see, not every order number has only one corresponding document number. So how can I filter this report so that it only gives me back those order numbers that have more than 1 document number? Because there is no out of the box filter operator that allows us to easily create a filter based on such condition, we will have to work around it.

Let's first start by creating a report that counts the occurrence of every order number. So bring in a new column and call it 'count'. In the column formula, let's enter a formula: sum(count (distinct "Facts Info"."Order Number") by "Facts Info"."Order Number")

What this formula does is that it will count every order number's occurrence as 1 and them sum all the 1s by each order number. So the goal is that for those order numbers that have let's say 2 document numbers, these order numbers will repeat twice and there corresponding counts will be 2 in both lines after the 'Sum' function. So see the report below:

Now the hardest part is over. For the final report, we can then create a filter on column count. Now depending on what you want to see, you can get the order numbers that have 2 document numbers, 3 document numbers, less than 5 or however you want. For the purpose of this demonstration, I will just set this filter to be greater than 1, which means it will bring back all the order numbers that have more than 1 document numbers:

Run the report and here is the final result:

Of course, we can always hide the column count so that others don't see it in the report

Next time.


Related Posts Plugin for WordPress, Blogger...