How Do I Reference Data Table Columns When The Column Names Are Unknown in RDLC Report.

[Auguy]

Windows app with RDLC report. Two questions.

1. How do I reference the columns in the report when I don't know the column names?
2. How do I get a sum of columns on each row when I don't know the column names?

The data is coming from an SQL Pivot command and the column names and number of columns may vary, but never more than ten. At run time I do know the column names so I could pass them as parameters. But then how do I construct the formula for the total at the end of the row?. If I had to I guess I could sum them before the data gets to the RDLC, but that still leaves me with question # 1.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

I think I'm closer to the solution. Here is what I have done so far in the SQL proc.

DECLARE   @PivotAsColumns AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
DECLARE   @SQLQuery AS NVARCHAR(MAX)

-- Drop Temp Table
IF OBJECT_ID('tempdb..#WoID1') IS NOT NULL
    DROP TABLE #WoID1
IF OBJECT_ID('tempdb..#WoID2') IS NOT NULL
    DROP TABLE #WoID2

SELECT WoCategoryID, RptColumn INTO dbo.#WoID1
FROM (Select Distinct Case When RptColumn > 0 Then WoCategoryID + ' AS RptCol' + RIGHT('00' + CAST(RptColumn AS varchar(2)),2) Else 'OTHER' End As WoCategoryID, 
	Case When RptColumn > 0 Then RptColumn Else 999 End As RptColumn
From dbo.WoCategory) As x
Order By RptColumn

