13
Thu, Jun
4 New Articles

TechTip: Exploit DB2 Web Query's Defined and Computed Fields

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

What do you do when you need to create a field that's a calculated value based on real data?

 

If you've used the DB2 Web Query for IBM i development tools, you may have noticed the ability to create two types of virtual fields: defined fields and computed fields. Often, when building a report, you need to create a field that is not in the original database but is instead a calculated value based on real data. For example, "gross profit" may not actually exist in your data, but it can be calculated from revenue and cost values. Each virtual field type has its own distinct advantages and purpose in report development. This TechTip uses a real-world example to illustrate when you should use which field type.

 

First, I'll define at a high level what each field type is used for, and then I'll explain how the field types are executed when you run a report. Further, I'll show an example that will help you visualize the differences between the field types. I'll also show a neat way to have your computed fields recalculated at the subtotal level to ensure correct subtotal results.

Defined Fields and Computed Fields Compared

A defined field is a virtual field that is executed at an individual row-value level. This means that it is calculated for every row selected in the query. If your query returns 10 rows, 10 distinct values of that virtual defined field are generated. This happens even if your report does not report at the lowest detail level, meaning the values will be executed and then aggregated.

 

To create a defined field, you use the "New Define Field" icon in Report Assistant, which is circled in Figure 1. 

 

090508EvenFigure1.JPG

Figure 1: Use the "New Define Field" icon to create a defined field.

 

You then use the "New Define Field" window to define your virtual field. Each defined field requires a name, formula, and format. In Figure 2, a defined field is created to calculate total cost.

 

090508EvenFigure2.JPG

Figure 2: Here's your "New Define Field" window.

 

A computed field is executed after all sorting and aggregating has been performed during report execution. This means that all of the report's measures (numeric field values) are aggregated (based on the specified sort by columns), and only after this aggregation has occurred are the define field values calculated.

 

Note: When the report is at the individual-row level, computed fields will have the same value as a defined field. Aggregation is what makes the difference.

 

To create a computed field, you use the "New Computed Field" icon in Report Assistant, which is circled in Figure 3. 

 

090508EvenFigure3.JPG

Figure 3: Use the "New Computed Field" icon to create a computed field.

 

You then use the "New Computed Field" prompt to define your virtual field. As was the case with defined fields, each computed field requires a name, formula, and format (see Figure 4).

 

090508EvenFigure4.JPG

Figure 4: This is the "New Computed Field" prompt.

 

More information on how to use functions, fields, and formats with defined and computed fields can be found in the IBM Redbook Getting Started with DB2 Web Query for System i.

Real-World Example

For this example, I will use a table, called Sales, that has five columns (fields) in its metadata: Customer (A10), Item (A10), Quantity (I8), UnitCost (P12.2), and UnitPrice (P12.2). Figure 5 shows the Sales table.

 

090508EvenFigure5.JPG

Figure 5: These are the Sales table contents used in the example.

 

The report I wish to build will show Price, Cost, and Margin values by Customer and Item. It will also subtotal Price, Cost, and Margin for each Customer. Each of the three measure fields--Price, Cost, and Margin--are virtual fields. First, I'll create the report using only defined fields. Then I'll create the report using only computed fields. And finally, I'll use a mixture of the two field types to create a report that returns the correct results.

 

Report 1: Defined Fields

 

Using the "New Define Field" icon, I'll create the three defined fields for my report: Price, Cost, and Margin (Figure 6). It is important to note that I need to create Price and Cost before creating Margin, since Margin uses them in its formula.

 

090508EvenFigure6.JPG

Figure 6: Here's the "New Define Field" prompt for Cost, Price, and Margin.

 

I then add the fields to the report, as shown in Figure 7.

 

090508EvenFigure7.JPG

Figure 7: I've defined Report 1.

 

In the Customer field options' Sorting tab, I select "Subtotal numeric sum/print fields" as shown in Figure 8. Then, I set every measure (Cost, Price, and Margin) to be included in the subtotal.

 

090508EvenFigure8.JPG

Figure 8: I sort the properties for Report 1.

 

Next, I'll run the report as both a detail report (Figure 9) and a summary report (Figure 10). This will help explain what's going on in the background of the summary report.

 

090508EvenFigure9.JPG

Figure 9: These are the detail-level results for Report 1.

 

090508EvenFigure10.jpg

Figure 10: These are the summary-level results.

 

Notice that the Margin value in the summary report is the sum of the calculated detail or row-level Margin. This is not what I wanted. The same thing also happened with the subtotal values. What I really wanted was for my Margin value calculation to be the sum of the Price and Cost values at the Item level. Will computed fields help me with this?

 

Report 2: Computed Fields

Using the "New Computed Field" icon, I create the three computed fields for my report: Price, Cost, and Margin (Figure 11). Again, I need to create Price and Cost before creating Margin, since Margin uses them in its formula.

 

090508EvenFigure11.JPG

Figure 11: Here's the "New Computed Field" prompt for Price, Cost, and Margin.

 

By default, the "New Computed Field" prompt will add the created fields to the report. All I need to do is add the Sort By fields, as shown by Figure 12.

 

090508EvenFigure12.jpg

Figure 12: I've defined Report 2.

 

As in Report 1, in the Customer field options' Sorting tab, I select "Subtotal numeric sum/print fields." I also set every measure (Cost, Price, and Margin) to be included in the subtotal.

 

Next, I run the report as a summary report (Figure 13). 

 

090508EvenFigure13.jpg

Figure 13: These are the summary-level results for Report 2.

 

Notice that the Margin values in the summary report are now correct. However, the subtotal value for Margin is still the aggregate of the computed values. To fix this problem, I need to select the "Recalculate computed fields using subtotal values" option in the Customer field options' Sorting tab (Figure 14). 

 

090508EvenFigure14.jpg

Figure 14: Now, I choose the "recalculate" option.

 

After running this report, I see some new problems have occurred (Figure 15). Each field in this report is now recalculating at the subtotal level, since I set each computed field to recalculate in Figure 14. But at least the subtotal for Margin is now using the aggregate values from the subtotal and recalculating itself.

 

090508EvenFigure15.jpg

Figure 15: Report 2 now recalculates all fields.

 

Report 3: Combining Defined and Computed Fields Puts All the Pieces Together

 

It is now apparent that, in order to generate the correct results, I need to use a combination of defined fields and computed fields. Because Cost and Price are calculations that can be made at the row level and simply aggregated at any "Sort by" or "Subtotal" level, it makes sense to use the "New Define Field" prompt to create them. For Margin, however, I need its value to always use the aggregated values to in order to calculate correctly at both the "Sort by" and "Subtotal" level. It would then make sense to create Margin as a computed field. Figure 16 shows the definition of Report 3.  Note that the Subtotal options are also shown.

 

090508EvenFigure16.jpg

Figure 16: Report 3 is defined!

 

As shown in Figure 17, running Report now returns the correct results I need from my report. Notice that the define fields Cost and Price act like normal fields in a row of the table and that the Margin uses the sums to calculate itself!

 

090508EvenFigure17.jpg

Figure 17: Finally! I get correct results by using a combination of defined and computed fields.

Defined, Computed, or Both?

This TechTip has explained the different purposes the two virtual field options for DB2 Web Query for IBM i. Defined fields and computed fields act in very different ways; however, using them in combination will help solve any reporting issue. Computed fields compute at the aggregate level and can recalculate at the subtotal level, while defined fields create a virtual field at the row level of your table.

Tyler Even

Tyler Even is a DB2 for i5/OS Technology Specialist in STG Lab Services System i group. He currently assists customers with application/database design and development using DB2 Web Query for IBM i, DB2 for IBM i, Java/VB, WebSphere, and Lotus Domino. His current responsibilities include providing consulting services to IBM i customers with a special emphasis in DB2 Web Query for IBM i and Business Intelligence practices for IBM i. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: