Dynamic Columns from Stored Proc Dataset

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply