How to Create a Custom Report

Added in Version 0.37

Overview

• In this article, we’ll describe how to create a Custom Report datasource and use it in a custom Google Sheets/Excel report.

Generate a Datasource

1. From the Main Menu, scroll down to CONFIGURE and click Custom Report Sources.
2. Click New.
3. Select a Username to use as security for the datasource. This will default to the logged in user.
4. Select a Source from the dropdown.
5. A unique URL for be generated for this datasource.
6. Copy the URL.

NOTE: Keep the URL private as anyone with the URL can retrieve data under the security of the datasource user. If a URL is compromised, please delete the datasource immediately as this will make the URL unusable.

Create a Custom Report

1. Create a copy/download one of the templates:
   • Google Sheets – https://docs.google.com/spreadsheets/d/1bZy3YQJKG_YsgkadQtOQkfuE-GHY-Ct8SLGkBDDKcBI/edit?usp=sharing
   • Microsoft Excel (Windows only) – https://drive.google.com/file/d/1k2aPrHZSMAQfc4TTwoL1ricGepWvPNTh/view?usp=drive_link
2. Make a copy/duplicate the Template sheet.
3. In cell A1 paste the URL for your datasource (see the section above).
4. The report is now ready to use and can be added as a source to Pivot Tables, Charts, etc.
5. If more datasources are required in the same Workbook, generate another datasource URL and follow steps 2 and 3.

Report Filters and Options

1. Report filters can be added in cells A2 – B11. The first column is the column to filter and the second column is the filter value.
2. Filter values can contain the same operators used in the Solahart App:
   • Wildcard search * eg: *Zoom App*
   • Less Than < eg: <10
   • Greater Than > eg: >100
   • Equal = eg: =Solahart
   • Not Equal <> eg: <>null
   • Less Than or Equal <= eg: <=99
   • Greater Than or Equal >= eg: >= 201
   • Between Two Values .. eg: 01/12/24..31/12/24
   • Or Filter | eg: Q1|Q2|Q3
   • And Filter & eg: <>Tim&<>Paul
3. Report options can be added to cells B14 – B18.
   • TOP – Returns the top # records. Defaults to 200.
   • OFFSET – Must be used with FETCH (advanced usage for “paging” results). Note: Cannot be used with TOP.
   • FETCH – See above.
   • SORTING – Comma seperate the columns to sort by – Eg: Suburb,Postcode (this will sort by the Suburb column, then by Postcode).
   • ORDER – Comma seperate order of sorting (must match the same number of columns in the SORTING option) – eg: Ascending,Desending (used with the SORTING above, this will sort the Suburb column in ascending order then the Postcode column in descending order).