Intellicus Enterprise Reporting and Business Insights 18.1

Ad hoc Report Designer – Introduction

Ad hoc Report Designer is a tool for non-technical, business end users to design or modify reports. By following simple steps, business users can design reports on their data and view or download them in different formats. There are multiple settings like filtering, highlighting, sorting etc. that gives options to modify your reports as per your exact needs.

In this document we will be taking you through different options in designing ad hoc reports and discuss their benefits.

The Ad hoc Report Designer is divided into sections, which appear as tabs. Each section’s functionality is discussed in the respective sections.

A user with the role of Report Designer having system privileges for Ad hoc Report Designer can design ad hoc reports.

Ad hoc report may have tabular data arrangement in the form of a matrix or as a chart. On Ad hoc Report Designer, you can also:

• Sort the data to re-arrange it in order of your choice
• Get totals / summaries
• Highlight selective records (when a condition is met)

Ad hoc Report designing includes simple steps as selecting a data source and fields, applying grouping, filtering, etc. as required.  You can view the report immediately after it is designed.

When you click a tab, it opens to make respective settings on that tab. To close an open tab, click that tab again. You can also expand (or collapse) all tabs one underneath the other by clicking Expand All (or Collapse All).

Figure 1: Ad hoc Report Designer

Information:

• Ad hoc Report Designer is configurable for many options that it provides. Your administrator may further simplify your experience in creating or editing by removing some options on the Report Designer.
• When you run an ad hoc report in HTML output format, the viewer provides a right-click menu that helps altering the report on the viewer itself. To know more about the menu options, refer UsingPowerViewer
• You can also customize the toolbar when a report is viewed in HTML output format. To know about customizing toolbar of HTML Viewer, you can refer to CustomizingHTMLViewer

Selecting a Data Source

Let us look at the Data Source tab where you need to specify the source query object for creating an ad hoc report.

Data Source properties

Item	Values	Comments
Query Object	Select from Query Object Selector	Open the Query Object Selector, navigate to the folder and select the data source Query Object for this report. Selecting a Query Object populates its fields in all the sections (tabs) of the Ad hoc Report Designer
Query Editor	Open Query Editor	If you are a Data Administrator you can either design or edit the selected Query Object
Report Title	Type yourself	Type the title that should appear on the ad hoc report
Report Format	Select from the list: HTML ACROBAT PDF MS EXCEL COMMA SEPARATED TEXT MS WORD MS POWERPOINT iHTML   SMART XML RAW TEXT	HTML = Default format Opens report in HTML Report Viewer ACROBAT PDF = Opens report in PDF viewer MS EXCEL = Downloads report in XLS format COMMA SEPARATED = Downloads report in CSV format TEXT = Downloads report in TXT format MS WORD = Downloads report in DOC format MS POWERPOINT = Downloads report in PPT format iHTML = Opens report in single page HTML with Grid and Interactive Charts SMART = Opens report in Intellicus Smart View XML = Downloads report in XML format RAW TEXT = Downloads large reports in CSV format, without formatting
Template	Select from the list	A template defines the layout part of the ad hoc report. This generally applies to color, fonts and page size of the report. Some templates could be specifically designed for certain reports – such as wide report etc.
Report Contents	Detailed     Summarized	Detailed = Shows detail section (expanded), hence showing the lowest level detail of the report   Summarized = Hides detail section, hence showing only the group level summary of the report. Summarized Report shows useful data only when groups and summaries are applied

View Options

View Options are format specific settings for the report:

Action Buttons

Selecting Display Fields

Under the Fields tab, you can select the fields to be displayed on the report.

Figure 2: Selecting Display fields

To select a field, check the checkbox displayed before each field from Available Fields and click  button to bring the fields in Selected Fields.  To select all the fields, click  button .

To select a group, check the checkbox displayed before the group and click  button.

To deselect a field, highlight it from Selected Fields and click  button.  To deselect a group, highlight the group-name from Selected Fields and click  button. Click  button to deselect all the fields.

