Report Designer

Creating a Report in the Report Designer

1. Open the report designer.

   

2. Select a template – in the sidebar menu, click New via Wizard.

   

3. Choose how to create the report:

   • From scratch – full configuration freedom (Blank report).

   • Based on a template – quick start, options include:

     • Blank report – build from scratch.

     • Table / Vertical / Label / Cross-Tab reports – ready-to-use, flexible elements.

     • nVision report – least flexible.

4. Decide whether to use a database.

   

5. Set the data source:

   • Views (require defining relationships, parameters unavailable),

     

   • Queries.

     

6. Choose the method for working with data:

   • Query Builder – simpler configuration.

   • Custom SQL queries – more advanced capabilities.

7. Add parameters.

8. Customize the visual layer to your needs.

Creating a Report Using Query Wizard

Goal: Create a simple summary of tickets by category for a selected department.

Creating the basic version of the report:

1. Open the report designer and select Table report.

   

2. Choose Database as the data source.

3. Set the data source to Queries / Query Builder.

   

4. Configure the query by defining data types and relationships:

   • In the right panel, search for tables and views.

   • Drag them into the main window.

   • Create joins between tables.

   • Select columns for the report.

     

   • Click Preview results to check data.

     

   • Click OK to confirm the query.

   • Click Next to proceed.

     

5. Define report details by selecting elements to display.

   

6. Fine-tune final settings (page parameters and report title).

   

7. Save the report:

   • Press CTRL + S or click Save in the sidebar.

   • On first save, fill in the dialog provide a description and select a folder.

     

Modifying Report Appearance

Check the current report layout in the Preview tab.

Go to the Design tab to change the report layout.

Adjust the appearance: change field titles, format dates, adjust column widths.

1. Edit fields

   Select a field you want to edit:

   • Either in the sidebar panel,

     

   • Or in the form under the gear icon.

     

2. Format dates

   Click the f icon to open the formatting form.

   

   Original value is the column name in square brackets – indicates database data.

   

   To change date format, use functions.

   

3. Resize and style fields

   Drag edges to resize. In the sidebar, change color, margins, font.

   

4. Manage report sections

   On the left, you’ll see vertical sections:

   • Report header (logo, date, title).

   • Group headers (column names, category headers).

   • Details (data).

   Drag edges to adjust section height.

   

5. Add elements to the report

   Drag and drop elements (e.g. image, checkbox, table, chart, separator) from the left menu.

   

6. Check the report appearance after adjustments.

   

7. Save changes

   Note the asterisk next to the report name in the bottom tab – indicates unsaved changes.

   

To make the report more useful, you can add initial parameters to limit the scope of analyzed data. For example, a department name related to the ticket could be such a parameter.

There are two types of parameters:

• Report parameters – visible to the user generating the report, selected via interface controls.

• Query parameters – variables used in database queries.

Adding a Parameter

1. Create a department list

   • Add a new dataset in the sidebar.

   • Click Add query.

     

   • Run the query builder.

     

   • Drag the reports.departments table into the main wizard area.

   • Select columns id and name.

   • Click OK and Finish.

2. Add a report parameter

   • In the sidebar, click + in Parameters section.

     

   • Fill in the form:

     • Name – used in the query.

     • Description – visible in the report.

     • Type – set to 32-bit number.

     • Allow null value – check.

     • Value – remove.

     • Value source – dynamic list.

     • Data source – select available.

     • Data table – set report_departments.

     • Value member – id.

     • Display member – name.

     

   • Confirm by clicking OK.

3. Open the report preview tab

   The report now requires department selection before being generated.

   

4. Link the parameter to the query

   • Return to the report design.

   • In the sidebar, edit the main query.

     

   • Launch the query builder.

   • In the sidebar, open Query properties.

   • Click the ellipsis in the filter field.

   • Add condition: department_id = ?department_id.

     

   • Confirm filter and query.

5. Set the query parameter

   • Click Next.

     

   • Show details of department_id parameter

     

   • Change parameter type to Expression.

   • Set result type to 32-bit number.

   • Remove value 0.

   • Click ellipsis

     

   • Select the report parameter by double-clicking

     

   • Confirm selection and finish.

     

6. Save the report

   Use CTRL + S to save all changes.

    

Creating a Report – Custom SQL Query

Goal: Show frequency of roles (total, activated, deleted) in user groups with two multi-select parameters: group and role.

1. Select report type and data source

   • In the report designer, choose Tabular report.

   • Go to data source settings.

   • Select Custom SQL query.

     

2. Insert SQL query into the editor:

   WITH allusers AS (
     SELECT
       report.users.id as id,
       isactivated,
       isdeleted,
       report.usergroups.id as groupid,
       report.usergroups.name as groupname,
       report.users.userhelpdeskroleid as roleid,
       CASE
         WHEN report.users.userhelpdeskroleid = 0 THEN 'user'
         WHEN report.users.userhelpdeskroleid = 1 THEN 'staff'
         WHEN report.users.userhelpdeskroleid = 2 THEN 'admin'
       END as rolename
     FROM report.users
     JOIN report.usersusergroups ON report.users.id = report.usersusergroups.userid
     JOIN report.usergroups ON report.usersusergroups.usergroupid = report.usergroups.id 
   )
   SELECT groupid, groupname, rolename, roleid, COUNT(id) AS total,
     SUM(CASE WHEN isdeleted = TRUE THEN 1 ELSE 0 END) AS deletedcount,
     SUM(CASE WHEN isactivated = TRUE THEN 1 ELSE 0 END) AS activatedcount
   FROM allusers
   GROUP BY groupid, groupname, rolename, role_id
         

   

3. In the next step select report fields (displayed, grouping, and summary).

   

4. Confirm and save

   • Fill in the report title.

   • Save changes.

   • Check report preview.

     

5. Prepare data for selection lists in parameters

   Add two datasets in the sidebar (for user_groups and user_helpdesk_roles).

   

6. Add report parameters

   • Create two parameters: group and role.

   • Set options:

     • Multi-select.

     • No null value.

     • Remove default value.

   

7. Add conditions to the SQL query:

   

   WHERE(
     @role_ids = ''
     OR
     report.users.user_helpdesk_role_id = any(string_to_array(@role_ids, ',')::int[])
   ) AND (
     @group_ids = ''
     OR
     report.user_groups.id = any(string_to_array(@group_ids, ',')::int[])
   )
         

   Explanation:

   • @role_ids, @group_ids – query parameter values.

   • Empty parameter check means selecting all.

   • string_to_array splits values into a list of numbers.

   • any() checks for matches in the list.

8. Create query parameters

   

9. Add parameter group_ids :

   • Type: Expression.

   • Result: text created by joining values from the report parameter GroupId (join function).

   

10. Add parameter role_ids in the same way.

    

11. Customize appearance – change column names, colors, fonts.

    

12. Adjust summary fields

    • Remove unnecessary values.

    • Change format for readability.

      

    • Check the result.

      

13. Add rows to the table

    • Right-click a table cell.

    • Add rows from the context menu.

      

14. Add titles and formatting

    • Enter parameter names.

    • Adjust font format.

      

15. Display parameter values

    • Click the function icon.

      

    • In the editor, add expression: Iif(Any(?RoleId) == False, GetLocalizedString('All'), GetDisplayText(?RoleId)), so that if role id is not selected, “all” translated into current language is displayed, and if role is selected, role’s name gets displayed.

      

    • Add similar expression for the second parameter.

16. Check the result.