Using Dynamic SQL for Column Headers in SSRS Report Builder Execute(@Query) - Use Values From 1 Table as Col Headers for Values From A Different Table

Question

I am using Microsoft SQL Server Report Builder 3.0 on SQL Server Reporting Services 2012.

I have a stored procedure that uses dynamic SQL. It works great when executing it in SQL, but when I use this as a data set in Report Builder, it doesn't load any data fields.

The reason I wrote this as dynamic SQL is because I want to use field values from a specific table as column headers for other fields accessed from different tables in my result set.

Below is a portion of the code from the sp. The actual sp joins to many more tables and pulls more data fields.

DECLARE @INRM as char(30)
        ,@OUTRM as char(30)
        ,@PROCSTART as char(30)
        ,@PROCEND as char(30)
        ,@Query as NVARCHAR(MAX)

SET NOCOUNT ON;

SELECT ST.SurTimeID, ST.Name
INTO #STL
FROM [livefdb].[dbo].[SurTime_Main] as ST

**SET @INRM = (select c.Name
            from #STL as c
            where c.SurTimeID = 'INRM')
SET @OUTRM = (select c.Name
            from #STL as c
            where c.SurTimeID = 'OUTRM')
SET @PROCSTART = (select c.Name
                from #STL as c
                where c.SurTimeID = 'PROCSTART')
SET @PROCEND = (select c.Name
                from #STL as c
                where c.SurTimeID = 'PROCEND')**

DROP TABLE #STL

