In BI testing, you sometimes have to test summary type dashboards. These dashboards contain all sorts of numerical figures with Total and subtotal. Something like this:
Generally, to test these types of dashboards, ETL QA is forced to write two separate queries. One for the detail data and another for the aggregate (Total). Now, there’s a clever way to do it. Use Rollup function with Group By. Here’s how you do it. In the below example, we are taking count on the basis of date. When you use only Group By:
Now, by adding rollup with Group by you can see magic row at the end which is showing the sum total of the column “No. of Cases”
This function can be used for multiple hierarchy data sets too.