-3

data in SSRS shows like this as below using the Matrix - Can someone please help me how to get the totals for BB-1,BB-2 ,AC-1 ,AC-2 below for all the countries.I am using the running value function between the dates Aug-2015 and Jan 2017 for Columns BB-1,BB-2,AC-1,AC-2. Type A always belong to BB-1 & BB-2. Type B belongs to AC-1,AC-2. row groups- Country Type

Column Group Date enter image description here

Kyle Williamson
  • 2,251
  • 6
  • 43
  • 75
Parkin
  • 13
  • 6

4 Answers4

1

It sounds like you want to create a secondary matrix that displays the information with a different grouping entirely. It is possible to have two matrix/tables that use the same data, and that's how I would go about solving this.

Jacobm001
  • 4,431
  • 4
  • 30
  • 51
  • I right clicked on column group to add total but it adds total column. I want to add the Totals below the main data as shown – Parkin Jun 28 '17 at 22:28
  • @Papil: I misunderstood what you were trying to do. I've updated my answer. – Jacobm001 Jun 28 '17 at 22:31
  • But if i create another matrix i need to repeat the first matrix again because in my current matrix i am doing running value from start month to last month. In this example its from Aug-15 to Jan-17. Any suggestions? – Parkin Jun 28 '17 at 22:35
  • 1
    @Papil: No. What you're asking for is to create two totally distinct grouping sets within the same matrix, which isn't possible. You need to copy the one you have and edit it to the different criteria you have. Performance wise, that shouldn't be a big deal. – Jacobm001 Jun 28 '17 at 22:40
  • ok. can you give some ideas on what criteria i use to get the totals as shown. – Parkin Jun 28 '17 at 22:48
  • I don't understand where you're confused. In the first Matrix you're displaying a running value for row groups (country->type->names) and a column group of (date). Do the same thing with the second matrix, except make the row grouping (type->name) and leave the country out of it entirely. – Jacobm001 Jun 28 '17 at 22:52
  • Names is not in row groups. I dint mention that in my question. Names is just a column. That column has all the values -BB1,BB2,AC1,AC2. In the Data section of Matrix i select these columns from my data set to see their data. I hope that makes sense. Will that change your answer. – Parkin Jun 28 '17 at 23:02
  • @Papil: not really, just that makes them the detail row – Jacobm001 Jun 28 '17 at 23:10
  • So basically there are 4 rows i created for 4 Names column in my matrix. – Parkin Jun 28 '17 at 23:11
  • I agree with Jacobm001. You can make and duplicate of the matrix (one below the other) with the same sizing and instead of using the country dynamic group in the second, set it to be static (total) – niktrs Jun 29 '17 at 06:20
  • philip webb's solution is better than the two separate matrices unless an old version of SSRS is used (eg 2005) – niktrs Jun 29 '17 at 12:34
  • @Jacobm001- Thanks for the suggestions. Your worked perfectly! – Parkin Jun 29 '17 at 19:24
  • I need to calculate percentage and YTD as well. Any suggestions please? Do i need to post a new question for that . Let me know – Parkin Jun 29 '17 at 19:28
  • @Papil: That would definitely be a new question. If this question is answered properly, please consider accepting one of the answers, and upvoting all the answers that were helpful. That will give you and the helpful people a rep boost :) – Jacobm001 Jun 29 '17 at 19:29
  • @Jacobm001- I have posted another connecting question for YTD. it would be great if you can take a look once. Thanks a lot. – Parkin Jul 07 '17 at 01:57
1

Amend the report design to the following:

enter image description here

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
0

Amend you report design, underneath main report add in another row group [Names], adjacent below, along with a sum(Value) field to look like this:

enter image description here

Report should like this:

enter image description here

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • This is not how my Totals looks like. I want the totals to be added like - Add All BB-1 of all countries together and display that ..similarly other columns. – Parkin Jun 28 '17 at 23:54
0

Amend the report design to the following:

enter image description here

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12