-- Insert statements for procedure here
SET @Query = 
'SELECT CAST(CIO.IntraoperativeOperationDateTime as date) as [Operation Date]
    ,REG.AccountNumber as [Account Number]
    ,HRM.Name as [Patient Name]
    ,CSE.OperatingRoom_CwsResRoomID as [Operating Room]
    ,SRG.NameWithCredentialsStored as [Case Surgeon]
    ,ISNULL(CASE    WHEN CONVERT(DATE,INRM.IntraoperativeDateTime) = ''1900-01-01'' THEN ''''
                    ELSE CONVERT(VARCHAR(23),INRM.IntraoperativeDateTime,121)
                    END,'''') as **['+ @INRM +']**
    ,ISNULL(CASE    WHEN CONVERT(DATE,PS.IntraoperativeDateTime) = ''1900-01-01'' THEN ''''
                    ELSE CONVERT(VARCHAR(23),PS.IntraoperativeDateTime,121)
                    END,'''') as **['+ @PROCSTART +']**
    ,ISNULL(CASE    WHEN CONVERT(DATE,PE.IntraoperativeDateTime) = ''1900-01-01'' THEN ''''
                    ELSE CONVERT(VARCHAR(23),PE.IntraoperativeDateTime,121)
                    END,'''') as **['+ @PROCEND +']**
    ,ISNULL(CASE    WHEN CONVERT(DATE,OUTRM.IntraoperativeDateTime) = ''1900-01-01'' THEN ''''
                    ELSE CONVERT(VARCHAR(23),OUTRM.IntraoperativeDateTime,121)
                    END,'''') as **['+ @OUTRM +']**

FROM [livefdb].[dbo].[SurCase_Main] as CSE with (nolock)

INNER JOIN [livefdb].[dbo].[SurCase_Inop] as CIO with (nolock)
on CIO.SourceID = CSE.SourceID
    AND CIO.CwsApptID = CSE.CwsApptID

**LEFT JOIN [livefdb].[dbo].[SurCase_InopTimes] as INRM with (nolock)
on INRM.SourceID = CSE.SourceID
    AND INRM.CwsApptID = CSE.CwsApptID
    AND INRM.IntraoperativeTimeLabel_SurTimeID = ''INRM''**

**LEFT JOIN [livefdb].[dbo].[SurCase_InopTimes] as OUTRM with (nolock)
on OUTRM.SourceID = CSE.SourceID
    AND OUTRM.CwsApptID = CSE.CwsApptID
    AND OUTRM.IntraoperativeTimeLabel_SurTimeID = ''OUTRM''**

**LEFT JOIN [livefdb].[dbo].[SurCase_InopTimes] as PS with (nolock)
on PS.SourceID = CSE.SourceID
    AND PS.CwsApptID = CSE.CwsApptID
    AND PS.IntraoperativeTimeLabel_SurTimeID = ''PROCSTART''**

**LEFT JOIN [livefdb].[dbo].[SurCase_InopTimes] as PE with (nolock)
on PE.SourceID = CSE.SourceID
    AND PE.CwsApptID = CSE.CwsApptID
    AND PE.IntraoperativeTimeLabel_SurTimeID = ''PROCEND''**

LEFT JOIN [livefdb].[dbo].[CwsAppt_Main] as APT with (nolock)
on APT.SourceID = CSE.SourceID
    AND APT.CwsApptID = CSE.CwsApptID

LEFT JOIN [livefdb].[dbo].[RegAcct_Main] as REG with (nolock)
on REG.SourceID = APT.SourceID
    AND REG.VisitID = APT.VisitID

LEFT JOIN [livefdb].[dbo].[HimRec_Main] as HRM with (nolock)
on HRM.SourceID = REG.SourceID
    AND HRM.PatientID = REG.PatientID

LEFT JOIN [livefdb].[dbo].[MisPerson_Names] as SRG with (nolock)
on SRG.SourceID = CSE.SourceID
    AND SRG.UnvUserID = CSE.Surgeon_UnvUserID

WHERE CIO.IntraoperativeOperationDateTime
      BETWEEN ''' + CONVERT(VARCHAR(23),@spFromOperationDate,121) + ''' AND '''
          + CONVERT(VARCHAR(23),@spThruOperationDate,121) + ''''

The SurTime_Main table contains types of times and their descriptions...not the actual times themselves.

SurTime_Main Record Example:

SurTimeID = 'INRM' (PK)
Name = 'Into Room'

I want to use the description from this table as a column header (SurTime_Main.Name) for a date/time pulled from the SurCase_InopTimes table. The SurCase_InopTimes table contains the actual times entered and the time types they were entered on.

SurCase_InopTimes Record Examples:

CwsApptID = '1234567890' (PK)
IntraoperativeTimeLabel_SurTimeID = 'INRM' (PK)
IntraoperativeDateTime = '2000-01-01 00:00:00.000'
CwsApptID = '1234567890' (PK)
IntraoperativeTimeLabel_SurTimeID = 'OUTRM' (PK)
IntraoperativeDateTime = '2000-01-01 01:00:00.000'

I hope this makes sense. If our time type descriptions are ever updated in the future, then the report would be ready to run with updated column header descriptions...at least this is what I was going for.

If dynamic SQL is not an option to use for pulling data fields into Report Builder, is there another way I can accomplish this in SQL or Report Builder itself so that my column headers will be values from the SurTime_Main table?

Thank you in advance.

score 0 · Answer 1 · answered Dec 08 '17 at 00:44

I've done something similar in the past.

Basically SSRS always expects your dataset to return the same columns every time it's executed.

So taking a very simple example, instead of returning something like

Creature    Legs    Claws
Dog         4       16
Cat         4       18

and then another run returns something like this

Creature    Arms    Fingers
Human       2       10
Zombie      2       3

What I do is return something like this...

Label    ColumnA    ColumnB     CaptionA    CaptionB
Dog         4       16            Legs        Claws
Cat         4       18            Legs        Claws

and next next run with different parameters might return

Label    ColumnA    ColumnB     CaptionA    CaptionB
Human       2       10            Arms        Fingers
Zombie      2        3            Arms        Fingers

In your report design, simply replace the column headers with an expression something like =First(Fields!CaptionA.Value) etc

Thanks Alan. I did figure out before I posted that I could write additional stored procedures and have each sp pull a specific time type record to use for column headers, add them as datasets, and replace the column headers with the expression like what you mentioned. I wasn't sure if there was a better way to accomplish this, but it sounds like it may be the only way to do it. Thank you for your reply! — jeggrad2001, Dec 08 '17 at 21:24

Using Dynamic SQL for Column Headers in SSRS Report Builder Execute(@Query) - Use Values From 1 Table as Col Headers for Values From A Different Table

1 Answers1