none
Multiple rows in total of a group

    Question

  • Hi,

    I have a tablix report which looks like this:

    Unit1 Project1 Line1 Value

    Unit1 Project1 Line2 Value

    Unit1 Project1 Line3 Value

    Unit1 Project2 Line1 Value

    Unit1 Project2 Line2 Value

    Unit1 Project 2Line3 Value

    I have groups made on Units, Projects and Line. Number of lines is always the same (3 for example above). Of course number of Units and Projects is different as it depends on parameter.

    Now I would like to make totals for Units by Project, which would sum not all the values in but would sum by lines. To give an example:

    Unit1 Total Line1 SUM(Value)

    Unit1 Total Line2 SUM(Value)

    Unit1 Total Line3 SUM(Value)

    Unit1 Project1 Line1 Value

    Unit1 Project1 Line2 Value

    Unit1 Project1 Line3 Value

    Unit1 Project2 Line1 Value

    Unit1 Project2 Line2 Value

    Unit1 Project2 Line3 Value

    If I use Group -> Add Total it summarize all the lines together, but that's not what I want.

    Anybody have any suggestions how to do it?

    Thanks,

    Danijel






    • Edited by danijeln Wednesday, March 06, 2013 2:20 PM
    Wednesday, March 06, 2013 2:16 PM

Answers

  • Hi Danijel

    If you are looking for a format as shown in picture below in part 1. Then -

    1. Insert table and create groups Units, Project and Detail (detail is by Line) as shown in part2.

    2. Increase the hight of the Unit Group row in the table, merge all cells.

    3. Insert another table in the merged cells and create groups as shown in part 3. Detail is by Line.

    Good Luck.

    Regards

    Srini

    • Marked as answer by danijeln Wednesday, March 06, 2013 7:54 PM
    Wednesday, March 06, 2013 3:27 PM

All replies

  • Hi Danijel

    If you are looking for a format as shown in picture below in part 1. Then -

    1. Insert table and create groups Units, Project and Detail (detail is by Line) as shown in part2.

    2. Increase the hight of the Unit Group row in the table, merge all cells.

    3. Insert another table in the merged cells and create groups as shown in part 3. Detail is by Line.

    Good Luck.

    Regards

    Srini

    • Marked as answer by danijeln Wednesday, March 06, 2013 7:54 PM
    Wednesday, March 06, 2013 3:27 PM
  • Hey Srini

    thank you for fast answer.

    Actually I need horizontal format so Lines is right of Projects and Project in right of Unit.

    Also I would like to extend my description a bit. Units are also group in parent groups which again are grouped. So basically I have something like this

    Unit1 Unit2, Unit3, Unit4, Project, Line, VALUE.

    All are grouped.

    Below is the amended screenshot of what I have at the moment

    Hope I explained clearly.

    Thanks,

    Danijel

    Wednesday, March 06, 2013 4:00 PM
  • Hi Srini,

    just to let you know, I'm exploring your method.

    so far is looking good an I'll let you know of results later.

    Danije

    Wednesday, March 06, 2013 4:45 PM
  • Hi,

    If you are looking in the following format -

    When creating groups, just select create footer.

    Let me now whether it is as per your requirement.

    Regards

    Srini

    Wednesday, March 06, 2013 5:29 PM
  • Hey,

    thanks again for reply.

    I've implemented your first recommendation and it turns even better, so I'll mark that as an answer.

    But with completing first task another one came up.

    If I use your previous post with 7 lines with values in detail, I have number 6 which is calculated based on values from 5 and 1 as 5/1*100. Basically a percentage.

    There is no problem in details as data is read from database, but when making totals over Unit4 for example above, SUM is not appropriate function as returns invalid value.

    Any suggestions?

    Thank you,

    Danijel

    Wednesday, March 06, 2013 7:54 PM
  • Hi,

    I got it.

    Had to make some custom code on report and use it in expression for the field.

    Danijel

    Wednesday, March 06, 2013 8:40 PM