I was working with a client recently when I had to create a rank within Crystal Reports 2008 on the result set using a universe based query. There are obvious solutions in applying a rank in a query. I do like, that in Web Intelligence, that there are built in functions to apply a database rank. Based on my brief research on this with Crystal Reports, I found there is no real rank function. I then set out on my own to see if I could come up with another way.
The first thing I wanted to do was to determine what functions I had available to get closer to achieving the goal. I began by trying to determine what the Nth largest value in the result set was based on revenue. Island Resorts Marketing is a great universe to demonstrate this capability, so we’ll work from that example.
To begin, create a new Running Total Field by right clicking on Running Total Fields in the Field Explorer. Click New to begin.
In the Running Total Field box, give the Running Total Name field a name you can reuse later. Select the Revenue object (or other measure) and add to the Field to summarize box. Set the type of summary to “Nth largest” and set N equal to the desired number of rows you wish to see in the finished result.
With this new metric in place, the result set shows that the Nth largest Revenue value (10) is 20,330, and displays accordingly in the report detail. We’ll use this as the basis for filtering out the remaining rows.
Next, enter the Section Expert. On the Details section, edit the formula to Suppress (No Drill-Down) using the formula editor button.
In the formula, enter the following code:
{IslandResortsMarketing_query.Revenue} < {#RevenueTopN}
Click OK to proceed.
Now back at the report, note that all that is left is the top 10 rows from the report in the detail section. You can now remote the RevenueTopN field from the detail section and the filter will remain in place.
That’s it. I’d love to hear about other ways this could be applied. Your comments are welcome.