Analyzer


Analyzer uses an OLAP interface to generate polished reports. Reports can be built off any object: Cubes, Tables, Unions, Joins, and Queries. Analyzer is a tool that enables a user to organize specific data from tables already on the host into a customizable column/row style table. This displays the finished results in an orderly way to easily reference in the future.

Analyzer provides dummy data in design mode to give you an idea of how your report will appear. By applying functions and custom formulas to the desired row or column, you can retrieve even the most finite information. In addition to applying formulas to the fields, you can add filters.

Topics

Creating an Analyzer Report

There are three primary areas where you can drop your fields: Row Dimensions, Column Dimensions, and KPIs (key performance indicators). Row and column dimensions allow you to control how the fields are stacked either on the row or column level. For example, suppose you want to see a breakout of sales by state and you want state sales to be broken out by female versus male.

In this case, you could drag and drop your state field into the row dimensions' area, then drag the field containing gender and drop it below the state field. Next, are the KPIs. The KPIs are the data that appears in the cells. Count_All is automatically provided as the initial value. For this example, you would take your field that holds revenue and drop it in the KPIs area below the column dimensions.

To Create an Analyzer Report

  1. From the MarketWide main menu, click the Analyzer button ().
  2. You will see a page where you can either select "Choose a Table" or "Open Existing Report".
  3. If you select "Choose a Table", the SQLManager module will open. Select a table, then press OK.
  4. If you select "Open Existing Report", select a saved report, then press OPEN.
  5. Drag and drop the fields into either the KPI, Column or Row Dimensions section.
  6. Click the Query Filter button () if appropriate.
  7. Press Submit ().

Note

  • If a cube was created with a field prompt, when you run the analyzer report you will be asked to supply a value for the field(s).

Add Formula Dialog

Analyzer enables you to create a custom field for your report by applying a formula to a row or column in the edit mode of Analyzer. This is extremely useful for performing calculations on sales totals that you would like to show in your report. Additionally, you are given the option to apply the formula to the total.

Operations

Icon

Description

Inserts a multiplication symbol.

Inserts a division symbol.

Inserts an addition symbol.

Inserts a subtraction symbol.

Inserts a grouping symbol.

Moves the selected field(s) to the expression area.

To Create a Formula

  1. Drag and drop the Formula function to the KPI area.
  2. In the Add/Edit Formula dialog, drag and drop the fields to be used into the expression box.
  3. Select your desired operations.
  4. Add a title for the new field.
  5. Set your print format to either Auto or Manual.
  6. If you select manual, you can click the ellipses button to choose a format category.
  7. You also have the option to hide subtotal.
  8. Click OK.

The Output Filter

Occasionally, after running a complex Analyzer report, you may realize that you forgot to include a filter, or you wanted to remove dimensions. Instead of correcting the criteria and re-running the report, you can save time by using the output filter tool to refine your results. A filter is placed on the result set, which results in very little extra processing time because most output filters take just seconds to run. 

An output filter allows you to filter records from the result set using a simple point-and-click technique. The output filter only places a filter on the Analyzer results data from the preprocessed query. You can only apply an output filter after submitting a report and viewing the results.

To Add an Output Filter

  1. After submitting a report, select view.
  2. Highlight Output Filter in the menu.
  3. Select Advanced.
  4. Build the filter using the fields in the left pane.
  5. Click OK.

Note

  • In the Output Filter dialog, the Fields folder contains the functions and dimension values you selected in the report.
  • If you select more than one row dimension, the output filter will apply to all dimension's values, not just the values displayed in the right pane.
  • Output filters are only saved in session history.

Time Saver

  • You can only use the fields that are already in the KPI area.
  • To edit the printed format of the field, please see the Print Format section of this manual.

Field Context Menu

The Field Context menu is accessed by right-clicking on a field that is being used in an Analyzer report. The menu has many uses which deal with editing the displays of fields in edit mode.

