Reports - Designing a Report

Reports - Designing a Report

If your system is licensed for IQanalytics Report Writer, you may design your own reports.
For a more comprehensive guide on how to design Izenda report, please scroll down to the end of the article to download the attachment.
*All credit to the Izenda team.



Understanding Reporting Views

Building a report requires selecting one or more printIQ database views and choosing one or more fields from those views. Presently, there are over two dozen views published from the printIQ database, and over 550 fields available from those views.

For example, information on Account Manager, Task and User comprises only one view for each, respectively.

But Data on Customer and Inventory is available from two or three views. Whereas fields for Jobs and Quotes will come from four or more views.

Information is joined to one another across different views via common fields. For instance, the Site ID field can join data between the Customer and Job views. Additionally, the Account Manager ID is found everywhere, from AccountManager and Customer views, to Invoicing, Jobs and Quotes.

The printIQ analytics engine will automatically find the correct field to join views together. It is recommended to build reports in this manner. But in certain circumstances, you may be required to manually specify the type of fields and thier joins to achieve the results you need.

Joins will determine the simplicity or complexity of report results you receive. There are 5 types of joins to choose from. What follows is the type and general description of each:
  • Inner (Direct) - this is the default join. This will return records at the intersection of the two data sources
  • Cross - Each record in the first data source is combined [multiplied] with each record in the second data source
  • Left (First Exists) - This will return each record from the first data source no matter if the second data source contains a match
  • Right - This will return each record from the second data source no matter if the first data source contains a match
  • Full - This will return every record from both data sources
Please test different join results with a few fields to avoid overloading queries on the database.
For a breakdown of available views and their fields, please refer to Knowledge Base Article here.

Accessing Reports Page

Reporting Roles

To either view reports or build them, you’ll need Reporting roles enabled per your User. Under any User Details screen, tick the boxes for Reporting Admin (create reports) and / or Reporting User (may only run reports). There are further Reporting Permissions which are explained later in this article.

By default, any User with Admin or Internal Role ticked will have access to reports.

Reports List Page

Once you’ve been enabled the proper Role, you can access the IQanalytics Report List screen from the My Reports tile of the Landing Page banner. This tile will be visible to Users with the following roles:
  • Admin
  • Customer
  • Internal
  • Sales Customer

The Report navigation button allows you to view existing reports and dashboards, as well as creating new reports and dashboards.

Remember to Save reports early and often. Report categories are created during the Save operation, using a forward or backward slash to configure sub categories.
Hovering over reports in the view allows quick access to Delete, Print or Edit a report.

Building a basic report

Data Sources

Depending on the data result you require for a report, one or many Data Sources may be selected. For our basic report example, we’ll want to see quotes created for the previous week, detail the customer and pricing information, and status of each quote. From our list of views and fields from referenced Knowledge Base Article, we can gather this information from combining the Quotes view with the QuoteProducts and Customers view.

As per below, we select our first data source, and use the add data source button next to it. Repeat until desired data sources and joins are established:


Ensure you configure the join between the Customer view and Quotes view to utilise the Customer ID field.

FIELDS

By selecting a Field row drop-down, you can choose fields from previously selected Data Sources (listed alphabetically) and customise the Description of the field to the report user as per below:

  • You may Sort a feild from A-Z
  • You may Visually Group a field, but this will override the Sort control
  • You may apply Arithmetic for basic calculations on a field
Configure the functions and formatting as per below:

You may delete, add additional rows above and below existing rows; and use the Advanced Field Settings to sort Z-A; control field result formatting and apply SQL expression code(s). For further information, please refer to:

PREVIEW

Once configured as desired, use the Preview to view results:

Applying a Chart

PIE CHART

Using our same quotes by customer and pricing report, we can apply a pie chart to visualise a breakdown of quotes by their status.
Go to the first Chart tab of the report designer, and choose Pie from the drop-down. Here are some charting basics:
  • Label: Represents the slice, bar, timepoint, or other element on the chart. Each value from the field selected as the Label field will be used to place items on the chart.
  • Value: Represents the number that is displayed within each Label. The size of a pie slice, the size of a bar, or the height of a point on a line will be represented by the values of this field.
