Advanced Reporting 2 - Queues and Reports

by George Cobaugh

Published 1997-12-01    Printer-friendly version

Download the code here

Here we are again, talking about reports. As important as I feel reports to be in an application, I don't have to like them much. In fact, I don't like doing them at all. I can fully understand why some developers like to give some kind of report writer to their end users, and tell them how much better life is when they can design their own reports the way they want to see them. This is great for the developer, less time to spend on formatting and generating reports. But, a really great application will stand alone on its own merit, without any need for other software to complete the package. Also, the less time that the end user has to spend processing the data (reports included) the more valuable that package is.

If you have ever developed a large application with very large database files, then you have seen how important it is to have enough keys in your file to handle any kind of order and selection of data you are going to need for reports. These extra keys can increase the overhead of your application. The files get larger, the time it takes to add a record increases because of the need to update all of the keys. And then, after you have taken great pains to insure that you have all the keys on the file you are going to need, someone asks for a report, or a new sort order on an existing report that requires another key. You have to add the key to your dictionary. This causes a recompile of your complete app (and any other apps you have generated from this dictionary). If your application is distributed to many different sites, then you have to write a conversion program for the data file to go along with the new distribution of your program. A report writer is starting to become very attractive at this point.

Another problem I have run into with reports is the case where you have to collect data from many different files. Some of the files are parent files with multiple child files. Sometimes not all of the files have a parent/child relationship. Some are lookup files, and others are just files with data that you need for some part of the report. One way to attack this is to create a temporary file to gather in all of the data into one place and just report on the one file (here we go again, changing the dictionary after the fact).

But wait. There is a better way. You can lessen the need for so many extra keys in your files, create new sort orders for your reports, eliminate the need for temporary files, and leave your dictionary alone. The magic word here is QUEUES. By using queues, you can accomplish the same things without any changes to your data structures, and a whole lot more. With what we learned in the last article about multiple detail lines and detail filtering, we can add a queue and get even more functionality out of just one report procedure. I am going to show you how we could have created the same report from last time, with a different twist. Also, we will see how to use one report procedure with the same data set in a queue, and give the end user 5 different reports. But first, lets look at a little tip for canceling a report when we are out of the template's timer loop (which, in reality, we are).

The report template provides the progress screen to show the progress of the report, and to give us a cancel button to cancel the report during execution. When we circumvent the template using the methods in my last article, and this one, we also circumvent the progress bar and the cancel button. To recover the progress functionality and a way to cancel the program requires a little coding. If you are the type of programmer that hates to code, don't worry. The code necessary here is very little. In fact, most of what we are about to do could be accomplished with a slight template modification. I will not go into that for now (and the crowd gives an even mix of boos and cheers) because that is a good topic for an article on template modifying.

The progress window (C4 will actually give us access to this window from the procedure properties) can be modified using the property statements for the caption, and two strings that are provided on the window. The label for the window is ProgressWindow. This leaves us two strings, ?Progress:UserString and ?Progress:PctText, the ?Progress:Cancel button, and Progress:Thermometer (the progress bar). We want to use the caption bar for messaging, the first string for a message on how to quit the report, and the second string for its intended use to show the progress in text form. We will hide the cancel button, and provide support for pressing Q to quit. Now for the fun part.

In the embed point After Opening Progress Window we need to add the following statements:

ProgressWindow{Prop:Text} = 'Queue 1 Report - Selecting Data'
?Progress:UserString{Prop:Text} = 'Press Q to Quit'
HIDE(?Progress:Cancel)

This sets up the progress window for our purposes now. The only thing left is to handle the progress thermometer and the progress text, which we will do from inside our processing loops.

A NEW TWIST ON AN OLD PROBLEM

In my last article, the report printed data from a parent file, multiple child files, a child of a child, and an unrelated reference file. Now we will accomplish the same report by using a queue. In this way, we can combine the data into one source and use the print engine to handling the group breaks and totaling for us. We will need to add a routine to the report to select the data and place it into the queue. To accomplish this, we will add one line of code in the embed point Beginning of Procedure, After opening Files:

