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
'
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
'
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