SELECT @PivotAsColumns = COALESCE(@PivotAsColumns + ',','') + QUOTENAME(WoCategoryID)
FROM (SELECT WoCategoryID FROM dbo.#WoID1) AS PivotExample2

Select @PivotAsColumns

SELECT WoCategoryID, RptColumn INTO dbo.#WoID2
FROM (Select Distinct Case When RptColumn > 0 Then 'RptCol' + RIGHT('00' + CAST(RptColumn AS varchar(2)),2) Else 'OTHER' End As WoCategoryID, 
	Case When RptColumn > 0 Then RptColumn Else 999 End As RptColumn
From dbo.WoCategory) As Junk
Order By RptColumn

-- Select this for Column Headings in report
SELECT WoCategoryID, RptColumn FROM dbo.#WoID2

SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(WoCategoryID)
FROM (SELECT WoCategoryID FROM dbo.#WoID2) AS PivotExample

SELECT @PivotColumns
--PRINT @PivotColumns
   
-- Create the dynamic query with all the values for pivot column at runtime
-- If RptColumn is 0 then make Cat ID = 'OTHER' to capture rest of Cat $ in one column

SELECT @SQLQuery =
    N'SELECT *
FROM (
    SELECT 
        JM.JobNbr, JM.JobMasterPK, JM.SalesmanFK, JM.City, JM.StateCode,
		Case When RptColumn > 0 Then ''RptCol'' + RIGHT(''00'' + CAST(RptColumn AS varchar(2)),2) Else ''OTHER'' End As WoCategoryID, 
        JD.Price as Amount,
		Isnull(CU.FirstName, ''N/A'') As CustFirstName,
		Isnull(CU.LastName, ''N/A'') As CusttName,
		Isnull(USR.UserID, ''N/A'') As UserID,
		Isnull(USR.FirstName, ''N/A'') As SalesFirstName,
		Isnull(USR.LastName, ''N/A'') As LastName

    FROM dbo.JobDetail JD

	Inner Join dbo.WoType WT on JD.WoTypeFK = WT.WoTypePK
	Inner Join dbo.WoCategory WC on WT.WoCategoryFK = WC.WoCategoryPK
	Inner Join dbo.JobMaster JM on JD.JobMasterFK = JM.JobMasterPK
	Left Outer Join dbo.Customer CU on JM.CustomerFK = CU.CustomerPK
	Left Outer Join dbo.UserID USR on JM.SalesmanFK = USR.UserPK

	Where JD.Price <> 0
) as src

PIVOT
(
    SUM(Amount)
    FOR WoCategoryID IN (' + @PivotColumns + ')
) as piv

Order BY JobNbr'

--SELECT @SQLQuery
--PRINT @SQLQuery

-- Execute dynamic query
EXEC sp_executesql @SQLQuery

-- Drop Temp Table
IF OBJECT_ID('tempdb..#WoID1') IS NOT NULL
    DROP TABLE #WoID1
IF OBJECT_ID('tempdb..#WoID2') IS NOT NULL
    DROP TABLE #WoID2

The @PivotAsColumns produces something like this:

[ROOFING AS RptCol01],[SIDING AS RptCol02],[SHUTTER AS RptCol03],[ENCLOSURE AS RptCol04],[DOOR AS RptCol05],[WINDOW AS RptCol06],[TROUGH AS RptCol07],[BRICK AS RptCol08],[CONCRETE AS RptCol09],[OTHER]

The @PivotColumns produces something like this:

[RptCol01],[RptCol02],[RptCol03],[RptCol04],[RptCol05],[RptCol06],[RptCol07],[RptCol08],[RptCol09],[OTHER]

This should allow me to create the report and sum the columns for each row and column and because now I know the column names and they are not dependent on the real column name. Still have some work to do if there are less than 10 columns, but it's a start. Any suggestions or improvements are welcome.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

Smiplified a bit to remove unused code.

DECLARE   @PivotColumns AS NVARCHAR(MAX)
DECLARE   @SQLQuery AS NVARCHAR(MAX)

-- Drop Temp Table
IF OBJECT_ID('tempdb..#WoID2') IS NOT NULL
    DROP TABLE #WoID2

SELECT WoCategoryID, ColName, RptColumn INTO dbo.#WoID2
FROM (Select Distinct 
	Case When RptColumn > 0 Then WoCategoryID Else 'OTHER' End As WoCategoryID, 
	Case When RptColumn > 0 Then 'RptCol' + RIGHT('00' + CAST(RptColumn AS varchar(2)),2) Else 'OTHER' End As ColName, 
	Case When RptColumn > 0 Then RptColumn Else 999 End As RptColumn
From dbo.WoCategory) As Junk
Order By RptColumn

-- Select this for Column Headings in report
SELECT WoCategoryID, ColName, RptColumn FROM dbo.#WoID2

SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(ColName)
FROM (SELECT ColName FROM dbo.#WoID2) AS PivotExample

--SELECT @PivotColumns
--PRINT @PivotColumns
   
-- Create the dynamic query with all the values for pivot column at runtime
-- If RptColumn is 0 then make Cat ID = 'OTHER' to capture rest of Cat $ in one column

SELECT @SQLQuery =
    N'SELECT *
FROM (
    SELECT 
        JM.JobNbr, JM.JobMasterPK, JM.SalesmanFK, JM.City, JM.StateCode,
		Case When WC.RptColumn > 0 Then ''RptCol'' + RIGHT(''00'' + CAST(WC.RptColumn AS varchar(2)),2) Else ''OTHER'' End As WoCategoryID, 
        JD.Price as Amount,
		Isnull(CU.FirstName, ''N/A'') As CustFirstName,
		Isnull(CU.LastName, ''N/A'') As CusttName,
		Isnull(USR.UserID, ''N/A'') As UserID,
		Isnull(USR.FirstName, ''N/A'') As SalesFirstName,
		Isnull(USR.LastName, ''N/A'') As LastName

    FROM dbo.JobDetail JD

	Inner Join dbo.WoType WT on JD.WoTypeFK = WT.WoTypePK
	Inner Join dbo.WoCategory WC on WT.WoCategoryFK = WC.WoCategoryPK
	Inner Join dbo.JobMaster JM on JD.JobMasterFK = JM.JobMasterPK
	Left Outer Join dbo.Customer CU on JM.CustomerFK = CU.CustomerPK
	Left Outer Join dbo.UserID USR on JM.SalesmanFK = USR.UserPK

	Where JD.Price <> 0
) as src

PIVOT
(
    SUM(Amount)
    FOR WoCategoryID IN (' + @PivotColumns + ')
) as piv

Order BY JobNbr'

--SELECT @SQLQuery
--PRINT @SQLQuery

-- Execute dynamic query
EXEC sp_executesql @SQLQuery

-- Drop Temp Table
IF OBJECT_ID('tempdb..#WoID2') IS NOT NULL
    DROP TABLE #WoID2

Auguy
Sylvania/Toledo Ohio