Module Navigation

Designing Your Custom Report

The bulk of report design occurs under the Design menu item, and there are many options here. Note the layout of this screen and some of the key sections. Basically, choose one or more Parts for the report in the Report Body. A Part can be a Chart, Form, Gauge, Grid, or KPI. Choose fields to include in each Part from the Selected Data Source section. Use the tabs on the far right to define Field Properties, Report Part Properties, and Format Properties. Add Filters if desired. Each of these basic steps is described in detail below.

image73.png

  1. Main menu items
  2. Choices of fields to add, including calculated fields
  3. Filter definition and Report Body sections which can be collapsed or expanded
  4. Field Properties and Report Part Property tabs

The far left of the screen displays the menu icons for report design. The Design menu icon is highlighted in blue to indicate this is the screen currently being utilized.

image74.png

Report Body/Report Parts

The Report Body is comprised of one or more Report Parts. Expand the Report Body section by clicking the small arrow located to the left of the description. You must include at least one Report Part. Click on the desired Report Part type to add the first Report Part.

image75.png

The different report part types available are Chart, Form, Gauge, Grid, and KPI. Each of these is described later in this document. A Grid type is the most used. Although each type is different, there are many similarities in their designs.

Snap to Grid - When the Snap to Grid checkbox is checked, each report part aligns itself right beside or below the last report part that was added. If you have already put the report parts right beside or below the last report part you created manually, you will not see any difference.

image76.png

When a Report Part is added, options appear to define the Report Part Properties. Notice the two tabs on the far right, Report Part Properties, and Field Properties.

image77.png

Hover your cursor in the middle of the screen, at the top of every report part, to display a dark grey bar.

image78.png

Options in the report part grey bar include:

  • Field for part name - The default name for a Grid type is Grid. If you add a second Grid report type, the default name will be Grid 2. If desired, change the name of the report part in the middle of the dark grey bar, which displays when you hover at the top of a Report Type. This becomes important for future design edits. You need to select the report part name from the top dropdown field located in the Report Part Properties tab to make edits.

    image79.png

  • Number of RecordsUse this field to limit the number of records included in the report. For example, use this feature to include only the top five values in the report.
  • Copy icon image80.png - Click this icon to copy this design of this Report Part to the clipboard.
  • Full Screen icon image81.png – Click this icon to enlarge the details for this Report Part.
  • Preview Mode icon image82.png – Click this icon to preview what a viewer sees for just this Report Part.
  • Delete report part icon image83.png - Click this icon to remove this Report Part from the report.

Tip – To view the grey bar, remember to hover at the top of the Report Part. If the Report Part is wide, you may need to use the horizontal scroll bar to see the icon options located on the right side of the bar.

image84.png

Add Report Part

Many reports contain just one report part. However, reports can contain more than one. For example, a report with one grid and two charts has three report parts.

To add more report parts, click Add Report Part located at the top of the Design window. When a report contains multiple report parts, they appear in the top dropdown in the Report Part Properties Tab. Use this dropdown to select an existing report part to edit or delete.

image85.png

Delete Report Part

To delete a Report Part, in Design:

  • Click the Report Part Properties tab
  • Select the report part name from the first dropdown at the top of the Report Part Properties tab
  • Hover over the top of the report part located in the middle of the screen to display the dark grey bar.
  • Click the delete icon

Tip – You may need to use the scroll bars to locate the desired report part.

image86.png

Report Part Properties

Each report part has a window where multiple properties of that report part can be defined and edited. To access the window, click the vertical tab located on the far-right side of the Design window. Click the dropdown at the top of the window to select the desired report part. Depending on the type of report part selected, the available properties may vary slightly. The example used below is a Grid report type.

image87.png

  • General – Select a Grid Style and Separator Style from dropdowns.

    image88.png

  • Table – Define a Border, Background Color and Alternative Background.

    image89.png

  • Columns – Define Width, Alignment and Word Wrap.

    image90.png

  • Headers – Define header formats

    image91.png

  • Grouping – Check or un-check whether to use separators.

    image92.png

  • View – Select options related to Data Refresh, Conditional Formatting, Pagination and Columns per Exported Page.

    image93.png

  • Printing – Check whether to include a Page Break After Separator when report is printed.

    image94.png

Report Part Types

The different Report Part types available to be added to a report or template are:

Grid

This is the most used report part. The types of grids available are Vertical, Horizontal, Pivot, and Drilldown. The most used grid type is Vertical, where you select fields from a database table to include in columns, and the grid contains rows of data.

image95.png

Chart

Include data in a visual, graphical format. The types of charts available are: Line, Column, Bar, Area, Pie, Funnel, Donut, and Combination.

LMS_-_AA_-_Charts_-_00.png

Form

Use a form to include HTML-designed content in a report.

image104.pngimage105.png

Gauge

Include data in a visual, graphical format. The types of gauges available are Linear Gauge, Simple Gauge, and Solid Gauge.

LMS_-_AA_-_Gauge_-_00.png

KPI - Key Performance Indicator

A KPI is a grid-oriented report part that displays a combination of simple metrics, images, and text in a more flexible layout. Users will be able to dynamically place various tiles along a grid for the organization.

Configuring a KPI includes three areas:

  • Field Properties - Used to manipulate the field-based data being displayed in the KPI
  • Tile Properties - Used to alter/edit the values for text and image tiles within the KPI
  • Report Part Properties - Used for configuring the structure of the KPI layout and setting a background image

image109.png

Fields

Adding Fields

Add fields from the Selected Data Sources to report parts. Exactly how and where the field is added depends upon the type of report part to which it is added. For example, in a grid type report part, add one or more fields into the Columns area. To add, either drag and drop or click the small plus icon, located to the right of the Columns area, and check the desired fields.

LMS_-_AA_-_Adding_Fields_-_00.png

Other Report Part types have similar areas which also state Drag or Add a Field.

Being familiar with the tables and fields available is helpful when designing reports. A separate document describes these relationships.

The available fields for a report are those in the selected tables and any calculated field.

Field Properties

Every field added to a report part has a window for field properties. To access the window, click the Field Properties tab found on the far-right.

image112.png

View and change each field’s properties by selecting the report part where it is included and then the name of the field. Report part and field name are available in dropdown boxes.

image113.png

Expand the section where the desired change is located:

Options available in each section are described below.

Data Source

This section provides information about the field. Users can change the Field Name Alias and may check or un-check the box to make the field Visible.

image114.png

Data Formatting

This section provides information about the field. Users can change the Field Name Alias and may check or un-check the box to make the field Visible.

image115.png

Header Formatting

Expand and utilize this section to format the field’s header.

image116.png

Drill Down

This option allows a report designer to link a report to a field. This sub-report will open when the viewer double-clicks on this field. Click the Sub Report Settings icon, which is shaped like a gear, to open the Subreport Settings window. Select the sub-report in the Reports dropdown. Be sure the sub report contains the field that is being drilled on.

LMS_-_AA_-_Drill_down.png

Calculated Fields

There are two main steps to including a calculated field in a report. First, design the calculated field. Second, add the calculated to the report as you would a database field. Calculated fields are designed for each report or template. Calculated fields have a limitation for nesting up to 6 levels.

Tip - If the same calculated field is desired in multiple reports, it must be designed for each report.

Add & Design a Calculated Field

  1. Navigate to the Design window of a report or template.

    image119.png

  2. Click Add Calculated Field.

    image120.png

  3. In the Add Calculated Field pop-up window, complete the fields.
    • Column Name – Type any name desired.
    • Expression – Click the light bulb icon to assist in creating valid syntax. Two tabs are available in the pop-up: Field Names and Functions/Operations. Clicking on a field name or function adds it to the Expression

      image121.png

      Expression syntax looks complicated; however, design is greatly assisted with the Expression pop-up.
      image122.png

    • Data Type – Select a data type from the dropdown.

      image123.png

    • Preview Result – Click the Preview button to view what the field values will look like in the report.

      image124.png

  4. Once calculated fields are created, they appear at the bottom of the list of available fields. Add the field as you would add a field from a table.

    image125.png

Delete a Calculated Field

Click the delete icon (shaped like an X) located to the right of the calculated field name.