To reposition fields on the report, you can use the up  and down  arrows.

Click the Apply button after selecting the fields.

In case of a hyperlinked field (specified at the query object level), you can drill down to open another report or URL on clicking the value of field on grid.

You can quickly search a field name by entering any text in the Search textbox. The searched field appears highlighted in the shown list.

Display Field Properties

Rendering data bars

Figure 3: Rendering Databar

In case Update Fields At Runtime is enabled at Query Object level and you have added a new field in the database file, you would see the new field when Add New Fields At Runtime is checked here. In case this is unchecked, then you can add new fields from PowerViewer.

Applying Filters

You can use filters to limit the data that appears in the report. You can narrow the information based on specific conditions. Filter is a condition, which you can choose to apply on your report data. You can apply multiple conditions by joining them with AND/OR operators.

Filter Section properties

Ad hoc Filters

Actions

Selecting values from Lists and Multi-Select List

The value select list may behave differently for different fields based on how they have been configured by your data administrator for best performance. You can configure Lookup Values for fields while designing the Query Object. To know more about Query Objects, refer WorkingwithQueryObjects

List behavior

Entering values for Network ID formatted fields (specified on the Query Object Editor screen)

• IP: It needs to be specified as a set of 4 numbers (decimal) separated by dots or written in hexadecimal and separated by semicolon. (IPv4, IPv6 IP addresses are supported)
• MAC Address: It needs to be specified as a set of 6 numbers (hexa) separated by colon. Each of the number needs to be between x00 (00) and xFF (FF).  Example: 15:FF:01:F1:01:B4.

TIP: While specifying MAC address, putting a colon between the hexa digits is not necessary.  The application will automatically insert colon after every second ‘digit’ (starting from right side).  Example: number entered is FF101B4; Number changed to is 00:00:0F:F1:01:B4.

Linked Filters

A Field could be linked to one or more other fields for fetching available values for filtering.

This is to handle situations like short listing states when a country is selected.

You may need to select parent field, apply filter before selecting a child field to apply filter. The child field is specified as a Link Lookup field linked to parent field at the Query Object level.

Figure 4: Filters as Link Lookup

Selecting Dynamic Dates

When you select a date field to apply filter, you have an option to specify a dynamic date variable – is in last, is in next etc.

This helps to re-run saved reports without having to change the date value to get then current date range applied.

For example,

• Date of hire is in last 10 days from today (report generation date).
• Date of sales transaction is in this Quarter.
• Transaction Date is in last month.
• Date of retirement is in next

For criteria, you may select any of the following:

• is in last
• in this
• is in next

If in this is selected as Criteria, the Value drop down box has following options to choose from:

• Year
• Quarter
• Month
• Week
• Day
• Hour
• Minute

If is in last or is in next is selected in Criteria, specify the number of Day(s), Week(s), Month(s), Quarter(s) or years (as the case may be) in Value entry box.  Explanation for each of the option is given below:

• Year(s): The number of years from the date of report generation.
• Quarter(s): The number of quarters from the date of report generation. A quarter is January to March, April to June, July to September and October to December.
• Month(s): The number of months from the date of report generation.
• Week(s): The number of weeks from the date of report generation. A week is considered from Sunday to Saturday.
• Day(s): The number of days from the date of report generation.
• Hour (s): The number of hours from the date of report generation.
• Minute (s): The number of minutes from the date of report generation.

Applying Grouping

Grouping brings together or summarizes the related data of a report based on the grouping key.

Group key can be arranged in ascending or descending order, based on group key value or a detail field’s summary value.

For example, if you group population details by region, you can arrange regions by name or by highest to lowest population.

Ad hoc reports support multi-level grouping, for example, you can group the report data by country; within country by states and within states by cities.

Figure 5: Selecting Grouping

Group properties

Actions

Item	Comments
Add Group	Add a new group
Remove Group	Remove current group

Date fields grouping

If you select date type field in Field dropdown, you can also group dates by:

• Minute: Number of the minute indicating the minute of an hour
• Hour: Number of the hour indicating the hour of the day
• Day: Day of the month
• Week: Week number of the month
• Month: Month number
• Quarter: Quarter number
• Year: Number indicating the year

Figure 6: Date fields Grouping

Applying Totals

Applying totals summarizes detail rows. The summaries can be applied at group level (in case grouping is applied), page level or report (grand total) level.

Figure 7: Selecting Totals (summary)

Totals properties

On a summarized report (Report Content = Summarized), only the group Keys and these summaries appear, hiding the detail rows.

In an iHTML grid report you can collapse group keys to show only summary and hide details. You can also expand the group keys to show detail rows.

Note: If total is applied on field that is not in display fields, it will be automatically added in the Display Fields list.

Custom-Defined Functions

You can also use custom-defined summary functions for summarizing data values in reports.

These summary functions can be defined in a class implementing IScriptFunction interface. The .jar file of this class should be placed in ReportEngine > lib folder.
There should also be an .xml file containing all the entries of .jar file and placed in ReportEngine > config folder.

Restart Intellicus Report Server and then Web Server to be able to see these summary functions as any other function in the list. The .xml file would look like:

<SUMMARYPROVIDERS>
	<!-- Summary Provider. Provider name is just a user friendly name to identify provider class.
	Class file is fully qualified java class name of the class containing summary functions.
	A summary provider may have as many summary functions as desired -->
     <SUMMARYPROVIDER PROVIDERNAME="PACKAGECAPTION"
		CLASSFILE="com.client.summaryfunctions.MathAlternate">				        <SUMMARYFUNCTIONS>		
			<!-- Summary function that contain logic for applying business logic on the selected field. Name is just a user friendly name that is displayed in the dropdown. Id is a unique number that must be unique and separate from IDs of in-built summary functions.
			Recommended: Start IDs from 1001. APPLYONDATATYPES is a comma-separated list of data types on which this summary function is applicable.
			Possible values are CHAR|NUMBER|DATE. Rest all are ignored  -->
                <SUMMARYFUNCTION NAME="AlternateSum" ID="1001"
				APPLYONDATATYPES="CHAR,NUMBER,DATE">
			</SUMMARYFUNCTION>
			<SUMMARYFUNCTION NAME="ReverseString" ID="1002"
				APPLYONDATATYPES="CHAR">
			</SUMMARYFUNCTION>
			<SUMMARYFUNCTION NAME="ModTen" ID="1003"
				APPLYONDATATYPES="NUMBER">
			</SUMMARYFUNCTION>
			<SUMMARYFUNCTION NAME="FutureDate" ID="1004"
				APPLYONDATATYPES="DATE">
			</SUMMARYFUNCTION>
		 </SUMMARYFUNCTIONS>
	      </SUMMARYPROVIDER>
</SUMMARYPROVIDERS>

Applying Sorting

You can sort the report to get the report data in a pre-determined (ascending or descending) order.

Note: If you have setup grouping for a report, you need not set sorting for it.  This is because data is already sorted to make groups.

Ad hoc Report Designer supports multiple level of sorting.  For example, you can Sort By “Country”; then within “Country”, sort by “State” and within “State” sort by “City”.

If not already expanded, click Sorting tab header to expand it.   The number of sort levels you have selected is displayed on right side of the header.

Figure 8: Specifying Sort Order

In Sort By row, select the Field on which sorting is to be applied.  After selecting the Field, select the sort order from Criteria dropdown menu.  You can set sort on more than one field.

To apply sorting on one field specify field in Sort By row.  To set sorting on another field select the field from Then By field.

You can choose to alter given sorting settings at the run time from Power Viewer.

Applying Highlighting

Highlight is a visual indication on an ad hoc report.  The purpose of highlighting is to catch user’s attention to specific records or groups while viewing the report output. Each highlight is configured along with an ad hoc condition or multiple conditions related by AND/OR operators. The specific record or group is highlighted only if the specified condition is satisfied.

You can also specify Alert with or without highlight. Setting alert creates a special head listing the record count for mentioned condition(s) in the report.

You can set multiple highlights on a report; a record falling into multiple highlights will be highlighted in combination style.

A highlight can be set at Detail level or Report level.  If the report is grouped, highlight can be set at group level too.

Figure 9: Setting highlighting

Highlighting properties

Creating Matrix

Use matrix to summarize your report data in the form of cross-section of fields in rows and columns.  For example, ‘Product Type’ and ‘Product’ in columns; ‘Location’ and ‘State’ in rows.  A cross section of ‘Product Type’, ‘Product’ and ‘Location’, ‘State’ will display sales of that product in that location.

Under Data Source tab, select the data source.

To get only matrix on the report, don’t select any display fields on the Fields tab.  If you select display fields, matrix will be placed below the tabular data.

If not already open, click Matrix tab header to open the Matrix tab.

Figure 10: Creating Matrix

To place a field as matrix row, drag it from Available Fields list and drop it in Row Fields (or, select a field and click  button).  To place a field as matrix column, drag it from Available Fields list and drop it in Column Fields (or, select a field and click  button).

To place a field on summary (intersection of row and column), drag a field from Available Fields list and drop it in Summary Fields (or click   button).

These fields will be calculated for summary/totals.  Functions listed in Function dropdown box will depend on data type of the Summary Field.

You can drop multiple fields in Row Fields, Column Fields and Summary Fields box. Fields appear higher in sequence in Row Fields and Column Fields will appear on outer side of matrix.   Summary Fields are placed left to right on the matrix. To move a field up or down, click  or  button.

Show When opens up filtering criteria to apply on column and row fields. The column/row that meets the condition shows up on the matrix when you run the report.

You can highlight a Cell, Cell Family, or an Entire Row or Column of a matrix based on a condition. The matrix highlights the summary field using the specified Style.

In case of a hyperlinked field (specified at the query object level), you can drill down to open another report or URL on clicking the value of field on matrix.

Grouping values of Numeric fields

You can specify an integer value to group numeric fields.  For example, to have groups of 0-9, 10-19 …  specify 10 in Group By box of respective row in Row Fields or Column Fields box.

Grouping values of Date type fields

You can group a date by Minute, Hour, Day, Week (Sunday to Saturday), Month, Quarter (Jan-Mar, Apr – Jun, Jul – Sep, Oct – Dec), Year.  Select an option from Group By box of respective row in Row Fields or Column Fields box.

Clicking Clear Matrix would clear the specified matrix properties to start all over again.

Designers/Users can link Matrix and Chart so that any changes made in one component gets reflected automatically in the other. Linking can be done in both ways- Matrix to Chart and vice versa.

Figure 11: Link Matrix and Chart

Creating Chart

Chart is used for graphical representation of data.  To address your charting needs ad hoc report supports most of the popular chart types like bar, line, pie and radar.

You can create multiple charts on an Ad hoc report.

Chart section provides + button to add more charts and specify the chart details in the new chart tab.

In the Ad hoc template you can control the number of charts displayed by the size. More charts flow to next row.

In case of a hyperlinked field (specified at the query object level) on either X or Y axis, you can drill down to open another report or URL on clicking the data point on chart.

Figure 12: Creating Chart

Chart properties

