none
To calculate Separately for count of Male and female gender in SSAS 2008 RRS feed

  • Question

  • Hi Friends,

                    In SSAS 2008 im unable to calculate  count for separate Male and Female gender in calculation tab.

    Can u Pls help me how to calculate.

    NOTE: I have designed a cube with 4 dimensions.

    Pls send me the query.

     

     

    Thursday, June 10, 2010 3:35 PM

Answers

  • Would making a calculated measure like 

    ([measures].[TotalCount],[Dim Name].[Gender].[Male])

    and 

    ([measures].[TotalCount],[Dim Name].[Gender].[Female])

    help you?


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Friday, June 11, 2010 6:55 AM
    Moderator
  • Ok I should have been more explicit. Do you already have a count measure defined? I assumed that you had made a base measure called TotalCount. If you have you can use the below formula <with a comma (measure, dimension member)>

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[M])

     

    If not, could you give more details how you want the count, What is the query, etc

     



    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Friday, June 11, 2010 8:23 AM
    Moderator
  • Hi Abdul

    You need to create any calculated measure for this. you can use the below MDX for that

    select [measures].[TotalCount] on rows,
    [DIM Customer].[Gender].member on columns
    from
    cube name

    or you can directly use the default measure with count aggregation if you are using any reporting tool. So, simple drag n drop the dimension attribute gender with measure count.

    Tell me if you need something else.

     

     


    lntinfotech
    Friday, June 11, 2010 9:51 AM
  • Abdul,

    In your cube design , select tab calculations and following that select 'New Calculated Member' form shortcut-menu.

    Rename the measure into : "Male Count" and put this in Expression field:

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[M])

     

    do the same for female, using this expression

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[F])

    deploy and process the cube. The new measures should become avalaibel.

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, June 11, 2010 10:54 AM
  • Hi Abdul,

    OK, let's take this from a different corner. If you have to make this available in SSRS , we could solve it by:

    1. adjusting the SSRS dataset to include the required percentage
    2. adjust the graph to display it.

    I just tested this out myself based on AW-cube, and it works fine.

    The dataset in SSRS is :

    with
    member [measures].[Gender Contribution Percent] as
    [Measures].[Reseller Sales Amount]/ ([Measures].[Reseller Sales Amount],[Employee].[Gender].[All Employees]), format_string = "Percent"
    SELECT  NON EMPTY {[measures].[Gender Contribution Percent]} ON columns,
    NON EMPTY { ( [Reseller].[Business Type].members * [Employee].[Gender].[Gender].MEMBERS  ) }  ON rows
    FROM [Adventure Works]

     

     

     

     

    With the dataset in place, You can add a piechart to the reports design area and use [measures].[Gender Contribution Percent] as the datafield and gender as the series field. You  can set it up similar to this, and add Creditors as a parameter

    Op the pie-chart, choose 'Show data labels'. As a result the data-labels are being displayed. To format them as a percentage, select one of them and after that activate the short-cut menu (via right mouse click) and choose 'Series Labels Properties'. Set to general property label data to this value: #PERCENT{P0}

     

    hth,

    Cees

     

     

     

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, June 11, 2010 2:08 PM

