Query to help create RDL

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

 

CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

       @view varchar(50)

)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

       --DECLARE @view varchar(50)

       --SET @view = 'V_EVALUATIONS'

      

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME  + '">

       ' + c.NAME  + '

       ' +

       CASE t.NAME

       WHEN 'varchar' THEN 'System.String'

       WHEN 'int' THEN 'System.Int32'

       ELSE 'true'

       END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

---COPY & PASTE INTO COLUMN SECTION  --

SELECT

'

       1.5in

' as 'COPY & PASTE INTO COLUMN SECTION  -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

 

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

'

      

              + c.NAME + '">

                     true

                     true

                    

                          

                                 

                                        

                                                ' + REPLACE(c.NAME,'_',' ') + '

                                               

                                        

                                 

                                 

                          

                    

            Textbox' + c.NAME + '

           

                          

                           SteelBlue

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

'

      

              +c.NAME+'">

                     true

                     true

                    

                          

                                 

                                        

                                                =Fields!'+ c.NAME + '.Value

                                               

                                        

                                 

                                 

                          

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

 

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

'

      

              =IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

      

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

 

 

GO