Item	Values	Comments
Query Object	Check ‘Use Parent’s’ or select from object selector	Select the parent query object to create chart on same dataset or a different query object to help you create chart on different datasets in the same report
Chart Title	Type yourself	Give a title to your chart
Chart Type	Select from list	Select the base chart type
Link	Select from list:   With Report Fields           Matrix	Report Fields = Take report grouping fields as chart grouping fields and number fields from detail or summary section as chart series fields at run time   Matrix = Take row and column group fields as chart group fields and cell value fields as chart series fields
Clear Chart	Action	Removes all chart settings
Value Fields (Y-Axes)
Value Fields Y-Axes	Tabs Y Y1 Y2 +	Create new tab using + for secondary Y Axis. Drag fields on respective Y Axis tab
Field	Drag from available fields	Each field becomes a Y axis on chart series
Function	Select from list Sum, Avg, Count, Min, Max and others	Value Fields will be aggregated on chart using this function
Series Type	Select from list	Select series level chart type. (Parent) = No series level chart type applied, use base chart type
Trend	Set Trend options	Opens trend dialog
Show Trend Line	Check/Uncheck	Check = Adds a trend line to this series. Trend line will be a line type chart irrespective of base chart type and series chart type   Uncheck = Switch off trend line for this series
Trend Type	Automatic           Manual	Automatic = The tool selects one of the trending algorithms automatically based on the data Manual = You can choose one of the algorithms for drawing trend line: Exponential, Linear, Logarithmic, Polynomial, Power, Moving Average
Trend Manual Polynomial Order	0 – 5	Defines the order of polynomial trend line. The order of the polynomial determines by the number of fluctuations in the curve
Trend Manual Moving Average	0-N	Determines the number of data points to average and use as average value for trending over a specific number of periods
Forecast	Forward Backward	Specify trend line for future or back period of time
Infographic	Select from Color or Clipart	You can select a color for the chart series or else default color would be picked from the system palette. You can also assign a color for negative values i.e. values below a specified’ Negative Base’. ‘Use Negative Color For Decreasing Values’ option enables viewing values following a decreasing trend in the chosen negative color. Choosing a clipart will help you display clipart image (SVG format) as chart patterns. You will have to place your SVG images as files or in folders under <Intellicus installation folder>> ReportEngine > Templates > Charts > Infographics Once you have added the images, you will see the folder names as categories and images will be displayed in the box as thumbnails. Choose the image of your choice and select a pattern (Layout Type). Click OK.
Title	Type yourself	Give a title to Y axis
Stack Type	Select from options: Normal Stacked 100% Stacked	Normal = No stacking Stacked = Stack all series of this Y axis 100% Stack = 100% stack all series of this Y axis
Base Value	Specify a value between 0-N	Specify a base value (scale) of Y axis in the chart
Threshold	Set Threshold options	Opens threshold dialog Threshold Lines help to effectively communicate important points in your data like a key value, sales threshold, important date or the average of your data
Show Threshold Line	Check/Uncheck	Check = Adds a threshold line for this series. Uncheck = Switch off threshold line for this series
Threshold Value	Specify value/range of values	You can either specify a value for Threshold Line or a range of values for Threshold Band
Threshold Label	Enter text	Specify label text to appear for the threshold line or band on the chart
Threshold Color	Select from color selector	Specify the color of Threshold Line or Band
Group Fields (X-Axis)
Field	Drag from available fields	Each field becomes a X axis on chart series
Group By	Select from list: YEAR QUARTER MONTH WEEK DAY HOUR MINUTE	Applicable for Date data type fields
Display Field	Select a field from list	On X axis data label, show the selected fields’ values instead of group field value
Pivot	Check/Uncheck	Check = Convert into series. All the values from this field become series at runtime
Show Axis Title	Check/Uncheck	Check = Give a title by typing it in the box
X–Axis Label Angle	Select from list: 0 30 45 60 90	Rotation or angle of labels on the X axis
Settings
Show Legends	Check/Uncheck	Switch On or Off legends, you can also define the position of Legend from the drop down beside Show Legend option if you Switch On the Legend
Show Title	Check/Uncheck	Switch On or Off title
Show Point Labels	Check/Uncheck	Switch On or Off Data point labels
Show Description	Check/Uncheck	Whatever Description you give in Miscellaneous, will be displayed alongside the chart if you check here
Align	Select from list   Top   Bottom	Position of chart when report has detailed data Top = Show the chart at the top of report Bottom = Show the chart at the bottom of the report
Level	Select from list   Report     Page	Level of data to aggregate for chart Report = All of report data will be aggregated in to one chart per report Page = Data rendered in one page of detail section will be aggregated into a chart per page
Sorting
Sort Order Field	Select field from list	The X Axis values will be sorted based on the value of selected field
Sort Order Order	Ascending Descending	Order of sorting
Show (N)	Select from list (All) 1-50	Restrict number of X axis values to given number
Miscellaneous Description	Click   to open	Type a description to show along with your chart

