locked
urgent..Place the column names in rdlc report from a query... RRS feed

  • Question

  • User1182587605 posted

    I have a requirement to use a table's column names as my Tablix column names. Please give me an example on how to do this. To describe my requirement clearly, In a table, I have column names which are StoreIDs. I need to display these StoreIDs as column names in my tablix. Please give me a sample code on how to do this.

    My report output should look like image in below link:

    http://imgur.com/a/Wsy1g  

    My table is RowDstbnMatrix [item],[204],[206],[207],[208]..

    The numbers should come into the columns of RDLC dynamically(They can be added or removed later as they are storeIDs). Please help me know how to do it. This is urgent

     

    Thursday, May 25, 2017 7:30 AM

All replies

  • User1068175894 posted

    have your query return in a single column what you have pivoted to columns

    use a Matrix

    add a column group based on that column 

    ----------------------------
    Please remember to click "Mark as Answer" the responses that resolved your issue.

    Friday, May 26, 2017 5:28 PM
  • User1182587605 posted

    Hi JBetanCourt,

    Thanks for the reply, Based on your reply, I have created a PIVOT query, It returns absolute values. Please look at it once and suggest me what I need to do further on it.

    USE [BSJ_APPLN]
    GO
    /****** Object:  StoredProcedure [dbo].[Project_To_Ship_All_Location]    Script Date: 05/27/2017 11:57:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:         <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[Project_To_Ship_All_Location]
         -- Add the parameters for the stored procedure here
        @FromDate date,@ToDate date
    AS
    BEGIN
         -- SET NOCOUNT ON added to prevent extra result sets from
         -- interfering with SELECT statements.
         SET NOCOUNT ON;
         DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ItemNumber) 
                        from PJ_Item_M
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    
    set @query = 'select * from
    (select Store_Number,ItemNumber, SUM(CAST(Tot_PlanToShip as decimal(9,2))) Qty from PJ_Daily_DbnMatrix_T 
    where 
     variation = '''' and Distbn_Date >= '''+Cast(@FromDate as varchar(15))+''' and
      Distbn_Date <= '''+Cast(@ToDate as varchar(15))+''' group by ItemNumber,Store_Number)t PIVOT
    (
      MAX(Qty)
      FOR ItemNumber IN(' + @cols + ')
    ) AS p;
     '
    
    execute(@query);

    Please help me modify this query if needed and tell me step by step how to work on Matrix column group.

    Thanks,

    Deepak

    Saturday, May 27, 2017 7:12 AM
  • User1068175894 posted

    sorry i had a typo in my answer,

    you don't have to create a pivot query, the matrix will do that for you, i meant to say:

    "have your query return in a single column what you WANT pivoted to columns"

    when you create a column group for that field the matrix will do the pivot for you, check the following article:

    https://msdn.microsoft.com/en-us/library/ms157334(v=sql.100).aspx 

    ---------------------------
    Please remember to click "Mark as Answer" the responses that resolved your issue.

    Sunday, May 28, 2017 4:17 AM