safeguard report designer training guide · 2010-07-30 · ~ safeguard report designer training ~...

~ Safeguard Report Designer Training ~ __________________________________________________________________________

SAFEGUARD REPORT DESIGNER WITH GRAPHICS TRAINING GUIDE

July 2010

- 1 –

Printed on Evolve Business 100% recycled paper


- 2 –



- 3 –


Contents

System Overview

Creating a Report Designer Template Previewing & Saving the Template Grouping and Ordering Tools – Icon definition

Inserting and Formatting Fields Variables and Expressions

Linking to Extractor Reports Triggered and Embedded Sections Corporate Aggregated Report Graphs Using Report Designer for a Mail Run

Tables


- 4 –



- 5 –


Overview of System The Safeguard Report Designer is an extension of the Extractor and is a powerful and user friendly reporting tool, giving the user complete control over how the data is formatted within a report. Simple drag and drop techniques are used to select and place fields and the data can be organised using sorting, grouping, lines and complex filtering. Triggered and Embedded sections can be included as can graphs, charts and tables. Templates can be linked to Extractor queries for regular reports and run and emailed automatically to key personnel on a daily, weekly, monthly, quarterly or yearly basis using the Safeguard Report Scheduler. System Conventions The Report Designer uses standard Windows conventions. Use a single left click to select an icon. Use a single right click to display popup menus. Double left mouse clicking to display editing mode, Expression Builder or Text editor.


- 6 –



Creating a Report Designer Template. The first part of the Report Designer process is to select the fields you want to include using the Extractor, for example Incident Type and Cause 1.

Create Extractor Report as normal. Save report Run Report On Results window select Report Designer Icon

A menu similar to the one below will be displayed.

Choose Standard Report The system will now open two new windows, one is the new template the other is the <Select Table> Window. This window will be active. You must choose the table you wish to use for this report before you can proceed any further.

Where more than one report has been linked in the Extractor using the multiple extraction function, each of the table names will be listed in this window. Highlight the required table Click on OK

- 7 –



The <Select Table> window will close and the default template window will be displayed.

This template has had the following properties applied:

Grid

The <Grid> function displays a grid of dots across the template. These dots will make it easier for the user to align fields inserted into the template.

- 8 –



Grid Resolution

The grid resolution has been set at 6 pixels both vertically and horizontally. This can be changed if required by selecting <File> <Properties><Report> from the main toolbar. This will open a window similar the one shown below.

Change settings in the <Grid Resolution> area of the window. Left Click on <OK> to confirm the changes.

Snap

The <Snap> function makes fields placed within the grid automatically align with the nearest set of horizontal and vertical pixels in the grid.

- 9 –



Page Properties The page properties options allow the user to change margins and page orientation. To access this use a right click on the <Standard> tab. This will enable a menu as shown below.

Click on <Page Properties>. This will open a new window.

Margins

These are the ‘print’ margins for the report and can be amended as required. These cannot be larger than the printer supports.

Portrait/Landscape

Click on the appropriate radio button to set the page orientation.

- 10 –



Page Layout

The report template is divided into four areas or bands. The size of these areas can be adjusted to fit the layout of your report and the data included.

To adjust the height, left click on the left end of the title bar for the area and drag up or down to the required size. The height is indicated on the title bar of each area. Please note that all fields that are to be entered into the report must be placed above the relevant Band Header.

Page Header

This area is used for report titles, column headers etc and will be printed on every page of the report.

Page Body

This area is used for the main bulk of the report. This is where listed fields such as Details of Incident will be placed. The height of this area is the height of each line of text that will be printed in the report

Page Footer

Any data fields in this part of the report will always appear at the foot of the page. Details such as page numbers or dates can be added here.

Summary

The Summary is used to add details to the very end of the report. It is in this area that you could insert a chart or graph. Whatever is included in this part of the report will appear after the last line of the main text of the report, but above the footer.

- 11 –



Removing Page Headers and Footers

Page Headers and Page Footers can be removed from a Standard section. This would not generally be done for the first section but could be for subsequent sections in a template. Page headers and footers would be removed from a section when the data in a section is to be printed immediately after the last printed line of data from a previous section rather than on a new page. (Where there is a page header a new page will always be printed). To remove the header and footer from a section right click on the <Standard> tab. This will enable a popup menu.

Use a left click on <Header and Footer Bands>. (The tick indicates that there are headers and footers on the section). This will remove them from the section. Once removed, they can be added back into the section if required by accessing the menu and using a left click again.

- 12 –



- 13 –



Report Sections

The template has a single section that contains the Page Header, Body, Footer and Summary. This is called a ‘Standard’ section. More sections can be added to the template. To add another section, use a left click in the <Standard> tab at the bottom of the template.

This will open a menu similar to the one shown below.

Use a left click on <Add Section> to continue. This will open a further menu for you to choose which type of section you are going to add.

- 14 –



The section types are described below.

Title

This is a separate sheet on which to include an introduction to, or description of your report, or perhaps your organisation logo.

Standard

This section will contain the main body of your report. (There can be more than one ‘Standard’ section in a template) Standard sections can be ‘triggered’ to print based on a user defined expression.

Column/Label

Used for label printing

Cross Tab

Used for setting up data tables etc.

Summary

In this section you could add a textual summary to the report. These additional sections will automatically be printed on separate pages in your report. Select the required section with a single left click and then click on <OK>. There will now be two tabs at the foot of your template.

Select which section you want to work on with a left click on the relevant tab.

- 15 –



Previewing & Saving the Template

It is advisable to regularly preview the report template to see the formatting and layout is what you expect. Along with previewing it is best to save the template file regularly in case of any system faults.

Previewing

As you design the report template, previewing it regularly will give an idea of how the report will look when it is run. The preview will also allow you to see the report coming together with Live data taken from the original extractor query.

Click on the <Preview> icon at the top of the page to view the report.

Saving

As you design the template, saving the template regularly will eliminate any problems that could arise should there be any system problems. With the template saved regularly you can open it from where you last left it rather having to start all over again.

- 16 –



- 17 –


NOTE: Please make sure that the template file is saved in a location where Safeguard can access. Please do not save the file to a local drive or local folder, as Safeguard will run the report but will be unable to find the template file. Try to save the file where the system prompts you to, normally the Reports folder on the Safeguard server.


- 18 –



Group As with the Extractor, data can be grouped by one or more selected data fields, for example Directorate or Incident Type. To do this select <Group> from the main window toolbar. This will open a window similar to the one below.

In the left of this window is a list of all of the fields selected in the Extractor. Choose which one(s) to group by, by highlighting the field name with a left click and then using a left click on the right arrow icon.

The field name will then appear on the right of the window. Select all grouping fields in this way. As with the Extractor, the order in which these fields are selected is important. Select the primary grouping field first, then the secondary and so on. If you select a field in error, or in the wrong order, deselect by highlighting the field name on the right of the window and then using a left click on the left arrow icon.

Alternatively double left clicking on the field will either add or remove the field within the Grouping window. When you have completed your selection, click on OK. To cancel the process, left click on <Cancel>.

- 19 –



For each field selected for grouping, there will now be a new header and footer bar in the main template window. These will have been added above and below the Page Body band and are additional areas into which data fields can be inserted.

Each Header and Footer will be labelled with the name of the appropriate data field. Use the Field Wizard to insert the field on which you have grouped (in this case it is Site) into the Group Hdr band.

The data displayed in your report will be sorted by the group data field(s). Additionally, each individual entry for this group, (i.e. each Site name), will only appear once in the report, rather than being listed with the other data fields (unless you have also put the same field into the Page Body band). This is shown in the sample report below.

- 20 –



Defining Groups

It is also possible to define groups to be used within the template based on more than one field, for example surname and first name fields can be linked together so data is grouped on staff/person full name rather than just surname, i.e. Fred Jones records will be grouped separately to Susan Jones records.

To define a new group go to <Groups> and click on the <Define> button.

This will open the <Define Group> window as shown below.

Group Name

Define the name to be used for the group, i.e. ‘Person’ or ‘Staff’.

- 21 –



Expression

Enter the expression to be used for the grouping. Either type directly into the field, or click on the <Exp> button to open the <Expression Builder> window.

To use surname and first name for the group, based on the field names displayed above, the following expression would be used.

Names.Surname+Names.First_Name To enter the expression, use a double left click to select the first field name from the <Fields> list, click on the + button, then double click on the second field name. Click on <OK> to confirm the details. Please note that when running the above example, you should ensure that the report also has an Order by Surname and First Name.

- 22 –



Order The data in your report will not be automatically sorted in alphabetical or numerical order. This is achieved by using the Order function. Select <Order> from the main Extractor window. This will open a window similar to the one shown below.

In the left of this window is a list of all of the fields selected in the Extractor. Choose which one(s) to order by, by highlighting the field name with a left click and then using a left click on the right arrow icon.

Note: If you have already or are going to use the Group function, you should firstly select the report to be ordered by the Grouping fields. For example, if your report is grouped by site, select that first. Then select any other fields to order by, i.e. Incident form number or incident date. The field name will appear on the right of the window. Select all order fields in this way. As with the Extractor, the order in which these fields are selected is important. Select the primary ordering field first, then the secondary and so on.

- 23 –



By default, data will be listed in ascending order. If you would the data listed in descending order select the appropriate field on the right of the window with a single left click. Select <Descending> from the dropdown list at the foot of the window.

- 24 –


(A) = Ascending (D) = Descending

If you select a field in error, or in the wrong order, deselect by highlighting the field name on the right of the window and then using a left click on the left arrow icon.

Alternatively double left clicking on the field will either add or remove the field from the Sort Order window. When you have completed your selection, click on <OK>. To cancel the process, left click on <Cancel>.

Unique Order

If this function is selected it will have the effect of printing only one line of data for each different entry in the exported table. For example, if you apply the Unique Order function on the Incident Date field, only one incident for each incident date will be printed on your report, regardless of how many incidents happened on that date.


- 25 –



Tools - Icons There are several icons on the tool bar which are used for inserting and formatting fields.

Pointer

The Pointer is used to select, move and resize fields that have already been inserted.

Line

The Line tool is used to add vertical and horizontal lines to the report to help define areas. For example you might want to add a line below the column headers or create a table layout for the data. The option to add vertical lines to the report has now been extended to include the option of automatically resizing the height of any vertical lines in line with auto sizing memo fields

Rectangle

The Rectangle tool is used to insert rectangles and squares. These may be used to outline report titles or areas of text.

Ellipse

The Ellipse tool is used to add circles or ovals outlines to text fields in reports.

Field

The Field tool is used to insert expression fields.

Text

The Text tool is used to add text fields to the report, i.e. titles and column headers.

Picture

The Picture tool is used to insert pictures and images such as logos into the report.

Note: When using the tool icons, the appropriate icon must be selected BEFORE selecting data, inserting a field, etc. After using any of the Tools, please remember to click back onto the Pointer tool to carry out any formatting of fields, lines, text boxes, etc.

- 26 –



Inserting Fields

Any of the fields from your Extractor report can be added to the template. In addition, text fields, variables and expressions (calculated fields), can be included. There are also some standard fields or ‘Functions’ for date and page numbers that can be added. Note: You cannot ‘free type’ anywhere in this window. You have to add a text field, and enter your text into that field.

Text Fields

Text fields are used for report titles, column headers etc. To add a text field select the <Text Tool> from the window tool bar with a single left click.

Now move the mouse cursor to the area where the text field is to be inserted, i.e. the Page Header section for titles. Using a left click, drag a box to the approximate size required for the text, starting at the top left corner of the box. When you release the mouse button you will have a text box with the cursor flashing ready to start typing.

Type in your text. When you have completed the text, click outside of the text box. Do not press return, or tab. Do not drag the mouse otherwise you will create a spare text box in the middle of the report. You will now have a text field in the report similar to the one below.

The text in this field can be formatted and aligned. There are also functions to align the field within the page area.

- 27 –



Editing a Text Field

Once the field has been added to the report click on the pointer tool will not allow editing of the field. You will need to reselect the Text Tool and click on the field to edit them, i.e. changing the spelling or words.