Values MUST be aggregated numeric output! It is possible to display simple static numeric values such as ‘value of employee ID per employee’ but the best practice is to only display aggregated data, such as a sum of all spending or a count of all employees, on a chart.
  • Separator: Represents different charts within one chart object. This means that we can draw multiple lines, bars, or pies per time point per chart object. Without a separator you will get only one pie chart, one line, or one set of bars. Values of the separator will create distinct visual elements, such as a pie chart for every reported employee gender, a line for each country over time, or a bar chart with multiple bars per department, one for each work site.
Configure your pie chart as below:


Format the labels and values, and use the Advanced button to sort Z-A, and configure labeling, scale and chart type. Use Preview to see result.



Create Filters

FILTERS

Filters may be controlled by the report user, or hidden to control results. Using our same quotes by customer and pricing report, we will apply filters for date ranges and customer, and hide discarded and rejected quotes.
Go to the Filters tab and configure as per below, choosing the data sources and fields for
  • Quotes / Date Created
  • Customer / Name
  • Quotes / Quote Status




  • Depending on volume of customers in your database, Autocomplete might be offer quicker controls
  • Multi-select both Discarded and Rejected quote status results
  • To apply a [hidden] filter by logged in Account Manager, Customer or User, deploy the following views / fields, Equals operator and description:

[Data Source].[Field]
Description
VW_Reporting_AccountManager.Account Manager ID
Current Account Manager
VW_Reporting_Customer.Customer ID
Current Customer
VW_Reporting_Customer.Name
Current Customer
VW_Reporting_Users.User ID
Current User


  • To automatically filter by current date the report is run, utilise any date field, use the Equals operator with no values, and type "Current Date" for the Description.
Configure each fields Description and how they may / may not appear for end user.
  • Filter properties are applied during report designer preview but are not available for modification. You will have to Save your report, go back to report list and open the report as an end user to have typical interaction of the filters.

Saved, re-opened Report:

Permissions and Scheduling

Setting permissions on the report is most important. By default, a newly created report is only viewable by the report’s designer. You must reconfigure the default permission, and apply other shares / user rights, to allow others to view or use the report. In addition, a printIQ User will require designation of new reporting groups roles to make report sharing easier.

User Control

Under the Users file menu, open any existing User; or create a New User. A Reporting tab next to Roles tab will allow you to designate a User to any number of reporting specific roles. You can create new reporting roles using the ‘+’ button. These reporting specifc roles will be referenced at the report design level.

Sharing Roles and Rights

In the Report Designer, under the Misc tab is the Share With / Rights area.

As stated before: by default, every report is Shared With Everyone, but they have None Rights. You then select standard reporting roles, or custom reporting roles you’ve created, and configure permissions as follows:
  • Full Access: Report is available to the user to view, modify, and save changes to the report. Note that owner user(UserName property of the ReportSet) will not be changed on saving, i.e. the original owner(creator) of the report will stay the same.
  • Read Only: Report is available to the user to view, user can add or remove filters, and modify existing filters’ values. Also user can modify(design) the report but cannot save it, however user could Save As this report.
  • View Only: Report is available to the user to view, but not to modify in any way or save changes to the report.
  • Locked: Report is available to the user to view, and user can modify existing filters’ values, but user cannot add or remove filters or fields or save changes to the report. Note that modifying filter operator and/or filtered column is restricted as well.
  • None: Report is unavailable to (hidden from) the user.
Any time another Reporting Admin opens an existing report and Saves As, they become the report Owner.

    • Related Articles

    • Reports - Designing a Dashboard

      Dashboards Once a number of reports and charts have been created, you can place different charts onto a single Dashboard for high level views of printIQ’s database. These dashboards have their unique sharing permissions and may be set as default for ...

    • Reports - Data Sources (Views / Tables)

      If your system is licensed for IQanalytics Report Writer, you may design your own reports. In order to design reports, please refer to Knowledge Base Article here. Designing reports requires an understanding of the available data sources and their ...

    • Standard Reports Walkthrough Video

      Standard Reports Walkthrough video with Lorne McKendrick, IQ's Custom Report Specialist. Further knowledge base article on Standard Reports

    • 90 Day Stock Movement Report

        The following outlines the process to obtain a report on stock that has had no movement in the past 90 days.   From the 'Inventory' header, select 'Groups" from dropdown:     First under the 'Create Group' header, create a new group called ...

    • IQ Standard Reports

      A brief walkthrough and explanation of standard reports included with IQ... Customer \ Customer Activity in Selected Period This report provides a list of ALL of your active customers showing Job activity in the selected date range. The sorting logic ...