Tip

Create formatted report files with stored procedures

This stored procedure allows you to create formatted report files that can be distributed to a reporting system by using stored procedures and ISQL. These can then be scheduled as a job to run monthly or even daily.

The example below is specific to our database of course, so you'll need to modify it to suit your needs. The first section shows the stored procedure and the second section shows the command line that runs in a batch file; the batch file is then scheduled. This one runs monthly and calculates values based on last month's data.

All the data gathered by the SP is printed into a flat file called RANKAR. We then import this file into our reporting system (REVEAL) which allows all of our users to view the report. Notice the PRINT CHAR(12) statements in the SP. The PRINT CHAR(12) causes a page break to occur when the export file is read by the reporting system.

This is great for information that gets reported over and over again, and keeps your users from running large adhoc queries that produce the same results month after month. Also I did some converting of dates so that they are dynamic and the SP can be run any day of the month and it will always report the previous months totals. This is especially important at the end of the year where you might usually just use something like:

Datepart(mm, dateadd(mm, -1, INVOICE_DATE) = 
Datepart(mm, dateadd(mm, -1, Getdate()) and datepart(yy, invoice_date) = Datepart(yy, getdate())

This

    Requires Free Membership to View

would work OK most months, but in January, when you are trying to report December totals, the system would be looking for December of the current year and not last year.

-- Stored Procedure 
CREATE procedure RPT_Top_50_Customers_By_Area as 
   -- This one is uglier than some of the others, mainly because of 
   -- the cursor open/close cycling that is going on.  Basically, the 
   -- idea was to get the top 50 customers for each area, in a single 
   -- report ... but grouping by the area turned out to be the fun part. 
   -- 
   -- Coding Reminders: 
   --    - Handle the "Rank" calculations where two customers can be the same 
   --      rank due to identical sales (i.e. 1,2,2,4,5,5,5,8,9...) 
   --    - Do not lop off number 51 if it is tied with 50. 

begin 
   declare @c_MaxLineCount        int      
                                  -- A "Constant" that will be set, showing the
                                  -- maximum number of lines per page

   declare @m_CurrDate            smalldatetime, 
           @m_MonthName           varchar(10), 
           @m_YearName            char(4), 
              -- Procedure Flow control 
           @m_LineCount           int,  
-- how many lines have been printed to a "page" 
           @m_PageCount           int,  
-- which page is being printed 
              -- Outer Cursor Values 
           @m_CurrentRegion       varchar(30), 
           @m_CurrentArea         varchar(30), 
              -- Inner Cursor Values 
           @m_Customer            varchar(8), 
           @m_CustomerName        varchar(28), 
           @m_MonthlySales        int, 
           @m_YTDSales            int, 
           @m_YTDCosts            int, 
           @m_PrevYrMonthlySales  int, 
           @m_PrevYrYTDSales      int, 
           @m_PrevYrYTDCosts      int, 
              -- Rank tracking Variables 
           @m_CurrentLine         int, 
           @m_CurrentRank         int, 
           @m_SavedYTDSales       int, 
              -- Calculated values 
           @m_YTDPct              int, 
           @m_PrevYrYTDPct        int 

      -- Initialize the "constants" 
   select @c_MaxLineCount = 55   
-- was 65, but reserved two lines for the 'totals' at the end of each page 

      -- The date is used in the report calculations, and is displayed 
      -- in the heading. 
      -- Save it to a variable here, rather than recalculating it for 
      -- each page, as it 
      -- would look really strange to see part of the report say "January 
      -- 28" while another part of the report says "January 29" 
   select @m_CurrDate = getdate() 

      -- Set the initial variable values 
   select @m_MonthName = datename (mm, dateadd(mm, -1, @m_CurrDate)), 
          @m_YearName  = datepart (yyyy, dateadd(mm, -1, @m_CurrDate)), 
          @m_LineCount = @c_MaxLineCount + 20,        
-- Initial value this high forces the columns to print on page 1 
          @m_PageCount = 0 

      -- First get the Region/Area listing, and use it to drive the reports 
   declare AreaCursor cursor fast_forward for 
      select distinct 
         cc.cc1_level3_desc, 
         cc.cc1_level1_desc 
      from 
         cost_center cc 
      where exists ( 
         select 1 
           from invoice_header ih 
          where ih.cost_center = cc.cost_center 
         ) 
      order by 
         cc.cc1_level3_desc, 
         cc.cc1_level1_desc 
   open AreaCursor 
   fetch next from AreaCursor into @m_CurrentRegion, @m_CurrentArea 
   Print Char(12) -- Prints a return character on the first page 
   while @@fetch_status = 0 begin 
       -- Reset the the Saved YTD Sales value each time that the inner 
       -- cursor is opened 
       -- This will solve some truly odd printed values when the last 
       -- YTD sales value of 
       -- one Area happens to be the same YTD sales number as the first 
       -- value of the next Area. 
      select @m_SavedYTDSales = -10000000 
         -- Force a page eject, as well 
      select @m_LineCount = @c_MaxLineCount + 1; 

      declare MonthlyDataCursor cursor fast_forward for 
         select top 50 with ties 
            c.customer_number        as Customer, 
            c.name                   as Customer_Name, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(6), dateadd (mm, -1, getdate()), 112) + '01' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'

                               then case when ih.total_sales is null then 0 else ih.total_sales end 
                               else 0 end)) as Monthly_Sales, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'

                               then case when ih.total_sales is null then 0 else ih.total_sales end 
                               else 0 end)) as YTD_Sales, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'

                               then case when ih.total_cost is null then 0 else ih.total_cost end 
                               else 0 end)) as YTD_Costs, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(6), dateadd (mm, -13, getdate()), 112) + '01' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'

                               then case when ih.total_sales is null then 0 else ih.total_sales end 
                               else 0 end)) as Prev_Year_Monthly_Sales, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'

                               then case when ih.total_sales is null then 0 else ih.total_sales end 
                               else 0 end)) as Prev_Year_YTD_Sales, 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01'

                               then case when ih.total_cost is null then 0 else ih.total_cost end 
                               else 0 end)) as Prev_Year_YTD_Costs 
         from 
            cost_center cc, 
            invoice_header ih, 
            customer c 
         where 
            ( 
              (CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' <= ih.invoice_date and  ih.invoice_date < CONVERT(char(6), dateadd(mm, 0, getdate()), 112) + '01') or

              (CONVERT(char(4), dateadd (mm, -13, getdate()), 112) + '0101' <= ih.invoice_date and ih.invoice_date < CONVERT(char(6), dateadd(mm, -12, getdate()), 112) + '01')

            ) and 
            ih.customer_number not like 'ZZ%' and 
            c.customer_number = ih.customer_number and 
            cc.cost_center = ih.cost_center and 
            cc.cc1_level3_desc = @m_CurrentRegion and 
            cc.cc1_level1_desc = @m_CurrentArea 
      group by 
         c.customer_number, 
         c.name 
      having 
            ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'

                               then case when ih.total_sales is null then 0 else ih.total_sales end 
                               else 0 end)) > 0 
      order by 
         ceiling (sum (case when ih.invoice_date >= CONVERT(char(4), dateadd (mm, -1, getdate()), 112) + '0101' and ih.invoice_date < CONVERT(char(6), getdate(), 112) + '01'

                            then case when ih.total_sales is null then 0 else ih.total_sales end 
                            else 0 end)) desc 
      open MonthlyDataCursor 
         -- get the first line 
      fetch next from MonthlyDataCursor into @m_Customer, @m_CustomerName, 
                                             @m_MonthlySales, @m_YTDSales, @m_YTDCosts, 
                                             @m_PrevYrMonthlySales, @m_PrevYrYTDSales, @m_PrevYrYTDCosts 
         -- And record that this *was* the first line for this area 
      select @m_CurrentLine = 1 
      while @@fetch_status = 0 begin 

            -- Print the page header information 
         if ((@m_LineCount >= @c_MaxLineCount)) begin 
             -- If a previous page has been printed, print the form 
             -- feed before starting the next page 
            if (@m_PageCount > 0) begin 
               print char(12) 
            end 
               -- Increment the page count 
            select @m_PageCount = @m_PageCount + 1 
               -- Print the page heading 
            print @m_MonthName + ', ' + @m_YearName + '                      Top 50 Customers by Area - ' + @m_CurrentRegion + '                    page: ' + cast(@m_PageCount as varchar(5))

            print 'Region: ' + cast(case when @m_CurrentRegion is null then ' ' else @m_CurrentRegion end as char(10)) + '     ' + 'Area: ' + cast(case when @m_CurrentArea is null then ' ' else @m_CurrentArea end as char(30))

            print ' '                                                  -- Title sep 
            print 'Customer  ' + ' ' + 'Customer                    ' + ' ' + '        Monthly' + ' ' + '           YTD ' + ' ' + '         YTD' + ' ' + '    YTD' + ' ' + '     Prior Year' + ' ' + '     Prior Year' + ' ' + '  Prior Year' + ' ' + '  Prior Year' + ' ' + '      Area'

            print 'Number    ' + ' ' + 'Name                        ' + ' ' + '         Sales ' + ' ' + '          Sales' + ' ' + '          PC' + ' ' + '   PC %' + ' ' + '  Monthly Sales' + ' ' + '      YTD Sales' + ' ' + '      YTD PC' + ' ' + '    YTD PC %' + ' ' + '      Rank'

            print '----------' + ' ' + '----------------------------' + ' ' + '---------------' + ' ' + '---------------' + ' ' + '------------' + ' ' + '-------' + ' ' + '---------------' + ' ' + '---------------' + ' ' + '------------' + ' ' + '------------' + ' ' + '----------'

               -- Finally, update the line count to show the number of lines that have been printed since the ^L 
            select @m_LineCount = 6 
         end 

            -- Figure out this customer's rank in this area.  Since 
            -- SQL Server is doing the sorting 
            -- for us, just use the current line number.  The trick 
            -- used here is that you leave the 
            -- Rank value alone if the YTD sales for this line is the 
            -- same as the YTD sales for the 
            -- previous line.  This will give you a
            -- pattern ,2,2,5,6,7,7,9,10 ... if such a pattern is appropriate 
         if (@m_SavedYTDSales != @m_YTDSales) 
            select @m_CurrentRank = @m_CurrentLine 
         select @m_SavedYTDSales = @m_YTDSales 
         
            -- Perform the percent calculations 
         if (@m_YTDSales = 0) 
            select @m_YTDPct = 0 
         else 
            select @m_YTDPct = 100 * (@m_YTDSales - @m_YTDCosts ) / @m_YTDSales 
         if (@m_PrevYrYTDSales = 0) 
            select @m_PrevYrYTDPct = 0 
         else 
            select @m_PrevYrYTDPct = 100 * (@m_PrevYrYTDSales - @m_PrevYrYTDCosts ) / @m_PrevYrYTDSales 
            -- Start printing out the line results 
         print cast((case when @m_Customer is null then '(null)' else @m_Customer end) as char(10)) + ' ' + 
               cast((case when @m_CustomerName is null then '(null)' else @m_CustomerName end) as char(28)) + ' ' + 
               str(@m_MonthlySales, 15) + ' ' + 
               str(@m_YTDSales, 15) + ' ' + 
               str((@m_YTDSales - @m_YTDCosts), 12) + ' ' + 
               str(@m_YTDPct, 7) + ' ' + 
               str(@m_PrevYrMonthlySales, 15) + ' ' + 
               str(@m_PrevYrYTDSales, 15) + ' ' + 
               str((@m_PrevYrYTDSales - @m_PrevYrYTDCosts), 12) + ' ' + 
               str(@m_PrevYrYTDPct, 12) + ' ' + 
               str(@m_CurrentRank,10) 

            -- Increment the line counters for the row that was just printed 
         select @m_LineCount = @m_LineCount + 1,      
            -- Lines printed on the page 
                @m_CurrentLine = @m_CurrentLine + 1   
            -- rows retrieved from inner cursor 

            -- Get the next record 
         fetch next from MonthlyDataCursor into @m_Customer, @m_CustomerName, 
                                                @m_MonthlySales, @m_YTDSales, @m_YTDCosts, 
                                                @m_PrevYrMonthlySales, @m_PrevYrYTDSales, @m_PrevYrYTDCosts 
      end 
      close MonthlyDataCursor 
      deallocate MonthlyDataCursor 
         -- Get the next Area to process 
      fetch next from AreaCursor into @m_CurrentRegion, @m_CurrentArea 
   end 
   close AreaCursor 
   deallocate AreaCursor 
end 
GO 

--- Batch Command Line 
isql -S servername -U username -P "password" -w 200 -d database -Q "exec RPT_Top_50_Customers_By_Area" > d:syscorereportsrankar

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.