Creating Excel Report From Dynamic Table

  • I have a table with the following structure:

    CREATE TABLE [#TEST] (

     [sCalldate] [varchar]((20) NULL ,

     [sProject] [varchar] (20)  NULL ,

     [dHours] numeric (10,4) NULL) 

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060401', 'A', 12)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060401', 'B', 0)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060401', 'C', 2)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060402', 'A', 2)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060402', 'B', 5)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060402', 'C', 4)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060403', 'A', 3)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060403', 'B', 5)

    INSERT [#TEST] (sCalldate,sProject, dHours)

    VALUES ('20060403', 'C', 1)

    I need to create an excel report that contains the calldate down the first column and the projects as cloumn headings across the top with the amount of hours worked each projects per day.

    I created a cross tab table with project codes across the top as columns. One major issue here is that project codes are created daily. One day I can have 3 next day I can have 5.

    So I have the table and the data populated the way I need, but I can't create the excel spreadsheet in a dts package (this report needs to be run on a nightly basis).

    Can anyone help me? Is there any way of accomplishing this?

    Thanks,

    Ninel

     

     

     

     

  • No idea if this will work in DTS, but here is an option.

    I wasn't born stupid - I had to study.

  • You may have better luck exporting the raw data to Excel, and then using a pivot table in Excel.

    Or you could look into OLAP cubes.

    Dynamic cross-tab queries are, in my experience, tricky in T-SQL.

  • Here is a crosstab SP that was posted a while back. I think this is what you are looking for.

     

    EXEC sp_CrossTab

      @table       = 'TEST',

      @onrows      = 'sCalldate',

      @onrowsalias = 'dhours',

      @oncols      = 'sproject',

      @sumcol  =  'dhours'

     

    USE master

    CREATE PROC sp_CrossTab

      @table       AS sysname,        -- Table to crosstab

      @onrows      AS nvarchar(128),  -- Grouping key values (on rows)

      @onrowsalias AS sysname = NULL, -- Alias for grouping column

      @oncols      AS nvarchar(128),  -- Destination columns (on columns)

      @sumcol      AS sysname = NULL  -- Data cells

    AS

    --LISTING 4: Step 1 of the sp_CrossTab Stored Procedure: Beginning of the SQL String

    DECLARE

      @sql AS varchar(8000),

      @NEWLINE AS char(1)

    SET @NEWLINE = CHAR(10)

    -- step 1: beginning of SQL string

    SET @sql =

      'SELECT' + @NEWLINE +

      '  ' + @onrows +

      CASE

        WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias

        ELSE ''

      END

    --LISTING 5: Step 2 of the sp_CrossTab Stored Procedure: Storing Keys in a Temp Table

    CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

    DECLARE @keyssql AS varchar(1000)

    SET @keyssql =

      'INSERT INTO #keys ' +

      'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +

      'FROM ' + @table

    EXEC (@keyssql)

    --LISTING 6: Step 3 of the sp_CrossTab Stored procedure: Middle Part of SQL String

    DECLARE @key AS nvarchar(100)

    SELECT @key = MIN(keyvalue) FROM #keys

    WHILE @key IS NOT NULL

    BEGIN

      SET @sql = @sql + ','                   + @NEWLINE +

        '  SUM(CASE CAST(' + @oncols +

                         ' AS nvarchar(100))' + @NEWLINE +

        '        WHEN N''' + @key +

               ''' THEN ' + CASE

                              WHEN @sumcol IS NULL THEN '1'

                              ELSE @sumcol

                            END + @NEWLINE +

        '        ELSE 0'                      + @NEWLINE +

        '      END) AS c' + @key

     

      SELECT @key = MIN(keyvalue) FROM #keys

      WHERE keyvalue > @key

    END

    --LISTING 7: Step 4 of the sp_CrossTab Stored Procedure: End of SQL String

    SET @sql = @sql         + @NEWLINE +

      'FROM ' + @table      + @NEWLINE +

      'GROUP BY ' + @onrows + @NEWLINE +

      'ORDER BY ' + @onrows

    -- PRINT @sql  + @NEWLINE -- For debug

    EXEC (@sql)

    GO

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply