主页 / dba / 问题 / 57480
Accepted
RateControl
Asked: 2014-01-25 06:15:25 +0800 CST

在存储过程中向 SSRS 公开临时表

  • 772

问题：将给定日期范围的数据与最多前 3 年的相同数据进行比较，并在 SSRS 报告中显示表格数据和图表。但是目前，我为 SSRS 报告创建的存储过程不起作用，因为它使用临时表，而这些临时表不暴露给 SSRS。我完全明白为什么，SSRS 对我创建的临时表上的结构一无所知。

例如，我想查看从 01/01/2012 到 04/01/2012 制作的计数小部件，按小时分组。

所以表格的逻辑布局是：

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

因此，对于该数据，我想将其与工厂在相同日期范围内的前一年（最多 3 年）的表现进行比较。看起来很简单，但我想出的解决方案却绝非如此。

我已经解决了，我有一个表值函数，它计算给定 start_date 和 end_dates 所需的数据，并从源表中提取数据。我相信这是正确的方法吗？

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

它走下坡路的地方是我构建存储过程以在 SSRS 报告中显示数据的方式。存储过程不需要解释，我所做的事情相当明显。这不是“正确”的方法吗？

 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.'

如果这是执行项目的方法，我如何将存储过程中的表公开给 SSRS？我必须通过将其转换为另一个 TVF 来摸索吗？我查找了 SET'ing FMTONLY 和 sp_describe_first_result_set，但这些似乎不是可行的解决方案。我应该将存储过程的所有内容汇总到第一个 TVF 中吗？，我可以保留存储过程并使用 CTE 代替临时表吗？

这就是 SSAS 的用途吗？

t-sql sql-server-2012

2 个回答

  1. 我相信你可以通过执行以下操作得到你想要的：

    (1) 将函数和过程组合成 1 个选择语句
    (2) 将按年分组添加到单个 SELECT
    (3) 我添加了递归 CTE 以生成“HOUR”维度并使用它而不是从实际数据中获取小时；这将显示小时为 0 然后 

    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) 您可以添加/使用 SSRS 参数来表示年数，将其传递给程序，但也可以使用它来隐藏不希望在报告中显示的年份的列。

    • 1
  2. Best Answer

    我找到了一种剥猫皮的方法，无需学习和使用 SSAS（这并不意味着我不会）。

    我的报告中有 4 个数据集，一个包含小时数和 CountPrime，其他包含 Prime -1、Prime-2、Prime-3。我正在使用帖子顶部的功能；

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

    我只是使用 DATEADD() 来完成前 3 个月的工作。我相信你会注意到，我没有做只抓取用户要求的前几年的逻辑，我总是给出前 3 年。

    进入 SSRS 后，我使用 LOOKUP() 函数从其他数据集中获取结果并将它们聚合到一个表/图表中。

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

    在我让 LOOKUP() 函数在 SSRS 中工作后，一切顺利。

    • 1

相关问题