All replies

  • do you have a customer dimension and want to count how many male/female customers you have?
    (gender is an attribute in any dimension?)

    or is gender a seperate key in your fact-table?

    greets,
    gerhard


    - www.pmOne.com -
    Thursday, June 10, 2010 4:11 PM
    Answerer
  • Hi Gerhard,

                     Yes i want to show  the total number of counts for  male and female customers.  Gender is an only attribute for only dimension.

    Thanks & Regards,

    Abdulrahim.S

    Friday, June 11, 2010 6:43 AM
  • Would making a calculated measure like 

    ([measures].[TotalCount],[Dim Name].[Gender].[Male])

    and 

    ([measures].[TotalCount],[Dim Name].[Gender].[Female])

    help you?


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Friday, June 11, 2010 6:55 AM
    Moderator
  • Hi Jason,

                 We need to include this query in calculated member Expression  rite???

    I have include this below mentioned query  in calculated member but i did not get the result

    Jason.

    ([measures].[TotalCount].[Dim FJ Client].[Gender].[M])

    Im very new to do this type calculations. Pls help me..

    Thanks & Regards,

    Abdulrahim .S

     

    Friday, June 11, 2010 7:51 AM
  • Ok I should have been more explicit. Do you already have a count measure defined? I assumed that you had made a base measure called TotalCount. If you have you can use the below formula <with a comma (measure, dimension member)>

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[M])

     

    If not, could you give more details how you want the count, What is the query, etc

     



    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Friday, June 11, 2010 8:23 AM
    Moderator
  • Hi,

         I want to calculate percentage of male and female of particular creditor.

    So first  we need to find out the total number of Male and Female customers rite..

    So im finding the total number counts for male and female customer.

    I have created measure called totalcounts.

     

     Note : Male = M Female = F

          After that i have included below query in calculated member

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[M])

    But it is not showing any values in the browser.

    Pls help me..

     

     

     

     

    Friday, June 11, 2010 8:34 AM
  • Hi Abdul

    You need to create any calculated measure for this. you can use the below MDX for that

    select [measures].[TotalCount] on rows,
    [DIM Customer].[Gender].member on columns
    from
    cube name

    or you can directly use the default measure with count aggregation if you are using any reporting tool. So, simple drag n drop the dimension attribute gender with measure count.

    Tell me if you need something else.

     

     


    lntinfotech
    Friday, June 11, 2010 9:51 AM
  • Hi Rahul,

                 where to write this query becz im very much new to do this calculations.. So pls tel

    me like step by  step...

    whether in New command script in Calculation tab???

    Pls do help me .....

    Friday, June 11, 2010 10:45 AM
  • Abdul,

    In your cube design , select tab calculations and following that select 'New Calculated Member' form shortcut-menu.

    Rename the measure into : "Male Count" and put this in Expression field:

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[M])

     

    do the same for female, using this expression

    ([measures].[TotalCount],[Dim FJ Client].[Gender].[F])

    deploy and process the cube. The new measures should become avalaibel.

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, June 11, 2010 10:54 AM
  • Hi Cees,

                Thanks for the reply.. Can u pls tel me how to calculate percentage of Male and

    Female for particular creditor in the Calculation tab.

    Or pls tel me how to find by using MDX query and where to write that MDX query.

    Scenario:

    1 creditor having 5 Clients

    5 Clients are different gender like 3 Males and 2 Females

    I want to show the Number percentages of Male and Female in SSRS Pie chart report. So im

    doing this works.

    Can u pls tel me what can i do create the Pie chart report for the above said scenario.

    Pls help me any one for this...

     

    Regards,

    Abdul

     

     

     

     

     

     

     

     

    Friday, June 11, 2010 11:02 AM
  • Abdul

    just 2 questions:

    1. did you already update the cube calculations based on my prevous post ?
    2. which version of SSRS are you using (2005 or 2008) ?

    r,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, June 11, 2010 11:06 AM
  • Hi Cees,

       Ya i got updated but  it is showing all both Male and Female. It is not filtering based on the

    creditor. I want to show this gender based on Creditor.

    Gender is available in Customer Dimension.

    Creditor is another dimension.

    Im using  SSRS 2008.

    Pls do help for me..

     

     

    Friday, June 11, 2010 11:27 AM
  • Hi Abdul,

    OK, let's take this from a different corner. If you have to make this available in SSRS , we could solve it by:

    1. adjusting the SSRS dataset to include the required percentage
    2. adjust the graph to display it.

    I just tested this out myself based on AW-cube, and it works fine.

    The dataset in SSRS is :

    with
    member [measures].[Gender Contribution Percent] as
    [Measures].[Reseller Sales Amount]/ ([Measures].[Reseller Sales Amount],[Employee].[Gender].[All Employees]), format_string = "Percent"
    SELECT  NON EMPTY {[measures].[Gender Contribution Percent]} ON columns,
    NON EMPTY { ( [Reseller].[Business Type].members * [Employee].[Gender].[Gender].MEMBERS  ) }  ON rows
    FROM [Adventure Works]

     

     

     

     

    With the dataset in place, You can add a piechart to the reports design area and use [measures].[Gender Contribution Percent] as the datafield and gender as the series field. You  can set it up similar to this, and add Creditors as a parameter

    Op the pie-chart, choose 'Show data labels'. As a result the data-labels are being displayed. To format them as a percentage, select one of them and after that activate the short-cut menu (via right mouse click) and choose 'Series Labels Properties'. Set to general property label data to this value: #PERCENT{P0}

     

    hth,

    Cees

     

     

     

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, June 11, 2010 2:08 PM