Início / dba / Perguntas / 57480
Accepted
RateControl
Asked: 2014-01-25 06:15:25 +0800 CST

Expor tabelas temporárias ao SSRS no procedimento armazenado

  • 772

Problema: comparar os dados de um determinado intervalo de datas com os mesmos dados de até 3 anos anteriores e exibir os dados tabulares e um gráfico em um relatório do SSRS. No entanto, atualmente, meu procedimento armazenado que criei para o relatório do SSRS não funciona, pois usa tabelas temporárias, que não são expostas ao SSRS. Entendo totalmente o motivo, o SSRS não tem ideia das estruturas nas tabelas temporárias que criei.

Por exemplo, quero ver os widgets de contagem feitos de 01/01/2012 a 01/04/2012, agrupados por hora.

Portanto, o layout lógico da tabela é:

 Hour Count
    0   100
    1   240
    2   34
    3   24
    4   55
    5   90
    …
    23  7657

Portanto, para esses dados, quero compará-los com o desempenho da planta no(s) ano(s) anterior(es) (até 3), para o mesmo intervalo de datas. Parece tão simples, mas a solução que encontrei é tudo menos isso.

Eu trabalhei para que eu tenha uma função com valor de tabela que calcula os dados necessários para as datas_inicial e_final fornecidas e extrai os dados da tabela de origem. Eu acredito que é a maneira certa de fazer isso?

ALTER FUNCTION [dbo].[Count_TS] (@SDate DATETIME2,@EDate DATETIME2)
    RETURNS @t TABLE ([Hour] INT,[Count] INT) AS
    BEGIN
     INSERT @t ([Hour],[Count])
            SELECT DATEPART(HOUR,AC.[FIELDC]) AS [Hour],COUNT(DISTINCT(AC.[FIELDD])) AS [CFS_Count_1]
            FROM [LINKEDSERVER].[DATABASE].[dbo].[TABLE] AS AC WITH (NOLOCK)
            WHERE (AC.[FIELDA] LIKE 'VALUE' OR AC.[FIELDA] LIKE 'VALUE')  
            AND AC.[FIELDB] = 'VALUE'
            AND (CAST(AC.[FIELDC] AS DATE) >= @SDate AND CAST(AC.[FIELDC] AS DATE) <= @EDate)
            GROUP BY DATEPART(HOUR,AC.[FIELDC])

    RETURN
    END

