Devlin Consulting Inc. > Products > MCSUPER Overview > Crosstab Generator


   User Guides


Return to Home Page

Crosstab Generator


The fourth tab on the navigation interface is the Crosstab Generator interface.  The Crosstab Generator interface allows you to format a crosstab report based on up to three row fields, a column for cross analysis and a value.


The Source drop-down list and the Show Criteria button remain available and provide the same functionality throughout the application.


You can continue to work with the criteria that you established in the Report Criteria interface.  Any criteria that were set on the Report Criteria interface will filter the data included in the report.  If you change the Source at this point, you will lose any criteria you have populated. 


Click the X-Tab Generator tab to display the Crosstab Generator interface.



The functionality of the Crosstab Generator is described in detail in subsequent sections, but basically the interface works as follows:


·         Select fields from the Row Fields drop-downs, the first is required, the other two are optional.

  • Select a Column Field from the drop-down.
  • Select a Value Field from the drop-down.
  • Click the Run X-Tab button to run the report.


The drop-down lists will contain all fields that relate to the Source that has been selected.



The Row Fields and Column Fields lists will contain the available grouping fields.


One Row Field is required; up to three can be entered.  These fields will form the horizontal rows in the final report.  You have the option to report this as a code value by clearing the Show Description checkbox or to report this as a description by selecting the Show Description checkbox.



One Column Field is required.  The Column Field will form the vertical columns in the final report.  You have the option to report this as a code value by clearing the Show Description checkbox or to report this as a description by selecting the Show Description checkbox.



The Value Fields list will contain the available summary and calculated fields.  One Value Field is required.  The Value Field will be the data displayed in the final report.


Creating a Crosstab Report


Fields to be entered:


·         Title - defaults to "Enter your title", should be edited to a valid title.

·         Subtitle - defaults to "Enter your subtitle", should be edited to a valid subtitle.

·         Footer - defaults to "Enter your footer", should be edited to a valid footer.

·         Row Fields - Grouping fields related to the selected Source.  Data entry of one is required, can enter up to three.

·         Column Field - Grouping fields related to the selected Source.  Each value of this field will become a column on the report.  Data entry is required.

·         Value Field - Summary and Calculated fields relating to the selected Source.  Data entry is required.


The crosstab report will contain a row for each distinct combination of the row fields and a column will be created for each value in the column field.  The values for the column fields can be filtered in the report criteria to include a subset.


The following image shows the selection of row field “Line Of Business”, column field "Paid Quarter" and a value field of "Paid Amount”.  In the example shown the data would be grouped by the Line Of Business. 


As you build the crosstab report, sample data will be placed in each of the columns to illustrate how the finished report will appear.



Running a Crosstab Report


Once you have finished formatting the crosstab report, click the Run X-Tab button.


The following image is a continuation of the previous example.  Clicking the Run X-Tab button brings the completed report to the screen, formatting it with titles as you entered them and the values for the data you requested.


The crosstab can be easily modified to show the Service Start Quarter.


Add a second row field, Service Start Qtr.



Click the Run X-Tab button.



If you wanted to see the Line of Business as an ID instead of a description, you would uncheck the Show Description box under the row field drop-down list containing Line of Business.



Click the Run X-Tab button to rerun the report.



You can see how quickly and easily a crosstab report can be created and modified.


Saving A Crosstab Format


After formatting a crosstab report, you can save the format and load it back at a later time.  The Save button will save your current format in a file name that you specify.


Click the Save button and an interface opens requesting that you enter a Saved Name and a Description.


Enter a saved name and a description.  Then click the Save button to save the report format or if you decide not to save it, click the Cancel button.  Either action will return you to the Crosstab Generator interface.


Loading A Crosstab Format


The Load button will load a previously saved crosstab report.  You can bring back a report format that you were working on or bring back a report format saved by someone else.  If you choose to load a previously saved crosstab report, verification will be displayed to verify that you want to overwrite any formatting that is currently on the interface.


Click the Load button and an interface opens with a list of saved report formats showing the Saved ID, the Saved Name of the report, the User ID of the person that saved it, the Create Date, which is the day it was saved, and a Description as entered by the person that saved it.


The list will contain only those crosstab report formats that relate to the current Source selection.  The list can be sorted by any one of the columns.  This is helpful when searching a large list of saved crosstab reports.  To sort a column on the Load interface, double-click the column header of the column you want to sort.


The Show Mine Only checkbox will filter the saved crosstab reports to display only those saved under your user ID.  This will help you to find crosstab reports that you saved and will provide a smaller list to search.


Place the cursor on any area of the row that you want to load and click the Load button to load the report or if you decide not to load it, click the Cancel button.  Either action will return you to the Crosstab Generator interface.


Deleting a Saved Crosstab Format


From the Load interface, you can delete saved crosstab formats that were previously saved under your ID.  You will only be allowed to delete formats that were created under your user ID.


From the Load interface, review the crosstab formats you have saved.


To delete a saved crosstab format, place your cursor on the row to be deleted and click the Delete button.


Exporting Data

Available on the Crosstab Generator interface is the ability to export data to a text or Microsoft Excel format.


Click the Export Data button and an interface opens with the choice of exporting to a Text file, Microsoft Excel or Microsoft Access.  Choose the file type and click the OK button.  An Export Data interface opens for you to enter a path and file name.  After entering the file name click the OK button.


The data will be exported in the format you requested to the file name you specified.


If saved to an Excel file, upon opening the Excel spreadsheet, you will see two sheets created:  a criteria sheet and a data sheet.  The criteria sheet will list the Source and the filtering criteria that were used when the data was exported.  The data sheet will contain the results of the report query run in the Crosstab Generator interface.


Recall that when exported as a code value, the first column of the data can be imported to MCSUPER as filtering criteria.


Data exported to text will be formatted as comma separated fields.


Data exported to Microsoft Access will be exported to a table called ‘MCSUPERtabularData’ in a database with the file name you provided.

Clearing Selections on Crosstab Generator Interface


The Clear button on the lower left will allow you to remove titles, footer and all fields from the report display.