SSRS: How to calculate percentage of some values? (Network Steve Forum)

SSRS: How to calculate percentage of some values?

Hi All,

I have a dataset which contains Area and EmpId. I need a report like below.

Area EmployeeCount %

India 8 30.77

Japan 13 50

Korea 5 19.23

Total 26 100

I need the total count of employees in each area and the at the last line I need the total count of employees from all the Area. The next field is calculating the %. That is finding the percentage of each row 8/26,13/26 and 5/26. I have created a row group for EmployeeId, So I am able to find the Employee count. Also, included "Add total" , that is giving the total of EmployeeCount. But, How can I find % and its total?

Regards,

Julie

April 16th, 2015 5:59am

Hi Julie,

You need to use ReportItems Collection as mentioned here

Code for the column as

=(Fields!EmployeeCount.Value/ReportItems!EmployeeCount1.value) *100

"ReportItems!EmployeeCount1.value" has EmployeeCount1 is the name of my text box.

lets run the report after setting expression in the new column:-

Added RDL code for reference

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Tablix Name="table1">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.22in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Area</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Employee Count</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Right</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox6">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value />
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Right</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox6</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.21in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Area">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!Area.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Area</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="EmployeeCount">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!EmployeeCount.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>EmployeeCount</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox7">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=(Fields!EmployeeCount.Value/ReportItems!EmployeeCount1.value) *100</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <Format>f2</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox7</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox4">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value />
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox4</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="EmployeeCount1">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!EmployeeCount.Value)</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>EmployeeCount1</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="EmployeeCount2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=(Sum(Fields!EmployeeCount.Value)/ReportItems!EmployeeCount1.value) *100</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>EmployeeCount2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember />
            <TablixMember />
            <TablixMember />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
              <RepeatOnNewPage>true</RepeatOnNewPage>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <Group Name="table1_Details_Group">
                <DataElementName>Detail</DataElementName>
              </Group>
              <TablixMembers>
                <TablixMember />
              </TablixMembers>
              <DataElementName>Detail_Collection</DataElementName>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <KeepWithGroup>Before</KeepWithGroup>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet1</DataSetName>
        <Top>0.37in</Top>
        <Height>0.68in</Height>
        <Width>3in</Width>
        <Style />
      </Tablix>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>How to calculate percentage of some values</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>14pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.37in</Height>
        <Width>5in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>1.05in</Height>
    <Style />
  </Body>
  <Width>5in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>DataSource1</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>a3167710-ae24-43ab-975a-e990b0f7d698</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>select 'India' Area ,8   EmployeeCount    
union
select 'Japan',   13    EmployeeCount        
union          
select 'Korea',    5       EmployeeCount</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Area">
          <DataField>Area</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="EmployeeCount">
          <DataField>EmployeeCount</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>b49aa4f4-1272-4739-acf2-8cde0fbed2cd</rd:ReportID>
</Report>

Thanks

Prasad

Edited by Prasad MSBITIPS.com 21 hours 4 minutes ago
Proposed as answer by Qiuyun YuMicrosoft contingent staff, Moderator 6 hours 33 minutes ago
Marked as answer by Julie Eliza Sam 8 minutes ago

Free Windows Admin Tool Kit Click here and download it now

April 16th, 2015 6:40am

Hi Prasad,

Thanks for the response. This makes sense. But, is this the right way we have to go for?

Regatds,

Julie

April 16th, 2015 6:52am

Hi Julie,

Better way can tell by Moderators (Technet team) of this forum.

On my opinion, All calculations should be done at SQL side, it will give performance(as it executes on database server) and accuracy(float values decimal precision) as well.

Hence calculate the percentage column in the SQL and use the column directly in the report.

Thanks

Prasad

Free Windows Admin Tool Kit Click here and download it now

April 16th, 2015 6:57am

Percentage you can use below expression

SUM(Fields!EmployeeCount.Value)/SUM(Fields!EmployeeCount.Value,"GroupName")

and for total use

SUM(Fields!EmployeeCount.Value,"GroupName")/SUM(Fields!EmployeeCount.Value,"DataSetName")

April 16th, 2015 7:10am

Hi Julie,

In your scenario, you could also calculate [EmployeeCount] and [Percentage] fields in the dataset using the query like below:

select Area, count(EmpId) as EmployeeCount,(count(EmpId)*100/(select count(*) from mytable))
as Percentage from my table
Group by Area

Then you could put those three fields in a tablix, and perform Add total to get the Total row.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

Free Windows Admin Tool Kit Click here and download it now

April 16th, 2015 9:31pm

Thank you Qiuyun Yu

April 17th, 2015 3:40am

This topic is archived. No further replies will be accepted.