DO GetData

Now we must add a Queue to the procedure. We do this the same way we add any other local variables to the program. By clicking the Data button on the procedure properties window. We add a variable called Loc:ReportQueue. The data type will be Queue. This sets up the Queue structure in the local data area of the report procedure. You will notice when you do this, that it gives us an end statement after the Queue. All queue elements must be inserted between the queue item and the end statement. The first Item we will add will be called RQ:Group. By putting our data elements in a group structure within the queue structure, we create the ability to initialize a queue record with one line of code, instead of a line for every element. Then we will add the following fields:

RQ:Type              BYTE
! This is a code for each type of transaction, used for sorting
RQ:TypeDescription   STRING
! This is the description of the transaction type
RQ:Component         BYTE
! This is used for component designation 0=Parent, 1=Component
RQ:SalesmanNumber    BYTE
RQ:SalesmanName      STRING
RQ:CustomerNumber    LONG
RQ:CustomerName      STRING
RQ:CustomerYTDSales  DECIMAL
RQ:Description       STRING
RQ:RefCode           BYTE
RQ:Amount            DECIMAL
RQ:PartNumber        STRING
RQ:SerialNumber      STRING
RQ:Qty               STRING

Then, in the Procedure Routines embed point we will add the following code to build the Queue.

GetData   ROUTINE
  RecordsToProcess = RECORDS(SalesMan)
  Progress:Thermometer = 0
  PercentProgress = 0
  RecordsProcessed = 0
  DISPLAY
  CLEAR(SAL:Record)
  SET(SAL:SalesmanKey)
  LOOP
    IF KEYBOARD()
      IF KEYCODE() = QKey
        DO ProcedureReturn
      ELSE
        ASK
      END
    END
    NEXT(SalesMan)
    IF ERRORCODE()
      BREAK
    END
    RecordsProcessed += 1
    IF PercentProgress < 100
      PercentProgress = (RecordsProcessed /RecordsToProcess)*100
      IF PercentProgress > 100
        PercentProgress = 100
      END
      IF PercentProgress <> Progress:Thermometer THEN
        Progress:Thermometer = PercentProgress
        ?Progress:PctText{Prop:Text} = FORMAT(PercentProgress,@N3) & '% Completed'
        DISPLAY()
      END
    END
    CLEAR(RQ:Group)
    RQ:SalesmanNumber = SAL:SalesManCode
    RQ:SalesmanName = SAL:Name
    CLEAR(Cus:Record)
    CUS:SalesManCode = SAL:SalesManCode
    SET(Cus:SalesManKey,Cus:SalesManKey)
    LOOP
      NEXT(Customer)
      IF ERRORCODE()
        BREAK
      END
      IF CUS:SalesManCode <> Sal:SalesManCode
        BREAK
      END
      RQ:Type = 1
      RQ:TypeDescription = 'Customer Section'
      RQ:CustomerYDTSales = CUS:YearToDateSales
      RQ:CustomerNumber = CUS:CustomerNumber
      RQ:CustomerName = CUS:CustomerName
      RQ:Description = CUS:CustomerName
      ADD(Loc:ReportQueue)
      CLEAR(HAR:Record)
      HAR:CustomerNumber = CUS:CustomerNumber
      SET(HAR:CustomerKey,HAR:CustomerKey)
      LOOP
        NEXT(HardWare)
        IF ERRORCODE()
          BREAK
        END
        IF HAR:CustomerNumber <> CUS:CustomerNumber
          BREAK
        END
        RQ:Type = 2
        RQ:TypeDescription = 'Hardware Section'
        RQ:Component = 0
        RQ:Description = HAR:Description
        RQ:RefCode = HAR:RefNumber
        RQ:Amount = HAR:Amount
        RQ:SerialNumber = HAR:SerialNumber
        RQ:Quantity = HAR:Qty
        RQ:PartNumber = HAR:PartNumber
        ADD(LOC:ReportQueue)
        If HAR:IncludeComponents
          COM:ParentPartNumber = HAR:PartNumber
          SET(Com:ParentKey,Com:ParentKey)
          LOOP
            NEXT(Component)
            IF ERRORCODE()
              BREAK
            END
            IF COM:ParentPartNumber <> HAR:PartNumber
              BREAK
            END
            RQ:Component = 1
            RQ:ComponentDescription = COM:Description
            RQ:PartNumber = COM:PartNumber
            RQ:Quantity = COM:Qty
            RQ:Amount = 0
            ADD(LOC:ReportQueue)
          END
        END
      END
      CLEAR(SER:Record)
      SER:CustomerNumber = CUS:CustomerNumber
      SET(SER:CustomerKey,SER:CustomerKey)
      LOOP
        NEXT(Service)
        IF ERRORCODE()
          BREAK
        END
        IF SER:CustomerNumber <> CUS:CustomerNumber
          BREAK
        END
        RQ:Type = 3
        RQ:TypeDescription = 'Service Section'
        RQ:Component = 0
        RQ:Description = SER:Description
        RQ:RefCode = SER:RefNumber
        RQ:Amount = SER:Amount
        RQ:PartNumber = ''
        RQ:SerialNumber = ''
        RQ:Quantity = ''
        ADD(LOC:ReportQueue)
      END
    END
  END
  Sort(LOC:ReportQueue,RQ:SalesmanName,RQ:Type,RQ:Description,RQ:Component)

You will notice that the beginning of the loop had code to check for a Q being pressed. This gives the user the ability to cancel the report. Also, we set the progress bar based on the records in the salesman file and incremented it when we read a salesman record. The last thing we did in this section was sort the queue to give us the order we need to create the same report as in the first article.

Now that the queue is built, I am going to take a break and play a game of football with my 4 boys (Nintendo of course, I quit playing the real thing after my boys got bigger than me).

I'm back now. I got beat three out of four games. My youngest son had mercy on me and let me win one. Thanks Josh. Anyway, now it's time to format the report. We will again set up multiple detail lines with use variables. This time though, we will determine which detail to print based on the RQ:Type field with a case statement. Check the report format out in the application you download for this article.

Now all we have left to do is add the code to handle the printing. This time we will place the code in the Before Printing Detail Section embed point.

ProgressWindow{Prop:Text}='Queue 1 Report-Printing Report'
RecordsToProcess = RECORDS(LOC:ReportQueue)
Progress:Thermometer = 0
PercentProgress = 0
RecordsProcessed = 0
LOOP i# = 1 TO RECORDS(LOC:ReportQueue)
  IF KEYBOARD()
    IF KEYCODE() = QKey
      DO ProcedureReturn
    ELSE
      ASK
    END
  END
  GET(LOC:ReportQueue,i#)
  IF ERRORCODE()
    BREAK
  END
  RecordsProcessed += 1
  IF PercentProgress < 100
    PercentProgress = (RecordsProcessed /RecordsToProcess)*100
    IF PercentProgress > 100
      PercentProgress = 100
    END
    IF PercentProgress <> Progress:Thermometer
      Progress:Thermometer = PercentProgress
      ?Progress:PctText{Prop:Text} = FORMAT(PercentProgress,@N3) & '% Completed'
      DISPLAY()
    END
  END
  CASE RQ:Type
  OF 1
    Report$?RQ:CustomerYDTSales{Prop:Hide} = ''
    Report$?String37{Prop:Hide} = ''
    PRINT(RPT:Detail)
  OF 2
    Report$?RQ:CustomerYDTSales{Prop:Hide} = 1
    Report$?String37{Prop:Hide} = 1
    IF RQ:Component
      PRINT(RPT:Detail3)
    ELSE
      PRINT(RPT:Detail1)
    END
  OF 3
    Report$?RQ:CustomerYDTSales{Prop:Hide} = 1
    Report$?String37{Prop:Hide} = 1
    PRINT(RPT:Detail2)
  END
END

You will notice that the first thing we did was to reset the progress thermometer and percent fields for starting the progress over. Also, we changed the caption to reflect the fact that we were now printing the report.

Now we have a complete report. It is the same as the first report, but using a completely different method to accomplish it. Why did we do it this way, since the other method seemed a little easier with somewhat less coding? I'll tell you why in the next section. I will demonstrate the advantages we can get out of building a queue like this to produce a report.

Multiple Reports from One Procedure

Selecting our report

We can use the above report procedure, with a few minor changes in code and some added structure in the formatter, to actually produce up to nine different reports, one or more at a time. The first thing we need to do is add a few global variables, a global queue to hold the selections, and a call to a window procedure to allow the user input for selecting which reports to print and the criteria for printing. In the Procedure Setup embed, we just call the procedurewndSelectCriteria, and then add some code to handle the cancel button. After calling the criteria window, we then prime the report title variable for the first report. All other titles are handled by the code.

The beauty here is that we can produce all of these reports and only run the data selection routine once. By looping through the global queue and processing according to the criteria selected by the user, we produce all of these reports and only call the report procedure once. By adding some more detail bands in the formatter (view the report structure in the application), and changing the code in the Before Printing Detail Section embed, we can create one report procedure that does it all. Just think what this saves in program footprint, and development time. Enough time to practice my Nintindo football and maybe, just maybe, beat my sons fair and square (NOT).

Here is the code needed to handle all the printing. It may be a little lengthy, but look at what it accomplishes. Also, the printing is actually accomplished in the routines called by this code. There isn't enough space here to add all of the routines, so I suggest that you review the routines directly in the application available for download.

! Body Printing Section
LOOP i2# = 1 TO RECORDS(Glo:ReportJobQueue)
  GET(Glo:ReportJobQueue,i2#)
  IF ERRORCODE()
    STOP(ERROR())
  END
  CASE RJQ:SelectedReport
  OF 1
    IF RJQ:SelectedSort = 1
      Loc:ReportTitle = 'Salesman by Number'
      DO SalesManbyNumber
    ELSE
      Loc:ReportTitle = 'Salesman by Name'
      DO SalesManbyName
    END
  OF 2
    IF RJQ:SelectedSort = 1
      Loc:ReportTitle = 'Customer by Number'
      DO CustomerbyNumber
    ELSE
      Loc:ReportTitle = 'Customer by Name'
      DO CustomerbyName
    END
  OF 3
    IF RJQ:SelectedSort = 1
      Loc:ReportTitle = 'Hardware With Components by Partnumber'
      DO HardwarebyNumber
    ELSE
      Loc:ReportTitle = 'Hardware With Components by Description'
      DO HardwarebyDescription
    END
  OF 4
    IF RJQ:SelectedSort = 1
      Loc:ReportTitle = 'Hardware Without Components by Partnumber'
      DO HardwarebyNumber
    ELSE
      Loc:ReportTitle = 'Hardware Without Components by Description'
      DO HardwarebyDescription
    END
  OF 5
    Loc:ReportTitle = 'Services Report'
    DO Services
  END
END

Well, here I am at the end of another wonderful, useful, well-written and enlightening article on creating reports with Clarion for Windows. I know it is because my wife said so, just before asking for the credit card to go shopping. Hopefully, I will be at it again next time (if my check to the editor doesn't bounce). I wonder if all writers pay the editor to publish their articles?

Editor's Note: We haven't received the author's check at time of publication - it remains to be seen if we will have more articles from him in the future.

Anyway, next time we will talk about another subject, building on what we have discussed here. We will add some images to our reports, static and dynamic, and check out the property syntax that affects the reports and makes life easier for our end users. Isn't that what makes our world go 'round? Easier life for our users can usually be translated into more $$$ for our software. That's what makes my life go 'round. Just ask my wife. Now where are those credit cards?

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942