Alternatively, if you have a newer version than 8.9.2.0, you will be able to double left click on the field and it will automatically open the Text Tool.

Deleting Text Fields

To delete a text field, use a left click on the pointer tool followed by a left click on the field to be deleted. (The field will have squares at each corner and each side, see below). Now press the delete key.

- 28 –



Using the Field Wizard

The field wizard tool is used to insert fields (table fields from the Extractor, variables and functions) into the template. This option is accessed from the main window tool bar. Select <Tools> <Field Wizard>.

This will open a window similar to the one shown below.

The list of fields in the Field Wizard includes all of the fields from the original extractor report, plus <<Functions>> and <<Variables>>.

Functions

These functions are standard for all report designer templates. Page No() - Adds the page number to each page of printed report. Time() - This field adds the time the report was printed.

This is taken from the time setting on your PC.

Today() - This adds the date the report was printed. This is taken from the date setting on your PC.

- 29 –



Listed under Variables will be any Expression you may have created. How to set up expressions is covered in a later part of the course, (Page 56 onwards). Use a left click to ‘Drag & Drop’ fields into the relevant area of the template, which is above the appropriate Band Header. Fields are not removed from the list when you do this. They can be used more than once if necessary. When you have added all the required fields, left click on the <Close> button to close the Field Wizard. Your report template will now look similar to the one shown below.

- 30 –



Memo fields

Memo or description fields (i.e. Details of Incident) when inserted into a template will by default only show the first line of text in the field. To force the Report Designer to show all the text within this field, use a right click on the field to enable a popup menu.

Select <Field Style>. This will open a new window as shown below.

Use a left click to put a tick in the <Vertical Size> check box and click <OK>. The template and field(s) will look the same but when printing the report, the <Page Body> section for each record will adjust vertically to ensure all text is displayed.

- 31 –



Deleting Fields

To delete fields added from the Field Wizard, select the pointer tool with a left click, followed by a left click on the field to be deleted. The field will have squares at each corner and each side as shown below.

Now press the delete key. This only removes the selected field(s) from the template, not from the <Field Wizard>.

Selecting Multiple Fields

If you want to highlight more than one field for deletion, edit, resizing or moving position in the template, simply hold down the <Shift> key and left click on each of the required fields. Release the <Shift Key> when you have completed your selection. Alternatively, place the mouse pointer below the first field you will be selecting, whilst holding the left hand mouse button down drag a box over all the fields that want to be selected and let go. This should now highlight all the required fields. Alternatively, select all of the fields in one band by using a right click on the band footer. This will enable a popup menu.

Select <Select All in Band> with a single left click. Any changes made will affect all of the highlighted fields.

- 32 –



Inserting Pictures and Images

To insert a picture into the template, i.e. your Trust logo and details, select the picture tool.

Use a left mouse click to drag an outline where you want the picture to be inserted i.e. Page Header for the trust logo. As soon as you release the mouse button a window similar to the one below will be opened.

Select Picture Source

The system defaults to ‘Directly from File’. This allows you to search via a normal Windows search window to find the image. Choose the appropriate picture source using a single click on the correct radio button.

Picture Type

Choose the type of file you are inserting into the template. Choose the appropriate picture type using a single click on the correct radio button. Once you have selected the Source and Type, click on the OK button. If you have chosen ‘Directly from file’ a search window will open allowing you to browse for the required image. Select the image with a double left click. It will then be inserted into the template.

- 33 –



Formatting Text

The text in any field, whether it is a text field, a data field inserted from an Extractor report or an expression, can be formatted, i.e. font type and size, underline and bold. Move your mouse cursor over the text field to be updated and use a right click. The system will then display a popup menu similar to the one shown below.

Select <Font> with a single left click. This will open a new window similar to the one shown below.

Make the required changes to the text using a single left click on the available format options. Click on OK to confirm the changes.

- 34 –



Borders and Backgrounds

All fields, including text boxes can have borders and backgrounds added to them. There are several options for the type’s border or background that can be applied.

Borders

To add a border to a field, insert the field(s) into the template, then use a right click on the field(s) to enable a popup menu as shown below.

- 35 –



Select Border Style with a single left click. This will open a new window as shown below.

Select the required line ‘style’ from those displayed, then select the appropriate border position icon, i.e.

Only at the top of the field. Only at the bottom of the field. All around the field.

More than one of these options can be used on a field. For example, a border could be added to the top and bottom of a field, or the left and right. The effect of your choices will be previewed in the top left area of the window.

- 36 –



Backgrounds

To add a background to a field, insert the field(s) into the template, then use a right click on the field(s) to enable a popup menu as shown below.

Select Background with a single left click. This will open a new window as shown below.

Choose the appropriate ‘Fill Style’ from the list and set the colour of the ‘fill’ by clicking on the Select Colour button and choosing the required colour.

The bottom section marked Field Expression is used for adding a logical expression for the field, i.e. using Actual Impact colour to change the background colour of the field on your report.

- 37 –



Alignment

Once you have inserted your fields into the template you have several options to align the fields within the bands and also to align the text within the fields. This can be applied to all field types.

Text Align

To align the text within a field, move the mouse cursor over the text and use a right click to enable a popup menu.

Select <Text Align> with a single left click. This will open a window similar to the one below.

Left Align

This will align the text to the left of the field.

Centre

This will centre the text in the field.

- 38 –



Right Align

This option will align the text to the right of the field.

Full Justification

This option would be used on details fields such as incident description and will wrap the text using the full width of the field as shown below.

These options apply to all text, whatever type of field it is.

Vertical Text

You can now change the direction of the text for those narrow columns to give you Vertical Text

- 39 –



Select All in Band

You can apply the same text format to several fields within a single band in one go. To do this, use a right click on the Band Footer. This will enable a popup menu.

Select <Select All in Band> with a single left click. This menu will then close and all of the fields within the band will have been selected for edit.

Continue as before, with a right click on any of the selected fields to enable the popup menu. All format changes will be applied to all of the selected fields.

- 40 –



Align (Fields within the Report) You can drag and drop fields wherever you want within the bands, but getting them lined up exactly can be tricky. Applying the <Grid> and <Snap> functions helps, but there are further alignment options that can be applied to all fields so they do line up precisely. To align a field within a band, move the mouse cursor over the field and use a right click to enable a popup menu.

Select <Align> with a single left click. This will open a window similar to the one below.

- 41 –



- 42 –


The options on this window are as follows:

Align o

the band and can be used in conjunction with the <Centre Vertically> option.

Align o

the band and can be used in conjunction with the <Centre Vertically> option.

Align o

band and can be used in conjunction with the <Centre Horizontally> option.

Align o

band and can be used in onjunction with the <Centre Horizontally> option

ft> and Align right> alignment options nd the <Centre Horizontally> option.

n Left Margin

This option will move the field to the left margin of

n Right Margin

This option will align the field to the right margin of

n Band Top

This option will align the field to the top of the

n Band Bottom

This option will align the field to the bottom of thec

Centre Vertically in Band

This option will move the field to the vertical centre of the band and can be sed in conjunction with both <Align leu

a


Centre Horizontally in Band

This option will move the field to the horizontal centre of the band and can be used in conjunction with both the <Align top> and <Align bottom> options and the <Centre vertically> option.

Set Position/Size

An alternative method of alignment is the <Set Position/Size> option. Move the mouse cursor over the field and use a right click to enable a popup menu.

Select <Set Position/Size> with a single left click. This will open a window similar to the one below.

With this function you can position one or more fields anywhere within the band by using the ‘Left’ and ‘Top’ settings. Left places the field in relation to the left margin of the band and Top, places the field in relation to the top margin. The <Width> and <Height> settings relate the size of the field.

- 43 –



Select All in Band

You can apply the same alignment format to several fields within a single band in one go. To do this, use a right click on the Band Footer. This will enable a popup menu.

Select <Select All in Band> with a single left click. This menu will then close and all of the fields within the band will have been selected for edit.

Continue as before, with a right click on any of the selected fields to enable the popup menu. There are some additional options available on the Align menu when all fields are selected.

- 44 –



Align Left

This option will align the fields to the left end of whichever field the cursor was on when the popup menu was selected. In this example, the fields will be aligned with the left edge of the middle field, as shown below.

This option can be used in conjunction with several others from the Align menu.

Align Right

This option will align the fields to the right end of whichever field the cursor was on when the popup menu was selected.


Align Top

This option will align the fields to the top edge of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

Align Bottom

This option will align the fields to the bottom edge of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

- 45 –



Align Horiz. Centre

This option will align the fields to the horizontal centre of whichever field the cursor was on when the popup menu was selected.


Align Vert. Centre

This option will align the fields to the vertical centre of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

Even Horizontal Alignment.

This option will align the fields end to end in the band.


- 46 –



Field Sizing

There are several ways to adjust the size of a field that has been inserted into your template. The Simplest way is to select the field to be resized using the Pointer Tool. Select the pointer tool with a single left click, and then left click on the field to be resized.

When you do this squares will appear at each corner and on each side of the field as shown below.

Move your mouse cursor over an appropriate square and using a left click, drag the field to the required size. Alternatively, if when the field is highlight, you can use the keyboard arrow keys to move the field. Holding down the Shift key and using the arrow keys will allow you to change the size of the field.

A second method of resizing fields is to move the mouse cursor over the field to be resized and use a right click. This will enable a popup menu.

- 47 –



Select <Set Position/Size> with a left click. This will open a window similar to the one below.

The Width and Height fields can be used to precisely size the field. This functionality can be used on more than one field at the same time. To select all the fields in a band, right click on the band footer and select <Select All in Band> from the popup menu with a single left click. Then follow the same procedure as described above. Make sure that any Lines have been added to the report are not selected, otherwise you will not be able to perform any formatting to Fields. To highlight a selection of fields in a band, hold down the <Shift> key and left click on the required fields. Then proceed as before. When selecting all of the fields in a band, you have an additional way in which you can resize fields. Use the right click on the band footer and select <Select All in Band>. Right click on one of the chosen fields to enable a popup menu.

- 48 –



Left click on <Align>. The Align window will open. In this window are three options for sizing fields.

Same Size

This option will resize the fields to the size of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

Same Vertical Size

This option will resize the fields to the vertical size of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

Same Horizontal Size

This option will resize the fields to the horizontal size of whichever field the cursor was on when the popup menu was selected. This option can be used in conjunction with several others from the Align menu.

These simple functions could be used to produce a simple report design as shown below.

- 49 –



Background Tab

When textual fields such as Details of Incident are used in a table layout in a report the <Borders> function will not work successfully. This is because the vertical size of the details field for each record will be different. In these reports the <Line tool> should be used with the <Background> tab.

This tab is accessed by clicking on the bottom left corner of the design window.

- 50 –


This will open a new window as shown below.

The <Line Tool> can now be used to insert vertical and horizontal lines into this window to create a table view of the data. This window and the Print Preview window can be open at the same time to aid placement of the lines.


- 51 –



Variables

Within Report Designer you can set up calculated fields – Variables. These can be as simple or as complex as necessary, i.e. you might wish to have the number of cases totalled on the report. To create a variable select <Variables> from the main Extractor window tool bar. This will open a window similar to the one below.

Name

In this field enter the name of the variable. You can call them anything you want to, but try to keep to some sort of standard conventions. Names for numerical calculations would perhaps be prefixed with ‘n’.

Position

This indicates the hierarchical position of the variable within your report, i.e. is it the first, second or third one to be inserted in the template. If you are using variables for totals by groups and overall totals, total by groups would be position 1, overall total, position 2.

Initial Value

Enter the starting value of your calculation. In the majority of cases this will be 0.

Reset Level

In this field you indicate where the calculation will start again from its Initial Value. For example, when calculating the number of cases by Department (where Department is Grouped), the reset level would be for each different Department. From the dropdown list, select Department. All Grouping fields will be listed in the Reset Level field. Other reset level options available include <Report> and <Section>.

- 52 –



Expression

