Rotavirus hits our House

Puke, diarrhea, high fevers.




Doc says, cold water, showers, clean up the mess and wait. No fun at all. :(

GoodData Filters: WITHOUT PARENT FILTER Clause

I have recently noticed some confusion regarding how the advanced filters work in the GoodData platform. Some of our users don't realize that all report filters automatically propagate to all report metrics and filters. Let me demonstrate this on a simple example.

I created a simple report that shows the Q1/2010 planned sales by current opportunity stages. The report contains the Closed Date Quarter attribute on top and the Stage attribute on left. There is one simple metric Amount [Sum] that aggregates the expected revenue.



The report above shows expected revenue for all regions. Lets say that we want to see only the revenue for the West region. We will get the numbers for the West by adding a simple line filter on the Region attribute.



Please note that some of the stages have disappeared. This is fine as there are no opportunities in the West region that are in the Cultivate and Qualify stages and are expected to close in the Q1/2010.

Now, we want to compute the percentage of the expected revenue in the West to the total expected revenue in all regions. We need to divide the West expected revenue by the total expected revenue in all regions. We already have the West revenue. How can we compute the total revenue for all regions in the same report? The answer is the WITHOUT PARENT FILTER clause that we can put into the metric that computes the total revenue for all regions. The clause simply ignores all report filters in the all regions metric. So the definition of the all regions total is:

Amount [All Regions] : SELECT Amount [Sum] WITHOUT PARENT FILTER



Compare the report with the WITHOUT PARENT FILTER clause with the report without the region filter (the first one in this article). You're right, these are the same. The report filter is ignored and the Cultivate and Qualify stages are back.



Now lets compute the percentage:

SELECT Amount [Sum] / Amount [All Regions]



And here is the final percentage report:



Let me quickly summarize. The GoodData platform automatically propagates all report filters are to all underlying metrics. If you need to override this behavior, you need to use the WITHOUT PARENT FILTER clause.

Easy or difficult? Do you need more? Let me know!