With the release of MITS Discover 11 and MITS Report 4, we have added the ability to create dashboard objects for MITS Discover dashboards based on MITS Report content.
This object is a specialized version of the grid object you may already be familiar with in MITS Discover. It displays information from MITS Report. MRQL can also be used in some of the other object types such as cells and stoplights. This document focuses on the Detail Grid object.
An example of this type of object would be to add customer information, such as a main contact name, phone number, and address, to a customer scorecard. This would allow a Sales Rep to pull up the customer’s scorecard and not only know what they are buying, not buying, AR information, and more; they would also know who to contact and how without having to it up elsewhere. Everything they would need to know before making a customer call would all be in one place.
Requirements
Creating and displaying MITS Report dashboard objects for MITS Discover requires versions 4 of MITS Report and 11 of MITS Discover. In addition, both MITS Report and MITS Discover must be installed on the same server and MITS Report must be configured to authenticate users against MITS Discover.
To add this dashboard object type to a MITS Discover dashboard or scorecard, you must have the System Permission to Manual Edit dashboards.
Preparing MITS Report
Before you can create a dashboard object, you must first prepare MITS Report to present the information desired in a way that MITS Discover can understand. There are four steps to this process.
- Identify the Report Source in MITS Report you want to use
- Modify key Report Source columns in MITS Report to match MITS Discover (optional)
- Create a Report in MITS Report containing the desired columns
- Export the MITS Report Query Language (MRQL) query. This will be added to the dashboard object in a later step.
Identify the Source
You can either use an existing Report Source in MITS Report or create a new Report Source if you don’t already have one containing the data you want to display. Either way, you must use a Report Source in MITS Report that is in a good state. Meaning it has been populated and the latest update contained no errors.
Modify Key Report Source Columns
Strictly speaking, this is not a required step. However, if you wish to employ MITS Discover security or have the MITS Discover Drill Across function to work with your object, you must configure certain columns to match their corresponding identifier in MITS Discover. This step maps a column in a Report Source in MITS Report to an identifier in a MITS Discover Hypercube. In our example, we’ll use Sales Rep.
Log into MITS Report using an account with permissions to modify sources. Go to the Sources tab, find the Report Source that you want, and click the Configure link.
Across the top of the column listing will be a link labeled, “Modify Column Labels and IDs”. Clicking this link will present a new page.
One the Modify Column Labels and IDs page, there are four columns. The first is the Source Column ID. This is what MITS Report uses to refer to the column. The second is the External ID. We will modify this value to match the identifier in MITS Discover. The third and fourth columns describe the column headings and descriptions. These are what the user sees.
By default, the External ID is simply the Source Column ID with the spaces and periods replaced by underscores. If there is no need for using MITS Discover Drill Across or applying security to any of these columns, they can be left as is. But if there is an expectation that you will need those features, there is some modification that needs to happen.
Identify any columns in the source where you want Drill Across or security to work. These are the columns that must have their External ID changed. Logically speaking, these must match the identifier you are mapping it to in MITS Discover. For example, a column containing a sales rep ID in MITS Report can be mapped to the SALESREP identifier in MITS Discover, but a column containing a customer’s zip code should not be mapped to the BILLTO Customer identifier.
In addition, the contents of the source column must match the format of the of the external ID it is being mapped to in MITS Discover. If the source column contains a Sales Rep ID in the format of three initials (ABC) and the Sales Rep ID in MITS Discover is two initials and a number (AB7), then drill across and security will not work in MITS Discover for that column.
The rule of thumb is to match like to like.
For the columns you’ve identified that need to match MITS Discover, you’ll change the contents of the External ID field to use the same identifier tag as is appears in MITS Discover for the corresponding identifier.
In the screenshot above, we’ve changed the default External ID, which was salesrep_id, to R. The letter R is the Identifier Tag used in this instance of MITS Discover for Sales Rep.
If you are unsure of the Identifier Tag for any given identifier is in MITS Discover, you can turn on the Identifier Tag display feature when viewing a report.
In MITS Discover, open a report that contains the identifier you want to know about. Click on Options in the menu and select Edit MQL. From here, you can find the identifier tags. An example is shown below.
In this screenshot, we have marked the identifier tags in green.
Create a MITS Report
Creating a report for use in a MITS Discover dashboard object is no different than creating any other report in MITS Report. This is covered in the MITS Report User Guide, but the short version is to click New Report on the Reports tab, select a Report Source, select the columns, and add any filters, sorts, or totals, and view the report.
A report doesn’t need to be saved for this process to work, but it is helpful in case you want to come back and modify it later.
Creating the Dashboard Object
Once you have a report created for display on a MITS Discover dashboard, there are two ways create the object. The first is to use the dashboard object creator and the second is to create it manually.
To use the dashboard object creator, click on Send in the menu while viewing the report. Select Add to Dashboard.
This will bring up a selection dialog where you can choose the dashboard library and specific dashboard or scorecard. Just as with any MITS Discover report, the first identifier in the report but match the prompting identifier on a scorecard for it to be eligible for placement on a scorecard. If the scorecard prompts for Sales Rep, then Sales Rep must be the first identifier in your MITS Report report that is the basis of the object.
The second way to add a MITS Report report to a MITS Discover dashboard is to manually gather the MRQL (MITS Report Query Language) for the report and manually add it to the MWML (MITS Web Markup Language) of the dashboard. The steps for this are provided below. This is a more technical process and is intended for advanced users.
Getting the MRQL Statement
To generate the MRQL while viewing the report, click the Send button in the menu of a MITS Report report and select Copy MRQL to Clipboard. At this point, it would be a good idea to paste that MRQL to a plain text editor like Notepad for later reference.
Here is an example of what an MRQL statement looks like:
REPORT R, salesrep_name, central_phone_number, credit_limit2, credit_limit_used, credit_status, highest_credit_limit_used, mail_address1, mail_address2, mail_city, mail_country, mail_postal_code, mail_state, terms_net_days, terms_type FROM Bill_to_Customers_old HEADING "Bill to Customers"
REPORT tells MITS Discover this is from MITS Report and not one of the hypercubes. R is the identifier column from the Report Source, in this case Sales Rep. Next come the columns of the report, such as salesrep_name and mail_state. FROM tells MITS Report which source the data should be pulled from. And HEADING puts a label on the report.
There are additional keywords you may see in an MRQL statement. These could describe other features of the report such as filtering or sorting.
Generating the object in MITS Discover
Creating a Detail Grid object in MITS Discover is a manual process. There is not a wizard like the one that exists for other dashboard object types.
Creating the Container
The minimum MWML for an MRQL object looks like the one below. You can use it as a template.
{DETAIL.GRID
FLASH='insert MRQL here'
HREF='insert MRQL here'
HEADING='object title'
MAX.ROWS='10'
}
There are a couple of syntax items to note. First, the contents of the attribute value can be delimited by a single quote, a double quote, or a backslash. If you are working in an editor that uses smart quotes, you’ll need to replace them with straight quotes.
Second, the object begins with {DETAIL.GRID and ends with }. These describe the start and end of the object and are required.
When modifying the template above, only change the text in red.
Copy that template to another plain text document and begin replacing all of the red text. For the FLASH and HREF attributes, copy and paste the MRQL query you exported in a previous step. The FLASH attribute determines what is displayed on the dashboard. The HREF attribute determines the report that will appear when the dashboard object is clicked. FLASH and HREF usually match, but it is not required. The FLASH may show a simple version of the report and the HREF could take the viewer to a more complex version of a report.
The HEADING is the title the object will display when viewing the dashboard. MAX.ROWS sets the maximum number of rows the object will display even if there are more rows than that in the actual report. This is useful for keeping and object from being too long.
Here is what a completed object might look like.
{DETAIL.GRID
FLASH= 'REPORT R, salesrep_name, central_phone_number, FROM Bill_to_Customers_old HEADING "Bill to Customers"’
HREF=’ REPORT R, salesrep_name, central_phone_number, FROM Bill_to_Customers_old HEADING "Bill to Customers"'
HEADING='Contact Information'
MAX.ROWS='10'
}
Placing the Object
With the code for the dashboard object created, it is ready to insert into the dashboard’s MWML.
Open the dashboard or scorecard in the manual edit mode. This can be done in the Library view by clicking Modify -> Manual Edit, or by opening the dashboard, clicking Modify and then clicking Manual Edit. This will open the MWML view of the dashboard. If you are familiar with HTML, this will look similar.
If these options aren't available to you, it is possible your user account does not have WRITE permission to at least one dashboard library. You may also be lacking the "Manually Modify Dashboards" permission. Your MITS administrator can help you with getting those permissions granted.
In the MWML code, each of the columns that contain objects start and end with the tags <td> and </td>. The MWML tags that define the section labels and objects in your dashboard will start and end with { and }. There is more about MWML in a document available from your MITS support provider.
The placement of your object must fall between the <td> and </td> of a column but must not be within the { and } of another object.
<table>
<tbody valign="top">
<tr valign="top">
<td>
{GOOD OBJECT PLACEMENT}
</td>
<td>
</td>
<td>
{GOOD OBJECT {BAD OBJECT PLACEMENT} PLACEMENT}
</td>
</tr>
</tbody>
</table>
Modifying Existing Objects
Once a MITS Report object has been manually placed and saved on a dashboard, the object can be modified using the visual editor in the same way as other dashboard objects. When in the Visual Edit mode of the dashboard, the object can be dragged to a new position, deleted, or modified. Clicking the modify icon for a Detail Grid object will bring up this object editor.
Header changes the displayed name of the object.
Return at most sets the maximum number of rows the object will display.
Swap rows and columns transposes the object’s grid.
Show Header Icons displays icons for columns with sorts or filters applied to them.
Disable Drill Across prevents drill across links if they are available.
Columns allows you to change the displayed header for each column to be different than the default.
Columns displays a column selector for the source, allowing for any column in the source to be added.
Dashboards, Scorecards, and Security
Security settings are not shared between MITS Discover and MITS Report. Restrictions on an identifier in MITS Discover will not restrict access on the same identifier in MITS Report, and visa versa. If there are restrictions on an identifier in MITS Discover you want honored in MITS Report, those same restrictions must be set up in MITS Report.
However, if you have WITH {RootPath} in the statement after the FROM <sourcename> clause of the dashabord, it will apply filters to the MRQL statement that match the prompt values on the dashboard.
{PROMPT.SEQUENCE APP="SALES" GENERATE.ROOT.PATH="YES"
{PROMPT.ITEM NAME="Warehouse" IDENT.ABBREV="CW" UI.INPUT.TYPE="DROPDOWN"}
}
{CELL
FLASH='REPORT CSR FROM ORDER.HISTORY WITH {RootPath} AND Extended_Price > 5 SORT BY CSR'
HREF='REPORT CSR, CW, CC, CPL, CP, Extended_Price, Extended_Cost FROM ORDER.HISTORTY WITH {RootPath} SORT BY CSR'
TEXT.BEFORE="Extended Price"
TEXT.AFTER=""
}
If it’s not a generated path dashboard then you can put WITH <identifier> = {prompt item value} or whatever to match the prompt sequence of the dashboard
{PROMPT.SEQUENCE APP="SALES"
{PROMPT.ITEM NAME="Warehouse" IDENT.ABBREV="CW" UI.INPUT.TYPE="DROPDOWN"}
}
{CELL
FLASH='REPORT CSR FROM ORDER.HISTORY WITH CW = {Warehouse} AND Extended_Price > 5 SORT BY CSR'
HREF='REPORT CSR, CW, CC, CPL, CP, Extended_Price, Extended_Cost FROM ORDER.HISTORTY CW = {Warehouse}SORT BY CSR'
TEXT.BEFORE="Extended Price"
TEXT.AFTER=""
}
We recommend setting up a test user with the same permissions and validating the security on an object is working as you expect it before granting your users access.
Comments
0 comments
Article is closed for comments.