Additionally, you can select the ellipses at the top of the list to change the way a field is displayed. For instance, if you create a field using the Add/Edit Formula dialog, and you wish to display the output of the field as currency, you would right-click the field, click the ellipses under print format, select currency, and click OK to apply your changes.

    • Rename: Renames the field.
    • Delete: Deletes the field from the report.
    • Use Descriptions: Allows the descriptions for each value to be toggled on/off.
    • Sort: Sorts the data in either ascending or descending order.
    • Print Format: Edits how the field is displayed.
    • Show or Hide Fields: You can show or hide fields from the desired output. For instance, if you want to show sales for all employees by state, but want to hide the results for Hawaii, you could right-click your state field, select hide and check Hawaii to hide it.
    • Cumulative Total: An additional column is added containing the sum of all the consecutive items preceding it. This is useful when comparing against projected totals.
    • Percent Column: Adds an additional column that holds the cumulative total of the field in the form of a percentage.
    • Advanced Functions: A list of more advanced available functions for the field.
    • Properties: Opens the field properties dialog which includes both editable and read-only properties of the field.
    • Groups and Ranges: Opens the Groups and Ranges dialog.

Groups and Ranges
Groups and Ranges allows you to take your row or column values and condense them into manageable pieces. When a group or a range is created, it sums the values of the KPI cells and collapses all the specified row values into a single row. 

For instance, suppose you had an Analyzer where you were breaking your sales out by specific states, such as here:


Count AllTotal Order Amount
Maryland123$462.00
New York456$850.00
California789$1,762.00
Idaho321$726.00

Instead, you want to show sales broken out by region. This is accomplished by creating a group for each region, and putting the appropriate states into each group. In this case, you would create a group for Northeast states, and add Maryland and New York. Next you would create a Northwest group and add California and Idaho. The results would appear as follows:


Count All

Total Order Amount
Northeast579$1,312.00
Northwest1,110$2,488.00

Additionally, the Groups and Ranges dialog allows you apply intervals. For instance, if you had a numeric field representing currency, such as order amount set as a Row Dimension, you would see values such as: $12.01, $12.24, $13.27. If you set up intervals of fifty, then all the values between zero and fifty would be condensed in one cell, fifty to one hundred would be in the next, and so on. For numeric fields, this is extremely helpful as it condenses values that differ by marginal amounts, such as a penny.

Note

  • Ranges that are split into even parts can be automatically generated in the Range tab by selecting the number of ranges and clicking generate.
  • For certain types of fields, such as dates, pre-defined groups such as "Month", "Year", "Quarter", etc. are provided in the "Built in Groups" tab.

Field Properties Dialog 

The Fields Properties dialog displays the properties for the selected field, allowing you to designate the function, visibility, print format, and read-only properties. There are two categories of Field Properties: Editable and Read-Only. Editable properties can be manipulated and Read-Only properties are unchangeable.

Editable

  • Caption - The user designated name of the field that will be displayed.
  • Print Format - The format in which the field is displayed.
  • Aggregate Type - The aggregate function that is selected for the field.
  • Show Value Description - If a picklist is available for the field, you can choose to show the descriptions of the values.
  • Total Function - The function that is used in the total column.
  • Visible - Controls whether the field appears in the report or not.
  • Show Totals - Controls whether the Grand Total appears in the report or not.

Read-Only

  • Name - The system designated name of the field.
  • Description - The description of the field (if available).
  • OK Group By - Indicates of this field can be included in a Group By statement.
  • OK Math - Indicates if mathematical functions can be performed on this field.
  • Reference Field - Indicates if this field is a reference field.
  • Composite Field - Indicates if this field is a composite field.
  • Cumulative Total - Indicates if this field shows a cumulative total.
  • Percent Total - Indicates if this field shows a percent total.

Note

  • If a field is selected as OK Group By then you can use that field as a KPI.

Reporting

The reporting options in Analyzer are used to setup the report's layout, auto-generate a report, or designate who the report is sent to via email if needed. Clicking the Reporting text will open the Preview dialog or the Reporting tab in the Properties dialog. The Preview dialog allows you to design how the Analyzer report appears when either printing or saving as an output file.

To Generate an Analyzer Report

  1. Open the Analyzer report that you wish to use or construct a new report.
  2. Select File.
  3. Highlight Reporting.
  4. Choose Preview... to open the Preview dialog box.
  5. You will have a few options to save your report:
    • Print - You can print the report to your saved printer.
    • Export Document - You can choose which file format to save the document in (PDF is selected by default).
    • Send Via E-Mail - You can send the report, via email in whatever format you choose (PDF is selected by default).
  6. Press Exit to close the Preview window.

