SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Columns from Stored Proc Dataset


Dynamic Columns from Stored Proc Dataset

Author
Message
sluedeke
sluedeke
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
Howdy,
I'm running into a little problem creating a report using a stored proc dataset that returns dynamic columns.

The Report
The client has requested a report where they can select a date range, select one or all Sites, select one or all Traits. The report needs to have each Trait as a column and each row as one Site.

The SQL
I wrote a stored proc that gives me exactly what the client needs in the report. I pass different parameters in, the columns change for each site. I had to use the PIVOT command since the customers can add/remove Traits whenever they want and the result has to be dynamic so we don't have to go in and keep changing the report every time they add or remove a Trait. The Stored Proc runs beautifully!

The Problem
I'm trying to pull the data into a SSRS Report where the Client can run this at any time, passing in a date range, selecting one or all Sites (Rows), selecting one or all Traits (Columns) - easy, Report Parameters, done. I add the stored proc as a dataset, select Stored Proceedure, click OK, click Run, enter 4 parameters (@StartDate, @EndDate, @SiteKey, @TraitKey) and it runs beautifully. Now Add this to the report. I'm thinking I need to add this to a Matrix instead of a Table. Now the Stored Proc has no Fields to choose from, the fields are dynamic, well the first 2 fields (Site and EffectiveDate) are static but the rest could be just one column or 15 columns depending on what the Client selected. And they can add/remove traits from the database whenever they want.

How do I get the fields from the Stored Proc that has none when I added it and the Fields are dynamic depending on what the Client Selects. BTW, the Default is all Sites and All Traits in the past 30 days, customer would just change the criteria after it pulls up the first time.

I've been googling all day long for a solution and can't seem to find one. Any help is very much appreciated.

Thanks!
Shay

Thanks!
Shay
sluedeke
sluedeke
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
Here is the Stored Proc


DECLARE @cols NVARCHAR(2000),
@query NVARCHAR(4000)

/*
SET @StartDate = '10/01/2002'
SET @EndDate = '10/31/2008'
SET @SiteKey = '88,89,90,91'
SET @TraitKey = '1,2,3,4,5,6,7,8,9,10,11'
*/

SELECT @cols = STUFF((SELECT DISTINCT TOP 100 PERCENT '], [' + T.Trait_Description
FROM Traits AS T WITH (NOLOCK)
WHERE T.Trait_Key IN (SELECT Param FROM FP_MVParam_FN(@TraitKey, ','))
ORDER BY '], [' + T.Trait_Description
FOR XML PATH('')),1,2,'') + ']'

SET @query = N'SELECT Site_Description AS Site, TraitAssignment_Effective_Date AS [Effective Date], ' + @cols +
' FROM
(SELECT S.Site_Key, S.Site_Description, TA.TraitAssignment_Effective_Date, T.Trait_Description, TV.TraitValue_Description
FROM Sites AS S WITH (NOLOCK) INNER JOIN
Trait_Assignments AS TA WITH (NOLOCK) ON S.Site_Key = TA.TraitAssignment_Site_Key INNER JOIN
Trait_Assignment_Values AS TAV WITH (NOLOCK) ON TA.TraitAssignment_Key = TAV.TraitAssignmentValue_TraitAssignment_Key INNER JOIN
Trait_Values AS TV WITH (NOLOCK) ON TAV.TraitAssignmentValue_TraitValue_Key = TV.TraitValue_Key INNER JOIN
Traits AS T WITH (NOLOCK) ON TV.TraitValue_Trait_Key = T.Trait_Key) AS P
PIVOT
(MAX([TraitValue_Description])
FOR Trait_Description IN (' + @cols + ' )) AS PVT
WHERE TraitAssignment_Effective_Date BETWEEN ''ThisStartDate'' AND ''ThisEndDate'' AND Site_Key IN (ThisSiteKey)
ORDER BY Site, [Effective Date] DESC;'

SET @query = REPLACE(@query, 'ThisStartDate', @StartDate)
SET @query = REPLACE(@query, 'ThisEndDate', @EndDate)
SET @query = REPLACE(@query, 'ThisSiteKey', @SiteKey)

EXECUTE (@query)




Thanks!
Shay
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search