image126.png

Available Calculated Field Expressions

Although expressions can be fully typed, the easiest way to access field expressions is to click on the light bulb icon located within the Add Calculated Field pop-up window.

image127.png

Expand to view a list of calculated field expressions
Function Name Syntax
expression expression
+ expression + expression
- expression - expression
/ expression / expression
expression < expression
<= expression <= expression
<>  expression <> expression
= expression = expression
expression > expression
>= expression >= expression
AND boolean_expression AND boolean_expression
AVG AVG (expression)
BETWEEN BETWEEN (test_expression, begin_expression, end_expression)
CASE WHEN…THEN…ELSE…END CASE WHEN (boolean_expression) THEN (result_expression) […n] [ELSE (else_result_expression)] END
CASE…WHEN…THEN…ELSE…END CASE (input_expression) WHEN (when_expression) THEN (result_expression) […n] [ELSE (else_result_expression)] END
CAST…AS CAST (expression AS data_type)
CONCAT CONCAT (expression, expression[,expression…])
CONVERT CONVERT (data_type [( length)], expression[, style])
COUNT COUNT (expression)
DATEADD DATEADD (datepart, number, expression)
DATEDIFF DATEDIFF (datepart, startdate, enddate)
DATEPART DATEPART (datepart, date)
DISTINCT DISTINCT (column) or DISTINCT column
GETDATE GETDATE ()
IF…THEN…ELSE…END IF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END
IIF IIF (boolean_expression, true_expression, [false_expression])
ISNULL ISNULL (check_expression, replacement_value)
LEN LEN (expression)
MAX MAX (expression)
MIN MIN (expression)
OR boolean_expression OR boolean_expression
ROUND ROUND (expression, length[, function])
RUNNING AVG RUNNINGAVG (column)
RUNNING COUNT RUNNINGCOUNT (column)
RUNNING SUM RUNNINGSUM (column)
SUM SUM (expression)

Separators

When a field is added to the separator section of a report part, the report groups together all records which have the same value in the selected field(s). Viewers can expand or collapse these groups. For example, a report which uses Department as a separator will group together records of users from the accounting department, HR department, IT department, etc. Report part types that allow Separators are: Grid, Chart, and Gauge.

image128.png

image129.png

Filters

Apply Filter

Filters are included in report designs to limit data included in a report to just those records which match the selected criteria. The designer uses the Filter area to define a filter and select default filter values. A viewer may or may not be able to change filter values. For a viewer to use a filter, the report designer must include the filter in the report design and check the box to Show Filters.

image130.png

To add a filter to a report design:

  • Navigate to Reports > Report List, located in the upper-left corner of the screen.
    image131.png
  • Click on the name of the report design. Select whether the desired design is in Reports or Templates to locate the design.
    image132.png
  • Click Edit and click Design.

    image133.png

  • In the design window, expand the Filter area, located in the middle of the Design window, by clicking the arrow icon located to the left of Filter.

    image134.png

  • Click the blue plus icon to add a filter.

    image135.png

  • The Filter Selection window opens, listing the fields included in the report design. Check the boxes next to the fields you wish to filter on. Click OK.

    image136.png

  • Notice the selected fields now appear in the Filter.

    image137.png

  • If desired, select filter values for one or more fields by clicking on the edit icon, which is shaped like a pencil and located to the right of each field name in the filter area. Click Single or Multiple, to define if multiple values can be included in the filter. Click the dropdown and select one or more default filter values.

    image138.png

  • If desired, enter AND/OR logic into the Filer Logic area to specify how filters are applied in the report. Click the Validate Syntax button to check for logic errors.

    image139.png

  • Check the Show Filters checkbox if you want viewers to be able to change filter values. Click Apply Filter.

    image140.png

TipApply Filter an Clear Filter buttons will appear as icons only if the browser window is too narrow.

image141.png

Clear Filter

To clear a filter in a report design:

  • Navigate to Reports > Report List, located in the upper-left corner of the screen.

    image131.png

  • Click on the name of the report design. Select whether the desired design is in Reports or a Templates to locate the design.

    image132.png

  • Click Edit and click Design.

    image133.png

  • In the design window, expand the Filter area, located in the middle of the Design window, by clicking the arrow icon located to the left of Filter.image134.png
  • Click Clear Filter.

    image142.png

