locked
Summing a column based on value of another column RRS feed

  • Question

  • I have a one column that holds salary and another column that shows depts. I am trying to sum the total salaries by dept but I cannot find the right formula to enter into my report. Please help! In other words....Show me the sum of all salaries for dept 1234. This will be in a report and I have mulitple depts that I have to calculate for.
    Monday, May 2, 2011 5:57 PM

Answers

  • Hi,

    I think, simplest way  and t to achive this is to create row grouping on department and then put total againt dept name.


    - Chintak (My Blog)

    • Proposed as answer by Elvis Long Wednesday, May 4, 2011 7:38 AM
    • Marked as answer by Tony Chain Monday, May 9, 2011 12:27 PM
    Monday, May 2, 2011 6:28 PM
  • Hi hammers,

    From your description, you want to sum of all salaries based on department. As Chintak posted, you should add a group and group by department, and then add a total for salary.

    For example, I have two fields in my dataset which are Category and SalesAmount, I want to sum of all SalesAmount based on the Category. Please take the following steps of my sample as a reference:
    1. Drag a Table from the Toolbox to the design surface, and then drag the SalesAmount from the dataset to the second row of the first column.
    2. In the Row Groups pane, right-click “Details” and point to “Add Group”, and then click “Parent Group”.
    3. In the Tablix group dialog, click the “Group by” drop-down, and then select [Category].
    4. Right-click the SalesAmount field in the table, and then select “Add Total”.

    For more information about Adding Grouping and Totals (Reporting Services), please refer to the article below:
    http://msdn.microsoft.com/en-us/library/ms170712.aspx

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

    Thanks,
    Bin Long

    • Marked as answer by Tony Chain Monday, May 9, 2011 12:27 PM
    Wednesday, May 4, 2011 7:38 AM

All replies

  • Hi,

    I think, simplest way  and t to achive this is to create row grouping on department and then put total againt dept name.


    - Chintak (My Blog)

    • Proposed as answer by Elvis Long Wednesday, May 4, 2011 7:38 AM
    • Marked as answer by Tony Chain Monday, May 9, 2011 12:27 PM
    Monday, May 2, 2011 6:28 PM
  • Hi hammers,

    From your description, you want to sum of all salaries based on department. As Chintak posted, you should add a group and group by department, and then add a total for salary.

    For example, I have two fields in my dataset which are Category and SalesAmount, I want to sum of all SalesAmount based on the Category. Please take the following steps of my sample as a reference:
    1. Drag a Table from the Toolbox to the design surface, and then drag the SalesAmount from the dataset to the second row of the first column.
    2. In the Row Groups pane, right-click “Details” and point to “Add Group”, and then click “Parent Group”.
    3. In the Tablix group dialog, click the “Group by” drop-down, and then select [Category].
    4. Right-click the SalesAmount field in the table, and then select “Add Total”.

    For more information about Adding Grouping and Totals (Reporting Services), please refer to the article below:
    http://msdn.microsoft.com/en-us/library/ms170712.aspx

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

    Thanks,
    Bin Long

    • Marked as answer by Tony Chain Monday, May 9, 2011 12:27 PM
    Wednesday, May 4, 2011 7:38 AM