Making the GENERATE Statement Easy Using N-of-N
by John Hormaechea , Director, Product Marketing at Pyramid Analytics
Generally, creating top counts is easy using the filtering capability in BI Office. But what happens when you want to nest the top 5 products and show the top 3 geographies for each product? What about the top 10 products that have the highest Order Count for each Sales Rep in the Sales Org dimension? These are commonly requested reports. Normal count functions will not work.
In the past, you could use multiple steps and manually add this script using the BI Office MDX editor. More details on this action were posted at the Top Counts Using Auto Generated MDX Statement last year.
With Version 6, this capability is very simple to create. Literally a single click will get you started.
The N-of-N wizard allows you to perform a series of nested loop queries (iterations) across multiple hierarchies and display the results in a grid. When you run the query, the series of nested loops are performed in succession, where each loop inherits the output data from the previous loop. Each loop is associated with a single row hierarchy and a single loop filter. The following scenario demonstrates how to use the N-of-N wizard.
Step 1 - Set Up Hierarchies
Before opening the N-of-N wizard, you need to set up your desired columns and rows using the Metadata Tree. In order to run the N-of-N wizard, you are required to specify two or more row hierarchies. In this scenario, we have specified three row hierarchies, as shown below.
When setting up your row hierarchies, it is recommended to use the "All" option to achieve the broadest possible search. For example, for the Products hierarchy, you can select "All Products/Products" in the Elements Panel. This will cause a search through all products when a nested loop is performed across the hierarchy.
Step 2 - Choose a Column Basis
Upon opening the N-of-N wizard, you are required to select a single column around which to display grid results. In this scenario, three column options are available in the pop-up list (Customer, No Discount, Reseller) and we'll select the "Customer" column. The content of the pop-up list depends upon your column selections in the Metadata Tree.
Step 3 - Apply Row Filters
For each of the three row hierarchies, we'll click the filter icon and specify a data filter. The following data filters are available:
- Top Count
- Top Percent
- Top Sum
- Bottom Count
- Bottom Percent
- Bottom Sum
- Non Empty
For example, for the Products hierarchy we'll specify the following filter:
- Select the Filter Type as "Top Count".
- Specify a count value of "4".
- Select "Sales" as the measure for which we'll filter this row hierarchy.
The table below shows the three filters that we'll specify for this scenario.
When using the N-of-N wizard, it's important to distinguish between the two measure types:
- Measures within N-of-N Wizard - A measure selection in the Advanced Filter dialog determines how the filter is performed for the particular row hierarchy.
- Measures in Metadata Tree - The measure selections in the Metadata Tree determine which headers appear in the grid. This is standard BI Office functionality.
Step 4 - View Grid Before N-of-N
Before we run the N-of-N wizard, the grid appears as follows.
Step 5 - View Grid After N-of-N
When we run the N-of-N wizard, the grid content is updated as follows:
- Month Dates - The first query loop arranges all items by year, without any filtering.
- Products - The second query loop finds the top four products (based on sales) for each year.
- Sales Team - The third query loop finds the top two sales persons (based on sales) for each of the top four products.
- The text "Iteratively Generated" appears in the report header, as shown below. This text is deleted from the report header if N-of-N functionality is removed from the report.