Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Problem: Compare data of a given date range to the same data of up to 3 previous years, and display the tabular data and a graph in an SSRS report. However currently, my stored procedure that I have made for the SSRS report doesn’t work, as it uses temp tables, which aren’t exposed to SSRS. I totally get why, SSRS has no clue about the structures on the temp tables I’ve created.

For example, I want to see the count widgets made from 01/01/2012 to 04/01/2012, grouped by hour.

So the logical layout of the table is:

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

So for that data, I want to compare it to how the plant did the previous year(s) (up to 3), for the same date range. It seems so simple, yet the solution that I came up with, is anything but.

I’ve worked it out so that, I have a table-valued function that calculates the required data for the given start_date and end_dates, and pulls the data from the source table. I believe that is the right way to do it?

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

The bit where it goes downhill is the manner in which I have constructed the stored procedure to display the data in the SSRS report. The stored proc doesn’t need explaining, it’s fairly evident of what I have done. Which it cannot be the “right” way to do this?

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

If this is the way to do the project, how can I expose the tables inside of the stored proc to SSRS? Do I have to fumble my way through converting it to another TVF? I looked up SET’ing FMTONLY, and sp_describe_first_result_set, but those did not seem like viable solutions. Should I roll up all guts of the stored proc into the first TVF?,can I keep the stored proc and use CTEs in place of temp tables?

Is this what SSAS is for?

share|improve this question
1  
Yes, SSAS would probably help you a lot here, especially if you plan to ask more/different questions against the data. –  Jon Seigel Jan 24 at 14:35
    
damn, I knew I shouldn't have asked that question :) –  RateControl Jan 24 at 14:37
1  
Sorry. :) I know it's a lot of extra work up-front, but in the end, if you can see more of this coming down the line, it's definitely worth the effort. –  Jon Seigel Jan 24 at 14:42
1  
Totally, plus I've been meaning to dip my toes in the SSAS waters. And, as my grand pappy used to say "never ask a question you don't want the answer to" –  RateControl Jan 24 at 14:48
    
The problem is not that "temp tables aren’t exposed to SSRS", the problem lies in that your sp gives a result set with different number of columns depending on your parameters. That's not allowed in SSRS. You should always return the same number of columns for your dataset –  Lamak Jan 24 at 15:21

2 Answers 2

I believe you can get what you want by doing the following:

(1) combine function and procedure into 1 select statement
(2) add grouping by Year to single SELECT
(3) I added recursive CTE to generate "HOUR" dimension and use this instead of getting hour from actual data; this will show hours with 0 then

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) You can add/use SSRS parameter for number of years, pass that to procedure, but also possibly use it to hide columns for years not desired to show on report.

share|improve this answer
    
Okay, yeah. I like where you're going with this. I'll play around with it. thanks. –  RateControl Jan 27 at 19:27
up vote 0 down vote accepted

I found a way to skin the cat, without learning and using SSAS (which doesn't mean I won't).

I have 4 datasets in my report, one houses the hour count and CountPrime, the others house Prime -1, Prime-2, Prime-3. I am using the function from the very top of my post;

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

I am just using DATEADD() to do the work of getting me the previous 3 months. As I am sure you'll notice, I am not doing logic of grabbing only the previous years that the user asks for, I am always giving the previous 3 years.

Once in SSRS, I am using the LOOKUP() function to grab the results from the other datasets and aggregate them into one table/graph.

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

After I got the LOOKUP() function working in SSRS, it was clear sailing.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.