In this field, type the actual expression. This will start with the variable name, followed by := and the calculation e.g. gtotal:=gtotal+1 Note: the variable name must be entered exactly as it appears in the <Name> field. Once you have created your Variables, they will be listed in the Field Wizard window and can be inserted into the template from there. The example below show the variables to calculate the number of cases by Department, where there is a single line entry for each record to be counted, i.e. there is listed as well as grouped data.

Variable – Total for “Grouped By” Department

This will then need to be inserted into the Group Footer for Department using the Field Wizard.

This will add the number of cases as the last line of data for each Department.

- 53 –



Variable – Total for the Report

To calculate the number of records, follow the same process as for a group total but for Reset Level choose ‘Report’, i.e.

Using the ‘Field Wizard’ place the variable in the Summary section of the report.

This will ensure that the total value appears immediately after the last line of data printed.

- 54 –



Note: For reports where there is no ‘listed’ data and you have used ‘Group’ in the Extractor, there will be fields called ‘Incdet.Total’. This field can be used in variables as shown below. (Data Source name for this example is IncDet)

This variable will add all the values in the total field for each department – i.e. the total for each Cause Group as shown below.

- 55 –



- 56 –



Expressions Expressions are used for <print when> statements, for <filters> and <Triggers> and can be used to automatically insert Extraction Criteria details from the Extractor report into the template, for example, to insert the date range for the report into the title.

NOTE: If you want to edit a saved expression within the field, you will need to use the right hand mouse click and select <Field Style>, see below:

Once the Field Style window open, clicking on the button in the Expression section with the expression printed on it, see below. The screen should then open the Expression Builder and allow the user to edit the Expression if required.

- 57 –



Alternatively, if you have a newer version than 8.9.2.0, you will be able to double left click on the field and it will automatically open the Expression Builder.

Set <Functions> as listed in the window below can also be used when creating an expression.

- 58 –



Add a Date Range field to the Template

To do this you use the Field Tool icon.

Use a left mouse click to ‘Click & Drag’ where you want to insert the field in the template, i.e. the Page Header. When you release the mouse button a window similar to the one below will open.

Enter the correct syntax in the white area at the bottom of the window, using the appropriate icons to add symbols. When building an Extractor query which has a date range as part of the extraction criteria, use the following syntax to show this on the report “From” +GetExtracts(1,1)+ “to” +GetExtracts(1,2)

- 59 –



To ensure the correct references are used, refer to the Extractor <Properties> window. i.e. Get extraction Criteria, row 1 Get extraction Criteria, row 1 Incident Date, column 1(from date) Incident Date, column 2 (to date) GetExtracts(1,1) GetExtracts(1,2)

- 60 –


In this example, GetExtracts(2,1) would be used if the name of the directorate for the report was to be automatically inserted. (Row 2, column 1)

The words in speech marks, “ “, will be inserted around the extraction criteria data in the report, exactly as entered. Spaces can also be inserted in this way.


Once the expression is complete, click on the <OK> button. The system will display a message if it detects an error in the syntax and ask if you wish to return to the Expression Builder. If the syntax is OK, you will return to the main window. The expression will be inserted in the template as shown below.

All of the text formatting and field sizing and alignment functions are available for this type of field. When printing the report the text will be displayed as shown below.

Useful Expressions For further Useful Expressions which are available, please refer to the Appendix at the back off the manual entitled Appendix A – Useful Expressions.

- 61 –



- 62 –



Page Breaks

There is the facility to force a page break at the end of each ‘Grouped’ set of data, i.e. for each new Department or Directorate. To do insert a page break, use a right click on the ‘Group Ftr’ bar. This will enable a popup menu.

Choose ‘Force Page Eject’ with a single left click. All details from the Page and Group Header sections will be printed on each new page.

- 63 –



Print on Every Page

An additional function that can be used on its own or in conjunction with <Page Break>, it is ‘Print on Every Page’. This can be used when there are many items in the list for each of the Grouped fields and so the list may be more than one page long. In these circumstances it is likely you will want the Group title (i.e. the Directorate or Department), to be printed on each page so there is no confusion as to which group the information relates to. To force the title to be printed on each new sheet, when necessary, use a right click on the appropriate ‘Group Hdr’ bar. This will enable a popup menu as shown below.

Select Print on Every Page with a single left click.

- 64 –



Print When The <Print When> function is used when a field is only to be printed under certain defined conditions. For example coloured text fields can be printed to indicate a risk rating. Each colour would be printed with the appropriate score.

Print When can also be used with variables to print fields a defined number of times within the report. This can be used for example, on Risk Register reports where the details of a risk might be repeated because there are multiple actions or controls associated with the risk, or to print the top ten risks from the database.

Using Print When for Colours

To print colours for risk ratings or scores, first create the required number of fields using the <Text tool>. Apply the colour using the <Background> option from the right click menu as shown below.

Select the background colour by clicking on the Select Colour button to have access to the palette. Once the background colour has been applied use a right click on the field to enable to menu and select <Print When>.

- 65 –



This will open the <Expression Builder> window.

To print the field only when the Risk Rating is ‘High’, double click on the Risk Rating field in the <Fields> list. Click on <Browse Field Values> to view the exact description required.

Enter the description as shown below. NOTE: This must be EXACTLY as seen in the <Browse Field Values> list.

- 66 –



Click on <OK> to confirm the details. Repeat this for each field to be coloured. The fields should then be placed on top of each other in the template. For the other coloured fields they may look like the examples below:

If the risk scores are to be used for the <Print When> rather than the Risk Rating the expression would be set up as shown below.

Using Print When with Variables

To print just the 10 highest rating incidents a variable would be used to count the records in the report and the print when to only print the records if the ‘count’ was below 10. The variable would be set up as shown below.

The <Print When> would then be applied to the <Page Body> and the expression would be as follows:

- 67 –



- 68 –



Extra Functions Date Formats Dates are displayed in the Report Designer in DD/MM/YYYY format. There are several functions that can be used to alter how they are shown in a report, for example, to show the just month name or the day of the week. This is set up using the Field Tool.

Month Name (CMonth)

To display the name of the month (i.e. July), the function ‘CMonth’ is used. Use a left click on the <Field Tool> button and then left click and drag a box where you want to insert the date in the template, i.e. the Page Header. The Expression Builder window will open.

Use a double left click on ‘CMonth(d)’ in the <Functions> column. CMonth(d) will now appear in the white area at the foot of the Expression Builder window, as shown above. Move the cursor to the lower part of the window and left click again between the brackets and highlight the ‘d’ as shown below.

- 69 –



Now use a double left click on the required date field in the <Fields> column. This will update the expression to include the date field as shown below.

Click on OK to save the expression. Additional text can be added to the expression using speech marks. For example, for a report title to read ‘Clinical Incidents for *name of Month’ enter the following in the foot of the Expression Builder window

"Clinical Incidents for "+CMonth(Incdet.Incident_Date) This will display in the report as shown below.

- 70 –



Using CMonth and GetExtracts

When using the ‘GetExtracts’ function to insert the dates from the Extractor Properties into the report, the date is inserted in ‘character’ NOT ‘date’ format. The CMonth function will only work on fields that are in ‘date’ format. So, when using CMonth with ‘GetExtracts’ an additional function must be used to convert the date from ‘character’ to ‘date’ format. This function is called ‘CTOD’ (Character to Date). The expression required to give back the month from a date in the Extractor Properties window is quite complex and is best created in 3 separate stages as described below. Use a left click on the <Field Tool> button and then left click and drag a box where you want to insert the date in the template, i.e. the Page Header. The Expression Builder window will open.

The final Expression will look as below, just need to build up the expression using the following stages

- 71 –



The first stage in creating this expression is to enter the ‘GetExtracts’ function. Enter the appropriate syntax as described in the Expressions section of this manual (pages 58-60) i.e. GetExtracts(1,1)

The second stage is to insert ‘CTOD’. Move the cursor to the beginning of the expression and double left click on CTOD(c) function.

Make sure the GetExtract function is in between the brackets by removing the (c) from the CTOD function. The final stage is to add the CMonth(d) function, At the beginning of the expression, before CTOD, use a double left click on the ‘CMonth(d) in the <Functions> column. Make sure the rest of the expression now sits between the brackets.

Click on OK to save the expression.

- 72 –



Using CMonth and GetExtracts, adding Year to the Expression

If the month and year are required, i.e. June 2010 the ‘year’ function will be used. The following will need to be added to the expression shown above. +” “+str(Year(CTOD(GetExtracts(1,1))),-1) ‘year’ is in the list of functions and will display the year from the date entered in the Extractor Properties window. ‘+” “+’ will ensure that there is a space between the month and the year when the date is displayed. The complete expression will read CMonth(CTOD(GetExtracts(1,1)))+” “+str(Year(CTOD(GetExtracts(1,1))),-1)

The date would be displayed as ‘June 2010 ‘ rather than ‘24/06/2010’, see below:

- 73 –



Add a Name field to a Template

When you have listed Person or Staff names in the Extractor report, these details can be used in the Report Designer template as any other field. When inserting these fields you would align them side by side, as shown below and want a single space to be between the two fields

The names will be displayed as shown below.

To ensure even spacing you will need to ‘trim’ the blank spaces from the end of the name to the end of the field, i.e. the field length might be 20 characters, but the first name of a person will vary from 1 character up.

- 74 –



Use the ‘Alltrim(c)’ function to do this.

‘Alltrim’ will remove all of the blank spaces after the first name and the “ “ will insert a single character space between the first name and surname. The names will now be displayed as shown below.

This could be used on any fields where necessary, i.e. Address fields Titles Names

- 75 – Printed on Evolve Business 100% recycled paper


Trim Functions Alltrim

This function is used on its’ own or in conjunction with others. It ‘trims’ the blank spaces between the end of the text in a field and the actual field length. For example, a name in a field might be 10 characters long, but the actual field length as specified in the database is 25 characters, so there are 10 ‘blank’ spaces at the end of the name. This may cause a problem when the text is printed as shown below.

In this case, theTitle, First Name and Surname fields will be used in a single expression where the ‘Alltrim’ function is applied to both the Title and First Name fields. This function is applied via the Expressions window. To do this, use the Field Tool icon. Using a left click, drag and drop a field in the appropriate place in the template. When the left button is released the Expression Builder window will open. The expression is entered in the white area in the lower part of the window as shown below.

Note: the speech marks are used to insert a space between the printed text. Click on OK to confirm the changes.

- 76 –



When printed the text will now be as shown below.

Left This function is used to truncate the description displayed in the report. For example, Incident Causes may have codes in the descriptions, i.e. ‘F001 – Fall from Bed’, and only the codes are wanted in a report. ‘Left’ can be used to print a set number of characters from the description, starting from the left. i.e. if the field is 20 characters and the first 4 (F001) are all that is required using Left(field_name,4), will remove all characters after the first four from the description.

Right

This function is used to truncate the description displayed in the report. For example, Incident Causes may have codes in the descriptions, i.e. F001–Fall from Bed, which are not wanted in a report. ‘Right’ can be used to ‘trim’ a set number of characters from the description, starting from the right. i.e. if the field is 20 characters and the first 5 (F001-) are not required using right(field_name,15), will remove the first 5 characters from the description, so displaying only the ‘Fall from Bed’ part of the cause.

- 77 –



Upper and Lower Case Details such as names will not always be entered into the database in the same format, i.e. inconsistent use of upper and lower case letters. This will obviously mean that the text will appear in the Report Designer report in the same inconsistent format.

There are functions in the Report Designer which can be used to standardise the format of text within a field.

The following example shows how these functions can be used to print Surname and First name in the format ‘Jones John ‘ whatever the case the name has been entered in the database.

This requires 3 functions to be applied within the Report Designer.

1) To remove all trailing spaces from the Surname field- Alltrim 2) To change all upper case to lower case - Lower 3) To change 1st letter of each word to upper case - properize

To do this use a right click on the required name field and choose field style form the popup menu.

Click on the button in the Field Definition of this window.

This will open the Expression builder window.

The expression already entered in this window is using the Alltrim function to remove trailing spaces from the Surname field.

Click in the box, so your cursor is in front of Alltrim, then scroll through the list of Functions to find Lower(c).

Use a double left click on Lower(c). this will insert it in front of Alltrim as shown below.

- 78 –



Insert your mouse cursor before Alltrim, as before and delete ‘c)’. Now add another ‘)’ after ‘First.Name)’. So this now reads as shown below.

Now, click on the box, so your cursor is in front of ‘Lower’ then scroll through the list of Functions to find ‘Properize(c)’.

Use a double left click on ‘Properize(c)’. this will insert it in front of Lower as shown below.

Insert your mouse cursor before Lower and delete ‘c)’. Now add ‘another )’ after ‘First.Name))’. So this now reads as shown below.

Click on <OK> to save these changes. This will now display all names as required.

- 79 –



Logical Fields

Logical fields are exported from the database as .T. (true) or .F. (False).

To display these fields in the Report Designer as Y or N, insert the field into the template then use a right click to enable a popup menu. Select <Field Style>. In the <Picture> field enter ‘@Y’.

Alternatively, to display a logical field as ‘Yes’ or ‘No’, insert the field into the template then use a right click to enable a popup menu.

Select <Field Style>.

Click on the <Expression> button to access the Expression Builder window. In the field at the bottom of this window enter the following:

iif(field.name,"Yes","No")

For example, to display the Patient Safety field as ‘Yes’ or ‘No’ enter:

iif(Incdet.Patient_Safety_Inc,"Yes","No")

- 80 –



- 81 –



Filter

The Filter option allows you to decide what data from the database you want to be included in the report. For example, you could run one Extractor report that contained all of the data required for two different reports and using the filter function define which data is to be used in which section within the designer template. In this example, the data for each site will be put into a different section of the report. This process will need to be repeated in each section of the report template. To apply a filter to an inserted field, select the Pointer Tool and click on the field to which the filter is to be applied. Then select <Filter> from the main window tool bar.

This will open a window similar to the one below.

Listed in the <Fields> section of this window are all the fields selected in the extractor. Use a double left click on the field to which the filter is to be applied, i.e. Incdet.Department

- 82 –



The field name will now be highlighted in the fields list and displayed in the lower part of the window. Use a single left click to move the cursor next to it, as shown below.

Insert the = sign by either using the = key on the keyboard, or the = icon on the window. Next add speech marks “

To select which Department is to be included in this section of the report, left click on the Browse Field Values button. This will display a list of all of the Departments included in the extractor report.

Write down the EXACT details of the name of the Department, i.e. ‘Patients Home’, as this will have to be typed into the Expression window. When it is typed into the expression window it must include all capitals, apostrophes, hyphens etc. If the name is not accurate the filter will not work. Add another set of speech marks at the end of the Department name.

- 83 –



Confirm the expression by clicking on the <OK> button. If there is an error in the syntax, an error message will be displayed.

Select <Yes> to return to the expression window to check the details. This process will need to be repeated for each section of the report. If two sites were required in each section of the report the expression would be set up as follows:

Using Logical Fields with Filters For logical fields, i.e. those fields that are either ‘Yes’ or ‘No’ (For example deceased), use the following expressions: To set the filter to only ‘Yes’ use the field name i.e. extrpdta.deceased To set the filter to only ‘No’ use ‘!(space)’ and the field name i.e. ! extrpdta.deceased

- 84 –



- 85 –



Linking to Extractor Reports

Link Files

A Report Designer template can be linked to an Extractor report so that each time the Extractor report is run, the results are automatically inserted into the required template. The Extractor Results window is not displayed. The resulting report is displayed in a Print Preview window from where it can be printed, saved or exported. To link a report, first select the appropriate, saved Extractor report in the Extractor.

Highlight the report and click on the <OK> button. (Alternatively, double left clicking will also open the selected report). This will open the <Create Report> window. Use a left click to select the <Properties> icon.

- 86 –



This will open a window similar to the one shown below.

Select the Options tab, using a single left click. This will open a window similar to the one shown below.

Click on the File icon next to the <Report Template> field. This will open a standard Windows search window allowing you to browse and locate the appropriate template. Once the file has been located, use either a double left click on the file name or a single click on the file and a further click on the <Open> button to confirm your selection and close this window. The file name will now be displayed in the Report Template field.

Create Table

Use a left click to tick this checkbox if the data is to be exported to the Report Designer in a Table format. Saving the Extractor Report confirms the link between the Extractor Report and template.

- 87 –



Multiple Reports Where more than one report needs to be created in the Extractor for all the required data for a report, the Multiple Extraction function is used. All the necessary reports are created, each is allocated a ‘data source name’, they are linked together using the Create Multiple Extraction function and run as one report. This data will not be displayed in the Extractor Results window but will be exported automatically to the Report Designer.

Creating a Multiple Report Firstly create the required individual reports and specify a name for the table that will be created and exported to the Report Designer. This is done via <Properties><Options> <Datasource Name> and is primarily for ease of identification of the different tables and data fields within the Report Designer.

Datasource Name

Enter a simple label for the table for the extraction, i.e. ‘Incdet’. The field is 30 characters long. Please note, if the Datasource name is longer than 8 characters, it will make it harder to view the field names within the Report Designer.

Internal Code

Both reports must contain a unique reference for each record and the reference must be the same for linking multiple Extractor report together from the same module. You do have the option to use most fields for the unique reference, i.e. the Incident Form Number. However, each system has a unique internal code. This is set up in the Extractor for most of the data fields. You must make sure that the same Internal Code is used from the same location for all reports.

- 88 –



Once all the required reports have been created and saved, select the <Create Multiple Extraction> icon from the main window toolbar. This will open a window similar to the one shown below.

- 89 –


Use a right click in the main white area of this window to enable a popup menu.

Click on <Select> to continue. This will open the <Select Queries> window. Browse for the first of the saved queries and use a double left click to select. For multiple report selection, you have the option of selecting multiple reports by holding down the CTRL key. Click on the first report, hold CTRL key down on the keyboard and click on the next report, repeat for as many reports are needed. Once they have all been selected click on OK.


If the required report was created in a different system, click in the <System> field at the top of the <Select Queries> window and select the appropriate system.

- 90 –


This will display the saved reports for the selected system. Repeat this process as often as necessary until all of the individual reports have been selected. The names of the selected reports will appear in the <Setup Multiple Report> window as shown below.


If you already have a Report Designer template created for this report click on the yellow search folder to browse and link the template to the multiple report. (See previous screen print). Then click on the <Save> button. This will open the <Save Report> window for you to save the multiple report in the same way as any other report. The multiple report will now appear in your list of saved reports and can be recalled and run in the normal way. Note: The data from multiple queries cannot be exported to Word or

Excel, only to the Report Designer.

Setting Up the Multiple Report

The following example shows how to create a report for all incidents reported within a date range and all the actions for those incidents. The method would be the same for all other systems, for example, Customer Services or PALS cases with all Categories. Two reports were created for this report, one starting from the Incident file and given a data source name of ‘Incdet’, the other report was started from the Actions file and given the data source name of ‘Actions’. The first stage in creating the report template in the Report Designer is to select the main data table for the report. A window similar to the one shown below will be displayed.

The Incident table is the main data source for this report. (Actions would not be used as the main table as there may be some incidents without any actions and so they would be excluded from the report). Highlight the required table name and click on the <OK> button to select. The Report Designer template will now be open and ready to use. The fields from the ‘Incident’ data source will be available under <Tools><Field Wizard> as normal.

- 91 –



Different Sections for including a Second Report Child Tables

The next stage is to add the ‘Actions’ data source to the report as a child table. To do this use a right click on the ‘Standard’ tab at the bottom left corner of the main template window.

- 92 –


This will enable a popup menu as shown below.

Use a left click to select <Section Properties> from this menu. This will open the Section Properties window as shown below.

This window shows us that for this section of the report, the Incident data source table is being used. We need to add the Actions data table to this as a


‘Child’ table, i.e. each of the records in the actions data source table will relate to one of the records in the Incident data table. To do this, use a right click on ‘Incident’. This will enable a popup menu as shown below.

Use a left click to select <Add Child Table/SQL Query>. This will open a window similar to the one shown below.

Use a double left click on ‘Actions’ to select the data table. The Section Properties window will now show the Actions table as a ‘child’ of the incident table.

- 93 –



Table Relationships

Next we need to ensure that the relationship between the two data tables is set up correctly. To do this uses a right click on ‘Actions’, the child table, to enable a popup menu as shown below.

Use a left click on <Relationship> to proceed. The following window will be displayed.

Parent & Child Expressions

These expressions are used to identify which fields in the tables are used to link records. When a child table is added, the system will select the field to be used in the expression automatically. If a different field is to be used in the expressions click on the <Change> button and select the appropriate field form the list. This should be done for both Parent and Child tables.

- 94 –



- 95 –


Relationship Type

This field is used to define how many records in the child table can be linked to a record in the parent table. The default is set as ‘One to Many’, i.e. there may be more than one action record for each incident record. When only one record is to be linked, change the relationship type to <One to One>. Click on the OK button to confirm the details. Click on the <Close> button on the Section Properties window to return to the template. The fields from the Actions data source table will now also be available under <Tools><Field Wizard>.


Triggered Sections

Triggered sections can be used where more than one data source is being used in the report, for example Incidents and Actions. A filter is setup on the triggered section to ensure the correct data is displayed. (Similar to the child and parent expressions). Triggered sections are particularly useful when there are several tables in a report such as a detailed report for an incident or complaint and the data will be displayed in separate sections in the printed report. To add a triggered section, use a right click on the <Standard> tab and select <Add Section>. This will open a new window as shown below.

Highlight <Standard> with a single left click. This will enable the <Triggered> checkbox. Use a further left click to tick the checkbox and click on <OK>. This will open the <Select Table> window.

Select the appropriate table. In this example, <Actions> and click on <OK>.

- 96 –




Enter the field that will trigger the new section. In this example, the ‘Inc_Code’ or ‘Incident_N’ field could be used. Use a double left click to select the required field, followed by <OK>.

Filters

Next the filter should be applied to ensure the correct actions are printed with each incident. To do this use a right click on the <Triggered> tab. This will enable a popup menu.

- 97 –



Select <Filter>. This will open the <Expression Builder> window.

The filter should link the record numbers in both data sources as shown below.

Click <OK> to confirm the details. The triggered sections will not have <header> and <Footer> bands. Set up the fields and formatting as required in this tab.

- 98 –



- 99 –



Embedded Sections

Embedded sections are areas within a report that are processed independently of the main table. These can be used for calculating percentages or for merging data from another source into a single field to aid formatting the layout of a report, for example a risk register report where all information for one risk including several controls and/or actions can be displayed across one section of a report. Variables and expressions are used when setting up embedded sections to calculate and reference fields.

To add an embedded section, click on the <Embedded Section> icon on the window toolbar.

- 100 –


Use a left click to drag and drop a field in the template. This should be when the ‘calculation’ needs to be done, i.e. for percentages this will be the <page header> for merging data, the <page body>. On releasing the mouse button, the <select Table> window will open to specify which data source is to be used for the embedded section. Use a left click to highlight the required source, followed by <OK>. A new tab, labelled <Embedded> will be added the template.

Click on this tab to access the new section. The work area in this section will be the size of the field that was added to the main section and will be in the <Summary> area as shown below.

The next step is to set up a variable to calculate the field.


Merging Text Fields To merge text data into one field the variable would be set up as shown below.

The <Expression> is as follows: value:=value+iif(empty(value),"",chr(13)+chr(10))+chr(149)+trim(datasouce.fieldname*)

This can be typed directly into the <Expression> field or use a left click on the <Exp> button to access the <Expression Builder> window and enter the expression in the text field at the bottom of the window. (datasource.fieldname) is the field to be merged, i.e. Action Description and can be selected from the list of fields in the <Expression Builder> window. This will add together all the entries for one record, start a new line and insert a bullet point at the beginning of each entry as shown below.

If a bullet point is not required remove +chr(149)

If an additional carriage return is required after each entry so a blank line is inserted between each record add ‘+chr(13)+chr(10)’ to the end of the expression.

