My Problem

I am trying to get some data to display a certain way in SSRS, but I cannot seem to get it to display exactly how I want, despite spending quite a bit of time searching for a solution.

My Data

The data I have is stored in a database table and consists of students' appointments with their advisors. Each appointment has an Appointment_ID unique to that appointment, but the Appointment_ID is not a unique value in the table. That is, whenever a change is made to a specific appointment, such as a location or time change for that appointment, an additional row associated with that Appointment_ID gets created in the table, and so there can be multiple rows associated with the same Appointment_ID that contain slightly different values depending on what was changed.

My Report

I am using SQL to pull the data from that table into SSRS, and within my query I use the table's row date creation field to calculate a new field for the purpose of identifying the first row that was created for an Appointment_ID, as well as a field consisting of a sequence of integers to identify the earliest to latest entries for that appointment.

I would like to display the data in SSRS exactly as I have it displayed below in "Example 1" with the first row being column headers and every subsequent row being column values with the caveat that I would like to nest rows with the value "CHANGE" under their associated Appointment_ID such that they are initially hidden and only visible once the user elects to expand the report by clicking on an Appointment_ID (or symbol next to the ID) so that all rows associated with that Appointment_ID are displayed. See "Example 2" below for a visual representation of how I would like the data to be displayed when a user clicks on an Appointment_ID.

Example 1: Initial Display of Data Upon Report Load

...etc.

Example 2: First Appointment_ID from Example 1 Expanded to Display Additional Entries Marked "CHANGE"

...etc.

And if I am being greedy, I wouldn't mind applying some indenting on those CHANGE value rows when the Appointment_ID is expanded.

My Failure

My first impression was to split the data up into two data sets by altering the where clause in my query to create a data set consisting of only rows with the value "INITIAL" and another dataset consisting of rows with the value "CHANGE" and to split the dataset up into two .rdl files so that I could make use of SSRS's subreporting feature by creating a parameter linking the Appointment_IDs, but the report simply will not load when I introduce the CHANGE dataset as a subreport. Apparently, subreports are really slow, and the data I have consists of about 100,00 rows right now with new rows being added every day. There are also a lot more columns that I want to display than what I have included in my example above. The SQL query I am using right now is using CTEs, potentially I could optimize the query a bit by using #temp tables instead, but I am not sure if that would work, and I would rather not go the subreporting route anyway since the subreport housing the CHANGE appointment rows would only ever be used in this one report rather than a several that would justify a subreporting approach.

My Sentiments

I feel there has to be a simpler way of achieving what I want that does not affect performance as much as a subreport, but none of my searching and reading of tutorials has provided me with solutions.

My Question

How can I display the data in my report so that rows with the value CHANGE are initially hidden until an Appointment_ID is expanded?

CodePudding user response：

This is not perfect but it's close.

The first thing I did was to expand the data in the dataset query so we have separate header and detail columns. It's only a single dataset but just more columns.

This is the query I used to recreate your sample data and to return it from the dataset query.

DECLARE @t TABLE (Appointment_ID varchar(20), Student varchar(20),  App_Location    varchar(20), Creation_Date date,    App_Change_Indicator    varchar(20), App_Change_Seq int)
INSERT INTO @t VALUES 
('d9fs73l41', 'Bob M.', 'COJ_123', '2020/7/7',  'INITIAL', 0), 
('d9fs73l41', 'Bob M.', 'ZOOM', '2020/8/7', 'CHANGE', 1), 
('d9fs73l41', 'Bob M.', 'TELEPHONE', '2020/9/7',    'CHANGE', 2), 
('d0s83jfos', 'Jul S.', 'ZOOM', '2020/3/8', 'INITIAL', 0), 
('dofs09sje', 'Ted B.', 'TELEPHONE', '2020/4/6',    'INITIAL', 0)


SELECT 
          hdr.Appointment_ID as hdr_Appointment_ID
        , hdr.Student as hdr_Student
        , hdr.App_Location as hdr_App_Location
        , hdr.Creation_Date as hdr_Creation_Date
        , hdr.App_Change_Indicator as hdr_App_Change_Indicator
        , hdr.App_Change_Seq as hdr_App_Change_Seq
        , det.*
    FROM (SELECT * FROM @t WHERE App_Change_Seq = 0) hdr 
        LEFT JOIN (SELECT * FROM @t WHERE App_Change_Seq != 0) det
            ON hdr.Appointment_ID = det.Appointment_ID

I added a simple table to the report to show the raw data as it comes from the dataset query for reference.

I then added a new table to the report and dragged all the columns except Appointment_ID to it in order.

Next, in the rowgroup panel under the main designer, I right-clicked the "details" group and did "Add Group -- Parent Group" Selected hdr_AppointmentID as the field to group by and selected the option to add a group header

Next I just selected each hdr_* copy of the fields into each column in the newly created row.

Finally, I right clicked the row header for the detail row, selected "Row Visibility"

Then I set the row to initially be hidden and the toggle item to the name of the textbox containing the hdr_appointment_ID field.

The final output initially looks like this

And if I expand the third row it shows this...

Unfortunately, if you expand rows with no detail it still expands but shows a blank row. There might be a way around this but it's 10:30 pom here so I'm not at my best ! :)

For you indents, now that the detail is on a different row, you can format that how you like.