To Setup an Analyzer Report to Auto-Generate

  1. Open the Analyzer report that you wish to use or construct a new report.
  2. Select File.
  3. Highlight Reporting.
  4. Choose Configure... to open the Reporting tab in the Properties dialog box.
  5. Click the Auto Generate toggle switch to ON.
  6. Click the ellipses button in the Location box to choose where the report will generate (My Output is selected by default).
  7. Enter a name for the Analyzer report in the box to the right of the Location box.
  8. Choose what format the report will be in (PDF is selected by default).
  9. To setup the Email Distribution list for recipients to send the report to, click the setup hyperlink.
  10. In the Email Distribution List Setup dialog, enter in the subject, recipient(s), and message for the list.
  11. Press OK to close the Email Distribution List Setup dialog box.
  12. The Include Page Numbers checkbox is already selected. De-select the checkbox if you choose not to include.
  13. Press OK to close the Properties dialog box.

Docking Sections

Sections can be docked, floating, or auto-hidden. To dock or undock a section, simply click its header and drag the section. As you drag the section around the page, docking hints appear (see the figure below). Use these hints to dock the section to the desired position.

Docking Zone

A docking zone is a semi-transparent preview of exactly where your section will be docked. This zone is displayed when you drag a panel into the area, but before you drop it.

Section Buttons

Sections have multiple state change buttons in their top right corner.

    • Menu – gives various menu options for the section.
    • Submit – runs the data for the section.
    • Done - takes the section out of edit mode.
    • Cancel – cancels out of the section.

The Analyzer Menu and Toolbar

The toolbar provides quick access to commonly used commands in the Analyzer main window. Click the icon once to carry out the action represented by that icon.

Icon

Description

Menu Equivalent

Creates a new Analyzer report.

File > New

Opens a saved Analyzer report.

File > Open  

Saves your current Analyzer report.

File > Save  

–

Allows you to select how and where the Analyzer report is saved.

File > Save As

Opens a report from your local machine.

File > Local > Open

Saves the report to your local machine.

File > Local > Save

Previews the document in a PDF format.

File > Print  

Allows you to set the order you would like each section to appear when you print.

File > Printing Order

Opens the Reporting section, where you have options to Auto-Generate a report or configure how the report will be saved or printed. See the Reporting section for more information.

File > Reporting > Generate

Checks the syntax of the report.

File > Validate

Submits your selected section for execution.

File > Submit

Submits all sections of the Analyzer report for execution.

File > Submit All

Opens the Scheduler module to schedule the current Analyzer report.

File > Schedule

Opens the properties window.

File > Properties

--

Closes the Analyzer module.

File > Exit

Copies the selected content.

Edit > Copy

--

Copies the currently selected values and column headers.

Edit > Copy with Headers

--

Highlights all the cells in the currently selected section.

Edit > Select All

Sorts the currently displayed segments in ascending order.

Edit > Sort Ascending

Sorts the currently displayed segments in descending order.

Edit > Sort Descending

Opens the Add Formula dialog.

Edit > Add Formula

Opens the SQLManager module to select an object to build the report from.

Edit > SQL Manager

Opens the Filter window.

Edit > Filter

Opens the KPI filter window.

Edit > KPI Filter

Allows you to sequentially undo up to the last 20 actions.

Edit > Undo

Allows you to sequentially redo up to the last 20 actions.

Edit > Redo

Adds a new section.

Edit > New Section

Duplicates the currently selected section.

Edit > Duplicate Section

Deletes the currently selected section.

Edit > Delete Section

--

Analyzer will prompt you if you make changes that will affect your counts after running the report.

View > Prompt on Changes

--

Automatically collapses the rows after a report has been submitted.

View > Auto Collapse

Opens the Output Filter window.

View > Output Filter

--

Changes the KPI values from columns to rows.

View > KPIs as Rows

Changes the view to Composition mode.

View > Views > Composition

Changes the view to Report mode.

View > Views > Report

–

Opens the options window.

Tools > Options

–

Displays help information for the Analyzer program.

Help > Analyzer Help