Report Builder Guide To

Creating Reports

Unify Corporation

2

Table of Contents Abstract...............................................................................................4 Introduction.........................................................................................4 Requirements......................................................................................4 Creating Different Types of Reports...................................................5 Multi-Column Reports..........................................................................5 Form Letters........................................................................................5 Break Group Summary Reports..........................................................5 Two-Pass Total Reports......................................................................6 Input Cross-Tabular Reports...............................................................6 Getting Started....................................................................................7 Creating a Query..................................................................................7 The Source Property Tab....................................................................8 The Conditions Property Tab............................................................12 To Define a Condition........................................................................13 The Sort Property Tab.......................................................................15 Defining a Group By Condition..........................................................16 Defining a Group Condition…………...................................................17 The SQL Property Tab.......................................................................17 The Result Property Tab...................................................................18 Input Totals.......................................................................................19 Defining Totals for Your Report........................................................19 Adding Input Totals to Your Template.............................................20 Input Crosstabs.................................................................................23 Formatting Your Crosstab Matrix.....................................................23 Creating Crosstab Reports...............................................................23 Picture Objects.................................................................................33 Importing a Static Object to Your Report........................................33 Formatting Picture Objects……………………………………………………...36 Creating Graphs in Reports...............................................................36 Creating Form Letter Reports...........................................................41 Creating Reports with Conditional Display…………………………….....46 Creating Reports with LONG Data Stored in Databases..................48 Conclusions.......................................................................................50

3

Abstract This paper is written as a tutorial to help the reader create reports with Report Builder using easy-to-follow steps and examples.

Introduction Report Builder is an easy-to-use dynamic reporting tool that creates on-demand reports to improve your business IQ. Report Builder works both on the Microsoft Windows and Linux operating systems. Organizations are always searching for tools to transform data into valuable information which enables better decision making. Report Builder affords organizations of any size the competitive advantage of working smarter and more effectively.

Requirements

To easily understand the steps in this guide it is strongly recommended that the reader has the following:

Report Builder 2005.1 SQLBase 9.0.1 with the sample database ISLAND installed

SQLBase should be running and Report Builder must be able to connect to the ISLAND database. If you need help with installation and connectivity please refer to the whitepaper Report Builder Installation & Connectivity for Windows or Report Builder Installation & Connectivity for Linux. It is also advisable to read the whitepaper Report Builder Workspace which will help you to understand the Report Builder user interface and other tools needed to create your business reports. Click on the Report Builder section and then select the Report Builder Starter Guide Samples link, login with your Unify account name (or register for a new account if you haven’t one), and download these files to your computer. We will refer to these necessary files as we progress throughout this guide. The samples include all the tutorials covered in this paper. If you have downloaded those samples you can then refer to them throughout the tutorials whenever required.

Creating Different Types of Reports

You can use Report Builder to generate reports consisting of data from various sources and to create report templates to design, test, print, and manage reports.

4

When you save a Report Builder file you actually save an encoded representation of all the objects, their formats, and their locations on the report template. As you edit the template Report Builder writes the representation in the background and saves the changes to a file when you save the report template. Report Builder can directly connect to a database, issue a query, and fetch the rows as data for the report all on its own. Application developers can view or modify a report’s design during designtime, and users can view the report at runtime. You modify the report design directly through the use of the Report Builder designer workspace. With Report Builder you can create many types of reports such as:

Multi-column (mailing labels) reports Form letters Break group summary reports Two-pass totals reports Input cross tabular reports

Let us explain these report types in more detail: Multi-Column Reports Create multi-column reports with two or more columns across the page. For instance, mailing labels or telephone directories, and various other reports where information can be broken into narrow columns. Form Letters A form letter lets you create a template that contains most of the text in the body of a letter. However, some information, such as a customer’s name, must be unique to each letter. Report Builder integrates the unique information with the repeated information to create a personalized form letter. Break Group Summary Reports Report Builder can create templates for reports that subdivide data into logical units called break groups. Often you may want to have data summaries of these groups. You can sort data in a way that best fits your report. For example, a company may have regional offices, regional departments, and one or more managers within each department. When creating a break group summary report, the company could logically sort these groups of data in order of regions, departments, and managers. For instance, when shifting data from the larger group to the smaller group. Report Builder lets you summarize each break group. When you define break groups, it automatically adds block captions for each break group where you can add a title in the header and a summary in the footer.

Two-Pass Total Reports Two-pass totals (also known as input totals) are another form of input data that Report Builder can use. With two-pass totals, Report Builder pre-calculates specified

5

totals or other aggregate types of calculations, and provides the results as input to your report. All input totals are numeric variables that can be assigned to a field, or used by a formula in Report Builder. With two-pass totals reports, Report Builder can pass the data twice to the report. In the first pass, Report Builder calculates the input totals, and in the second pass it formats the report, putting those totals wherever you want to see them. The advantage of a two-pass report over a one-pass report is that with the two-pass method totals are not restricted to appearing directly after the data.

Input Cross-Tabular Reports Use crosstab reports to gather cross tabular statistics on your report’s input rows. The crosstab feature summarizes the information your report receives. Crosstab reports generate a two-dimensional matrix of numeric data based on the categories and data you provide. The size of this matrix depends on the number of unique occurrences of each column and row category you use in your crosstabs definition. You can have from one to eight row categories and from one to eight column categories in your crosstab. The greater the number of row and column categories, the larger and more complex your crosstab matrix will be.

Getting Started

6

To launch Report Builder go to Start > All Programs > Unify > Unify Report Builder 2005.1 > Report Builder 2005.1. Creating a Query In order to create a report you should first create a query. To define a query you need to select one or more tables from the database. This can be done using a wizard-driven query tool. The Define Query property tabs allow you to define the queries for your report. Selecting the Define Query property tabs is done when you want to create a new report (File > New), or when you wish to define a query from an existing report.

To open the Define Query property tabs you can either choose Report > Define Query… from the Task menu (as in the screenshot above) or click on the Define Query button (shown left) located below the Task menu. There are seven property tabs that you can select from:

Source selects tables or views Conditions define the condition or conditions that must be met for the data

to be included in the result set Sort defines how the data will be sorted in the report Group By defines how the data will be grouped in the report Group Condition defines the condition for the group in the report

7

SQL displays the SQL (Structured Query Language) that will be generated to create the query

Result displays the actual data that will be returned

The Source Property Tab Use the Source property tab to select the tables or views for your query. The Source property tab initially shows the available servers (e.g. SQLBase, Oracle, Sybase, and Informix) on the left-hand side of the tab. The Source property tab is shown in the same screenshot above and by default it is the first tab shown when the Define Query window opens. To select a table:

1. Double-click on the desired server. The databases associated with the selected server will appear as shown below:

8

Note: If you do not see the database you wish to connect to you may need to add it to the list manually. In order to achieve this, right-click on the desired server and then select Add to List from the context menu. The Add to List dialog box appears.

9

Use the Server tab to add a new server to the list by name (this applies only to SQLBase and DB2 database servers). Use the Database tab to add a new database to the list by name.

2. Double-click on the desired database. The Connect To: dialog box will then appear.

3. Enter a user ID and password in the appropriate fields of the Connect To:

dialog box and click OK.

Note: Before you can select tables from the ISLAND database you must first login. Enter sysadm in the User: and Password: fields as shown on the previous page. The tables and views associated with the selected database appear under that database as shown below:

10

4. Double-click on or drag the desired tables or views to the right-hand side of

the screen.

You can select multiple tables or views to create reports but this can only be done if they are related. In this example the COMPANY table contains details of several companies and the CONTACT table contains details of contacts for the companies found in the COMPANY table. There can be more than one contact for each company and this relationship is called a one-to-many relationship. When you select more than one table or view, the links between them are displayed automatically based on their referential integrity (in other words, a relationship set in the database). In this example COMPANY_ID in the COMPANY table and COMPANY_ID in the CONTACT table are used to establish a one-to-many relationship. If you wish to change the links between the tables or views click on the button with the equals sign as shown here:

When you click on a table or view the columns which appear within that table or view are displayed.

11

The top screenshot on the next page shows the COMPANY and CONTACT tables linked in the right window pane. The second screenshot is the Define Link box that appears after clicking on the = (equals) button.

5. On the right side of the Define Query Source tab you can select or deselect the

columns that you want to appear in your report by check-marking the adjacent box. All the columns that are able to appear within the selected table are displayed if a check mark appears beside the columns.

6. Data types of the columns are also displayed.

12

The Conditions Property Tab Use the Conditions property tab to define conditions that must be met when including data to your report’s result set. Use each line of the table to enter a condition that will restrict the results when the rows for the table are fetched from the database. The Conditions property tab is shown on the next page.

To Define a Condition 1. To select a column or formula place your cursor in the Column/Formula

field and click the left mouse button. A list of the available columns associated with the selected table is displayed. To select a formula, click the Formulas… button, select the desired formula from the Formula Editor screen that appears and click OK. A screenshot of the Formula Editor dialog box appears in the section Form Letter Reports.

13

2. On the Conditions tab in the Define Query property window, select an operator by placing your cursor in the Op field and click on the left mouse button. A list of the available operators is displayed.

2. When entering a value for a condition you can double-click on a cell in the

Values column to access the Condition Value screen. The Condition Value screen helps you to select a value for that condition.

3.

Note: Use the AND/OR drop down list to group multiple conditions together.

14

After entering the conditions mentioned above, click on the OK button. You will be able to view the report you created thus far by simply switching between the Report Design mode and Report Preview mode options. When designing your report, you can preview it any time to make sure everything appears satisfactorily, and then return to Design mode to continue designing the report. The screenshot below shows the Design and Preview Toggle Mode icon used to toggle between these modes:

The Sort Property Tab Use the Sort property tab to specify how you want to sort the data in your report (that is, the order in which rows appear when fetched from the database). The screenshot below shows the Sort property tab:

15

The following steps will explain how to use the Sort function. How to define the data you wish to sort:

1. To select a column of data you wish to sort, place your cursor in the Sort By field, click the left mouse button, and select a column from the list that appears. To select a formula, click the Formulas… button, select the desired formula from the Formula Editor screen, and click OK.

2. Specify whether the data should be sorted in ascending or descending order by placing your cursor in the Asc/Desc field, click the left mouse button, and select either Asc or Desc from the drop down list menu.

Defining a Group By Condition

Use the Group By property tab to define how to group the data in your report. The Group By property tab allows you to create a summary query and you typically specify a Group By when you want to summarize data. Select the columns and/or formulas you wish to group the data. The Group By property tab is shown below:

16

To select a column: Place your cursor in the Group By field, click the left mouse button, and select a column from the list that appears. To select a formula: Click the Formulas… button, select the desired formula from the Formula Editor property tab, and click OK. Note: Using the Group By property tab may change the columns and formulas that you have already added to your query.

Defining a Group Condition The Group Conditions tab is used to restrict the results when rows are fetched from the database. For example, you can choose the condition as shown in the following screen:

The SQL Property Tab The SQL property tab displays the SQL statement that will be generated to create the report. If you wish to include unique rows only, check the box next to Include unique rows only under the Query Results section of the property tab. If you wish to copy the SQL to the clipboard select the data under the SQL Statement section and click the Copy to Clipboard push button. A sample picture is shown below:

17

The Result Property Tab Based on the information you have entered in the other property tabs in the Define Query screen the Result property tab displays the actual data which will be returned in your report. Below is shown a sample of the Result property tab.

18

Now click on the OK button to view the report. You can go to design time mode to give the report a proper title by clicking on the Toggle Mode button (again marked in red below) or by going to the Report > Report Design menu option.

Input Totals Report Builder pre-calculates specified totals or other aggregate types of calculations and provides the result as input totals to your report. Input totals can also be two-pass totals. All input totals are numeric variables assigned to a field or used by a formula in your report template. Use the Format Totals dialog box to define, modify, and delete the input totals in your report. You can assign input totals to fields just like input items and input variables. In the Define Total dialog box, set the pre-process flag ON to have the totals calculations performed before your report output is generated. If you do this the report data is passed once to the report engine and processed twice by the report. The first pass calculates your report input totals; the second pass formats the report. Alternatively, you can set the pre-process flag OFF. If you do this the report engines calculate the totals on an on-going basis as the report data is fetched for the first and only time.

Defining Totals for Your Report You will need to follow the steps below to create totals in the report. A sample complete with screenshots and steps are also provided.

1. Right-click on Input Totals in the left pane.

19

2. Click New… to create a new input total using the Format Totals dialog box. 3. Enter a name for your new input total. The name must not contain any spaces

and the only special character allowed is the underscore (_). 4. From the Formula drop down list box select the item you want to evaluate.

The items in the drop down list box must first be defined or it will only show inputs. They can be an input item or any numeric formula. Clicking on the Formula… push button opens the Formula Editor Dialog box where you can define formulas.

5. Select the type of statistic you want to calculate from the Statistic drop down list box. Each input total can evaluate one statistic. Select Value if you want to display the last value of the item you selected in the Formula list box.

6. Choose when you want to zero your totals from the Restart Event drop down list. This list box shows all the break group items. Report Builder restarts totals calculations whenever the selected break group changes.

7. Repeat steps 5 to 9 to create all your input totals. 8. Check the Pre-Process box if you want Report Builder to calculate your totals

before it formats your report. Use this if you plan to display break group totals at the beginning of a break group (for example, in the break group header) rather than at the end of the break group. By setting Pre-Process ON you ensure a valid total display at the beginning of the break group.

9. Close the open dialog boxes to return to your report template.

Adding Input Totals to Your Template

1. Drop fields in the appropriate locations of your report template for each input total. If you have totals for a certain break group you can draw the fields in the appropriate header or footer blocks.

2. Assign the input totals to each field. Right-click on the field and select Properties…, then use the Edit button to assign a total to the field.

3. Select Report > Report Preview from the task menu to view your report with the input totals.

The following steps & screenshots will help you generate and use totals within the reports:

1. Define a new query. 2. This time choose the table called BUDGET. 3. Choose the columns BGT_YEAR, DEPT_ID, MONTH_01 and MONTH_02

from the column list.

20

4. Click on the OK button to display the report as shown on the next page.

5. Now go to the menu option Report > Report Design or click on the Toggle

Mode icon in the toolbar to Report Design mode.

21

6. In the left pane, right-click on Totals and choose the popup menu option New… (the menu to select is shown below).

You should then see the Format Tools dialog box as shown on the next page.

7. Click on New… and another dialog box will appear whereby you can create

new totals. Here is an example to create the total for MONTH_01:

22

8. Repeat the same for MONTH_02 and close the window by pressing the OK

button. Now close the dialog box by clicking on the Cancel button or by simply closing the window with the X icon in the top right hand corner.

9. Expand Totals in the left window pane and you will see the newly defined totals. Drag and drop Total_Month01 in the Page footer under MONTH_01 and do the same to Total_Month02 under MONTH_02. It should now look the same as the following picture:

10. Go to the Report menu again and choose the Report Preview option this time. You will see the report and totals at the end of the page (you’ll need to scroll to the bottom of the report to see the totals).

We can also change the title of the report which can be done in design mode by double-clicking on the heading Untitled and then typing in your own report title.

Input Crosstabs Use the input crosstabs feature to gather cross-tabular statistics of your report input rows and to summarize the information your report template receives.

23

The Cross Tabs Data Collector generates a two-dimensional matrix of numeric data based on the categories and data you provide. The size of this matrix depends on the number of unique occurrences of each column and row category you use in your crosstabs definition. You can have from one to eight row categories and from one to eight column categories in your crosstab report. The greater the number of row and column categories the larger and more complex your crosstab matrix is. You must identify each crosstab with a unique name. A report can contain any number of crosstabs.

Formatting Your Crosstab Matrix Your crosstab computations result is a two-dimensional numeric matrix that you assign to any field in your report template. You must set Dynamic Line Wrapping ON for the field containing your input crosstab matrix since you cannot determine the size of your crosstab matrix until all calculations are complete. See the Format Fields command in the Report Builder online help for more information about this feature. Use the crosstab wizard to format your crosstab matrix.

Creating Crosstab Reports In order to create a sample Crosstab report a sample table has been created in the ISLAND database. Please refer to the Create_Sales.sql file for details. To create the Sales table using the script file, follow the instructions as listed below:

1. Launch SQLTalk by double clicking on the sqltalk.exe file, or you can start it from Start > All Programs > Unify > Unify Report Builder 2005.1 > SQLTalk Interactive SQL 2005.1.

The screenshot on the next page shows the SQLTalk window and the Connect button in the icons toolbar.

24

2. Click on the Connect icon in the SQLTalk toolbar and a dialog box will appear (as shown below). Enter the details in the fields as shown below.

3. Click on the Connect button and you will see the following screen (note the Current Connections field):

4. Now click on the Close button and you will see SQLTalk with the connection details in the bottom of the window as marked in red below:

25

5. Go to the menu option File > Open and select the Create_Sales.sql file from the directory you downloaded with this guide.

6. Go to the menu option Session > Execute Script to execute the script. To execute one statement only you can use the Execute icon in the toolbar as marked in red below:

26

To create a cross tab item follow the instructions below: 1. Launch Report Builder. 2. Make a new query by going into Report > Define Query. 3. Choose the Sales table that we just created and click OK. This will create all

the input items needed for the crosstab report we are going to create in the following steps. The Sales table is shown open in the Define Query window on the next page.

4. Go to Design mode by selecting the Report > Report Design menu option. 5. While the defining query also creates the normal column report, we need to

remove all items automatically created by Report Builder except the Report Heading. We do this by selecting each item and clicking Delete to remove. You can also remove the whole line by clicking on the row. Double-click on the heading and enter Sales Report by Quarter as the new heading. You can also remove the automatically created Date and Page Number items.

6. Go to the Crosstabs section in the left pane, right click and choose New… to create a new crosstab item. It will ask to define a row category, so we will choose Sales_Person and click Next>. You can leave the Sort order as it is or change it if required. In the screenshot on the next page you will see that we have selected None as the Sort Order.

Note: To aid you with the following steps we have added comments marked with red boxes in the next six screenshots.

27

7. You will then be asked to choose a column category. Choose QUARTER and

click Next>. Again select None for Sort Order.

28

8. You will then need to choose a Cell Value. Choose AMOUNT and click Next>.

9. In the next screen you can choose to define statistics for both the column and

row if suitable. Select Sum and click Next>. You can leave the Default Headings check box either checked or unchecked (in this sample we have unchecked the Default Headings check box).

29

10. Choose the statistics you would like to see; such as column, row and matrix (choose different options to see the effect). In our sample we will choose all three options and define headings for both column and row summaries as shown in the picture below. Now click on the Last>> button.

11. In the last step we will name the crosstab SalesReport and click OK.

12. Now the newly created crosstab item should appear in the list of crosstabs. 13. It is advisable to use a crosstab item in the page header or footer. Detail Items

are not suitable for Crosstab reports, however Footer or Header items can be used. In our sample we have used a page footer to show the report.

14. In the page footer, create enough empty lines to fit the data we are expecting in the report. You can have Report Builder display the Tools toolbar by going to the menu option View > Toolbars > Tools Toolbar. To add empty lines choose the Line toolbar as marked in red in the screenshot below (more

30

details about the Tools toolbar and other items of Report Builder’s work area can be found in the whitepaper Report Builder Workspace).

15. Go to the Report > Format > Report… menu option and then select the

Page Layout tab. Enter 0 for both the Left and Right margins as shown in the screen below. Click on the OK button to close the dialog box.

16. Drag the SalesReport item from the Crosstab and drop it on the first line of

the page footer as shown in the picture below. Make it the same length as the line by dragging the sides as shown in the picture. You can also add a heading by choosing the background text button from the toolbar (marked in red) and dropping it on the first line of the page header.

31

17. You can also give the report a title as shown in the above screen. This can be

done by selecting a background text tool from the Tools Toolbar and dropping it on the page header.

18. To view the report click on Report > Report Preview or click on the Toggle button. The report should look similar to the picture shown below:

32

19. Now save the report by going to the File > Save As… menu option saving it as Sales_Crosstab.

20. You can now toggle between design mode and preview mode by choosing

the menu option Report > Report Preview or by clicking on the Toggle Mode button.

Picture Objects

Report Builder accepts a large variety of graphical formats that you can input into your picture object. This chapter is a tutorial to help you use the different types of picture objects. Importing a Static Object to Your Report You can add static pictures to your report. A good example would be adding your company logo to the reports. Let us modify the Crosstab report we created earlier to include a picture:

1. Open the Sales_Crosstab.cqt file we created earlier by going to the File > Open… menu option as shown in the picture on the next page.

33

2. Add more lines in the report header by clicking the Line button (marked in red below) from the Tools toolbar. If the Tools toolbar is not visible go to the menu option View > Toolbars > Tools Toolbar.

3. Click on the Picture button located just right of the Line button and then click

in the report header area. Make the picture large enough to see (e.g. altering the size of a logo in a report).

34

4. Right-click on the Picture button and click on Properties… You should then see the following screen:

4. Click on the Paste From… push button to bring up the following screen shown below:

6. Browse and choose the file you want. The file type can be any of the ones listed under Files of type: drop down list. In this example we will choose the picture file Water lilies.jpg from the Sample Pictures folder. (You will also find this file if you have downloaded the samples as mentioned in the Requirements section found at the beginning of this paper.) Now click Open.

35

7. This will bring us back to the Picture properties tab we saw earlier. 8. Click OK and you should then see the picture chosen for the report.

Formatting Picture Objects 1. Choose your picture object and select Report > Format > Picture… to open

the Format Picture property tabs. You can also right-click on the picture object then select Properties… from the context menu thereby opening the same dialog box.

2. Set the picture dimensions on the Appearance property tab. You can also drag the borders of your picture object to resize it.

3. Accept the default Static option in the Source field on the Picture property tab. This tells Report Builder that you are going to import an object, but are not going to link the template to the application in which the object was created.

4. Click the Paste From… push button to open the Paste From dialog box. 5. Select a file type from the Files of Type: drop down list at the bottom of the

dialog box. This list box shows all the file formats you can import into your report template. When you select a file type Report Builder lists all the files in that format under the File name: text box.

6. Select the file you want to import and click Open to return to the Format Picture dialog box.

7. Select the type of border you want to surround your object with (on the Appearance tab) and click OK. Report Builder imports the contents of the selected file into your report. You can see this object in both design and preview modes of the report.

Creating Graphs in Reports The following tutorial steps will help you to create graphs in your report:

1. Create a new query by selecting the Report > Define Query… from the task menu. 2. On the Source tab select the SQLBase ISLAND database and login with your

username and password (as covered before in the Getting Started chapter of this guide).

3. Select the BUDGET table leaving DEPT_ID and a few random months checked but leave other columns unchecked.

36

4. Click OK to proceed with the report. 5. Go to the Report > Report Design menu option. 6. Add more lines to the page footer to make room for the graph. 7. Click on the Graph button (shown right) from the Tools toolbar, drop it onto

the report and drag the edges for the desired size.

37

8. Right-click anywhere on the right side of the graph and select Properties... A

Dimensions box appears on the Graph tab. As you manually dragged and re-sized the graph to the desired size the Dimensions shown in the Properties screen can be left unaltered.

Note: By default the dimensions shown in the screen below are in centimeters rather than inches.

9. Click on the QuickGraph Properties… button. The Graph Control property tabs will appear as shown on the next page.

10. Go to the Data Source tab, select [Report] from the Data Sources list, and click on the Apply Now button.

38

11. Select the Labels (X Axis) tab and check the DEPT_ID box in the Categories (X-Axis) list then click on the Apply Now button.

39

12. Go to the Labels (Y Axis) tab and check the MONTH_01 and MONTH_02 boxes from the Categories (Y Axis) list. Click on the Apply Now button.

13. Go to the Titles tab and enter a title for the graph. You can also enter titles to appear at the bottom and sides if required.

14. Click the Apply Now button and then OK. Close the dialog boxes. 15. Go to the Report > Report Preview menu option and you will see the report

as shown on the next page.

40

Creating Form Letter Reports You will find it quite easy to create letters with merged details from database tables. Step-by-step instructions to create letter reports are given below:

1. Start Report Builder. 2. Create a new query by going to the Report > Define Query menu option. 3. Select the Company and Contact tables and then choose columns as shown in

the following screenshot:

4. Click OK and a report will be created. 5. Go to the Report > Report Design menu option. 6. Remove all items including labels and fields from the report. This can be done

by clicking on each line and hitting the Delete button. 7. Now create a letter report with merged fields as shown in the screenshot

below:

41

Note: If you preview the report now everything will appear correct, but you will not have a page break after each letter. To insert a page break after each report continue with the steps listed below:

8. Click on the last line from detail block then, by going to Report > Format > Line…, you should see a dialog box similar to the screenshot on the following page.

42

9. On the Behavior tab Select the Page Break Before radio button and click OK.

10. If you preview now you will see a letter on each page as shown in the following screenshot:

11. Drop a data field on the line (choose this by clicking the Line button from the Tools toolbar).

43

12. Right-click on the field and choose Properties… which should give you the

following screen:

13. Click on the Edit Field Formula… button and you should see the following screen:

44

14. Choose CurrentDate() from the Functions list and then click OK. This will

make the date and times appear in the letter. 15. If you prefer only the date then you can format this by right-clicking on the

date field and going to the Properties… dialog box. You can choose the formats listed in the Format: drop down list as shown on the next page.

Creating Reports with Conditional Display Conditional Display allows you to hide and show lines or individual items in the report. The following section covers step-by-step instructions to create a report that prints lines based on this condition.

1. Define a new query using the Report > Define query menu option. 2. Choose the INVOICE table and check the COMPANY_NAME, STATUS and

AMOUNT_PAID columns as shown in the screenshot on the next page.

45

3. Click OK. 4. Now you will see all the invoices containing all the amounts. 5. Go to the Report > Report Design menu again. 6. Right-click the empty space on the line (not on the fields) in the detail blocks

section and select Properties…

46

7. Click on the Formula… button in the Conditional Display box. You will see the Formula Editor as shown below:

8. Now we are going to use a function called NumberIFF from the functions list.

NumberIFF(nInput, number1, number2, number3) returns one of number1, number2, or number3, depending on the value returned by the nInput parameter (refer to the Report Builder Online Help for details about any function found under the functions list).

9. Enter the formula as shown in the screen on the next page and click OK.

47

10. Double-click on the NumberIFF formula from the function list. Then highlight the first Number parameter and double-click on AMOUNT_PAID from the Data Items. Subtract 999 and enter 0, 1, and 1 as shown above. Following this formula, if the Number parameter is: (AMOUNT_PAID) -999 and <0 then we return zero. If =0 or >0 we return 1.

Note: This formula is used to conditionally display items. This will make sure the rows are displayed only if the following is: AMOUNT_PAID > = $1000. Now a newly created formula will appear in the Conditional Display field as shown below:

48

11. Click OK and preview the report by going to the Report > Report Preview menu option.

12. Now the report will list only invoice items with the amount $1000.00 or more as shown below:

Note: There are so many formulas that can be very useful when creating customized or complex reports. Refer to Online Help for details.

Creating Reports with LONG Data Stored in Databases Using Report Builder you can create reports from LONG data (i.e. pictures or any other form of binary data) stored in databases. With SQLBase it is stored as LONG VARCHAR columns. With Oracle it is stored as BLOB and CLOB, and other databases also have their equivalent types. The example below uses PRODUCT from the SQLBase table.

1. Go to the Report > Define Query menu option. 2. Choose the PRODUCT table and select a few columns from this table

including the PICTURE (LONG VARCHAR) column as shown on the next page.

49

3. Click OK and Report Builder will create a report containing pictures within seconds such as the one below:

50

Conclusions This paper explored some of the useful features of Report Builder. Report Builder can also be used to create more complex reports. Once you master the basic techniques discussed in this paper you will advance to more sophisticated features which you can find in the whitepaper Report Builder Workspace. The full list of titles in the Report Builder whitepaper series are:

Report Builder Installation & Connectivity For Windows Report Builder Installation & Connectivity For Linux Report Builder Guide To Creating Reports Report Builder Workspace

51

About Unify Unify is a global provider of software development technology and solutions that helps IT customers participate in Service-Oriented Architecture (SOA). Unify’s productive and re-liable development tools, migration solutions and databases enable organizations to build and modernize business essential applications for SOA. Composer for Lotus Notes offers a complete, like for like, production to production migration solution for Lotus Notes applications. Unify’s award-winning NXJ Developer enables IT teams to be extremely productive, learn new technologies fast and deliver Web services-based applications on time and on budget. The Team Developer, SQLBase, DataServer, ACCELL and VISION product families enable cross-platform rapid development on Java/J2EE, Linux or Windows. Unify has a rich heritage in delivering rich, cost-effective technologies to its thousands of IT customers and ISV, VAR and distributor partners. Unify is headquartered in Sacramento, Calif., and can be reached at (916) 928-6400 or by visiting www.unify.com.

Unify Corporation 2101 Arena Blvd., Suite 100

Sacramento, CA 95834 USA

Phone: 1.916.928.6400 Toll Free: 1.800.468.6439

Fax: 1.916.928.6404 Munich: +49 8 115 55430

United Kingdom: +44 (0)1753 245 510 France: +33 (0)1 34 58 28 30

COPYRIGHT © 2007. UNIFY CORPORATION. All rights reserved.

Unify, the Unify logo and Unify NXJ are registered trademarks of Unify Corporation. Composer is a trademark of Unify Corporation.

Java and J2EE are the trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.

All other company or product names are trademarks of their respective owners.