Creating custom reports in ValuePRO is usually for very advanced master user's. Many people rely on our support and development team to help create these for them in our professional services offering.
Should you wish to try your hand at it, here is a basic guide on how they are set up.
Set it up in ValuePRO
Create a new Custom Report
- To create a custom report in ValuePRO Standard Edition, navigate to Reports > Custom Reports.
- If you have already set up custom reports, they will be listed here.
- On the bottom of this screen is a button: New Report Wizard and Add New (Advanced Mode).
- Click on Add New.
- Navigate to the Templates > Report Templates tab.
- Select Custom Reports from the drop‐down list.
- Click the Add New button.
Both these ways opens the same template editing screen to create your new custom report. To go back later to edit your report after you have created it, use the second option.
To edit an existing custom report, go to this article's Edit section at the end.
Fill in the fields:
- Enter a name for the custom report.(There is a default name, but just go over this and name it an appropriate name.)
- Enter Template Type as Custom Report (should be the default).
- Enter a short description of the report’s purpose.
- Set Edit Layout in to Microsoft Excel.
- Check the Enabled box to allow the report to be generated.
- Check the Mark as Draft until Approved box to include the Draft Copy watermark (Optional)
- Click the Save Data button.
- Create your Excel report template for your custom report and have that ready. (see steps below)
- Click the Layout tab.
Custom Report screen (Layout tab)
- Click the Add Custom Data Source button.
Custom Data Source
You can name your data source anything fitting to this report, like “New_Data_Source” as shown below. Remember to save your changes.
- Set the title for the Data Source.
- Set the Data Source Name (DSN). This is the same as the Data Source listed in your template, eg ‘Job_List’.
- Enter a short description of the data source.
- Select the table from which to obtain the data. (We recommend "Valuations_Extended" for most data.)
- Set the access level for this data source.
- Check Do Not Send/Preview Report if this Data Source is Empty to prevent users generating empty reports.
- ADVANCED USERS: You can also add more than one custom data source for this report, should one table not be enough to pull all your data required for this report.
- Click the Save Changes button.
- Click the Search Criteria tab.
Set the Search Criteria
Search criteria comes in handy when you want to run this report under filters such as Date in, or even financial or customer information.
1. Click on the Search Criteria tab, then the "Add Criteria" button on the right bottom of this screen. A new selection drop down list should come up with available fields to use as search criteria.
2. After selecting one, you can select further criteria such as "User defines custom date range" or "Is not Null". There are many options available.
3. Click Save Changes.
(If you need help defining the search criteria, our team can estimate the work involved and help do that for you under our professional services offering.)
- See all the Criteria Fields you selected on the Add Criteria screen.
- Repeat steps 2‐3 for each Criteria Field.
- Click the Save Changes button,
- Click the Sort Order tab.
- Select the fields you want to use to sort results in your report.
- Click the Save Changes button.
Keep the above screen open and open Excel for the next step.
Create a Custom Report template in Excel
Start with building your report in Excel:
Building the report
Create in Microsoft Excel the template document (such as the sample above, or use existing templates for a guide. Download Custom Report samples by clicking on their name link as you do with Report Templates.)
For the Custom reports, you will not use the normal "Valuations.field", but rather “Datasource.field” as the merge fields structure to use in Excel.
Note that fields to be merged are entered in the format:
&=(Custom Data Source Name).(Field Name)
Once you have created your template/ row. Select the row with the merge fields and create a named range, that matches the Custom Data Source Name by entering a name in the named range box.
- Only add one merge field per cell. You can however, merge data of cells with normal Excel functions and calculations. Hidden rows will not show in the report.
- Save this file to your desktop as a normal excel workbook (.xlsx)
- For the best ways to display data and to see layout samples, follow our Custom Report samples uploaded to your system for a guide. There is a Client List and a Job List sample.
- For details on the Custom Data Source name, please go through the whole article first to understand where this is entered. You will need this name to create this Excel spreadsheet as sampled above. You can decide on any name you like. We used a name close to the purpose of the report, for instance on a report making a list of jobs, it could be "Job_List". (See the sample reports for help on this, or contact us.)
- Reports created with &=Valuations.field may also work (in some cases) but is not the best option for this sort of report, and can have errors. You have to set your custom data source to use the selected table. &=(Custom Data Source Name).(Field Name) (if you need help here, as it can get tricky, let us know)
To upload the Excel file in ValuePRO
Go back to ValuePRO. Click the Upload New Template File button.
- Click the Browse button.
- Find your new template Excel file. Select it.
- Click the Upload button.
- See the new name link that also allows you to download the template file appeared.
- Notice the Run Diagnostics button has appeared. This allows you to run an automatic check of your template to identify merge field errors.
- Click the Save Changes button when you are done editing and uploading the template.
Test your final result
Select your report under Reports > Custom Reports.
Select the criteria set for this report, like for example date range you want to run it for, and click next.
Your report will generate. This report is available to download as PDF, XLS or XLSX files.
Did you set up who to send the report to?
Click on Next to send it to the predefined users. (done when setting up your report)
To edit an existing custom report
Go to Templates > Report Templates, and select Custom Reports from the drop down menu.
Click the report you want from the name list, by clicking on the name of the report you want to edit.
Download it from the blue link in the Layout tab screen by just clicking the link. (In the sample below it is the "Test Report" blue text link.)
Make changes to the downloaded file (see your Downloads folder) by following our guide above, on your desktop, (usually in Excel) and save the file. Re-upload this new file with the "Upload New Template File" button.
PS: If there is an error on your report, the system will change the name text of the report to red as well as give you some diagnostic information. Contact us at email@example.com should you need help.
How to make changes to search criteria of your custom report: To get back to search criteria, click in the name of the data source you added under the Layout tab. This will open the data source card where you can navigate to the search criteria to make changes to those. (such as date range criteria settings etc.)
After making your changes, save and close the card.
Question: When I generate my custom report, the view is not showing all the columns I added in the report, but if I download the excel file everything is there. Why is that?
Answer: The preview is shown as a pdf and the width is limited. You should always view your reports from the downloaded excel file.
Question: Do you have a training session available for custom reports?
Answer: ValuePRO engage users who need training upon enquiry. See more details on custom training our website: https://www.valuepro.com.au/training/