A parte em que desce é a maneira como construí o procedimento armazenado para exibir os dados no relatório do SSRS. O procedimento armazenado não precisa de explicação, é bastante evidente do que eu fiz. Qual não pode ser a maneira “certa” de fazer isso?

 ALTER PROCEDURE [rptCount_TS] 
      @PREYears INT = NULL
     ,@SDate DATE = NULL
     ,@EDate DATE = NULL
    AS
    BEGIN
    IF DATEDIFF(MONTH,@SDate,@EDate) <=3 AND DATEDIFF(YEAR,@SDate,@EDate) =0
    BEGIN

    IF @PREYears = 0
        BEGIN
            IF OBJECT_ID('tempdb..#CountPrime') IS NOT NULL DROP TABLE dbo.[#CountPrime]; 
            CREATE TABLE #CountPrime ([Hour] INT,[CountPrime] INT)
            INSERT INTO #CountPrime([Hour], [CountPrime]) 
                SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
            SELECT  CCP.[Hour]
                        ,CCP.[CountPrime]
            FROM    #CountPrime AS CCP
        END
    ELSE
        IF @PREYears =1
            BEGIN
                IF OBJECT_ID('tempdb..#CountPrime1') IS NOT NULL DROP TABLE dbo.[#CountPrime1]; 
                CREATE TABLE #CountPrime1 ([Hour] INT,[CountPrime] INT)
                IF OBJECT_ID('tempdb..#Count-11') IS NOT NULL DROP TABLE dbo.[#Count-11]; 
                CREATE TABLE [#Count-11] ([Hour] INT,[Count-1] INT)
                INSERT INTO #CountPrime1([Hour], [CountPrime]) 
                                SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                INSERT INTO [#Count-11]([Hour], [Count-1]) 
                    SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                SELECT  CCP.[Hour]
                        ,CCP.[CountPrime]
                        ,SC1.[Count-1]
                FROM    #CountPrime1    AS CCP
                INNER JOIN [#Count-11] AS SC1 ON CCP.[Hour] = SC1.[Hour]
            END
        ELSE
            IF @PREYears =2
                BEGIN
                    IF OBJECT_ID('tempdb..#CountPrime2') IS NOT NULL DROP TABLE dbo.[#CountPrime2]; 
                    CREATE TABLE #CountPrime2 ([Hour] INT,[CountPrime] INT)
                    IF OBJECT_ID('tempdb..#Count-12') IS NOT NULL DROP TABLE dbo.[#Count-12]; 
                    CREATE TABLE [#Count-12] ([Hour] INT,[Count-1] INT)
                    IF OBJECT_ID('tempdb..#Count-22') IS NOT NULL DROP TABLE dbo.[#Count-22]; 
                    CREATE TABLE [#Count-22] ([Hour] INT,[Count-2] INT)     
                    INSERT INTO #CountPrime2([Hour], [CountPrime]) SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                    INSERT INTO [#Count-12]([Hour], [Count-1]) 
                        SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                    INSERT INTO [#Count-22]([Hour], [Count-2]) 
                        SELECT [Hour],[Count] AS [Count-2] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-2,@SDate),DATEADD(YEAR,-2,@EDate));
                    SELECT  CCP.[Hour]
                            ,CCP.[CountPrime]
                            ,SC1.[Count-1]
                            ,SC2.[Count-2]
                    FROM    #CountPrime2    AS CCP
                    INNER JOIN [#Count-12] AS SC1 ON CCP.[Hour] = SC1.[Hour]
                    INNER JOIN [#Count-22] AS SC2 ON CCP.[Hour] = SC2.[Hour]
                END
            ELSE
                IF @PREYears =3
                    BEGIN
                        IF OBJECT_ID('tempdb..#CountPrime3') IS NOT NULL DROP TABLE dbo.[#CountPrime3]; 
                        CREATE TABLE #CountPrime3 ([Hour] INT,[CountPrime] INT)
                        IF OBJECT_ID('tempdb..#Count-13') IS NOT NULL DROP TABLE dbo.[#Count-13]; 
                        CREATE TABLE [#Count-13] ([Hour] INT,[Count-1] INT)
                        IF OBJECT_ID('tempdb..#Count-23') IS NOT NULL DROP TABLE dbo.[#Count-23]; 
                        CREATE TABLE [#Count-23] ([Hour] INT,[Count-2] INT)
                        IF OBJECT_ID('tempdb..#Count-33') IS NOT NULL DROP TABLE dbo.[#Count-33]; 
                        CREATE TABLE [#Count-33] ([Hour] INT,[Count-3] INT)
                        INSERT INTO #CountPrime3([Hour], [CountPrime]) SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                        INSERT INTO [#Count-13]([Hour], [Count-1]) 
                            SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                        INSERT INTO [#Count-23]([Hour], [Count-2]) 
                            SELECT [Hour],[Count] AS [Count-2] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-2,@SDate),DATEADD(YEAR,-2,@EDate));
                        INSERT INTO [#Count-33]([Hour], [Count-3]) 
                            SELECT [Hour],[Count] AS [Count-3] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-3,@SDate),DATEADD(YEAR,-3,@EDate));
                            SELECT  CCP.[Hour]
                                    ,CCP.[CountPrime]
                                    ,SC1.[Count-1]
                                    ,SC2.[Count-2]
                                    ,SC3.[Count-3]
                            FROM    #CountPrime3    AS CCP
                            INNER JOIN [#Count-13] AS SC1 ON CCP.[Hour] = SC1.[Hour]
                            INNER JOIN [#Count-23] AS SC2 ON CCP.[Hour] = SC2.[Hour]
                            INNER JOIN [#Count-33] AS SC3 ON CCP.[Hour] = SC3.[Hour]
                    END
    END
    ELSE
    PRINT 'Sorry, at maximum you can only search a 3 month time span.'

Se esta é a maneira de fazer o projeto, como posso expor as tabelas dentro do procedimento armazenado ao SSRS? Tenho que me atrapalhar na conversão para outro TVF? Pesquisei SET'ing FMTONLY e sp_describe_first_result_set, mas essas não pareciam soluções viáveis. Devo acumular todas as entranhas do procedimento armazenado no primeiro TVF? ,posso manter o procedimento armazenado e usar CTEs no lugar de tabelas temporárias?

É para isso que serve o SSAS?

t-sql sql-server-2012

2 respostas

  1. Acredito que você pode conseguir o que deseja fazendo o seguinte:

    (1) combinar função e procedimento em 1 instrução select
    (2) adicionar agrupamento por ano a um SELECT único
    (3) adicionei CTE recursivo para gerar a dimensão "HORA" e usá-la em vez de obter a hora dos dados reais; isso mostrará horas com 0 então 

    WITH HourOfDay ([hr]) AS  
(  
  SELECT 1 as [hr]  
  UNION ALL  
  SELECT [hr] + 1  
  FROM HourOfDay  
  WHERE [hr] < =24       
) 
SELECT   
    DATEPART(YEAR,AC.[FIELDC]) AS [Year]  
    --DATEPART(HOUR,AC.[FIELDC]) AS [Hour],   
    hod.hr AS [Hour],  
    COUNT(DISTINCT(AC.[FIELDD])) AS [CFS_Count_1]  
FROM [LINKEDSERVER].[DATABASE].[dbo].[TABLE] AS AC WITH (NOLOCK)  
JOIN HourOfDay AS hod  
    ON hod.[hr] = DATEPART(HOUR,AC.[FIELDC])  
WHERE (AC.[FIELDA] LIKE 'VALUE' OR AC.[FIELDA] LIKE 'VALUE')    
AND AC.[FIELDB] = 'VALUE'  
AND (CAST(AC.[FIELDC] AS DATE) >= @SDate AND CAST(AC.[FIELDC] AS DATE) <= @EDate)  
GROUP BY   
    DATEPART(YEAR,AC.[FIELDC]),  
    DATEPART(HOUR,AC.[FIELDC])

    (4) Você pode adicionar/usar o parâmetro SSRS para o número de anos, passá-lo para o procedimento, mas também possivelmente usá-lo para ocultar colunas de anos que não deseja exibir no relatório.

    • 1
  2. Best Answer

    Eu encontrei uma maneira de esfolar o gato, sem aprender e usar o SSAS (o que não significa que não o farei).

    Eu tenho 4 conjuntos de dados em meu relatório, um abriga a contagem de horas e CountPrime, os outros abrigam Prime -1, Prime-2, Prime-3. Estou usando a função desde o início do meu post;

    SELECT *
FROM [Count_TS](DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate))

    Estou apenas usando DATEADD () para fazer o trabalho de obter os 3 meses anteriores. Como tenho certeza que você notará, não estou fazendo a lógica de pegar apenas os anos anteriores que o usuário pede, estou sempre dando os 3 anos anteriores.

    Uma vez no SSRS, estou usando a função LOOKUP() para pegar os resultados dos outros conjuntos de dados e agregá-los em uma tabela/gráfico.

    =Lookup(Fields!Hour.Value,Fields!Hour.value,Fields!Count.value,"Minus1")

    Depois que fiz a função LOOKUP() funcionar no SSRS, foi fácil navegar.

    • 1

relate perguntas