Web Adhoc Query and Reporting Wizard

Go Up to Solution Browser

ER Tools > Shared Reports | click New Report under User Actions (social users)
Shared Reports | click New Report under User Actions (non-social users)
Home | click Create a Report (non-social users)

The Web Adhoc Query and Reporting (WAQR) wizard is a report generation tool designed to help you easily generate ad hoc business model reports using the Pentaho BI Platform. This wizard steps you through the creation of a business model report. You can select a business model type, apply a pre-set template, select the items you want to include, set constraints, calculations, and sorting filters, and configure the formatting.

Note: The only requirement for a valid report is to have at least one column in the Details section of the Step 2: Make Selections page. When you add an item to the Details section, the Go, Save, and Save As buttons are enabled.

Wizard Pages

To navigate the pages of the wizard, use the Next and Back buttons in the Navigation Bar.

Alternatively, you can navigate the pages in the wizard using the big numbers in the header of the wizard: 1, 2, 3, 4.

Step 1: Select a Business Model

In this page:

1. Under Select a Business Model, select a business model.

   When you select a model, the associated details and descriptions appear in the panels to the right of the business models list.

2. Under Apply a Template, select a report template.

   A thumbnail of the selected template appears when you select a template. The template provides predefined content, colors, font, font size, text color, and alignment for the report header and footer.

3. Click Next to continue.

Step 2: Make Selections

This page provides two columns:

• Available Items. This column contains any item available in the business model that you selected in the previous page.
• Selected Items. This column contains the following sections:
  • Groups. Items here determine how the report data is grouped. You may use up to five levels of grouping.
  • Details. Items here determine what data to show in each column of the report.
  • Filters. Filters provide a mechanism for identifying columns that you can apply constraints to, but that do not appear in the report. A constraint limits the set of values returned for the constrained column. You can apply constraints to filters in the next step.

To add data to your report, add items from the Available Items column to any section in the Selected Items column. See Selecting Items.

The Distinct Reports checkbox is enabled by default and stops the generation of duplicates in your final report.

Click Next to continue to the third step.

Selecting Items

To add items to one of the sections in the Selected Items column, do either of the following:

• Use the arrow icons:
  1. Click an item in Available Items to select it. Hold the Ctrl key while clicking to select more than one item.
  2. Click the button to the left-hand side of Groups, Details or Filters to add the selected items to the target section.

     Note: Using this method you can only add items to the first level in Groups. Use drag and drop to add items to other grouping levels.

• Use drag and drop:
  1. Click and hold over an item in Available Items.

     Note: You cannot drag more than one item at once.

  2. Drag the item to the target section. The background color under the cursor must turn green.

     Note: To be able to add items to a grouping level, in Groups, there must be items in any previous level. For example, to add an item to Level 3, first there must be items both in Level 1 and Level 2.

  3. Release the mouse button to add the item to the target section.

After you add an item to the Selected Items column, that item appears italicized in the Available Items column.

Reordering Selected Items

To reorder the items in any section of the Selected Items column, do either of the following:

• Use the arrow icons:
  1. Click an item to select it. Hold the Ctrl key while clicking to select more than one item.
  2. Click any of the arrow keys at the top of the current section:
     • Click to move the selected items one slot up.
     • Click to move the selected items one slot down.
• Use drag-and-drop:
  1. Click and hold over an item.

     Note: You cannot drag more than one item at once.

  2. Drag the item up or down, to the desired position. The background color under the cursor must turn green, and a straight black line is displayed either over or under the item that is currently under the cursor, indicating the position where the dragged item would be placed if dropped.
  3. Release the mouse button to relocate the dragged item onto the target position.

You can also use drag-and-drop to move items between the sections in the Selected Items column.

Removing Selected Items

To remove the items in any section of the Selected Items column:

1. Click the target item to select it. Hold the Ctrl key while clicking to select more than one item.
2. Click at the top of the current section to remove the selected items.

Step 3: Customize Selections

In this page you can set constraints, configure grouping levels, customize the items appearance, or choose sorting options. Your selected items are shown on the left side of the page.

Setting Constraints

Constraints allow you to filter what information appears in the report.

To set constraints for an item:

1. Select an item from the Groups, the Details or the Filters box, in the bottom-left corner of the page.
2. Under Constraints, click Add a Constraint to add a new row to the Constraints list for the target item.
3. In the combo box of the new constraint row, choose one of the following constraint types:
   • exactly matches
   • contains
   • ends with
   • begins with
   • does not contain
4. In the input field to the right-hand side of the combo box, enter a value to compare the target item data with(using the constraint type selected in the combo box).

Configuring Grouping Levels

You can customize how you want each grouping level to appear in the report.

To configure a grouping level, click the target level to select it. The following options appear:

Item	Description
Level Name	Display name for the selected level.
Repeat Group Header	Repeats the group header.
Show Group Summary	Shows the group summary.
Group Summary Label	If you select the Show Group Summary option, you can enter here the text of the group summary.
Page Break	Determines where to insert a page break relative to the selected group. You can choose any of the following options: None (default). Does not insert a page break. After Group. Inserts a page break after the selected group. Before Group. Inserts a page break before the selected group, so that the group always starts a new page.
Alignment	Determines the alignment of the group. You can choose any of the following options: Default. Uses the default alignment. Top. Aligns to the top. Middle. Centers vertically. Bottom. Aligns to the bottom.

