Search code examples
reporting-servicesssrs-2012

SSRS % of total across columns

I am trying to show the value of a summarized column as a percent of the whole. In the image attached, I would like to show the "Buy Out %" column as a % of the whole Extended Price for both "Buy Out" and "Stock".

For the 1st group "HOUMA LA" it would be 6,547,543.37/(6,547,543.37 + 2,647,444.26)

enter image description here

My design is as follows: enter image description here

Here is a data snippit:

enter image description here

Solution

  • Add the following expression in your Buy Out % texbox:

    =Fields!ExtendedPrice.Value / (Fields!ExtendedPrice.Value + Fields!ExtendedPrice.Value)

    Then go to the textbox properties and chose Percentage as the number format.

    If the calculation should happen inside a group (for example your Houma La) then you have to add the scope to the fields:

    =(Fields!ExtendedPrice.Value, "HoumaLaGroupName") / 
 ((Fields!ExtendedPrice.Value, "HoumaLaGroupName") + (Fields!ExtendedPrice.Value, "HoumaLaGroupName"))

    UPDATE

    You have two ways to go here.

    1. You can reference to extended price (Buy out) and extended price (Stock) with the following expression, which you put into the textbox Buy out %. For this you have to use the name of the textbox, where extended price (Buy out) and extended price (Stock) are (this way you reference directly to the value in the textbox, which would be 6,547,543.37/(6,547,543.37 + 2,647,444.26)):

      =ReportItems!ExtendedPriceBuyOutTextboxName.Value / (ReportItems!ExtendedPriceBuyOutTextboxName.Value + ReportItems!ExtendendPriceStockTextboxName.Value)

    2. You use the expression from the extended price (Buy out) and extended price (Stock) textboxes again:

      =(ExpressionExtendedPriceBuyOut) / ((ExpressionExtendedPriceBuyOut) + (EypressionExtendendPriceStock