The field now needs to be inserted into the template and given a reference so it can be called from the main section. This is done using the <Field> tool. Drag and drop a field using the <field tool> into the <Summary> area of the embedded section.

- 101 –



This will open the <expression Builder> window. Enter the <SetDescription> function, variable and reference number as shown below.

The last stage in the setup is to insert a field in the main section of the template where the data should be printed. Use the <Field Tool> to drag and drop a field into the <page body>. Enter the following expression in the <Expression Builder> window.

The embedded section is now set up. Additional sections can be added in the same way. The reference number should be unique in each section, i.e. the expression will be SetDescription(2,value), GetDescription(2) for the second section, SetDescription(3,value), GetDescription(3) for the third and so on as shown below.

- 102 –



This will print as shown here, saving space on each printed sheet of the report and allowing a simple ‘excel’ type layout to be set up.

- 103 –



Renaming Sections

The Rename option allows the user to change the name of any sections, whether they are Standard, Triggered or Embedded. To access this use a right click on the <Standard> tab. This will enable a menu as shown below.

Click on <Rename>. This will open a new window.

- 104 –



- 105 –



Corporate Theme Aggregate Reporting Overview

Aggregate reporting utilises the Extractor and Report Designer tools to show trends across the various integrated governance Corporate Themes. Therefore for a selected theme the user can report on all Incidents, Customer Service cases, PALS enquiries, Litigation cases, RFI requests, PPI and Risks.

To set up a report like this it is necessary to change the maintenance files and use a multiple extraction as detailed below.

Maintenance Files

There is a file in the Shared Files area called “Corporate Theme” where the general themes of the organisation can be entered.

There is also a file called “Corporate Theme Group”. Here you can add a grouping level for the “Corporate Themes”. This allows you to either report on the individual Corporate Theme or run a report based on several themes that are linked to a specific Corporate Theme Group.

- 106 –



Example Corporate Theme Group

Example of Corporate Theme’s

- 107 –



Incident Maintenance Tables

The corporate themes entered in the shared files can be linked to causes in Incident in <Maintenance><Incident Files><Causes>.

Customer Services Maintenance Tables

The corporate themes entered in the shared files can be linked to categories in Customer Services in <Maintenance><Customer Services Files> <Category>.

- 108 –



PALS Maintenance Tables

The corporate themes entered in the shared files can be linked to enquiry categories in PALS in <Maintenance><Pals Files><Category of Enquiry>.

Litigation Maintenance Tables

The corporate themes entered in the shared files can be linked to litigation categories in Litigation in <Maintenance><Litigation Files><Litigation Category>.

- 109 –



RFI Maintenance Tables

The corporate themes entered in the shared files can be linked to request categories in RFI in <Maintenance><RFI Files><Category>.

- 110 –


PPI Maintenance Tables

The corporate themes entered in the shared files can be linked to sub themes PPI in <Maintenance><PPI Files><Sub-Theme>.


Risk Maintenance Tables

The corporate themes entered in the shared files can be linked to risk types in the Risk Register in <Maintenance><Risk Register Files><Risk Types>.

- 111 –



Creating the necessary Corporate Theme Report

The first step in setting up the aggregate report is to create individual reports needed for each module from which data is to be included. These reports should include a date range (based on the relevant date field for each module) and the Corporate Theme field using the <Group> function. Corporate Themes are not entered in records, but linked in maintenance to key data fields such as <Cause 1> and <Category>. They are listed under the relevant Primary file for each module and can also listed be found by right click on the field that it is linked to in maintenance. For example, in Customer Services, Corporate Theme is available within the Case Categories data file and is also linked to <Category>. A right click on <Category> will enable a list as shown below.

Select <Corporate Theme> from this list. Or by double left clicking on it in the Case Categories data File, see screen shot above for Customer Services.

- 112 –



- 113 –


Finding the Corporate Field in the Safeguard Modules

For other Safeguard modules the Corporate Theme field is found in the following primary data files:

Module Data File Incident Incident Customer Services Case Categories PALS Enquiry Issues Litigation Litigation RFI Request Categories PPI Themes Risk Risk Register

For the other Safeguard modules the right click for the Corporate Theme field is found by using a right click on the following fields:

Module Data File Field Description Incident Incident Cause Customer Services Case Categories Category PALS Enquiry Issues Category Litigation Litigation Category RFI Request Categories Category PPI Themes Sub Theme Risk Risk Register Risk Type


Include Zeros

For every corporate theme to be included in the reports, including those that have not been used in the selected date range, the field properties need to be set to ‘include zeros’. To do this, right click on the field in the Current Selections window to enable a popup menu. Click on <Properties> to open a new window.

- 114 –


Use a left click to tick the <Include Zeros> checkbox, followed by a click on the <Save> button. This needs to be done for each Corporate Theme used in each report.


Module Grouping of Corporate Theme

Incident Extractor Report

Customer Services Extractor Report

- 115 –



PALS Extractor Report

Litigation Extractor Report

- 116 –



RFI Extractor Report

PPI Extractor Report

- 117 –



Risk Extractor Report

- 118 –



Datasource Name

A <Datasource Name> should also be added to each report to aid setting up the report template. This could simply be the module name, i.e. ‘Incident’. If not used, each of the tables exported to the Report Designer will be labelled ‘extrpdta’, with a number suffix, i.e. extrpdta1. This number relates to the order in which the data was exported, 1st, 2nd, etc. Opening the <Properties> window by clicking on the appropriate icon

The <Datasource Name> field is on the <Options> tab in the <Properties> window

Enter an appropriate name for the table and then save the report.

- 119 –



Date Range for Report

Use the appropriate date field for each module to enter a date range, i.e. <Incident Date> or <Date Received> for each of the individual reports. The <Auto Dates> function should be used for reports that are to be run or scheduled on a regular basis.

To use Auto Dates go to the <Properties> window, ensure the date field is highlighted and right click in the <From> field at the bottom of the window. This will enable a popup menu as shown below.

Click on <Auto Dates> to continue and open a new window.

A left click in the <Auto Date> field will open a list of reporting periods. Select the required option with a further left click.

- 120 –



Creating the Multiple Extraction

Once all of the separate reports from the modules have been created and saved, they need to be linked together so they can be run as one extraction and exported to the Report Designer. This is done by double clicking on the <Create Multiple Extraction> icon on the menu window in Extractor.

- 121 –


It doesn’t matter which module has been selected in Extractor, but this will be the module from where the report is run.

The <Setup Multiple Report> window will open.

Use a right click in the main white area of the window to enable a popup menu.


Click on <Select> to continue. This will open the list of saved reports for the current module. In the example below, this is Incident, as shown in the <System> field.

- 122 –


Browse through this window to find the required report. Use a double left click to select the report. It will now be displayed in the <Setup Multiple Report> window.

Repeat this process until all the required reports have been added. Reports in other modules can be found by changing the <System> option to the appropriate module.


The system for each report is displayed in the <Setup Multiple Report> window as shown below.

When all the required reports have been added, the multiple reports should be saved and then run to export the data to the Report Designer so a template can be created. (It will need to be accessed later to link it to the Report Designer template).

- 123 –



Creating A Report Designer Template

Create a basic template, selecting any of the tables as the primary file for the report, and ‘grouping’ on a Corporate Theme field from that table to get group headers. Next, create embedded sections for the other modules. Click on the <Embedded Section> tool.

Drag & drop a field in the <Page Body> of the template. The <Select Table> window will open.

Select the appropriate table and click on <OK>. Each section needs to be filtered on the Corporate Theme to ensure the data from each data set is collated correctly in the report. To do this, click on the <Embedded> section tab and then select <Filter> from the main toolbar. This will open the <Expression Builder – Filter> window. Below is an example of the expression to be used.

Click on <Save> to confirm the filter details.

Repeat this process for all tables.

- 124 –



The completed template should look similar to the one shown below.

- 125 –



Example Report Output

To complete the setup of this report, when the template has been created and saved, it needs to be linked to the multiple extractions in the Extractor. Close the Report Designer to go back to the Extractor. The <Setup Multiple Report> window will still be open. Click on the folder next to the <Report Template> field to open a window and browse for the saved report designer template. Then found, highlight the report and click on <Open>. The template name will now be displayed in the field as shown here.

- 126 –


Click on <Save> to confirm.


- 127 –



Graphs

The Graphs function in the Report Designer is a wizard that allows the user to specify the type of graph to be used with layout and colour options in the same way as the <Graphics> module in Safeguard.

To create a graph, click on the <Graph Wizard> icon on the main toolbar.

Use a left click to drag and drop a field in the page body or summary area of the template. The field should be approximately the required size of the finished graph. On releasing the mouse button the <Select Table> window will open.

In this window will be the tables exported to the Report Designer from the Extractor. Use a left click to highlight the required table, then click <OK>.

- 128 –



Next, the Graph Wizard will open.

Datasource

