This lesson will cover:
- Ad hoc Reports
For users who thrive on Microsoft Excel, IMPACT! HD 2.0 Ad Hoc provides full support for detail-oriented operational reports needed to keep day-to-day business operations moving. You have the power to slice and dice data with dynamic pivots and analysis grids.
View reports by name and by category.
Each report can be printed by clicking on the print icon or edited from the report preview by clicking on the edit pencil and opening the report designer.
The Report Designer lets you create new reports and modify existing reports.
The Report Viewer lets you easily modify a previously create report and save a variation, print it, export to various formats, and share it through email.
Ad Hoc Tool Bar
The toolbar is where report designers can see how their reports will look, save and create reports, and get technical readings of the report before the report is published for view.
The table below describes the functions of the toolbar buttons. Some buttons are disabled and won’t show up.
The Data Sources tab shows you which Views you have access to from the database.
IMPACT! Ad Hoc report writer leverages the power of views to simplify the data selection process, allowing for easier report creation and less time consumed trying to understand complex data models. Some examples of these views are:
- Debtor related data (Debtor Information View)
- Demographic related data (Debtor Demographics View)
- Account historical data (History Information View)
- Account transactional data (Transaction Information View)
Note: only one data source can be chosen, the joining of data sources is accomplished during the creation of the views.
Views are represented by checkboxes in simple mode and views can be selected via dropdown in the advanced mode:
The Fields tab is where the actual report is built. This tab allows you to define which fields from your Data Source that will be included in your report and any grouping, sorting, and totaling that needs to be performed. There are a multitude of options that are available to users from this tab. Listed below is a brief description of the properties available on the Fields tab.
Field Dropdown Menu
The Field dropdown menu is a list of the available fields in the table(s)/view(s) that are selected on the Data Sources tab.
Automatically inputs the name of the associated Field that you selected. You have the option to change the description and it will appear as the new title when displayed.
Sort, VG, & Arithmetic Check Boxes
Other Buttons on the Fields Tab
The , , and buttons apply to the row that they are displayed in and allow you the flexibility to re-organize your data without creating a whole new report.
Below is a table that describes the function buttons of the Fields tab.
Functions allow you to perform calculations on the data and can be chosen from the dropdown menu based on the data type of the selected Field.
Text (String) Functions Available
Date/Time Functions Available
Numeric Functions Available
Format Dropdown Menu
The Format dropdown menu is a list of formats for the entries of the field selected in the Field dropdown menu directly to the left to appear in. Depending on the Field data type and the function, you will have differing options for formatting. The main two formats used will be available for all numbers and dates.
Numeric Formats Available
Quick Add lets you add multiple fields to the report at one time by selecting from a list:
Advanced Field Settings Button
Advanced field settings provide more granular control over what data is displayed in your report. They can be accessed by clicking on the gear icon to the right of any row on the fields tab or the summary tab in the report designer. There are a number of options that can be selected here.
Add Pivot lets you generate a pivot attached to the right side of the tabular report. This pivot is always on the right side of the fields tab – nothing is further to the right of the pivot. You may add one and only one pivot on the fields tab, meaning that you may have one and only one pivot per report.
In the Summary tab, a table can be created that summarizes the chosen fields using different functions (Ex: Average, Count, Maximum, and so on). You can also make a group of fields by choosing the Group option from the Function dropdown menu.
Most actions and properties available in the Summary Tab are available in the Fields Tab. The only difference is the removal of the Quick Add and Pivot capabilities, and addition of the Add Deltas checkbox.
The word delta means "a change in". Any time there is a change in a quantity, that change is calculated by taking the latter value of that quantity and subtracting from it the prior value of that quantity.
This can be used in a summary report by checking "Add Deltas" checkbox.
Result: The Deltas, denoted with parenthesis, show the change in the Referral Amount over a period of time.
In the Chart tab, a chart can be created using the data in the view selected on the Data Sources tab.
The Charts Tab lets you create different variations of charts, including:
You can have multiple charts in one report.
Each chart tab hosts a single chart object. Each chart tab behaves identically, and is used to host a single distinct chart.
Basic Charts each have a custom GUI Charts generally follow the pattern of Label -> Value -> Seperator, where:
- Label: Represents the slice, bar, time point, 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.
NOTE: Values MUST be aggregated numeric output! It is possible to display simple static numeric values such as 'value of user ID per user’ but the best practice is to only display aggregated data, such as a sum of all received amount or a count of all debtors, 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 debtor demographic, a line for each state over time, or a bar chart with multiple bars per department, one for each company.
When the chart type is None (…), no chart appears. If you want to remove a chart from a report, choose this option. This option is selected by default.
Selecting a Trend chart type will generate a line graph with a number of different options to choose from on your report. There are two modes to select various options from: a simple view and an advanced view.
Basic Trend Chart
Note: Some options may not be available with certain charting engines.
The table below describes the features of the Trend chart type selection.
Basic Bar Chart Tab
Advanced Bar Chart Tab
Note: Some options may not be available with certain charting engines.
The table below describes the features of the Bar chart type selection.
Basic Pie Chart Tab
Advanced Pie Chart Tab
Note: Some options may not be available with certain charting engines.
The table below describes the features of the Pie chart type selection.
In the Gauge tab, you can add a panel of gauges to a report.
There are five different types of gauges you can create:
The gauges also have a number of options that can be selected. They are described below:
Misc (Miscellaneous Tab)
The Miscellaneous (Misc) tab allows you to add a title, description, header, and footer to the report. It also allows justification to be used on each of these items. This is also where the sharing and Scheduling controls are located as well as tools to create a drill-down.
There are two types of headers - Report and Page. The Report header shows up once, at the beginning of the report. This header is visible in the report viewer.
The Page header shows up at the top of every printed page. This header is not visible in the report viewer, since the concept of printable 'pages' does not apply to the report until it is rendered for print.
Header and footer controls can accept HTML to create custom content.
Share With & Rights
The Share With dropdown allows you to share your report with:
- Everyone - Share this report with everyone who is listed in the dropdown
- Users with specific roles - Share this report with only users who have specific roles (e.g. marketing)
- Specific users - Share this report with specific users (e.g. bob)
The Rights dropdown allows you to give the users you are choosing to share the report with different levels of access. The levels of access you can grant are as follows:
- ...- No rights are applied to this report.
- None- The report is unavailable to (hidden from) the users.
- Full access- The report is available for users to view, modify, and save changes to.
- Read only - The report is available for users to view, add or remove filters, and modify the existing filters' values. Users may also modify(design) the report but cannot overwrite the existing report. They may, however, save it as a new report.
- View only- The report is available to the user to view, but not to modify in any way or save changes to.
- Locked (v6.4+)- The report is available to users to view, and users can modify existing filters' values, but users cannot add or remove filters or fields, and they cannot save changes to the report. Users may also not change the column or operator of the filters.
Note: Access privileges will only apply to the user or group selected in the Share With dropdown. All other users will have an access level of None.
Note2: The owner will not change when a user other than the owner saves the report. Choosing "Save As" will create a new report and its owner will be the user that saved the report.
Note3: The "Rights" dropdown menu is hard-coded into the ad hoc and is designed to cover all possible scenarios concerning user access to reports. Therefore these values cannot be altered, but you can specify which one is selected by default.
The only difference between a main report and a sub-report is the drill-down key field. Setting this field will enable a report to be used by another report as a sub-report. There is no other difference between a report and a sub-report. Also, a sub-report can have sub-reports of its own. So let's start by creating a report in the Report Designer.
Designing the Sub-Report
Setting the Drill-Down Key
We have a report with three fields created. Now we will set the drill-down key. A drill-down key is simply a field which the sub-report looks through to match values when it is passed a value by some other report. The Ad Hoc does not check to see if the value passed is valid or logical, it simply looks through the drill-down key field to see if there are any matches. If there are matches, it returns records associated with that match as if we ran a filter which equals that value. If there are no matches, it returns a page which states "No Results".
We can either set one drilldown key or both of them. A sub-report with one key set will only search through the first drill-down key field for a match. A sub-report with both drill-down key fields set will search through the first (top) field, obtain a set of matches, and then search those matches for values in the second drill-down key field. This means that if the first field returns no matches at all, the second field will have no valid records to search through.
We can also select whether we want to ignore the first key and just use the second key. This means that we will pass the first value to the sub report, but then the sub-report ignores this value and immediately searches through the second drill-down key field as if the first drill-down key field returned ALL results. The main report will still require both keys to be passed, which we will cover below as well. As an example, if you have a field you do not wish to expose to users, such as an ID field, then you can specify that the drilldown link appears on a name field (such as Client Name) and pass the Client Code field to the sub-report without ever exposing it to the user.
For now, we will set the drill-down keys to Client Code and Client Name 1 and we will not ignore Client Code.
Note: Earlier versions of Ad Hoc reports only allow one drilldown key to be used.
Designing the Main Report
Once the drill-down key is selected on the sub-report, you will need to design a main report to use it. In order to use the sub-report we made, we must select the fields corresponding to the drill-down key fields. You may hide the field corresponding to the second key value and the value will still be respected.
Setting the Sub-Report
You will then want to click on the Advanced Field Settings button for the fields you designated as the drill-down keys in your sub-report. I.e. if you made Client Code and Client Name 1 your drill-down keys, you will want to apply advanced settings for both of those fields on your main report.
On the first field you selected as a drill-down key, you should set the sub report and drilldown style properties. For the second field, you will select the sub report and the drilldown style "Combo key". This is a special drilldown style which lets this field know it is part of a combination, and therefore it should follow along with the most previous field that is also pointed at the same sub report.
This is the advanced properties form for the Client Code key.
This is the advanced properties form for the Client Name 1 key.
Here is what the main report can look like with the drilldown fields selected.
You may now interact with your main report to view your sub-report. When you click on Client Code, you will notice that the sub-report filters data by both Client Code and Client Name. If you were to use data in a many-to-many relationship, only data that satisfied those two conditions would be shown.
Now when you click on the Client Code column, the result will be filtered by Client Code and Client Name. In the result below, Client Code 000286 was clicked.
Your application will need to be properly configured before the scheduling controls appear on the page. If you do not see them, please contact CSS Support.
There are two types of time period in the Repeat Type: Every TimePeriod and Alert TimePeriod. Every will run the report every specified time period, and produce an export even if there are no results to display. This means that you may get a scheduled report email with no results, if there are no results to be displayed. Alert will run the report but only email output to specified recipients if there are results - a report which returns 0 records will not send anything to recipients.
The style tab is where you can customize the look and feel of your report. You can also change the color of the border, header, and rows, as well as the order of the report items. We will go over the controls in detail here and how you can use them to maximize the effectiveness of your reports.
There are six separate controls that you can use to select the colors used for various elements on your report. You can see the results of your changes in the sample grid that is displayed directly below the dropdown menus. The colors that can be changed are: Border color, Header color, Header foreground color, Item color, Item foreground color, and Alternating item color.
Explanations of these features are given below:
CSS & Printing Controls
These controls will allow you to control the style of your reports on a granular scale if need be and also to apply various visual elements to printed reports.
Report Viewing Style
These controls will allow you to control the visual flow of the elements in your report as they appear on the report viewer.
Visual Group Styles
Using visual groups, you can change how reports display grouped data. There are a number of different options to choose from. We will provide an example of each of them and an in-depth look at the last two.
Most of the styles you can choose from will put the VG grouped fields at the top of the report and the non-grouped fields will then be displayed in a normal grid. The bottom two options are the exception to this and provide users with more control over what data they are viewing by allowing them to click on the (+)/(-) symbols. This will expand or collapse the container for each level of data.
The analysis grid VG style will initially start with every expandable row collapsed. When you click on the (+) next to the value, it will expand that section of the analysis grid and display the data inside. You can have multiple levels nested inside each container and the system will automatically calculate the totals for numeric fields inside the non-visual-grouped fields.
Below is an example of a 3-level
The white area contains all the columns that were not checked as "VG" in the report designer. Here, the columns were the count of the clients for that visual group, how many total clients are on the company, and the client name of each item. You can modify the behavior of the VG aggregate functions on the Advanced box of the fields tab by changing the subtotal function.
Note: Analysis Grid has some requirements.
- You must have at least two fields selected as Visual Groups - Analysis Grid constructs a hierarchy, and you cannot have a hierarchy of one.
- You must have at least one field selected as an aggregate. Once a hierarchy has been built, the grid must have something to display.
- Best practice is for all of the visually grouped fields to be placed at the top of the field order stack.
The VG hierarchy style will create a flat hierarchical representation of the data using the visual group fields as containers. But instead of displaying each level of the hierarchy below the container field's value, it displays it to the right. It also displays all levels expanded by default instead of collapsed by default. You can expand or collapse containers like with the analysis grid.
If you attempt to expand a level without also expanding the one before it, nothing will happen. But if you then click the level before it, then all the data within the child level will be expanded as well. If you click the parent level first, then all the child levels will retain the expanded/collapsed state they had when you closed the parent level. Below is an example of the VG hierarchy.
Filter Field Drop Down Menu
The Filter Field dropdown menu is a list of the available fields in the table/view that have been selected or created in the Data Sources tab, simply select the field you want to filter on from the dropdown menu. The figure below demonstrates what the filter field dropdown looks like.
Filter Operator Drop Down Menu and Value(s)
The Filter Operator Drop-down lets you select your operator based on the data type that has been selected in the filter. The filter fields available are only those under the data sources selected in the Data Sources tab.
Blank and Param Checkboxes
The "Blank" and "Param" checkboxes (see image above) allow you to control the filtering behavior in the report viewer. They do not affect the “Preview” tab of the report designer.
Blank Checkbox: To use this, first set a “Filter Field” and then set an “Operator”. A value can be entered, but it is not required. If “Blank” is checked, the filter will return data that matches the “Value(s)” dropdown/textbox and also data that has a blank/null value in that field.
Param Checkbox: To use this, first set a “Filter Field”, set an “Operator”, and then set “Value(s). This will display the filtered report in the report viewer and allow the user to change the filter value. If it is not checked, then the filter will not be visible to the end-user and the end-user will not be able to change the filter.
Other Buttons on the Filters Tab
Other Filter Features
The system allows you to have an arbitrary number of filters on a new report. These filters can even be applied with custom logic that you define. This is done with the advanced logic filter.
The text box on the Filters tab labeled "Filter Logic" is where you configure this logic. By default, there is an example filter already populated into the box to guide users in the format they should use.
Changing the contents of this box will cause the Ad Hoc to attempt to apply this logic to the filters on the report when "Preview" is clicked or when the Report Viewer is accessed for this report. Using the example, "(1 OR 2) AND (3 OR 4)", results will be filtered conditionally based on meeting either Filter 1 or Filter 2 in addition to meeting either Filter 3 or Filter 4. You can construct extremely simple to extremely complex logic using this control. If you have a mere two filters, a simple "1 OR 2" will force your results to meet either the first or the second filter condition. Alternatively, you can nest parenthesis and combine logical operators to form sophisticated advanced filters.
Require [blank] Parameters in Viewer
The example below requires the user to select at least two parameters before the report will run in the Report Viewer.
Alias Filter Field Names
The filter field name can be aliased using the Description field. This can be accessed in the Report designer from the filter tab or from the viewer by clicking the gear icon in the filter.
Here the Description can be entered in the Report Designer's Filter Tab.
The preview tab is where report designers will see how their report is generated and what it will appear like visually in the report viewer before publishing it.
In the Preview tab (see image below), a preliminary version of the created table can be viewed, along with its Summary table, Chart(s), Map, and Gauges. This will also display any other properties that have been set. You may go back and change selections in the previous tabs and see how they affect the table by returning to the Preview tab.
Note: As of version 6.6, you can interrupt the loading operation for the preview tab, simply click the Cancel button when the loading overlay appears.
- HD 2.0 Documentions may be accessed at: http://help.cssimpact.com