This tutorial shows how to create Custom Reports and print to specifc formats.
Overview
The Custom Reports feature allows users to create their own custom reports inside kwiklook. The below instructions use Pre-Defined Global Variables and Drop Down Filtering Options that are defined on the Custom Reports knowledgebase article.
Please note: The ‘Creating a PDF Report’ tutorial can be used for creating Word reports as well.
Please note: To print reports to Word or Excel, Microsoft 365 must be installed.
Please note: Any type of report can be printed to a physical printer.
Please note: When emailing a report directly from the Print Preview window, the report will be saved as a PDF file. If any other report format is required then it is recommended to print directly to that format, then attach the printed report to a new email using your email client.
Please note: The minimum version of kwiklook required for this tutorial is v6.8.
Creating a PDF Report
Step 1: Importing the default ‘New PDF Report’ template
- Login to kwiklook.
- On the top menu bar click ‘Reporting > Custom Reports’.
- In the ‘Custom Reports’ window, click the ‘Import’ button.
- In the ‘Import Report Form’ window choose the ‘New PDF Report.xml’ file which is in the ‘.\kwiklook\Program’ folder, then click ‘Open’.
- The default ‘New PDF Report’ template will now be imported.
Step 2: Editing the report
- Select the imported report called ‘New PDF Report’ in the ‘Custom Reports’ window and click the ‘Edit’ button to start editing the report.
- The ‘Edit Report’ window will now appear. This window can be enlarged if the view is too small.
- The report name first needs to be changed. To do this:
- The default report contains placeholder information in the Graphic Band Editor that can be changed to suit your company details. Firstly click the top left area in the Graphic Band Editor > Header and a red box should appear. This is the logo placeholder.
Double click the ‘File > Value’ field in the Property Editor and choose your logo file to add into the report.
- Click the ‘Report Name’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to ‘Maintenance Jobs by Date, Time and Worktype’. You may also need to increase the width of this field by either clicking and dragging the right side of the red box to the desired width in the Graphic Band Editor, or double click the ‘Width > Value’ field and increasing the value in the Property Editor.
- Click the ‘Company Name’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company name. You may also need to increase the width of this field.
- Click the ‘Company Address’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company address. You may also need to increase the width of this field.
- Click the ‘Company Contact Details’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company contact details. You may also need to increase the width of this field.
- The header band should now look similar to this:
- The footer band can be edited if desired.
- Now lets add fields to the detail band. First we need to set the ‘Primary File’ in the Report Structure List. Click the ‘Primary File’ in the Report Structure List. Then double click the ‘Primary Source > Value’ field in the Property Editor. This will display a drop list. Click the drop list and choose the ‘MaintReportLog’ option. This is the primary table that stores all of the Maintenance Jobs in kwiklook.
- Now we are going to set the sort order of the report. While we have the ‘Primary File’ selected in the Report Structure List, double click the ‘Order By (Key) > Value’ field in the Property Editor. This will display a drop list. Click the drop list and choose the ‘KeySysID’ option. This will sort our results in the report by their ID number in ascending order. Tip: If you want reverse this sort order and show the results in descending order (highest ID number first) then choose the ‘KeyRevSysID’ option.
- It’s recommended to keep the detail bands named correctly as per their table name. In the Report Structure List click ‘Print New Detail’, then in the Property Editor double click the ‘Detail Name > Value’ field, then type in ‘MaintReportLog’ and press enter.
- The detail band height is a bit small. To increase the height of the detail band, double click the ‘Height > Value’ field in the Property Editor and change the height to ‘110’ and press enter. Please note: When changing the width and height of bands you need to save and re-open the report for the change to appear in the Graphic Band Editor.
- Let’s add our first text field to the ‘MaintReportLog’ detail band. In the Graphic Band Editor click the ‘A’ and click on the top left of the ‘MaintReportLog’ detail band to place a text field.
- In the Propery Editor pane the ‘Text > Value’ field should automatically be selected and ready to be changed. Change this value to ‘Job ID:’ and press enter.
- In the Property Editor pane click the ‘Fields’ tab. This tab lists all the available database fields in the ‘MaintReportLog’ database table that we can use in the report. Scroll down until you find the ‘MRL:SysID’ field. Click and drag this field into the Graphic Band Editor next to the ‘Job ID:’ text field.
- Repeat steps 16 to 18 for the following fields, placing the text fields underneath the ‘Job ID:’ field and the value fields underneath the ” field. You may also need to increase the width of the fields and values:
- Add Date: MRL:AddDate.
- Add Time: MRL:AddTime.
- Due Date: MRL:DueDate.
- Due Time: MRL:DueTime.
- Work Type: MRL:Description.
- Status: (no value field, we will add this later).
- Details: MRL:Details.
- The ‘MaintReportLog’ detail band should now look like this:
This looks a little untidy, so let’s align our fields properly. First we will put the ‘Add Time:’ field and value on the same line as the ‘Add Date:’ field and value. Click the ‘Add Time:’ text field, then hold down ‘Ctrl’ on the keyboard and click the ” value field. This will now select both felds at the same time. Click and drag these fields to be in line with the ‘Add Date:’ fields:
Do the same to line up the ‘Due Time:’ field and value with the ‘Due Date:’ field and value:
Now line up the following text fields to be directly underneath each other:- Job ID.
- Add Date.
- Due Date.
- Work Type.
- Status.
- Details.
Hold down Ctrl and select those fields. Right click one of the selected fields and choose ‘Spread Vertically’. Right click one of the selected fields and choose ‘Alight Left’. These fields should now be aligned nicely:
Select the ‘MRL:SYSID’ field, then hold down Ctrl and select the ‘Job ID:’ field. Right click the selected fields and choose ‘Align Bottom’. Repeat this step for each of the values that need to be lined up:
Now align the lefts of the remaining fields. Select the fields ‘MRL:SYSID’ to ‘MRL:DETAILS’ then right click the selected fields and choose ‘Align Left’. Select the fields ‘Add Time:’ and ‘Due Time:’ then right click the selected fields and choose ‘Align Left’. Select the fields ‘MRL:ADDTIME’ and ‘MRL:DUETIME’ then right click the selected fields and choose ‘Align Left’. All the fields in this band should now be aligned:
- The ‘MRL:ADDTIME’, ‘MRL:DUETIME’ and ‘MRL:DETAILS’ fields need some of their properties changed to print correctly:
- MRL:ADDTIME:
- Click this field, then in the Property Editor ‘Properties’ tab scroll down to the ‘Justify’ property and double click the ‘Value’ field to show the drop list of options. Select the ‘Left’ option.
- Double click the ‘Value’ field for the ‘Picture’ property. In the ‘Lookup Picture’ window that opens click the ‘Time’ tab. In the ‘Format’ drop list choose the ‘hh:mmXm’ option. Click the ‘Select’ button. This will display the time in a specific format.
- MRL:DUETIME: Repeat as per the ‘MRL:ADDTIME’ field.
- MRL:DETAILS:
- Click this field, then in the Property Editor ‘Properties’ tab scroll down to the ‘Width’ property and double click the ‘Value’ field. Change the value to ‘500’ then press enter to save the new value.
- Double click the ‘Fit Height to Text’ value field so the value changes to ‘Yes’.
- Double click the ‘Text Box’ value field so the value changes to ‘Yes’. This option will ensure that this field will automatically resize the height when there is lots of text to print.
- Double click the ‘Justify’ value field to show the drop list of options. Select the ‘Left’ option.
- MRL:ADDTIME:
- The last field we need to setup for this detail band is the ‘Status’ field. The ‘Status’ field inside the kwiklook database is a number and printing this number on the report won’t mean much to whoever is reading the report, so we need to change this number to a text field and print the text field result instead. To do this we are going to make use of ‘Local Variables’ and ‘Calculations’:
- In the Property Editor window click the ‘Local’ tab.
- In the top right on the Property Editor window ‘Local’ tab there are two buttons: A red X button and a white paper button. The white paper button is used to add new local variables – from now on called the ‘New’ button. The red X button is used to delete any local variables – from now on called the ‘Delete’ button. Click the ‘New’ button:
- Change the name of the new local variable to ‘Status’ and press enter to save. The rest of the local variable fields can be left as is:
- Now drag and drop this local variable onto the detail band next to the ‘Status:’ field and align the fields:
- In the Report Structure List window, click the ‘Primary File MaintReportLog’ line, then click the ‘Calculator’ button to add a new calculation operation. Click the new ‘Calculation New Calc’ line, then click the right arrow button to indent the new calculation field in the Report Structure List:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusPending’:
- Double click the value field for the ‘If’ property and change the value to:In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘Pending’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
< MRL:STATUS > = 0
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”Pending”. Make sure you include single quotes around the word ‘Pending’:
- The calculation fields should look like this:
- In the Report Structure List window, add a new calculation and indent as per step 22.5.
- In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘Assigned’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusAssigned’.
- Double click the value field for the ‘If’ property and change the value to:
< MRL:STATUS > = 1
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”Assigned”. Make sure you include single quotes around the word ‘Assigned’.
- In the Report Structure List window, add a new calculation and indent as per step 22.5.
- In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘In Progress’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusInProgress’.
- Double click the value field for the ‘If’ property and change the value to:
< MRL:STATUS > = 2
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”In Progress”. Make sure you include single quotes around the word ‘In Progress’.
- In the Report Structure List window, add a new calculation and indent as per step 22.5.
- In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘Completed’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusCompleted’.
- Double click the value field for the ‘If’ property and change the value to:
< MRL:STATUS > = 3
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”Completed”. Make sure you include single quotes around the word ‘Completed’.
- In the Report Structure List window, add a new calculation and indent as per step 22.5.
- In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘Closed’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusClosed’.
- Double click the value field for the ‘If’ property and change the value to:
< MRL:STATUS > = 4
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”Closed”. Make sure you include single quotes around the word ‘Closed’.
- In the Report Structure List window, add a new calculation and indent as per step 22.5.
- In the Property Editor window, click the ‘Properties’ tab. Here we are going to set the properties of this new calculation to display the word ‘Cancelled’ in the ‘Status’ local variable field if the MRL:Status database field equals a specific number:
- Double click the value field for the ‘Calc Name’ property and change the value to ‘StatusCancelled’.
- Double click the value field for the ‘If’ property and change the value to:
< MRL:STATUS > = 5
- Double click the value field for the ‘Set’ property and change the value to:
< Status >
- Double click the value field for the ‘To’ property and change the value to ”Cancelled”. Make sure you include single quotes around the word ‘Cancelled’.
- Now the ‘Status’ local variable field will print the correct status of the Maintenance Job:
- This report can now be printed as is, but the resulting report will contain all Maintenance Jobs in the kwiklook database. Depending on your kwiklook database this could be a huge report! Let’s add some date filters:
-
- In the Property Editor window click the ‘Local’ tab, then click the ‘New’ button.
- Change the ‘Name’ of the new local variable to ‘FromDate’.
- Double click the ‘Picture’ field.
- In the ‘Lookup Picture’ window click the ‘Date’ tab.
- In the ‘Format’ drop list, choose the ‘dd/mm/yyyy’ option. Click the ‘Select’ button:
- Double click the ‘Type’ field and choose the ‘User’ option:
- Double click the ‘Prompt’ field and type in ”From date:”, then click the ‘Save’ button. Make sure you include single quotes around the text ‘From date:’:
- The ‘FromDate’ filter should look like this:
- In the Property Editor window click the ‘Local’ tab, then click the ‘New’ button.
- Change the ‘Name’ of the new local variable to ‘ToDate’.
- Double click the ‘Picture’ field.
- In the ‘Lookup Picture’ window click the ‘Date’ tab.
- In the ‘Format’ drop list, choose the ‘dd/mm/yyyy’ option. Click the ‘Select’ button.
- Double click the ‘Type’ field and choose the ‘User’ option.
- Double click the ‘Prompt’ field and type in ”To date:”, then click the ‘Save’ button. Make sure you include single quotes around the text ‘To date:’.
- The ‘ToDate’ filter should look like this:
- In the Report Structure List window, click the ‘Primary File MaintReportLog’ line.
- In the Property Editor window, click the ‘Properties’ tab.
- Double click the ‘Value’ field for the ‘Report Filter’ property and type the following filter:
< MRL:ADDDATE > >= < FromDate > AND < MRL:ADDDATE > <= < ToDate >
Click the ‘Save’ button:
- In this filter we are checking if the Maintenance Job Add Date is greater than or equal to the ‘From Date’ local variable, and if the Maintenance Job Add Date is less than or equal to the ‘To Date’ local variable.
-
- The report is now ready to save and test. Click the ‘Save’ button to save the custom report and close the ‘Edit Report’ window.
- In the ‘Custom Reports’ window, select your custom report and click the ‘Print’ button:
- The ‘Report Options’ window will now appear. In this window you can see our two filters ‘From date’ and ‘To date’. Click the question mark button next to each of these filter fields to select a ‘From date’ and a ‘To date’. Click the ‘Start’ button to run the report and display the ‘Report Preview’ window.
- The ‘Report Preview’ window will now appear. For the purpose of this tutorial click the ‘PDF’ button on the bottom left of the ‘Report Preview’ window. This will set the file path and name. Click the ‘Save’ button to print this report. The report should open automatically in the PDF application installed on the PC:
- Congratulations! You have successfully completed this tutorial.
Creating an Excel Report
Step 1: Importing the default template
- Login to kwiklook.
- On the top menu bar click ‘Reporting > Custom Reports’.
- In the ‘Custom Reports’ window, click the ‘Import’ button.
- In the ‘Import Report Form’ window choose the ‘New CSV Report.xml’ file which is in the ‘.\kwiklook\Program’ folder, then click ‘Open’:
- The default ‘New CSV Report’ template will now be imported.
Step 2: Editing the report
- Select the imported report called ‘New CSV Report’ in the ‘Custom Reports’ window and click the ‘Edit’ button to start editing the report:
- The ‘Edit Report’ window will now appear. This window can be enlarged if the view is too small.
- The report name first needs to be changed. To do this:
- Excel reports normally do not include images. The default CSV report template does not include a logo image and we will skip adding a logo for this tutorial.
- Click the ‘Report Name’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to ‘Report Name: Maintenance Jobs by Date, Time and Worktype’. You may also need to increase the width of this field by either clicking and dragging the right side of the red box to the desired width in the Graphic Band Editor, or double click the ‘Width > Value’ field and increasing the value in the Property Editor to ‘400’.
- Click the ‘Company Name’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company name. You may also need to increase the width of this field.
- Click the ‘Company Address’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company address. You may also need to increase the width of this field.
- Click the ‘Company Contact Details’ field in the Graphic Band Editor, then double click the ‘Text > Value’ field in Property Editor to change the name of the report to your company contact details. You may also need to increase the width of this field.
- The footer band should now look similar to this:
- The next step is to set the ‘Primary File’ in the Report Structure List. Click the ‘Primary File’ in the Report Structure List. Then double click the ‘Primary Source > Value’ field in the Property Editor. This will display a drop list. Click the drop list and choose the ‘MaintReportLog’ option. This is the primary table that stores all of the Maintenance Jobs in kwiklook.
- Now we are going to set the sort order of the report. While we have the ‘Primary File’ selected in the Report Structure List, double click the ‘Order By (Key) > Value’ field in the Property Editor. This will display a drop list. Click the drop list and choose the ‘KeySysID’ option. This will sort our results in the report by their ID number in ascending order. Tip: If you want reverse this sort order and show the results in descending order (highest ID number first) then choose the ‘KeyRevSysID’ option.
- It’s recommended to keep the detail bands named correctly as per their table name. In the Report Structure List click ‘Print New Detail’, then in the Property Editor double click the ‘Detail Name > Value’ field, then type in ‘MaintReportLog’ and press enter.
- The detail band height is a bit large. To decrease the height of the detail band, double click the ‘Height > Value’ field in the Property Editor and change the height to ‘25’ and press enter. Please note: When changing the width and height of bands you need to save and re-open the report for the change to appear in the Graphic Band Editor.
- Now let’s change the header band details to match the fields that will be in our report. Click the ‘Field 1’ text field in the header band detail in the Graphic Band Editor, then double click then double click the ‘Text > Value’ field in the Property Editor to change the name of this field. Change the name of this field to ‘Job ID’.
- Change the other header text fields to match the following field names:
- Scroll to the right inside the Graphic Band Editor and you will see an extra field called ‘etc…’. Change the text of this field to ‘Details’. Also change the ‘Font Style’ property to ‘Bold’.
- Let’s add our first value text field to the ‘MaintReportLog’ detail band. In the Property Editor click the ‘Fields’ tab then drag and drop the ‘MRL:SYSID’ field into the ‘MaintReportLog’ detail band underneath the ‘Job ID’ header field.
- Align the ‘Job ID’ and ” fields by first clicking the ” field then pressing the Ctrl button also click the ‘Job ID’ field to select both fields together. Then right click on one of these fields and choose ‘Align Left’.
- Now add the other fields into the ‘MaintReportLog’ detail band, lining up with the header text fields:
- Add Date: MRL:AddDate.
- Add Time: MRL:AddTime.
- Due Date: MRL:DueDate.
- Due Time: MRL:DueTime.
- Work Type: MRL:Description.
- Status: (no value field, we will add this later).
- Details: MRL:Details.
- The ‘MaintReportLog’ detail band should now look like this:
Let’s align the bottoms of these fields so they all display correctly. Select all the ” fields then right click on one of the selected fields and choose ‘Align Bottom’. The value fields will now be aligned correctly. - The ‘MRL:ADDTIME’, ‘MRL:DUETIME’ and ‘MRL:DETAILS’ fields need some of their properties changed to print correctly:
- MRL:ADDTIME:
- Double click the ‘Value’ field for the ‘Picture’ property. In the ‘Lookup Picture’ window that opens click the ‘Time’ tab. In the ‘Format’ drop list choose the ‘hh:mmXm’ option. Click the ‘Select’ button. This will display the time in a specific format.
- MRL:DUETIME: Repeat as per the ‘MRL:ADDTIME’ field.
- MRL:DETAILS:
- Double click the ‘Text Box’ value field so the value changes to ‘Yes’. This option will ensure that this field will automatically resize the height when there is lots of text to print.
- MRL:ADDTIME:
- The last field we need to setup for this detail band is the ‘Status’ field. Please follow step 21 in the ‘Creating a PDF Report’ instructions above, then move on to step 23 here.
- You might notice the ‘ < MRL:DESCRIPTION > ‘ and ‘ < Status > ‘ fields are overlapping. The ‘Status’ and ‘ < Status > ‘ fields will need to be moved to the right to allow for the space. Change their ‘X Pos’ values to ‘800’.
- This report can now be printed as is, but the resulting report will contain all Maintenance Jobs in the kwiklook database. Depending on your kwiklook database this could be a huge report! Let’s add some date filters. Please follow step 23 in the ‘Creating a PDF Report’ instructions above, then move on to step 25 here.
- The report is now ready to save and test. Click the ‘Save’ button to save the custom report and close the ‘Edit Report’ window.
- In the ‘Custom Reports’ window, select your custom report and click the ‘Print’ button.
- The ‘Report Options’ window will now appear. In this window you can see our two filters ‘From date’ and ‘To date’. Click the question mark button next to each of these filter fields to select a ‘From date’ and a ‘To date’. Click the ‘Start’ button to run the report and display the ‘Report Preview’ window.
- The ‘Report Preview’ window allows previewing the report before printing to an Excel file (*.XLSX). Click the ‘Excel’ button in the ‘Output’ bar, then click the ‘Save’ button. The report will be created and Microsoft Excel will automatically open the report.
- Congratulations! You have successfully completed this tutorial.