In the example above, the data source has already been defined (via the <Select Table> window. Note: If the table being used in the main report is the basis for the graph, the table and all fields in it will have a ‘1’ added to their names. All fields selected in the graph wizard must be those including the ‘1’ suffix. Now click on the <Next> button or the <Columns> tab to move to the ‘Columns’ tab.

- 129 –



Columns

In this window the data fields to be used for the values and the data labels (x and y axis) in the graph are selected. To select the values, (Y Axis) use a left click on the <Show Filed List> checkbox.


Highlight the required field with a left click and then click on the <Add> button. More than one data field can be added for stacked graphs, i.e. incidents by Directorate and Financial quarter. Note: This must be a numeric field i.e. ‘Total’ as shown above.

- 130 –



When all the required columns have been added, click on the X in the top right corner to close the window.

Alternatively, clicking on the Graph All Fields checkbox, the Graph Wizard will automatically populate the graph fields for you.

- 131 –



Filling out the rest of the Graph

X-Axis Labels The x-axis labels are selected from the list of fields by clicking here. This will open the <Expression Builder> window.

Use a double left click in the <Fields:> column to select the data field for the required x-axis labels. Click on <OK> to close the <Expression Builder> window and then move on to the <Graph Style> tab.

- 132 –



Once the X Axis label has been selected using the Expression window, click on <ok> to close the window and the field name for the X Axis should appear in the box at the foot of the Columns tab.

- 133 –



Graph Style

Title/Subtitle & Footer

A title, sub title and footer can be added to a chart. Simply type the required details into the appropriate field.

Graph/Pie

Use a left click to select the appropriate option for the chart, either graph or pie. The selection made here will define which options are available on the following tabs. Use a left click on the appropriate radio button to select either graph or pie chart format. When <Graph> is selected there is a list of graph types to select from including Bar (default), line, stacked area and points. A preview of the graph will be displayed on the left of the Graph Wizard window when a graph type is selected (including Pie).

Multi Graphs

Multi Graphs can be used as an alternative to stacked charts, with each data set being plotted in a separate chart, but displayed in a single chart window for ease of comparison. This option is not available with pie charts.

- 134 –



Separate Axis

This function is used in conjunction with the <Multi Graph> option and is used to separate out the different data sets in the Multi Graph along the X Axis.

Graph and/or Table

Click on <Graph and Table> to include a table layout of the data for the graph as well as the graph. If a table OR graph are required, click on the appropriate option from this list. Note: the table option will only display correctly if two or more fields have been grouped, i.e. directorate and financial quarter.

Data Shadows

This option allows the user to add shadow and/or 3D effects to charts.

Gradient Bars

Tick this option to change the bars from graded to solid as shown below.

Graded Solid

- 135 –


Disable shadow below pie chart

When the 3D effect is used with a pie chart, a shadow will be added below the chart. Use a left click to tick this checkbox if this shadow is not required.

Quick Style

Pre defined colour and style formats for chart lines/bars, grids and backgrounds can be applied by selecting the required option from the <Quick Style> lookup. When selected, the effect of each option can be seen in the preview pane on the left of the window.

Bitmap/Gradient Style

This option will apply a ‘faded’ effect to the chart background.


- 136 –


Border Type

The border for the background can be changed. When selected, the effect of each option can be seen in the preview pane on the left of the window.

Decimals

This option is used with percentages to define how many decimal places are displayed. The default is zero.


Graph Axis

The options on this tab are used to apply formatting to the axis and grid for the chart.

X Axis Label

Enter a label for the x axis, if required. This will be displayed below the chart.

Y Axis Labels (L & R)

Enter labels for the y axis, if required. These will be displayed vertically down the left and right sides of the chart respectively.

Point Labels Orientation

The labels for the columns can be rotated for the best fit, horizontally, vertically or slanted. <Auto Orientation> is the default selection and will allow the wizard will apply the best fit.

Grid Options

The grid lines on the background can be removed using this function. Click on the down arrow to view the options.

Style

When the grid is in place, the style of the grid lines can be changed. Click on the drop down arrow to view the list of options, i.e. thin line, dotted line.

- 137 –



Grid in Front

This option will move the grid to the front of the chart, i.e. overlaying the bars, as shown below.

Use a left click on the checkbox to enable this option.

Extend Y Axis

A tick in this checkbox will put additional Y axis grid lines on the chart as shown below.

Mark Data Points

Mark Data Points is used primarily with line and area graphs and marks the values for the dataset on the grid with ‘points’ or ‘dots’.

Alt Freq & Points Displayed

The graph wizard will automatically display legible point labels, based on the number of data points in the chart. This mechanism can be overwritten by using the Alt Freq and Data Points Displayed functions. For example, if for a graph where there are over 50 data points only 5 axis labels are to be displayed, enter ‘50’ in the <Alt Freq> field and ‘5’ in the <Points Displayed> field. The system will then display the ‘best fit’ for data labels depending on the <Alt Freq> and <Points Displayed> fields AND number of data points on the graph. For example if there were 50 data points in the chart it would label 5 points and if there were 55, it would label 6 points.

- 138 –



An example of a line chart using this function is shown below.

Order X Axis – Ascending/Descending

Use a left click to tick the appropriate checkbox if the order of the columns across the graph is to be by ascending or descending values, rather than alphabetically.

Manual Y Axis – Minimum Scale

The system will detect the lowest values in a dataset and set the scale accordingly. Use a left click on the checkbox to enable this option if the scale is to be set manually. Enter the minimum scale to be used. If when you are displaying a Line graph and would like to display the graphical line going down to zero as opposed to stopping and then carrying on. Put a -1 in the Minimum Scale and this will change the line graph to show Zero’s

Manual Y Axis – Maximum Scale

The system will detect the highest values in a dataset and set the scale accordingly. Use a left click on the checkbox to enable this option if the scale is to be set manually. Enter the maximum scale to be used.

Y Axis as Whole Numbers

Where the values in a graph are low the y axis scale will automatically show with decimals, i.e. 1.5, 2.0, 2.5 etc. If whole numbers only are to be displayed, use a left click on this checkbox.

- 139 –



Pie Options

The options on this tab are for pie charts only.

Referring back to the Graph Style select Pie radio button to show the Pie chart

- 140 –


Please note: They will not be greyed out if a bar chart is being created, but selecting them will have no impact on the format or layout of the bar chart.


Pie Labels

This lookup gives options for the labels for the sections of the pie chart. Use a left click on the down arrow to view the list and a further left click to select the required option. The default is for Percent plus Label. Other options available are

Percent Only Label Only No Label

Data Labels as Values

By default if values are displayed in the labels they will be percentages. If the actual values should be displayed, click on this checkbox to enter a tick.

Grouping Percent

This option controls the size of pie chart slices that get grouped into the ‘Other’ slice. For example, all values below 3% go into the ‘other’ slice to stop the chart becoming over complex with lots of small slices.

Explode Pie Segments

Segments of the pie can be ‘exploded’ away from the main chart as shown below.

Highest

When this field is selected, the largest (highest value) slice of pie will move away from the main pie chart. Use a left click to tick this checkbox.

Lowest

When this field is selected, the smallest (lowest value) slice of pie will move away from the main pie chart. Use a left click to tick this checkbox.

- 141 –



- 142 –


Percentage and Value

The above/below parameters can be set for both percentages and value, depending on which is being used in the pie chart and are applied in conjunction with the <highest> and <Lowest> fields and allow more than one slice to be exploded from the pie chart.

Above

Enter the percentage/value above which all sections should be exploded, i.e. all over 20.

Below

Enter the percentage/value below which all sections should be exploded, i.e. all under 10.


Legend

Show Legend

Use a left click to tick this checkbox if the legend should be shown on the report. Once this has been selected the <Location> and <Style> fields will become active.

Location

The location field positions the legend on the page in relation to the chart. Click on the down arrow to view the list of options. Select the required option with a further left click.

Style

The style option works with pie charts and graphs, but not all options work with both chart types. It allows the user to define the location and layout of the legend.

Labels

This option is for showing either the Description and/or value for the bars on the graph. The default is for No Labels. Other options available are

Description Description, Value Value

- 143 –



Font

Option for picking the type of font for the legend AND the data labels.

Size

Use a left click to select the required font size for the legend AND the data labels. This will be Small, Medium or Large.

Bold/Italic/Underline

Use a left click on one or more of these options to apply additional formatting to the legend and labels text.

Colour Points

If the graph only has one dataset, all the bars will be the same colour. The Colour Point option gives the user the ability of making each bar a different colour across the page.

In the lower part of the window is where the subset styles are set up.

Subset

This is the number of the subset as allocated automatically by the system.

Note: For pie charts Subset ‘1’ will be the first alphabetically in the data set, subset ‘2’ the second and so on. For a risk rating pie chart the following would apply: High (Red) subset ‘1’ Low (Yellow) subset ‘2’ Moderate (Orange) subset ‘3’ Very Low (Green) subset ‘4’

- 144 –



Colour Colours can be defined for pie charts and for graphs with two data sets, e.g. incidents by risk rating and quarter. Double click in the <Colour> column to access the colour palette.

Click on the required colour then click on the <OK> button. The colour palette will close. Repeat this process until all items in the data set have been allocated a colour.

Translucency

Click on the down arrow to display a list of options. The higher the number the ‘denser’ the colour on the graph.

Point Type

Where data points are being used on a graph e.g. a line graph, the style of the points can be defined using <Point Type>. Click in the <Point Type> column to view the list of options. Use a further left click to select the required style.

Plotting Type

The plotting type is the way the data is displayed, e.g. Bar, Stacked Bar, Area. Click on the down arrow to view a list of options. The list is the same as shown on the <Graph Style> tab. This would then allow the user to put a line graph behind a bar graph, i.e. showing the number of Incidents from one quarter as a line graph and the second quarter results as a bar graph in front.

R Axis

Use a left click on this checkbox for the labels for the selected subset to be shown on the right side of the graph.

- 145 –



Auto Points

Auto Points are used to add trend lines to charts.

Simply use a left click to select the required option.

- 146 –



Linear Trend

Use a left click to enter a tick in this checkbox to add a trend line to the graph data as shown below.

Corresponding trend values are also added to the legend below the graph.

Average all subsets all points

This option will add a line across the graph to indicate the average of all the points in the data set.



Average all subsets per point

This option will add a line which indicates the average of each subset, e.g. the average for each quarter in the year and should only be used for multiple data sets, e.g. Incidents by Directorate and Financial Quarter.

Sum all subsets per point

This option will add a line which indicates the total for each subset within the graph and should only be used for multiple data sets, e.g. Incidents by Directorate and Financial Quarter.



Moving Average

A moving average is calculated in an attempt to even out short-term oscillations and thus identify long-term trends. For example, over a financial year, the average is calculated for April to May, then the April figures are dropped and the average is calculated for May to June and so on. The ‘moving average’ is shown as a trend as seen in the example below.

Corresponding trend values are also added to the legend below the graph.

- 149 –



SPC (Control Limits)

SPC or ‘Statistical Process Control’ is an effective method of monitoring data through the use of control charts. A tick in this checkbox will add a ‘UCL’ or ‘Upper Control Limit’ line to the graph as shown below.

The corresponding UCL value is added to the legend below the graph.

- 150 –



Warning Limits

This option only becomes available when the <SPC (Control Limits> option has been selected. A tick in this checkbox will add a ‘UWL’ or ‘Upper Warning Limit’ line to the graph as shown below.

The corresponding UWL value is added to the legend below the graph.

- 151 –



Y Axis Annotations

These are annotations that line the inside of the graph and mark a location on the axis. They could be used to display the previous year’s average incidents. Up to three annotations can be added to a graph. Enter the y axis point and the details for the annotation as shown below.

This will be displayed on the graph as shown in the example below.

Click on <Finish> when all changes have been made. The changes will be applied automatically to the displayed graph. The <Graph Wizard> can be accessed and changes made to the layout as many times as required.

- 152 –



- 153 –



Graph Examples Bar Chart

Below is an example bar chart with a table and legend. It shows a range of incident cause groups by financial quarter.

This section details how to create a chart similar to this one.

Extractor Report

The Extractor report for this chart groups on Cause Group and then on Incident Date by Quarter. Before the report was run the <Create Table> checkbox on the <Properties><Options Tab> was ticked.

- 154 –



Ticking this checkbox will turn the data into a table format before it is sent to the Report Designer. The data needs to be in a table format when it is sent to Report Designer so that data fields for Cause Group and then each quarter are in the exported table. These are the fields that are required to create a bar chart in this format. When the Extractor results are displayed, select the Report Designer icon from the toolbar

Choose <Standard Report> from the menu. This will open the Report Designer and the <Select Table> window.

Click on <OK> to proceed.

- 155 –



Next, click on the <Graph Wizard> icon on the main window toolbar.



- 156 –




Data Source


- 157 –



Columns Tab In this window the data fields to be used for the values and the data labels (x and y axis) in the graph are selected. In this example, these are the totals for each financial quarter for the y axis and cause groups for the x axis.

To select the values, (Y Axis) use a left click on the <Show Field List> checkbox.

- 158 –



Displayed in this window are the data fields exported from the Extractor. <Extrpdta1.1> is the data for quarter 1, <Extrpdta1.2> is the data for quarter 2, and so on.


Each of these fields needs to be added as columns. Use a left click to highlight <Extrpdta1.1>, then click on the <Add> button. Next, highlight <Extrpdta1.2> and click on <Add>. Repeat this process until all four quarters have been added. Click on the x in the top right corner to close the <fields> window and return to the Graph Wizard. Alternatively, use a left click to tick the <Graph All Fields> checkbox. This will have the same effect.

The fields added will be displayed as shown below.

The default <Title> for each column is the suffix (whatever is after the ’.’) for each data field name. In this example, ‘1’ for quarter 1, ‘2’ for quarter 2 etc. These titles can be changed at this point. If you have used the ‘Graph All Fields’ option, you will need to remove the tick to uncheck this box prior to editing the titles. To do this, use a right click on the <Title> field to enable a popup menu.

- 159 –



Click on <Edit Title> to proceed. This will open a window similar to the one shown below.

Enter the required title, i.e. ‘Quarter 1’ and click on <OK> to confirm. Repeat this process until all column titles have been amended.

The next step is to define the labels for the x axis. Click on the button at the end of the x axis field.

- 160 –




Use a double left click on <Incgrph1.Cause_Group> in the <Fields> list, followed by a click on <OK>. The cause group data field will now be displayed in the <X Axis Labels> field as shown below.

Click on the <Next> button or the <Graph Style Tab> to move on to the next stage.

- 161 –



Graph Style

In this window the type of graph, format of the bars and of the background are specified. The bar chart being produced uses most of the defaults from this tab as shown below.

The only change is to the <Border Type> field, where the default option should be changed to <Simple line border round grid>. If this option is not changed, a shadow will be applied to the borders around the main chart and table. The rest of the settings used to produce the chart, are the defaults from each tab, so to complete the bar chart and table simply click on the <finish> button.

To view the bar chart, click on the <Print Preview> button.

- 162 –



Stacked Bar Chart

The standard bar chart can be changed to a ‘stacked’ format by selecting <Bar Stacked> from the <Graph> lookup list. To access the Graph Wizard, use a right hand mouse click on the Graph, select the Graph Wizard and make any amendments.

There is also a <Bar Stacked Percent> option available.

- 163 –



Multi Graphs The <Multi Graphs> option allows the user to display charts with multiple data points as a series of charts, rather than one with separate bars or lines for each set of points as shown below.

This chart is produced in the same way as the bar chart in the last section and in addition the <Multi Graphs> and <Separate Axis> checkboxes on the <Graph Style Tab> should be ticked.

- 164 –



Note: When selecting <Multi Graph> the <Separate Axis> flag will be selected automatically. If this option is not required, use a left click to un-tick this field. Removing the tick will change the layout of the multi graph as shown below.

- 165 –



Pie Chart Below is an example pie chart with a legend. It shows total incidents by cause groups.

This section details how to create a chart similar to this one.

Extractor Report

The Extractor report for this chart groups on Cause Group

When the Extractor results are displayed, select the Report Designer icon from the toolbar

- 166 –



Choose <Standard Report> from the menu. This will open the Report Designer and the <Select Table> window.

Click on OK to proceed. Next, click on the <Graph Wizard> icon on the main window toolbar.



- 167 –




Data Source


- 168 –



Columns Tab In this window the data fields to be used for the values and the data labels (x and y axis) in the graph are selected. In this example, these are the totals for each financial quarter for the y axis and cause groups for the x axis. To select the values, (Y Axis) use a left click on the <Show Filed List> checkbox.

- 169 –



Displayed in this window are the data fields exported from the Extractor. <Incgraph.Cause_Group> is names of the cause groups, <Incgraph.Total> number of incidents for each cause group.


< Incgraph.Total> needs to be added as the y axis column. Use a left click to highlight < Incgraph.Total>, then click on the <Add> button.

Click on the x in the top right corner to close the <fields> window and return to the Graph Wizard. The fields added will be displayed as shown below.

The default <Title> for each column is the suffix (whatever is after the ’.’) for each data field name. In this example, ‘Total’ for group totals etc. These titles can be changed at this point. To do this, use a right click on the <Title> field to enable a popup menu.

- 170 –



Click on <Edit Title> to proceed. This will open a window similar to the one shown below.

Enter the required title, i.e. ‘Incidents Totals’ and click on <OK> to confirm.

The next step is to define the labels for the x axis. Click on the button at the end of the x axis field.

- 171 –


This will open the <Expression Builder> window as shown below.

Use a double left click on <Incgrph1.Cause_Group> in the <Fields> list, followed by a click on <OK>. The cause group data field will now be displayed in the x axis field as shown below.

Click on the <Next> button or the <Graph Style Tab> to move on to the next stage.


Graph Style

In this window the type of graph, format of the bars\slices and of the background are specified.

Click on the <Pie> radio button as shown above. The only other option changed on this window to achieve the format of this report is <Border Type>. This has been changed to ‘Simple line border round grid’. This removes the ‘drop shadow’ effect which inserts a grey shadow below the grid which can at time overlap the legend when as it is in this example, placed below the pie chart. Note: the default for values in pie charts is for whole numbers. If values are required with decimals, this must be set on this window in the <Decimals> field. Click on <Next> twice or go to <Pie Options> to move to the next stage. Note: There are no applicable options for pie charts on the Graph Axis tab.

- 172 –



Pie Options

The options on this window are specific to pie charts.

The example chart being created uses the default settings and <Explode Pie Segments> <Highest>. This will have the effect of ‘exploding’ from the main pie, the largest – highest value – slice. Select this field with a single left click. Click on <Next> or <Legend> to move to the next stage.

- 173 –



Legend

The options on this tab enable the legend and set its location and format.

Use a left click on <Show Legend> to display the legend for the pie chart. This will enable the <Location> and <Style> fields. Click on the dropdowns in these fields to view the options. For this example the <Location> is <Bottom> and <Style> is <Two Lines>. In addition to this the <Medium> <Font> option has been selected. Colour Points doesn’t really have much effect on a Pie chart as all the slices are already different colours.

- 174 –



Colours can be defined for pie charts Double click in the <Colour> column to access the colour palette.

Click on the required colour then click on the <OK> button. The colour palette will close. Repeat this process until all items in the data set have been allocated a colour. Click <Finish> to complete the set up process for the pie chart and <Print Preview> to view the chart.

- 175 –



- 176 –



- 177 –


Mail Run using Report Designer

As Report Designer can be used to create complex reports, it also has the ability be used for generating letters as a mail run, e.g. within the Bereavement system, you might want to run off a list of follow up letters for those patients who have died over a time period to see how the Next Of Kin are coping.

Create the usual Extractor reports, one report which has the case details, the other report with the personal details for Person/Patient/Staff dependant on what letter is being created. Using the Multiple Extraction function, join the reports together and create the letter template on the system, refer to page 88.

The letter will be broken down into three bands

Page Header

This band will have the trust logo, address details for both the trust (on the right) and the person/patient on the (left). Using the <Today()> function will put the date the letter is printed on.

Page Body

This will have the body of the letter along with any grammar and formatting required.

Page Footer

This will have any letter footer required, Which could be, signatures and any disclaimers needed. If the signatures for the trust are electronic, using the Pictures tool, these can be put onto the letter.

NOTE: This facility does not replace the letter function within the Safeguard modules. When using the Report Designer for a mail run, the case will not be updated with a date or note that the letter has been sent. This will still need to be logged independently by the user. That is, they will need to update the module case with a note to say the letter has been produced and sent.


- 178 –



Tables

Templates can be set up displaying data in tables using a variety of methods, the ‘Cross Tab Wizard’, the ‘Standard Wizard’ and manually in a ‘Standard’ report.

Standard Report Wizard The Standard Report Wizard can also be used to create a table in Report Designer. When using the Standard Wizard you will need to have selected the <Create Table> option on the <Properties> window, <Options> tab.

Or the <View As A Table> icon in the extractor Results window.

- 179 –



The data will automatically be displayed as shown below.

Now select the Report Designer icon and choose <Standard Report Wizard> from the popup menu.

This will open a window as shown below.

- 180 –



Title

On this tab you can enter a title for your report in the Report Title field. You can also enter a comment in the box if you wish, but this will not appear on the report itself. When you have entered the report title click on the <Next> button or the <Data Source> tab to move to the next step in the wizard.

- 181 –



Data Source

On this tab we define which data source we are going to use as the basis of the table. Left click on the grey <Select Main Table> button. This will open a window similar to the one shown below.

There will normally be only one table to select, for this example it is ‘Incdet’. Ensure the required table is highlighted and click on the <OK> button to confirm the selection.

- 182 –



This will close this window and return you to the Data Source window where the selected data source will now be listed.

Click on the <Next> button or the <Fields> tab to continue.

- 183 –



Fields

On the Fields tab we select the fields from the Extract data source that we are going to use in our table.

To select a field, highlight it in the Available Fields window with a single click and then click on the <Select> icon.

The field will then be listed in the Selected Fields window. In this case we want to use all of the available fields, so we can click on the <Select All> icon.

To change the Font type at this stage, click on the <Font> button.

- 184 –



As you click on each field in the Selected Fields window, a suggested heading for the column will be displayed in the Heading field.

This heading can be overtyped if necessary. When you have completed this tab, click on the <Next> button or the <Sorting> Tab, to move on to the next step in the wizard.

- 185 –



Sorting

On the Sorting tab we decide by which columns of data we want the table ordered/sorted.

Select fields to sort by with a double left click in the <Available Fields> window on the field name. Click on the <Next> button or the <Grouping> Tab to continue.

- 186 –



Grouping

On the Grouping tab we decide by which columns of data we want the table Grouped by.

Select fields to group by with a double left click in the <Available Fields> window on the field name. The example at the end of this Table Section is not using any Grouping within the Wizard but has the Table checkbox clicked in the Properties window. Click on the <Next> button or the <Filtering> Tab to continue.

- 187 –



Filtering

On the Filter tab we decide by how the data will be ‘filtered by’ if needed.

The first dropdown is defaulted to “is”. You then have the option of selecting how the filter is to work:

- 188 –



The grey button opens the Expression Builder, so an Expression could be used to filter the data.

Click on the <Next> button or the <Style> Tab to continue.

- 189 –



Style

There are several different functions on the Style tab.

Lay Fields out in Table Format or Form Format

Choose to layout fields in Table Format (as displayed in the Extractor results window after the ‘View as a table’ icon was selected), or in Form Format. (Form format will display the fields as they were in the Extractor results window before after the ‘View as a table’ icon was selected. Use a single left click on the appropriate radio button.

Simulate Greenbar in Body Band

Use a left click to tick this checkbox for each alternate line of the body of the report to be highlighted.

Include Title Page

Use a left click to tick this checkbox to add a title page section to the template.

- 190 –



Page Properties

Click on the Page Properties icon to adjust margins and page orientation.

Colum/Label Properties

Column/Label Properties is not used in the Standard Report Wizard. Click on the <Finish> button when all selections on this tab are complete. This will close the wizard and open a template window similar to the one shown below.

- 191 –



To view the table, click on the Print Preview icon.

There are no totals on this report. If you need totals you will have to use a variable.

- 192 –



Cross-Tab Report Wizard

On the Extractor Results window, select the Report Designer icon. This will enable a popup menu.

Click on ‘Cross Tab Wizard’. This will open a window similar to the one shown below.

Title

This tab is not used. Any text entered here will not appear on your report. Click on the <Next> button or the <Data Source> Tab.

- 193 –



Data Source

On this tab the data source to be used as the basis of the table is selected. Left click on the grey <Select Main Table> button.

This will open a window similar to the one below, listing all tables exported in the current Extractor report.

Ensure the appropriate table is highlighted and click on the <OK> button to continue. This window will close and return you to the Data Source window. The selected data source will now be listed in this window as shown below.

- 194 –



- 195 –


Click on the <Next> button or the <Cross Tab> Tab.


Cross-Tab

On this tab which data field is being used for columns, rows and the summary or totals is entered.

The procedure is the same for selecting the Row, Column and Summary expressions. For the Row Expression, click on the grey icon in that area of the window. This will open a window similar to the one shown below.

- 196 –



Use a double left click to select a field from the list that is to be used for the Row data in the table, i.e. Incdet.Site. Then click on the OK button. This will close this window and return you to the Cross Tab window. The selected field will be displayed in the Row Expression field.

Repeat this process for the Column and Summary Expressions. Note: The summary expression will always be a numeric field, such as Incdet.Total. For the Summary Expression you will also need to select ‘Sum’ form the Summary Function field.

You also have the facility in the Summary area to format the numbering sequence for the table, i.e. with or without decimal places, how many decimal places. This is done in the ‘Picture’ field as shown above.

- 197 –



When you have completed all three expressions the window should like the screen print below:

Click on the <Next> button, or the <Filtering> tab.

- 198 –



Filtering

On the Filter tab we decide by how the data will be ‘filtered by’ if needed.

The first dropdown is defaulted to “is”. You then have the option of selecting how the filter is to work:

- 199 –



The grey button opens the Expression Builder, so an Expression could be used to filter the data.

Click on the <Next> button or the <Page Properties> Tab to continue.

- 200 –



Page Properties

On this tab default page properties such as margins and orientation are set up.

Make any necessary adjustment for your table to fit the page. Click on the <Finish> button to complete the table. This window will close and your template will open as shown below.

- 201 –



Prior to previewing the template, you may need to extend the first column, as the text may not show, the Site name will not show. Select the Print Preview icon to view the table.

If any amendments to the format of the table are necessary, use a right click on the ‘Cross-Tab’ tab at the foot of the main window. This will enable a popup menu.

Select <Cross-Tab Config> to open the Cross-Tab Wizard again.

- 202 –



- 203 –



- 204 –


INDEX

CONTENTS .............................................................................................................................................3

OVERVIEW OF SYSTEM......................................................................................................................5

SYSTEM CONVENTIONS .............................................................................................................................5

CREATING A REPORT DESIGNER TEMPLATE..............................................................................7

Grid.................................................................................................................................................8 Grid Resolution...............................................................................................................................9 Snap ................................................................................................................................................9

PAGE PROPERTIES ..................................................................................................................................10 Margins.........................................................................................................................................10 Portrait/Landscape .......................................................................................................................10

PAGE LAYOUT .........................................................................................................................................11 Page Header .................................................................................................................................11 Page Body.....................................................................................................................................11 Page Footer ..................................................................................................................................11 Summary .......................................................................................................................................11 Removing Page Headers and Footers...........................................................................................12

REPORT SECTIONS............................................................................................................................14

Title ...............................................................................................................................................15 Standard........................................................................................................................................15 Column/Label................................................................................................................................15 Cross Tab......................................................................................................................................15 Summary .......................................................................................................................................15

PREVIEWING & SAVING THE TEMPLATE ...................................................................................16

Previewing ....................................................................................................................................16 Saving ...........................................................................................................................................16

GROUP ..................................................................................................................................................19

DEFINING GROUPS .................................................................................................................................21 Group Name..................................................................................................................................21 Expression.....................................................................................................................................22

ORDER ..................................................................................................................................................23

Unique Order................................................................................................................................24

TOOLS - ICONS....................................................................................................................................26

Pointer ..........................................................................................................................................26 Line ...............................................................................................................................................26 Rectangle ......................................................................................................................................26 Ellipse ...........................................................................................................................................26 Field..............................................................................................................................................26 Text ...............................................................................................................................................26 Picture...........................................................................................................................................26

INSERTING FIELDS ...........................................................................................................................27

TEXT FIELDS ..........................................................................................................................................27 Editing a Text Field ......................................................................................................................28 Deleting Text Fields......................................................................................................................28

USING THE FIELD WIZARD .....................................................................................................................29 Functions ......................................................................................................................................29 Memo fields...................................................................................................................................31 Deleting Fields..............................................................................................................................32


- 205 –


Selecting Multiple Fields ..............................................................................................................32

INSERTING PICTURES AND IMAGES ............................................................................................33

Select Picture Source ....................................................................................................................33 Picture Type..................................................................................................................................33

FORMATTING TEXT ..........................................................................................................................34

BORDERS AND BACKGROUNDS .....................................................................................................35

Borders .........................................................................................................................................35 Backgrounds .................................................................................................................................37

ALIGNMENT ........................................................................................................................................38

TEXT ALIGN ............................................................................................................................................38 Left Align.......................................................................................................................................38 Centre ...........................................................................................................................................38 Right Align ....................................................................................................................................39 Full Justification ...........................................................................................................................39 Vertical Text..................................................................................................................................39 Select All in Band..........................................................................................................................40

ALIGN (FIELDS WITHIN THE REPORT) ....................................................................................................41 Align on Left Margin.....................................................................................................................42 Align on Right Margin ..................................................................................................................42 Align on Band Top ........................................................................................................................42 Align on Band Bottom...................................................................................................................42 Centre Vertically in Band .............................................................................................................42 Centre Horizontally in Band .........................................................................................................43 Set Position/Size............................................................................................................................43 Select All in Band..........................................................................................................................44 Align Left.......................................................................................................................................45 Align Right ....................................................................................................................................45 Align Top.......................................................................................................................................45 Align Bottom .................................................................................................................................45 Align Horiz. Centre.......................................................................................................................46 Align Vert. Centre .........................................................................................................................46 Even Horizontal Alignment...........................................................................................................46

FIELD SIZING .....................................................................................................................................47

Same Size ......................................................................................................................................49 Same Vertical Size.........................................................................................................................49 Same Horizontal Size ....................................................................................................................49

BACKGROUND TAB............................................................................................................................50

VARIABLES..........................................................................................................................................52

Name.............................................................................................................................................52 Position .........................................................................................................................................52 Initial Value ..................................................................................................................................52 Reset Level ....................................................................................................................................52 Expression.....................................................................................................................................53 Variable – Total for “Grouped By” Department..........................................................................53 Variable – Total for the Report.....................................................................................................54

EXPRESSIONS .....................................................................................................................................57

Add a Date Range field to the Template .......................................................................................59 Useful Expressions........................................................................................................................61

PAGE BREAKS.....................................................................................................................................63


- 206 –


PRINT ON EVERY PAGE....................................................................................................................64

PRINT WHEN.......................................................................................................................................65

USING PRINT WHEN FOR COLOURS........................................................................................................65 USING PRINT WHEN WITH VARIABLES ...................................................................................................67

EXTRA FUNCTIONS ...........................................................................................................................69

DATE FORMATS ......................................................................................................................................69 Month Name (CMonth) .................................................................................................................69 Using CMonth and GetExtracts ....................................................................................................71 Using CMonth and GetExtracts, adding Year to the Expression..................................................73 Add a Name field to a Template....................................................................................................74

TRIM FUNCTIONS..............................................................................................................................76

ALLTRIM .................................................................................................................................................76 LEFT .......................................................................................................................................................77 RIGHT .....................................................................................................................................................77

UPPER AND LOWER CASE ...............................................................................................................78

LOGICAL FIELDS ...............................................................................................................................80

FILTER .................................................................................................................................................82

USING LOGICAL FIELDS WITH FILTERS..................................................................................................84

LINKING TO EXTRACTOR REPORTS .............................................................................................86

Link Files ......................................................................................................................................86 Create Table .................................................................................................................................87

MULTIPLE REPORTS.........................................................................................................................88

CREATING A MULTIPLE REPORT.............................................................................................................88 Datasource Name..........................................................................................................................88 Internal Code ................................................................................................................................88 Setting Up the Multiple Report .....................................................................................................91

DIFFERENT SECTIONS FOR INCLUDING A SECOND REPORT ..............................................92

CHILD TABLES ........................................................................................................................................92 Table Relationships.......................................................................................................................94 Parent & Child Expressions .........................................................................................................94 Relationship Type..........................................................................................................................95

TRIGGERED SECTIONS ............................................................................................................................96 Filters............................................................................................................................................97

EMBEDDED SECTIONS...................................................................................................................100

RENAMING SECTIONS ...........................................................................................................................104

CORPORATE THEME AGGREGATE REPORTING .....................................................................106

OVERVIEW ............................................................................................................................................106 MAINTENANCE FILES ...........................................................................................................................106

Example Corporate Theme Group..............................................................................................107 Example of Corporate Theme’s ..................................................................................................107 Incident Maintenance Tables ......................................................................................................108 Customer Services Maintenance Tables .....................................................................................108 PALS Maintenance Tables ..........................................................................................................109 Litigation Maintenance Tables ...................................................................................................109 PPI Maintenance Tables.............................................................................................................110 Risk Maintenance Tables ............................................................................................................111

CREATING THE NECESSARY CORPORATE THEME REPORT....................................................................112


- 207 –


Finding the Corporate Field in the Safeguard Modules .............................................................113 Include Zeros ..............................................................................................................................114

MODULE GROUPING OF CORPORATE THEME.......................................................................................115 Incident Extractor Report ...........................................................................................................115 Customer Services Extractor Report...........................................................................................115 PALS Extractor Report ...............................................................................................................116 Litigation Extractor Report.........................................................................................................116 RFI Extractor Report ..................................................................................................................117 PPI Extractor Report ..................................................................................................................117 Datasource Name........................................................................................................................119 Date Range for Report ................................................................................................................120

CREATING THE MULTIPLE EXTRACTION ..............................................................................................121 CREATING A REPORT DESIGNER TEMPLATE ........................................................................................124

GRAPHS ..............................................................................................................................................128

DATASOURCE ........................................................................................................................................129 COLUMNS..............................................................................................................................................130

X-Axis Labels ..............................................................................................................................132 GRAPH STYLE .......................................................................................................................................134

Title/Subtitle & Footer................................................................................................................134 Graph/Pie....................................................................................................................................134 Multi Graphs...............................................................................................................................134 Separate Axis ..............................................................................................................................135 Graph and/or Table ....................................................................................................................135 Data Shadows .............................................................................................................................135 Gradient Bars .............................................................................................................................135 Disable shadow below pie chart .................................................................................................135 Quick Style ..................................................................................................................................135 Bitmap/Gradient Style.................................................................................................................135 Border Type ................................................................................................................................136 Decimals .....................................................................................................................................136

GRAPH AXIS..........................................................................................................................................137 X Axis Label ................................................................................................................................137 Y Axis Labels (L & R) .................................................................................................................137 Point Labels Orientation.............................................................................................................137 Grid Options ...............................................................................................................................137 Style.............................................................................................................................................137 Grid in Front...............................................................................................................................138 Extend Y Axis ..............................................................................................................................138 Mark Data Points........................................................................................................................138 Alt Freq & Points Displayed.......................................................................................................138 Order X Axis – Ascending/Descending .......................................................................................139 Manual Y Axis – Minimum Scale ................................................................................................139 Manual Y Axis – Maximum Scale ...............................................................................................139 Y Axis as Whole Numbers ...........................................................................................................139

PIE OPTIONS.........................................................................................................................................140 Pie Labels ...................................................................................................................................141 Data Labels as Values ................................................................................................................141 Grouping Percent........................................................................................................................141 Explode Pie Segments .................................................................................................................141 Highest ........................................................................................................................................141 Lowest .........................................................................................................................................141 Percentage and Value .................................................................................................................142 Above ..........................................................................................................................................142 Below ..........................................................................................................................................142

LEGEND ................................................................................................................................................143 Show Legend ...............................................................................................................................143 Location ......................................................................................................................................143 Style.............................................................................................................................................143


- 208 –


Labels..........................................................................................................................................143 Font.............................................................................................................................................144 Size..............................................................................................................................................144 Bold/Italic/Underline ..................................................................................................................144 Colour Points..............................................................................................................................144 Subset ..........................................................................................................................................144 Translucency...............................................................................................................................145 Point Type ...................................................................................................................................145 Plotting Type...............................................................................................................................145 R Axis ..........................................................................................................................................145

AUTO POINTS........................................................................................................................................146 Linear Trend ...............................................................................................................................147 Average all subsets all points......................................................................................................147 Average all subsets per point ......................................................................................................148 Sum all subsets per point ............................................................................................................148 Moving Average..........................................................................................................................149 SPC (Control Limits) ..................................................................................................................150 Warning Limits ...........................................................................................................................151 Y Axis Annotations ......................................................................................................................152

GRAPH EXAMPLES ..........................................................................................................................154

BAR CHART...........................................................................................................................................154 Extractor Report .........................................................................................................................154

DATA SOURCE.......................................................................................................................................157 COLUMNS TAB ......................................................................................................................................158 GRAPH STYLE .......................................................................................................................................162

Stacked Bar Chart.......................................................................................................................163 Extractor Report .........................................................................................................................166

DATA SOURCE.......................................................................................................................................168 Graph Style .................................................................................................................................172 Pie Options .................................................................................................................................173 Legend.........................................................................................................................................174

MAIL RUN USING REPORT DESIGNER .......................................................................................177

Page Header ...............................................................................................................................177 Page Body...................................................................................................................................177 Page Footer ................................................................................................................................177

TABLES...............................................................................................................................................179

STANDARD REPORT WIZARD.................................................................................................................179 Title .............................................................................................................................................181 Data Source ................................................................................................................................182 Fields ..........................................................................................................................................184 Sorting.........................................................................................................................................186 Grouping.....................................................................................................................................187 Filtering ......................................................................................................................................188 Style.............................................................................................................................................190 Lay Fields out in Table Format or Form Format .......................................................................190 Simulate Greenbar in Body Band ...............................................................................................190 Include Title Page.......................................................................................................................190 Page Properties ..........................................................................................................................191 Colum/Label Properties..............................................................................................................191

CROSS-TAB REPORT WIZARD ...............................................................................................................193 Title .............................................................................................................................................193 Data Source ................................................................................................................................194 Cross-Tab....................................................................................................................................196 Filtering ......................................................................................................................................199 Page Properties ..........................................................................................................................201

safeguard report designer training guide · 2010-07-30 · ~ safeguard report designer training ~...

Documents