Search

Index

Revindex Storefront

How to create custom reports

Last updated on 2020-06-04 2 mins. to read

You need to be logged in as a Host superuser to edit or create new reports. Standard reports cannot be edited.

  1. The easiest way is to clone one of the existing reports and make the modifications. Alternatively, you can click on the Add new to create a new custom report from blank.
     
  2. Give your report a name and optionally a description. Select the report group to determine where the report will show up under the Catalog, Sales, People or Marketing menu.
     
  3. Enter one or more SQL SELECT statements in the Data source tab. You have full access to all the standard SQL commands including variables and temp tables. If you require any input parameters you can use the special @param placeholders. For example:

    SELECT col1, col2 FROM MyTable WHERE col3 = @Param1 AND col4 = @Param2

    SELECT col5, col6 FROM MyTable2 WHERE col8 = @Param1 AND col9 = @Param2
     
  4. Add the matching input parameters required by your data source in the Parameter tab. The names must match exactly your @Param placeholder names. Parameters can be a form input or one of the predefined variables.
     
  5. In the Visualizer tab, enter the HTML to render the report. The HTML can contain XSL Tokens to extract the returned values from your SQL execution. Each SQL SELECT statement, will generate a data table under the dataSet node. For example, the XML input below is generate from your data source. Your XSL tokens can be used to extract the result set to render the HTML:

    <in>
         <dataSet>
             <dataTable>
                 <dataRow>
                     <col1 dataType="int">...</col1>
                     <col2 dataType="nvarchar">...</col2>
                 </dataRow>
                 <dataRow>
                     <col1 dataType="int">...</col1>
                     <col2 dataType="nvarchar">...</col2>
                 </dataRow>
             <dataTable>
             <dataTable>
                 <dataRow>
                     <col5 dataType="boolean">...</col1>
                     <col5 dataType="nvarchar">...</col2>
                 </dataRow>
                 <dataRow>
                     <col4 dataType="boolean">...</col1>
                     <col5 dataType="nvarchar">...</col2>
                 </dataRow>
             <dataTable>
         </dataSet>
      </in>



    You can also include colorful graphics using Chart.js or Google charts simply by generating the correct Javascript statements needed to render the charts.
  6. This part is optional. In the Export tab, if you want to allow exporting the data to CSV, you can create a transform using XSL Tokens to generate the comma delimited data.

Comments


Powered by Revindex Wiki