Configuring Items Formatting and Calculations

You can set the formatting and the calculations of the selected items.

To configure the formatting and the calculations of an item, click the target item in either the Groups or the Details box to select it. The following options appear:

Item	Description
Numeric Format (only numeric items)	This list box provides a set of different numeric formats. Click a format from the list to select it, so that the current item is displayed in the report with the selected format.
Alignment	Alignment of the selected item in the report. You can choose any of the following options: Default. Uses the default alignment. Left. Aligns the item value to the left-hand side. Center. Centers the item value horizontally. Right. Aligns the item value to the right-hand side.
Use Function (only items from the Details box)	This list box provides a set of functions that you can apply to the selected item value, to process it. When you use one of these functions, for each grouping level you get a single row, which displays the result of applying the selected function to all the records within the grouping level. You can choose any of the following functions: None (default). Uses the item value as it comes. Count. Displays the total amount of records returned by the query. Sum. Displays the sum of the value of each record in this field. Average. Calculates the average value that records have in this field (Sum/Count). Min. Displays the minimum value that a record has in this field. Max. Displays the maximum value that a record has in this field. Non-numeric items provide only the following values: "None", "Count". For example, if you have no grouping levels and you have a single item in Details called "Entity Key": If you select "None", the report displays a list of records with a single column, "Entity Key". Each row in the report displays its own value for the "Entity Key" field. Lets say the previous example returns 10 rows. If you select "Count", the report displays a single column, "Entity Key", with the number of records as value: 10.

Configuring Sorting

You can configure how items in the Groups box or items in the Details box are sorted in the report.

When you select an item of either one of those boxes, a Sort Columns section appears with a table of items, the sorting table, where the item name is displayed in the Column column. For each entry in that table, you can change the value in the Asc/Desc column to choose how the target item should be used for sorting, either in ascending order or in descending order.

Items that come first in the sorting table are used first for sorting. Only when two records have the same value in the record field that is the first item in the sorting table, does the next item in the sorting table count for sorting. For example, if the sorting column has two entries, "Key Type" and "Primary Key", the records of the report are sorted by "Key Type", and only when two records have the same "Key Type" are they sorted by their "Primary Key".

For items in the Groups box, the sorting table always shows all the items in the Groups box, in a fixed order. You cannot add, remove, or reorder the items in the sorting table.

However, for items in the Details box, you are free to choose which items to use for sorting, and in which order. That is, when you select an item from the Details box, the following controls are enabled in the sorting table:

Item	Description
Add	Adds the item selected in the Details box to the sorting table.
Move item up	Moves the selected items one slot up in the sorting table.
Move item down	Moves the selected items one slot down in the sorting table.
Delete item	Removes the selected items from the sorting table.

Step 4: Report Settings

In this page you can configure the appearance of the report.

You can use the following options to configure the appearance of the report:

Item	Description
Orientation	Orientation of the report, either "Portrait" or "Landscape".
Paper	Paper format of the report. This combo box provides a wide array of paper format that you can choose.
Report Description	Text that describes the report. This text is displayed in the Solution Browser page, along with the name of the report.
Header	Text to be added to the header. You can set both a header for the whole Report and a header to be added at the top of every Page in the report.
Footer	Text to be added to the footer. You can set both a footer for the whole Report and a footer to be added at the bottom of every Page in the report.

After you enter all the information for the report, you can use any of the commands in the Navigation Bar to continue.

Navigation Bar

At the bottom of the wizard, there is a navigation bar that provides the following controls:

Item	Description
Preview As	Here you can select the file format of the report preview that ER/Studio Team Server generates when you click the Go button. You can select any of the following file formats: HTML PDF Excel CSV
Go	Loads a preview of the report with the current configuration, using the file format selected in the Preview As combo box.
Back	Goes to the previous page of the wizard.
Next	Goes to the next page of the wizard.
Save	Saves the current settings of the report. If the current report is a new report (that is, you have never saved if before), this control behaves as the Save As control (see below).
Save As	Opens a dialog window where you can select a new name for the report, as well as a new location and file format. Click Save in this dialog to save the report with the new data. When you save a report, three files are generated in the server: An .xreportspec file, an XML file describing the report, so existing reports can be edited and modified. A JFreereport file (.xml ), an XML file created from the .xreportspec file. This file is used by the JFreereport engine to generate the report. An Action Sequence file (.xaction ), an XML file allowing the Pentaho BI Platform to generate a user interface that facilitates the creation of the report. For example, if you save your report as "myReport", the following files are created on the server: myReport.xreportspec myReport.xml myReport.xaction After you save a report, it is displayed in the Solution Browser page, inside the folder where you save it, for example, in Shared Reports/Business Reports . From the Solution Browser page you can find and run your custom reports. Custom reports are automatically shared with all users, unless the super user has changed the permissions of the report. For information on restricting access to your custom reports, see Managing Permissions on Action Sequences.
Cancel	Cancels the custom report creation process, and exits the window or tab of the Web Adhoc Query and Reporting wizard.

See Also

• Using Reports
• Solution Browser