locked
SSRS+Dynamic fields RRS feed

  • Question

  • Hi,

    Please can anyone help me

    I use this query:

    USE [Reporting]
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    /******************************************************************************
    Script Name :
    Object :
    Author :
    Date :
    :
    ******************************************************************************
    /**/

    ******************************************************************************
    :
    :
    :


    */


    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.VisuTempBusinessReport') AND type in (N'P'))
    DROP PROCEDURE dbo.VisuTempBusinessReport
    GO

    CREATE PROCEDURE  [dbo].[VisuTempBusinessReport]   @Office As nvarchar(MAX)='1304,1316,1314',
                                                       @BusinessLine As nvarchar(MAX)='P,H,C',
                      @Specialism AS nvarchar(MAX)='CE120,ZZ151,ZZ101',
    --           @Boss AS nvarchar(MAX),
              @Year As nvarchar(4) ='2014',
              @Month AS nvarchar(MAX)='1,2,3,4,5,6,7,8'
    AS
    BEGIN 
    SET NOCOUNT ON;
    DECLARE @columns NVARCHAR(MAX),@columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns2 NVARCHAR(MAX),@columns3 NVARCHAR(MAX);

    IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##TempParamOffice') AND type in (N'U')) 
    Begin
    DROP TABLE ##TempParamOffice
    end

    IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##TempParamBusinessLine') AND type in (N'U')) 
    Begin
    DROP TABLE ##TempParamBusinessLine
    end

    IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##TempParamSpecialism') AND type in (N'U')) 
    Begin
    DROP TABLE ##TempParamSpecialism
    end

    IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..TempFees') AND type in (N'U')) 
    Begin
    DROP TABLE TempFees
    end

    SELECT * INTO ##TempParamOffice FROM [dbo].[Split](@Office,',')
    SELECT * INTO ##TempParamBusinessLine FROM [dbo].[Split](@BusinessLine,',')
    SELECT * INTO ##TempParamSpecialism FROM [dbo].[Split](@Specialism,',')



    SELECT @columns1 =
     STUFF(( 
     SELECT  ',' + QUOTENAME(Value)
    FROM dbo.SplitString(@Month,',')

    FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)') 
                            , 1, 1, '');

    select @columns2 = STUFF(( 
     SELECT  ',sum( isnull (p.' + QUOTENAME(Value)+',0) '+'+ isnull (p1.' + QUOTENAME(Value)+',0)) as ' + QUOTENAME(Value)
    FROM dbo.SplitString(@Month,',')
    FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)') 
                            , 1, 1, '');


    SET @sql = N'
    SELECT distinct ' + @columns2 + '--,p1.[CALF_NPERIODE],p.[CALF_AN]

       FROM 
       SCHHAYS.dbo.WTEFAC EF 
      INNER JOIN  SCHHAYS.dbo.WTFAC F ON EF.EFAC_NUM = F.EFAC_NUM 
      INNER JOIN  SCHHAYS.dbo.WTFACINFO BS ON F.FAC_NUM = BS.FAC_NUM 
      INNER JOIN SCHHAYS.dbo.WTLFAC LF ON F.FAC_NUM = LF.FAC_NUM 
      INNER JOIN SCHHAYS.dbo.WTRUBVARIANTEFAC WRU ON LF.RINT_ID = WRU.RINT_ID 
      INNER JOIN SCHHAYS.dbo.WTACUMFAC WTA ON WRU.RUV_ID = WTA.RUV_ID 
      INNER JOIN SCHHAYS.dbo.WTCUMFAC WTA1 ON WTA.CUM_ID = WTA1.CUM_ID 
      INNER JOIN SCHHAYS.dbo.WTVTAT TAT ON BS.TIE_ID = TAT.TIE_ID AND BS.RFAN_ID = TAT.RFAN_ID AND BS.PER_ID = TAT.PER_ID AND BS.CNT_ID = TAT.CNT_ID
      INNER JOIN SCHHAYS.dbo.WTTIEINT INT ON (TAT.TIE_ID = INT.TIE_ID) AND (TAT.VTAT_IORDRE = INT.TIEI_ORDRE) 
      INNER JOIN SCHHAYS.dbo.PYCONTRAT CC ON TAT.PER_ID = CC.PER_ID AND TAT.CNT_ID = CC.CNT_ID 
      INNER JOIN SCHHAYS.dbo.CMTIERS T ON TAT.TIE_ID = T.TIE_ID
      INNER JOIN SCHHAYS.dbo.WTMISS M ON CC.PER_ID = M.PER_ID AND CC.CNT_ID = M.CNT_ID 
      INNER JOIN SCHHAYS.dbo.WTCNTI COT1 ON M.PER_ID = COT1.PER_ID AND M.CNT_ID = COT1.CNT_ID
      INNER JOIN SCHHAYS.dbo.WTQUAEU Q ON COT1.TIE_ID = Q.TIE_ID AND COT1.QEU_CDE = Q.QEU_CDE
      INNER JOIN SCHHAYS.dbo.PYMTEMBAUCHE R ON M.MITEMB_ID = R.MITEMB_ID 
      INNER JOIN SCHHAYS.dbo.WTSCCT C ON CC.RGPCNT_ID = C.RGPCNT_ID AND CC.PER_ID = C.PER_ID AND CC.CNT_ID = C.CNT_ID AND''SECT3'' = C.STTQ_COD 
      INNER JOIN SCHHAYS.dbo.WTVAPO D ON C.STTQ_COD = D.STTQ_COD AND C.VAPO_CODE = D.VAPO_CODE
      INNER JOIN HaysStaff HF ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
      INNER JOIN 
    (SELECT 
      [CALF_AN]
          ,[CALF_NPERIODE]
          ,[CNT_ID]
          --,[PER_ID]
        ,[MontantAccoss]
      from [Reporting].[dbo].[TEMPAccoss]
      WHERE [CALF_AN] ='+@Year+'
    ) AS j
    PIVOT
    (
     sum([MontantAccoss])
      for [CALF_NPERIODE] in ('+@columns1+')
    ) AS p  ON p.CNT_ID = COT1.CNT_ID 
     INNER JOIN 
    (SELECT 
      [CALF_AN]
          ,[CALF_NPERIODE]
          ,[CNT_ID]
          --,[PER_ID]
        ,[MontantHorsAccoss]
      from [Reporting].[dbo].[TEMPHorsAccoss]
      WHERE  [CALF_AN] ='+@Year+'
    ) AS j
    PIVOT
    (
     sum([MontantHorsAccoss])
      for [CALF_NPERIODE] in ('+@columns1+')
    ) AS p1  ON p1.CNT_ID = COT1.CNT_ID
    WHERE WTA1.CUM_CODE=''HT''
    AND HF.[businessline_id]='''+@BusinessLine+'''
    AND HF.[offices_id]='''+@Office+'''
    AND HF.[specialism_id]='''+@Specialism+'''
    '
    ;
    PRINT @sql;
    EXEC sp_executesql @sql;

    END 

    and the problem is that I don't know how can I do to  display the results into SSRS because the fields are dynamic.

    thanks for ur help 


    ghada

    Thursday, September 18, 2014 4:17 PM

Answers

  • Hi ghada,

    According to your description, you want to display data from a stored procedure which will output result dynamically. Right?

    In Reporting Services, when query in dataset will only be executed one time when retrieving data. Once the data is retrieved, the query will not execute until next time rendering the report. So your requirement can't be achieved in Reporting Servcies currently. It's the feature by design.

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Sunday, September 21, 2014 2:35 PM