Need Help with Creating a Matrix to Display Event and Summary Info

Question

Post reply

Need Help with Creating a Matrix to Display Event and Summary Info

treyagrimes

Ten Centuries

Points: 1001
More actions
March 10, 2015 at 2:00 pm

#398159

Hello,
I'm looking for some advice on how to get my tablix/matrix to properly display the information on my report. The first image is the existing Crystal Report that I am trying to convert to SSRS. The first line lists the event, where it took place, the recruiter, status, etc... and the section underneath shows the changes that were made to the event, which can be one change or it can be many. Or no changes, but then it would not appear on the report. The CR report functions as it should.
I am trying to accomplish the same task within SSRS but I'm having some trouble with the changes summary. The event summary displays for each record of a change. In the screen shot below, you can see that DriveID 682144 had three changes, but for each change, the event summary is displayed/printed. So instead of Evoqua listed once and the three changes made to the event listed underneath, I am getting the summary displayed for each and every change within the report.
I have a row grouping for DriveID since each event has an unique ID. And I also have a Details row group that displays the changes. I've tried adding a grouping on the Details row group, but if I group on SourceDriveID, it will only display one record for each drive. I am joining the two tables on DriveID (Drivemaster.DriveID=CriticalDriveChanges.SourceDriveID)
I think it would work if the Critical Drive Changes view had a unique identifier, but it doesn't so I am trying to work with what I have. Any suggestions on making this work?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sql Sarah SSCrazy Points: 2081 More actions · Answer 1

It looks like you need to put the fields in Group Header lines instead of Detail lines.

Right click on the group header, choose Insert Rows, Inside Group - Below.

Move the fields Drive Date, Account Center Name, Recruiter, etc. to that line.

Then create another group header line and put the detail field headings in it - Change Date & Time, Changed by, etc.

The only thing that should be in the detail row are the detail fields.

treyagrimes Ten Centuries Points: 1001 More actions · Answer 2

Hey Sarah,

I've gone back and created a new table and placed the summary information in the group header lines so it only appears once. Screenshots below so the table setup and results.

So that's off to a good start. But I don't truly understand what you mean when you say to create another group header line to put the detail fields in it: Change Date, By, etc...

Should I be adding a new row inside the group? Or a new child grouping? No matter what I try, I can't see to get it to look correct.

Thanks,

sql Sarah SSCrazy Points: 2081 More actions · Answer 3

There are 2 things to do.

1.Add the labels for the detail fields

a.Right click on the Drive ID Group Header row, choose Insert Row, Inside Group – Below

b.Put the labels for the detail fields in there: Change Date & Time, Changed By, Value

Changed, Previous Value, New Value, and Type

2.Add the detail fields

a.Your screenshot looks like there isn't a Detail row (it just might not be showing). That is

needed to show the fields: Change Date & Time, Changed By, Value Changed, Previous

Value, New Value, and Type.

i.If you need to add the Detail row back in, here are directions:

ii.https://msdn.microsoft.com/en-us/library/dd220470(v=sql.105).aspx"> https://msdn.microsoft.com/en-us/library/dd220470(v=sql.105).aspx

iii.The Grouping Pane is at the bottom of the report design window.

b.Put the fields in the detail row: Change Date & Time, Changed By, Value Changed,

Previous Value, New Value, and Type

I am attaching some screen shots that I hope will help you

treyagrimes Ten Centuries Points: 1001 More actions · Answer 4

Thank you very much Sarah.

First time creating a report like this with a Detail section. Frustrating but I made it through with your advice and the report looks like I wanted it to.