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.
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
- From the MarketWide main menu, click the Analyzer button ( ).
- You will see a page where you can either select "Choose a Table" or "Open Existing Report".
- If you select "Choose a Table", the SQLManager module will open. Select a table, then press OK.
- If you select "Open Existing Report", select a saved report, then press OPEN.
- Drag and drop the fields into either the KPI, Column or Row Dimensions section.
- Click the Query Filter button ( ) if appropriate.
- Press Submit ( ).
- 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.
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
- Drag and drop the Formula function to the KPI area.
- In the Add/Edit Formula dialog, drag and drop the fields to be used into the expression box.
- Select your desired operations.
- Add a title for the new field.
- Set your print format to either Auto or Manual.
- If you select manual, you can click the ellipses button to choose a format category.
- You also have the option to hide subtotal.
- 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
- After submitting a report, select view.
- Highlight Output Filter in the menu.
- Select Advanced.
- Build the filter using the fields in the left pane.
- Click OK.
- 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.
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 All||Total Order Amount|
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:
|Total Order Amount|
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.
- 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.
- 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.
- 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.
- If a field is selected as OK Group By then you can use that field as a KPI.
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
- Open the Analyzer report that you wish to use or construct a new report.
- Select File.
- Highlight Reporting.
- Choose Preview... to open the Preview dialog box.
- 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).
- Press Exit to close the Preview window.
To Setup an Analyzer Report to Auto-Generate
- Open the Analyzer report that you wish to use or construct a new report.
- Select File.
- Highlight Reporting.
- Choose Configure... to open the Reporting tab in the Properties dialog box.
- Click the Auto Generate toggle switch to ON.
- Click the ellipses button in the Location box to choose where the report will generate (My Output is selected by default).
- Enter a name for the Analyzer report in the box to the right of the Location box.
- Choose what format the report will be in (PDF is selected by default).
- To setup the Email Distribution list for recipients to send the report to, click the setup hyperlink.
- In the Email Distribution List Setup dialog, enter in the subject, recipient(s), and message for the list.
- Press OK to close the Email Distribution List Setup dialog box.
- The Include Page Numbers checkbox is already selected. De-select the checkbox if you choose not to include.
- Press OK to close the Properties dialog box.
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.
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.
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.
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