none
stacked chart total display in ssrs with ssas data source

    Question

  • Hi ,

    I have small requirement in ssrs.Could you please help me.

    My requirement is stacked chart total display at top in ssrs with ssas data source.

    I am able to do with sqlserver data source in ssrs.but using mdx query in ssrs , i am not getting required solution.

    Could please help me urgent.


    indu

    Sunday, December 22, 2013 10:59 AM

Answers

  • Hi indu,

    According to your description, you want to get total row with mdx query. If in this scenario, we can directly use MDX to query an additional row act as a total row to replace union a statement in T-SQL. Based on my research, in order to get the grand total, the query can be like below (This assumes that the aggregation type of the measure is "sum", which is the default):
    SELECT [Measures].[MeasureName] ON COLUMNS
    FROM [CubeName]

    The following blog about how to get the grand total and sub total with mdx query is for your reference:
    http://vnu10.blogspot.in/2011/01/mdx-grand-total-sub-total.html

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, December 24, 2013 11:25 AM
    Moderator

All replies

  • How did you do this with a SQL Server data source? I imagine you modified your data source to your query for your data set that showed the total.  You can do the same thing with SSAS.  You can modify the query to to include the All member if it is simple, or write a calculated measure if needed.

    As an example, the following query will include the all member for Product Category.

    SELECT [Measures].[Sales] on 0, 
    [Product].[Product Category].members on 1 
    from [Cube] 

    If you wanted the members without the All you would change .members to .children.

    Once you have that information in your query, you an proceed as normal.  Here is a link to some instructions.

    Basically, you:

    1. Populate your chart and show data labels.
    2. Modify the data label property to subtract the total number from the total so it isn't double-counted.
    3. Modify the label on the series so the total doesn't show up in the legend.
    4. Set the fill on the total category to be transparent

    Sunday, December 22, 2013 8:28 PM
  • Hi,

    Thank you for giving reply.

    here my doubt is how to pass mdx query with union of total name.

    you suggested one link, in that sql union with total name.

    how we will do in mdx query?

    SELECT   Month,
                 Category,
                 Sales
    FROM     Sales
    UNION
    SELECT   Month,
                 'Total' AS Category,
                 0.1 * SUM(Sales) AS Sales
    FROM     Sales
    GROUP BY Month;

    Could please suggested me?


    indu

    Monday, December 23, 2013 9:07 AM
  • Hi indu,

    According to your description, you want to get total row with mdx query. If in this scenario, we can directly use MDX to query an additional row act as a total row to replace union a statement in T-SQL. Based on my research, in order to get the grand total, the query can be like below (This assumes that the aggregation type of the measure is "sum", which is the default):
    SELECT [Measures].[MeasureName] ON COLUMNS
    FROM [CubeName]

    The following blog about how to get the grand total and sub total with mdx query is for your reference:
    http://vnu10.blogspot.in/2011/01/mdx-grand-total-sub-total.html

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, December 24, 2013 11:25 AM
    Moderator