Guidance to create a Multi-level Analytic Report/Chart RRS feed

  • Question

  • Hello,

    I would like to get some guidance to create a Multi-level Analytic Report/Chart

    My System Details:

    Win XP
    SQL Server 2005
    AdventureWorks DW Database
    PPS 2007

    I have created a sample dashboard with a simple report in it.

    If I have to show another detail of the report, how do I do it ?

    for ex, if my dashboard is displaying "Sales by Product Category"

    In my dashboard, I have a zone to display this report as a "Analytic grid".

    Highlevel Product 1    Sales Amount of Product1     Gross Profit Product 1
    Highlevel Product 2    Sales Amount                        Gross Profit
    Highlevel Product 3    Sales Amount                        Gross Profit

    As a user of the dashboard, I should be able to click on Highlevel Product 1 and it should show me the sublevels of the Highlevel Product 1 with its corresponding Sales Amount and Gross Profit.

    Is there any step by step guide to do the same ?

    Please advice.

    Thank you!!

    Friday, December 12, 2008 11:46 PM


  • To get a Analytical Grid or Chart to be able to display drilldown capabilities you are going to want to reference the hierarchies that are defined in the dimensions.  So if we take a look at Adventure Works for the Analytical Grid you would do the following:

    1. Place the Product Categories hierarchy on the rows.
    2. Then the two measures on the columns - Reseller Sales Amount and Reseller Gross Profit. 
    3. You can then click on the black dropdown arrow in Product Categories in the rows and select the members that you want to display initially in the report (probably uncheck the default and then right-click oin the All Products and select check children). 
    4. At this point you can then click on the browse button to preview this and you will see that you are now presented with the ability to expand out the Products on the rows and drill into the data. 
    5. You can then right-click on the values in the grid and select show details to get at additional data (this will only work for Reseller Sales Amount since the Gross Profit is a calculation and will not allow you to drill into detail.

    If you are going to do this for a chart it is about the same except that you would put the Product Categories on the bottom axis and the two measures on the series.

    • Proposed as answer by Dan English Tuesday, December 30, 2008 11:06 AM
    • Marked as answer by AseemN Tuesday, January 13, 2009 6:51 PM
    Sunday, December 14, 2008 12:35 PM