Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

'Rubbin' Sticks Reporting (RSR)'

'Rubbin' Sticks Reporting (RSR)'

Myth: "Creating reports with InfoMaker is easy," a partially accurate statement, on the surface.

Reality: Let's change that to: "Formatting your report with the InfoMaker tool is easy, once you've figured out the data to be reported." Semantics? Maybe. Over the years I've had many gigs that were presented to me as "...just writing some InfoMaker reports." Hey, I'm not too shabby in PowerBuilder; it should be easy, I said to myself...Wrong! Every project that began that way soon became a nightmare of epic proportions. Let's set up a typical scenario:

The ACME Shoe Company needs 24 different custom in-house reports (due 30 days from yesterday) created in InfoMaker because the software package they purchased from OffShore Coders, Inc., uses InfoMaker for its reporting tool. The Business Analysts at ACME are familiar with basic programming concepts but are not programmers; they're experts in the footwear biz. They create high-level design specs based on their business needs, with no correlation to the current or future database schema. They use common "footwear industry" terms for columns they desire, with some columns being metadata instead of individual pieces of data. There's a huge disconnect between what they design on paper and the data structure. Nevertheless, they did attempt to create some semblance of a design document and believe these specs are ready for implementation. As such, they hire an InfoMaker guru to write said reports.

Ms. Guru comes on board and asks to see the latest database schema and/or Data Definition Language (DDL) so she can start putting the SQL together. Sorry, ACME doesn't have either of those handy right now. Okay, the guru then asks to see the latest entity relationship diagram (ERD). After the lead DBA stops choking and gasping, the words "We don't have the latest printed out" are heard. She asks to see the "old" one then. Sorry, that document seems also to have grown legs and run out the door. Ms. Guru shakes her head and keeps her cool by remembering that she got into contracting to make big bucks, not to make life easy. Regardless, she's between a rock and a hard place. Trying to be proactive about the situation, she asks if she could bring in PowerDesigner and reverse-engineer a schema from the live database. Sorry, corporate IT security policy doesn't allow unapproved software to be loaded on the workstations. Now life is really becoming interesting for Ms. Guru; she's got a short deadline, mismanaged expectations, and not one shred of documentation to assist her with the database schema. What to do?

At this point, you have to decide if you're gonna "fish" or "cut bait." With the current economy, "cutting bait" probably isn't an option. Okay, so let's fish. How in the world are we going to produce these reports?

Let's assess our skills. Do we have a solid grasp of SQL, and I'm not talking about simple two-table joins. I'm talking fluency with outer joins, unions, folding/unfolding data, and sub-queries. Do we pass those specs? Let's say we do so far. Next we need a decent grasp of database objects. What indexes are used for, and the different types. What tables, views, rules, and defaults are. Most important, do we understand primary/foreign key relationships? Last, are we comfortable with InfoMaker? If we can honestly say yes to all of these, then we'll move into a concept that will help us get started with those reports. I've used this technique successfully since 1991, so it has withstood the test of time for me. I call it "Rubbin' Sticks Reporting."

Rubbin' Sticks Reporting
Everybody knows that, in principle, you can take two sticks, rub them together, and create fire. The trick is knowing the correct way to rub the sticks. It's primitive, but effective in an emergency. The same concept applies here. We're in an emergency situation and need to make something positive happen, without many tools handy. We have some loose specs and InfoMaker, and we need to make fire in a hurry.

The following steps are simple, but need to be executed exactly and in the right order. Quite possibly, you may find that this is a good "plan" for all your report creation. Great! But for now, let's just say this is for those "break glass in an emergency" situations only.

Step 1: Review the specification, open the DB Painter, and create an "SQL Outline"
We have no tools other than InfoMaker and SQL Plus (see important note below); I chose InfoMaker. I opened up InfoMaker and went immediately to the DB Painter. At this point, we have no idea what table(s) we will need or what the join or where criteria is, but we can start "roughing out" our query.

/***************************************/
/* SALES REPORT SQL [server side] */
/* AGAINS ORACLE 8.1 EAS DEMO DB IV IM */
/***************************************/

select 'SalesRegion',
'SalesRep',
'DateSold',
'InvoiceNumber',
'ProductSold',
'NumberOfProductsSold',
'Customer'

Important Note: The database platform you are using plays a very important part in the syntax you'll use for column aliasing. In all the examples shown here, we'll be using Oracle 8.1 instead of Sybase ASE. The database is the EAS Demo DB IV IM database, the one that's installed with the InfoMaker standalone product. Notice the single quotes? If this is a "created field," you must use single quotes; if it is a column alias (shown later), you must use double quotes. The ODBC driver can also wreak havoc. For example, the MS ODBC driver for Oracle will allow spaces in your alias names, but the Oracle ODBC driver for Oracle does not. Be cautious and make sure all configurations, yours and your users, are identical in every respect!!!

