INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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

CODE

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:

CODE

[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:

CODE

[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

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

(OP)
Smiplified a bit to remove unused code.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close