locked
Creating Analytic Grid report RRS feed

  • Question

  • Hi,

         I have an analytic Grid report where i have top 20 products based on Sales Amount.
    I wrote named query (Top20Product) in SSAS cube designer and deployed it.. In dashboard Designer, I created an analytic grid report and  placed the Named set in rows section, I could get the report for top 20 Products based on Sales Amount

    Now, I want a report where the number N(instead of 20) will be passed by the user. and the N top Products will be displayed .
    how should i do that?
    I might not need drill down in the report.. But I want the user to pass  the number.. in the report? Can  I use a text box in report? How?
    Do I need to write MDX query in Dashboard Designer ? What will be the MDX query and how to pass the parameter?? I am using Adventure Works DW sample database and SQL SErver 2005
    Please help.
     
    Tuesday, February 17, 2009 8:54 AM

Answers

  • Sanjana,
    You have to filter out  your query for the not null values or >0 to avpoid this also i have used order by BASC(ascending ) you can change that into BDESC(Desc) if you want.

    SELECT {order(BottomCOUNT(Existing(filter(([Product].[Product].[Product].Members),

    [Measures].[Sales Amount]>0 )),20,

    [Measures].[Sales Amount]),[Measures].[Sales Amount], BASC)} ON ROWS,

    [Measures].[Sales Amount]ON COLUMNS

    FROM [Adventure Works]

    Thanks


    Dibyant S U padhyay
    • Marked as answer by AseemN Thursday, April 16, 2009 5:13 PM
    Wednesday, February 18, 2009 3:31 PM

All replies

  • Hello Sanjana,

    Have you checked out this post yet?

    Thanks,
    Jon Thomas
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 17, 2009 4:58 PM
  • Sanjana,
    You can not use ssas 2005 named set for your analytical grid here to define the filter on it,
    You have to use query here because named set you will create there would be hard coaded like to 10, 20 etc those can't be set dynamically in ssas 2005 which is possible to define dynamically in ssas 2008(Dynamic named set).
    Now what you have to do do you have to use the query here in the place of drag and drop,
    You may use same query what you are using in the cube to define the named set for top count
    like here is a sample i am using

    SELECT NON EMPTY { [Measures].[xyz] } ON COLUMNS ,

    NON EMPTY { ORDER( { TOPCOUNT( { [Company].[Desc].[All].CHILDREN }, 20, ( [Measures].[COP Freight Spend] ) ) }, ( [Measures].[xyz] ), BDESC ) } ON ROWS  FROM [Cube]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

    Now since this is hardcoaded for 20, you can go and select 20 and at the bottom you will see parameters filter insert, click there
    and now your query will become something like this

    NON EMPTY { ORDER( { TOPCOUNT( { [Company].[Desc].[All].CHILDREN }, <<Parameter>>, ( [Measures].[COP Freight Spend] ) ) }, ( [Measures].[xyz] ), BDESC ) } ON ROWS  FROM [Cube]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL


    And the parameter will have top 20(there you can replace with any member using mdx) value,publish it and now you go to the dashboard create filter for it and then wire it with filter

    http://blog.theple.com/bizsharp/folder/2.html?uid=1180

    Let us know



    Dibyant S U padhyay
    Tuesday, February 17, 2009 4:58 PM
  • Hi,
       Great!! The solution worked. I could get the Top  "N" Products by Sales Amount and the number  "N"is accepted from the user.(I added filter for this to link to the parameter). 

    One more question, to get the bottom "N" Products, I am writing the MDX query  in Query tab of Dashboard Designer as follows;

    SELECT BottomCount
    ({[Product].[Product].[Product].Members}
    ,20
    ,[Measures].[Sales Amount]
    ) ON ROWS,
    [Measures].[Sales Amount]ON COLUMNS
    FROM [Adventure Works]

    (Note: I can change 20 as <<parameter>> later on , I know that)

    When I am  going to the design mode, I get all the 20 rows with columns as Product and  Sales Amount.
    But the Sales Amount data is empty for all the rows..

    How can I modify the above query so that I can get Bottom 20 Products based on Sales Amount and the sales amount data is not null .

    Please help.
    Your solution  works great all the time.

    I really appreciate your help.
    Wednesday, February 18, 2009 10:15 AM
  • Sanjana,
    You have to filter out  your query for the not null values or >0 to avpoid this also i have used order by BASC(ascending ) you can change that into BDESC(Desc) if you want.

    SELECT {order(BottomCOUNT(Existing(filter(([Product].[Product].[Product].Members),

    [Measures].[Sales Amount]>0 )),20,

    [Measures].[Sales Amount]),[Measures].[Sales Amount], BASC)} ON ROWS,

    [Measures].[Sales Amount]ON COLUMNS

    FROM [Adventure Works]

    Thanks


    Dibyant S U padhyay
    • Marked as answer by AseemN Thursday, April 16, 2009 5:13 PM
    Wednesday, February 18, 2009 3:31 PM