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

Question

问题:将给定日期范围的数据与前三年的相同数据进行比较，并在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

因此，对于这些数据，我想将其与前一年(S)在相同日期范围内的工厂表现进行比较(最多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.'

如果这是执行该项目的方法，那么如何将存储的proc中的表公开给SSRS？我是否必须通过将它转换成另一个TVF来摸索自己的方式？我查阅了SET和sp_describe_first_result_set，但这些似乎并不是可行的解决方案。我是否应该将存储过程的所有内脏卷到第一个电视中？，我可以保存存储的进程并使用CTEs来代替temp表吗？

这就是SSAS的目的吗？

Answer 1

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

我的报告中有4个数据集，其中一个包含时间计数和CountPrime，另一个包含Prime -1、Prime-2、Prime-3。我正在使用的功能，从我的职位最顶端；

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

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

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

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

当我在SSRS中使用了LOOKUP()函数后，情况就一帆风顺。

Answer 2

我相信你可以通过做以下事情得到你想要的：

(1)将函数和过程组合成1条select语句。

(2)按年份将分组添加到单个选择

(3)我添加了递归CTE来生成“小时”维度，并使用它而不是从实际数据中获取小时；这将显示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参数的年数，将其传递给过程，但也可能使用它来隐藏不希望在报表上显示的列。