Display multiple comment rows for a single record- within the same group
-
I have a single row group with 5 records. 3 of those records have notes associated with them. 2 of those have multiple notes. The notes are tied to the RECID of the record.
So, I would like to display those notes under a single incidence of the record. Exactly like this:
Record 1
DateCreated CreatedBy Note 1a
DateCreated CreatedBy Note 1b
Record 2
Record 3
DateCreated CreatedBy Note 3a
Record 4
DateCreated CreatedBy Note 4a
DateCreated CreatedBy Note 4b
DateCreated CreatedBy Note 4c
When I insert a row for the note(s) within the group, I get multiple lines of the record:
Record 1
DateCreated CreatedBy Note 1a
Record 1
DateCreated CreatedBy Note 2a
Record 2
Record 3
DateCreated CreatedBy Note 3a
Record 4
DateCreated CreatedBy Note 4a
Record 4
DateCreated CreatedBy Note 4b
Record 4
DateCreated CreatedBy Note 4c
How can I achieve the top result?
-
The simple answer is that you do this in a reporting tool like Reporting Services instead of straight out of SQL. Certainly there are ways to do this directly in a script; but they're not the right way.
-
I thought I was asking how to do this in reporting services. Should I have posted somewhere else in the forums?
-
Here is okay. Could you post the data as a UNION query, or CREATE TABLE query with INSERTS to make it easier for us to help? It sounds like all you need is a simple tablix with a grouping above by RecordID.
As for the Records without Notes.... you would do that in the query that your report is based on. If you OUTER/LEFT JOIN Records to Notes, then you'll get all the rows from the Records table whether or not they have corresponding rows in the Notes table. Then you can base your report on that.
-
I am getting all of the records. That is not the problem. The problem is that I cannot figure out how, in the report, to get all of the notes for a record to show under a single incidence of that record. When I add a row for the note inside the group...
...I get multiple lines for the record, when there multiple notes. Look at 17.11.01B. It has 8 different notes. So I end up with seeing 17.11.01B 8 times. How can I get all 8 notes to show up under a single 17.11.01B?
-
My apologies... I was looking at new posts made today and completely missed that you posted this in the SSRS subforum. My bad 🙁
Assuming that your result set looks something like this:
RECID Note1 Note 1 for record 1
1 Note 2 for record 1
2 Note 1 for record 2
2 Note 2 for record 2
etc..
An easy way to get what you want would be:
1. Using the New Report wizard, put RECID in the Group section and Note in the Details section;
2. Choose the Stepped grouping option, not the Block grouping option;
3. When you finish the wizard, you'll see RECID in the first column and Note in the second column, one row down;
4. Move (cut and paste or just drag) Notes from the second column to the first column, under RECID.
If you've got an existing report and/or don't want to use the wizard...
1. Create a table object on the report;
2. Create a group and make RECID the group expression, make sure you have show header selected for that group;
3. Place the RECID expression in the newly created header field;
4. Place the Notes expression in the detail row.
-
When I grouped by RECID...
... I only get the first note for each record:
-
See the bracket at the far left in your first image? That is showing the grouping; right now you are grouping both rows. This is why it's repeating the whole thing every time.
You need to add a detail row below the group.
1. Do not delete anything (yet);
2. Right-click on the group (either row) and select Add Group --> Row Group Child Group;
3. When a box appears asking what to group by, select the Show Details radio button;
4. You'll know you've successfully created a detail row if you see three horizontal lines INSIDE the bracket on that row;
5. COPY the existing second row and PASTE it into the newly created detail row;
6. Right-click the second row of the group and delete that row.
At this point, your report in design mode should look the same as your image except that, on the second row, there should be three lines indicating a detail row. You should get the results you want when you run the report.
-
Thank you SO MUCH!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply