[Tutorial] Report Builder First Steps

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

[Tutorial] Report Builder First Steps

Post by chrisb »

UPDATED 15/Jun/2010. (Because it found its way into Tutorials).
This is a basic guide which new users of Sun Report Builder may find useful.
I wrote it in response to a question asked on the forum.
I think the difficulty in getting to grips with Report Builder is a general lack of basic information/guidance.
I hope you're able to follow my step by step approach.
In order to help users try this I've started by making it easy to create a table & query.
I've used the data contained in the original post in order to build a table.
The data may not be meaningfull but it serves its purpose.

HOPEFULLY THIS GUIDE WILL HELP NEW USERS OF REPORT BUILDER TO QUICKLY GRASP THE BASIC STEPS REQUIRED IN THE STRUCTURE OF REPORTS.

TAKE NOTE YOU CAN COPY TEXT, FORMULAS ETC. FROM HERE INSTEAD OF TYPING IT ALL IN. JUST DRAG THE MOUSE ACROSS IT.

START HERE:

First of all open a new database.
Select from menu 'File/Save As'.
Choose a folder. Name your database. Hit 'Save'

BELOW IS THE TABLE DATA. Highlight the text between the asterisks by dragging the mouse over it. Right click. Select 'Copy'

TABLE
***********************************************************************************

Create Table "PayData_T"
(
ID Integer,
"Hours" Decimal(8,2),
"PayRate" Decimal(8,2),
"Dept" Varchar(1),
Primary key (ID)
)

Insert into "PayData_T" ("ID", "Hours", "PayRate", "Dept") values ('0','40','10', 'a')
Insert into "PayData_T" ("ID", "Hours", "PayRate", "Dept") values ('2','40','11', 'b')
Insert into "PayData_T" ("ID", "Hours", "PayRate", "Dept") values ('3','40','17', 'b')
Insert into "PayData_T" ("ID", "Hours", "PayRate", "Dept") values ('1','40','12', 'a')
Insert into "PayData_T" ("ID", "Hours", "PayRate", "Dept") values ('4','40','23', 'b')

***********************************************************************************

Back to database.
Select from menu 'Tools/SQL', Right Click over window that opens, Select 'Paste'. Hit 'Execute'.
If you don't receive the message 'Command successfully executed' then try copying the data again.
Hit 'Close'.
Under 'Database' left of screen Click 'Tables' icon.
Select from menu 'View/Refresh Tables'. Your new table should now be visible.

BELOW IS THE QUERY TEXT. Highlight the text between the asterisks by dragging the mouse over it. Right click. Select 'Copy'

QUERY
**************************************************************************************************

SELECT "Hours", "PayRate", "Hours" * "PayRate" AS "Salary", "Dept" FROM "PayData_T" AS "PayData_T"

**************************************************************************************************

Back to database.
Click 'Queries' icon.
Under 'Tasks' click 'Create Query in SQL View'.
Click 'Paste' (toolbar icon).
Click 'Run Query' (toolbar icon) to view result.
You can toggle between 'SQL View' & 'GUI' view by clicking 'Switch Design View On/Off' (toolbar icon).
Click 'Save' (toolbar icon).
Name your query (any name ) & hit 'OK'.
Close window.

REPORT INFO STARTS HERE:

TO CREATE REPORT

Click 'Reports' icon
Click 'Use Wizard to Create Report'
In window that opens ensure your query name is showing in top gadget.
Hit '>>' button to insert all fields into report.
Hit 'Next'. Change labels if you wish. All I did was insert a space between 'Pay' & 'Rate'
Hit 'Next'. We're going to group by Department. Click on 'Dept'. Click '>'.
Hit 'Next'. Sort by 'Dept' Ascending, then by 'PayRate' Ascending.
Hit 'Next'. Layout should be 'Tabular'. Orientation for this report can be 'Portrait'.
Hit 'Next.
Input your report title. Select 'Dynamic Report'.

I believe the difference between Dynamic & Static is that Static will only ever display the data that is currently held in your table even when new records
are input. Dynamic will display all.

Select 'Modify report layout'.
Hit 'Finish'.

You can now see the basic layout of your report. Click 'Execute Report' (toolbar icon). Close window when done.

IMPORTANT: Save your data regularly. When you hit save in 'Report Builder' your data is saved to memory.
You then need to go to the 'OpenOffice.org Base' window & hit save there in order to save to disc.

CUSTOMISING REPORT:

When i made this report a fixed horizontal line was automatically generated in the 'Dept Header' below 'Dept' & above 'Hours'.
Locate this line. Select it by clicking the mouse over it. Press 'Delete' key to get rid of it.

Menu 'Edit/Delete Page Header/Footer'
Menu 'Edit/Insert Report Header/Footer'
We need the Properties Pane to be visible on the right side of screen. Hit 'F4' to toggle on/off.
Between the footer/header titles & left side of report there's a blank margin.
Click this margin to view the properties of the various page areas.
Click the grey area below Report Footer to view the 'Report' properties.
Note we have a 'Data' tab here.
Click 'Data' tab. Click '....' button to right of content. Query Design window opens where we can view, run & if necessary edit our query.
Incidentally any alterations we make will only apply to our SQL command & not effect the original query.
Close Window.

Click 'Sorting & Grouping' (toolbar icon). Make 'Group Footer' Present. Close the 'Sorting & Grouping' window.

FUNCTIONS:

We need 2 accumulation functions.
We could use the built in function 'Accumulation' for both functions which would be quicker but in large reports with many calculations User Defined
Functions save time, enable the use of error trapping (empty cells, specific values etc.) & are easier to read.

In order to create these functions we must use the 'Report navigator'.

FUNCTION. Group Function.
We have grouped by 'Dept' (a/b) & wish to display a summation of salary for Department in their respective group footers.
All GROUP functions must be created within their group. Here's how.
NavGroupFunc.PNG
NavGroupFunc.PNG (13.28 KiB) Viewed 30400 times
Click 'Report navigator' (toolbar icon) to open navigator.
Drill Down:- Report/Groups/Dept.
We can now see 'fx Functions'.
Right click 'fx Functions'.
Select 'New Function' & a new function named 'Function' is inserted directly below.
Click 'fx Function' & its properties appear in the properties pane.
PropsGroupFunc.PNG
PropsGroupFunc.PNG (4.19 KiB) Viewed 30400 times
Change the name from 'Function' to 'SumDept'. Enter.
Formula= '[SumDept]+[Salary]'. Enter.
Initial Value= '[Salary]'. Enter. That's it done.

FUNCTION. Report Footer Function.
We wish to display a summation of salary for all Departments in the Report Footers.
If the result of a function is to be displayed in the Report Footer then it must be created in 'Report/fx Functions'. Here's how.

In 'Report navigator'
2nd from top below 'Report' is 'fx Functions'.
Right click 'fx Functions'.
Select 'New Function' & a new function named 'Function' is inserted directly below.
Click 'fx Function' & its properties appear in the properties pane.
Change the name from 'Function' to 'SumSalary'. Enter.
Formula= '[SumSalary]+[Salary]'. Enter.
Initial Value= '[Salary]'. Enter. That's it done.

LABEL FIELDS.
'Label Fields' are used to display descriptive text.
At present we can see four label fields 'Dept','Hours', 'Pay Rate' & 'Salary'
Click on any one & its properties will be shown in the properties pane.
We need a Label Field in the 'Dept Footer'. Here's how.

LABEL Dept Footer.
Click 'Label Field' (toolbar icon).
Drag mouse in 'Dept Footer' to make a rectangle.
Its properties are displayed in the properties pane
Change 'Label' to 'Total Salary For Department'. Enter.
Resize & reposition label field by editing its properties.
Try 'Position x'=0.00cm, 'Position y'=0.20cm, 'Width'=6.00cm, 'Height'=0.5cm.
The easiest way to enter these values is to highlight the existing entry by dragging the mouse over it and then enter the new value .2 etc.
Another way to resize a label field is by dragging its green handles with the mouse.
Another way to reposition a label field is to select it with the mouse & then drag it. Not to be recommended as strange things can happen.
Yet another way to reposition a label field is to select it with the mouse & then click one of the red/blue arrows (toolbar icons).
To move a label field by small increments use the keyboard arrow keys (very useful).
To move a label field by very small increments hold down 'Alt' key & press arrow key.
Click 'B' (toolbar icon) to make bold text.
That's it done.

TEXT BOX.
A 'Text Box' is used to display the result of a function, calculation or field value.
It will often be positioned adjacent to a label field.
We need two text boxes in the 'Dept Footer'. One to display the department('Dept') & the other to display the result of our function('SumDept').

TEXT BOX (Dept Footer/Dept).
Click 'Text Box' (toolbar icon). Drag mouse in 'Dept Footer' to make rectangle.
Position it to the right of label 'Total For Department' using any of the methods mentioned for moving label fields. Position y should=0.2cm.
Resize it by using one of the methods mentioned for resizing label fields. Height should=0.5cm.
Click the 'Data' tab in properties.
'Data Field Type'='Field or Formula'.
Click arrow to right of 'Data Field' & select 'Dept'.
Click 'B' (toolbar icon) to make bold text.

Execute the report to view your work. Close window. Make adjustments as required.

TEXT BOX (Dept Footer/SumDept).
Make another text box. Resize & position it to the right of previous.
Click the Properties'Data' tab. 'Data Field Type'='User-defined Function'.
Click arrow to right of 'Function' & select '[SumDept]'.

Execute the report to view your work. Close window. Make adjustments as required.

HORIZONTAL LINE ('Dept Footer')
Click on 'Horizontal Line' (toolbar icon)
Drag mouse to insert a horizontal line below the gadgets. Use keyboard arrow keys to reposition it if necessary.
Position the mouse over the bottom of the 'Dept Footer' & drag the section all the way up.

REPORT FOOTER.
In the 'Report Footer' we want to display the result of the function 'SumSalary'.
A common requirement within a report is the need to average an amount. We'll also do this.

TITLE
First we want a title.
Insert a 'Label Field' & name it 'Summary:'. Position it at 0,0.
Default text size is 12. A title needs to be larger. Change text size on toolbar to 14. Enter. Hit 'B' (toolbar icon) for bold text.
Resize by dragging handles. Make sure its deep enough.

To produce a neat well spaced report.
Insert a suitable space between the bottom of the 'Dept Footer' & the top of the 'Report Footer' by dragging up/down the bottom of 'Dept Footer'

LABEL1 (Total Salary For All Departments)
Insert a 'Label Field' somewhere below the title.
In the properties pane change the label to 'Total Salary For All Departments'. Enter
Make Position x=0
Make bold text & resize.

TEXT BOX
To the right of the label field insert a 'Text Box'. Make Position y the same as your label field. Click 'Align Right' (toolbar icon).

We now want to select both gadgets.
There's two ways to do this.
1. Hold down shift key then click on each gadget.
2. Ensure 'Select' (toolbar icon) is active then drag out a rectangle around both gadgets.

Position both gadgets by pressing the keyboard arrow keys.

We now want to copy both the label field & text box. Make sure they are selected.
Hold down 'Ctrl' key. Press 'C' key to copy.
Hold down 'Ctrl' key. Press 'V' key to paste. Paste it twice.

TEXT BOX1 (SumSalary)
Click topmost text box.
In Properties Pane click 'Data' tab.
Click arrow to right of 'Data Field Type' & Select 'User-defined Function'.
Click arrow to right of 'Function' & select '[SumSalary]'. Enter.

Execute the report to view your work. Close window. Make adjustments as required.

LABEL2 (Total Salary For Department A)
Edit 2nd Label Field so that Label='Total Salary For Department A:'.

LABEL3(Average Salary For Department A)
Edit 3rd Label Field so that Label='Average Salary Department A:'

FUNCTION (Average)
The average function in base does not work so we'll have to create our own.
The formula will be 'Sum Total' / 'Number of Entries'
For our purposes we need 2 functions.
1. total salary for dept a
2. number of entries for dept a.

FUNCTION1 (SumSalaryA)
In 'Report navigator'
2nd from top below 'Report' is 'fx Functions'.
Right click 'fx Functions'.
Select 'New Function' & a new function named 'Function' is inserted directly below.
Click 'fx Function' & its properties appear in the properties pane.
Change the name from 'Function' to 'SumSalaryA'. Enter.
Remember you can copy & paste from here to save time & eliminate errors.
FunctionWizard.PNG
FunctionWizard.PNG (8.96 KiB) Viewed 30400 times
To the right of 'Formula' click the '....' button. A new window opens up.
input '[SumSalaryA]+if('. When you type in the bracket '(' the function wizard opens.
Test= '[Dept]="a"'
Then_value= '[Salary]'
Otherwise_value= '0'
Hit 'OK'
Initial value= IF([Dept]="a";[Salary];0). Enter
That's it done.

FUNCTION2 (CountA)
In 'Report navigator'
2nd from top below 'Report' is 'fx Functions'.
Right click 'fx Functions'.
Select 'New Function' & a new function named 'Function' is inserted directly below.
Click 'fx Function' & its properties appear in the properties pane.
Change the name from 'Function' to 'CountA'. Enter.

To the right of 'Formula' click the '....' button. A new window opens up.
input '[CountA]+if('. When you type in the bracket '(' the function wizard opens.
Test= '[Dept]="a"'
Then_value= '1'
Otherwise_value= '0'
Hit 'OK'
Initial value= 'IF([Dept]="a";1;0)'. Enter
That's it done.

SECOND TEXT BOX (SumSalaryA)
Select text box to right of label 'Total Salary For Department A:'.
In Properties Pane click 'Data' tab.
Click arrow to right of 'Data Field Type' & Select 'User-defined Function'.
Click arrow to right of 'Function' & select '[SumSalaryA]'. Enter.

Execute the report to view your work. Close window. Make adjustments as required.

THIRD TEXT BOX (SumSalaryA/CountA)
Select text box to right of label 'Average Salary Department A:'.
In Properties Pane click 'Data' tab.
'Data Field Type' = 'Field or Formula'.
Click '....' button to right of 'Data field'
A new window opens.
Input '[SumSalaryA] / [CountA]'
Hit 'OK'

Execute the report to view your work. Close window. Make adjustments as required.

Position the mouse over the bottom of the 'Report Footer' & drag the section all the way up.

TO FINISH OFF.

Make a label field in the 'Report Header'. Name it 'Salary By Department:'. Position it at 0,0.
Position the mouse over the bottom of the 'Report Header' & drag the section up/down to create a suitable gap between 'Report Header' & 'Dept Header'.

Normally with reports i use a text size of 10 which not only produces a neater report but is also often necessary (space).
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Post Reply