TipApply Filter an Clear Filter buttons will appear as icons only if the browser window is too narrow.

image141.png

Cross Filtering

Utilize Cross Filtering to add drill down into charts and gauges. To utilize Cross Filtering, you must have at least two report parts linked together. This is accomplished in the Report Part Selection window (see below). Charts and gauges utilizing cross filtering must have more than one field in the x-axis, and the order the fields are shown in the design determines the drill order.

View Cross Filtering - Example

In this example, Chart and Grid display aggregated data for Location and Job Description fields. When using cross filtering, it is likely you will want to select All in the Preview Records dropdown.

image143.png

Next in this example, we double clicked on the Alexandria location bar in the chart. Notice we have drilled down in the chart and now see all the job positions held by users in Alexandria. Also, the filter shows the Alexandria breadcrumb and the grid contains only those users in Alexandria,

image144.png

Next in this example, we double-clicked on the Carpenter job title, and now view the four users that hold that position in Alexandria. The Filter breadcrumb displays Alexandria|Carpenter. Click on any portion of the breadcrumb to drill up,

image145.png

Design Cross Filtering

  1. Navigate to the Design window of a report.
  2. Design report parts. Include at least two parts of which at least one is a chart or gauge (where cross-filtering drill down will be available). Be sure to include two or more fields in the x-axis of these charts and gauges.
  3. Set up report parts to have common data source fields. In this example Chart and Grid display aggregated data for Name, Location and Job Title. This allows drill down first into different locations, and then into different job descriptions, ending with individual user names.

    image146.pngimage147.png

  4. Click the Cross Filtering icon, located at the top of the Design window, and click OK.

    image148.png

  5. Select related report parts to apply cross filtering. In this example Chart and Grid will be drilled up and down together.

    image149.png

  6. View report and drill down on one report part by clicking on a data point in the chart.

    image144.png

  7. The related report parts are filtered automatically and the Cross Filtering breadcrumb tells which report part is being drilled into.
  8. The related report parts are filtered automatically and the Cross Filtering breadcrumb tells which report part is being drilled into.

    image150.png

Remove Cross Filtering

Click the small red x icon, located next to the Cross Filtering option.

image151.png

Edit Report

To edit a report:

  • Navigate to Reports > Report List, located in the upper-left corner of the screen.

    image131.png

  • Select whether the desired design is in Reports or Templates to locate the design.

    image132.png

  • There are two ways to access the Design menu item. Navigate to Reports>Report List and click Edit and click Design.

    image152.png

  • Another way to access the Design menu item is to open and view the report by clicking on the report title in Reports > Report List, then click Edit and click Design.

    image133.png

  • Depending on the desired changes, select the appropriate tab: Field Properties, Report Part Properties, Format Properties.

    LMS_-_AA_-_Edit_Report.png

  • Based on the tab selected, choose the field, report part, or format item from the top dropdown.
  • Make desired changes to fields as described in the Design section of this document.

Tip – You may need to use the scroll bars to locate the desired report part.

Quick Edit

This menu option allows you to make changes to Field Properties, Report Part Properties and Format Properties while viewing the report.

image156.png

Access the Quick Edit menu option when viewing a report:

image157.png

Quick Edit menu option when viewing report details in Reports > Report List:

image158.png

Copy

Use this menu option to make a copy of the report design. Supply a Report Name and select Category and Subcategory if desired. Click OK and view the copy in Reports > Report List.

image159.png

Access the Copy menu option when viewing a report:

image160.png

Access the Copy menu option when viewing report details in Reports > Report List:

image161.png

Move

Use this option to change the category under which this report or template is listed in Reports > Report List. The option is not available on global reports, only reports located in Local Categories can be moved.

image162.png

Access the Move menu option when viewing a report:

image163.png

View History

Use this option to review the edit history for the report.

image164.png

Access the View History menu option when viewing a report:

image165.png

 Next Article

The next article is Formatting your Custom Designed Report

Was this article helpful?
0 out of 0 found this helpful