Custom Chart Type

With the implementation of this feature, you can use your own customized SVG images to be rendered as a chart type. You need to place the custom SVG file containing the visualization rendering information, data representation including animation etc. under <Intellicus Installation Folder Path>\Intellicus\ReportEngine\templates\charts\custom

Intellicus chart control supplies data and activates the SVG to render.

A sample report output using the custom chart type appears as shown in the below image:

Figure 13: Custom Chart using SVG file

SVG (Scalable Vector Graphics) based Visualizations

With Intellicus’ data visualization capabilities, you can let your creativity flow when telling the story of your data unlike ever before. You are no longer tied to bar charts and pie graphs. Now, you can display comprehensive maps, entire shop floors, images of trucks or animals, and virtually any relatable image to the business. This provides responsive, interactive charts that facilitates you to build pictograms and custom-tailored visuals. You can place the SVG template folder (containing SVG image, HTML and CSS files) under <Intellicus installation folder>\Intellicus\ReportEngine\templates\ihtml

The above folder would be available at Administration > Configure > Config Files > iHTML Template Files on portal as shown in Figure 14.

Figure 14: iHTML Template Files

Next, you need to configure the iHTML template. Go to Navigate->Design->Ad hoc Template and choose a template that you want to map the iHTML template with. Select your iHTML template under iHTML item. Click Save to save the configuration.

Figure 15: Ad hoc Template Configuration

You can now use this SVG template to design an ad hoc report. Go to Navigate->Design->Ad hoc Report and select the desired Query Object as a data source.

Figure 16: Selected Query Object with Template

With iHTML as the Report Format, select the template that you had mapped the SVG template with. The SVG Mapping tab is now visible at the top on the Ad hoc toolbar.

Go to the Chart tab to create a chart for your ad hoc report.

Figure 17: Chart in an Ad hoc Report

Under SVG Mapping tab, select a container on the SVG image and link this to the above created chart.

Figure 18: Link SVG Container to Chart

Note: A single chart control can be mapped with a single container only.

You can run the ad hoc report with your SVG template as shown in the image below.

Figure 19: Chart using SVG Template

Auto truncate X-Axis labels in Chart

You can now choose to trim some characters of X axis labels automatically so that the chart gets enough space to render. This can be customized in the respective ICT file to enable or disable the auto truncate feature, truncate either the initial or trailing characters and specify the maximum percentage height for X-axis labels.

Below is a sample ICT file (<Intellicus installation path>\Intellicus\ReportEngine\templates\charts\amchart_javascript\Default) with the specified configurations:

categoryAxis.disableEllipsisInLabels = true;

categoryAxis.ellipsisPosition = “suffix|prefix”;

categoryAxis.maxHeight = “25%”;

Figure 20: Custom Chart using SVG file

Creating GIS Maps

You can add interactive mapping functionality to your reports with vast customization options. It enables to zoom or pan the map, drill-down to other reports or external URLs.

You can create GIS maps on smart reports and achieve the following:

1. Heat maps: A heat map uses shading to display how a value differs in proportion across a geography or region. You need to set light (start) and dark (end) color, so that for the corresponding values for your Value Field, the map will automatically choose intermediate color corresponding to its value. Refer the “GIS section properties” table on page 32 to know more about heat map properties.
2. Attributes on balloon: You can specify how the value of a particular field should appear when you click an area on the map (as shown in Figure 13).
3. Drill down: In case of a hyperlinked field (specified at the query object level), you can drill down to open another report or URL on clicking the area on map.

Figure 21: Creating Map

Attributes Dialog

Area attributes dialog helps you design the content of the balloon that opens when an area on the map is clicked as shown in Figure 14.

Figure 22: Attributes Dialog

GIS section properties