Displaying Company Header Information

 

Yesterday, a customer asked me how to handle a single record "Company" table. The single record in this table contains information about the Company that is using the software, like the Company Name and the Address. So, if we here at Stonefield were looking at our entry in that table, the Company Name field would contain "Stonefield Software Inc.", and the Address field would be "2323 Broad Street".

The question becomes, "How do we get this information into our reports?". When I first ran across this situation, my initial reaction was to simply create a join/relationship with that table and every other table in the database. There are a couple of problems with doing it this way:

• We don't have a field in the table to join on, so the join would need to be an expression that evaluates to true.
• If the database has a large number of tables, creating a relationship with all of them would be impractical.
• Having to include this company information in the data selection step of Stonefield Query's report wizards would be very clumsy. The information would repeat itself over and over again at the detail level. Information like this generally belongs in the header of a report.

A better way to handle this situation is to use a script that looks this information up for us and stores it in a global variable. Here is an example of such a snippet from a GetCustomMetaData script:

*variable declarations 
public gcAddress, gcCompanyName 
local lnSelect, loDatabase, lcSelect 
*store the current work area 
lnselect = select() 
*get a reference to the database we are currently connected to 
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase() 
lcSelect = "select CompanyName, Address from Customers" 
*send the SQL statement to the database 
loDatabase.ExecuteSQLStatement(lcSelect, .NULL., 'curCompanyCursor') 
*store the address and company name we just retrieved 
gcAddress = curCompanyCursor.Address 
gcCompanyName = curCompanyCursor.CompanyName 
*restore the workarea 
select (lnselect)

Once this is done, we have two global variables (gcAddress and gcCompanyName) that we can reference from the output expression of a field object in the advanced report designer. We could even reference these from a template, and have company information like name and address appear in the header of every report by default.