Step 2: Start with one table and fill in fields from that table only
We take our first column, 'SalesRegion', and scan the list of tables in Figure 1. Bingo! We got lucky this time and found a table with a name similar to our field. In the real world, this may not be so easy; you might have to dig around for a while. Do not assume the name will be the same as the spec!! Opening up the table in Figure 1, we see it has only one column, the region. Close enough. We now put that field into our SQL, sort by it, and try it out. At this time you can remove the alias or rename it. I recommend leaving it alone because sometimes the actual column name is so funky you won't remember what you were using it for in the future. Also, when you need to "talk turkey" with the users, you'll be using the same lingo they used for this piece of data. We hit CTRL-L and see that all is well so far in our code; we're getting regions in alphabetical order. Onto the next step...

select sr.region "SalesRegion",
'SalesRep',
'DateSold',
'InvoiceNumber',
'ProductSold',
'NumberOfProductsSold',
'Customer'
from sales_regions sr
order by sr.region asc ;

Step 3: Find one table to join a piece of that information to, preferably the "key" field of your report
After getting results, verify that the join was correct. Querying the sales_order table (see Figure 2) returned 650 records, and there are exactly 650 records returned on our query. A cursory look at the data appears that all is well, so we're good to continue "joining" to complete the report. Notice that we're using table aliases as well. Saves mucho typing and cleans up the SQL. Do you have to do this? No, but you'll have to put tablename.columnname in for any ambiguous columns. Learn to use aliases; you'll be much happier.

select sr.region "SalesRegion",
so.sales_rep "SalesRep",
so.order_date "DateSold",
so.id "InvoiceNumber",
'ProductSold',
'NumberOfProductsSold',
so.cust_id "Customer"
from sales_regions sr,
sales_order so
where so.region = sr.region
order by sr.region asc,
so.sales_rep asc,
so.order_date desc,
so.id desc ;

Step 4: Fill out the remaining fields one table at a time
In Listing 1, we'll use the sales_order_items table next (see Figure 3).

Step 5A: Review the data output and "flesh out" any fields that need to be decoded
In our example, the product ID, sales rep ID, and customer ID are not "decoded." We'll fill in those fields for a more descriptive report. Add fields from only one table at a time. Update your field aliases and sort orders as needed. In Listing 2 we're going to flesh out the Sales Person, Product, and Customer information.

Step 5B: Keep working with only one table at a time until finished
Be sure to include your table name in the from clause and put in the join criteria. A rule of thumb: you will need N-1 join conditions in your where clause. N represents the number of tables. If you have 10 tables to join, you'll need 9 join conditions in your where clause (see Listing 3).

Step 6: After debugging, copy SQL into clipboard

  • Select "NEW" from the PowerBar.
  • Select the "OBJECT" tab.
  • Pick the report type; we'll use a Group for our example. Select "Group" and then hit the "OK" button.
  • We must pick our SQL source; we'll use "SQL Select". Select "SQL Select" and then hit the "NEXT" button.
  • In the table selection screen, do not pick any tables!!! Select "CANCEL", then select the "DESIGN" menu option on the PowerBar.
  • Under "DESIGN" select the "CONVERT TO SYNTAX" option. When the blank input area appears, paste your SQL into the area. Be sure to remove the trailing ";" if you haven't already.
  • Press the "RETURN" button. If all is well, you should now have the Grouping dialog box. If not, you have some SQL error that must be corrected.
  • In the Grouping box (see Figure 4) select the sales region as our grouping criteria. We will create another group when we get into Design Mode. Press the "NEXT" button.
  • Now it's all downhill from here. Select a title, etc. Notice that you can set your page breaking here also. You can change it in Design mode also if you don't want to here.
  • If you haven't done so already, take the time to set up your default settings for a Group report. Check the "Save as default" option to save it for all future Group reports.
  • Finally, the "last chance" screen - if all is well, select "FINISH" or "BACK" to correct something from a previous step.

    I added another group (see Figure 5) to clean up the report and suppressed some repeating values. The final product is shown in Figure 6.

    Taking a systematic approach like "Rubbin' Sticks Reporting" will help simplify your work and let you progress as you build.

  • More Stories By David Hart

    David Hart is a former Sybase Tools and Database Instructor. He is a certified PB developer and certified ASE DBA from Rancho Cucamonga, CA. He is currently on contract assignments in Southern California.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.