With the release of MITS Discover 10.2 and MITS Report 3.6, we have released a new Detail Grid dashboard object type for MITS Discover dashboards that allows for MITS Report content to be displayed.
This object is a specialized version of the grid object you may already be familiar with in MITS Discover. It will display 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 3.6 of MITS Report and 10.2 of MITS Discover or higher. 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.
This guide assumes familiarity with creating and editing standard MITS Discover dashboard objects as well as an understanding of MitsWeb Markup Language (MWML). If you need assistance with those topics, please contact your MITS support provider.
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 Display Options in the menu and mark the radio button for Yes next to Identifier Tags. This will enable the display of the identifier tag to the left of the identifier.
In this screenshot, we have marked the identifier tag we will be using in green. The other identifier tag used in this MITS Discover report is in blue for reference.
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.
Getting the MRQL Statement
Now that the source is setup and the report has been generated, it is time to capture the MITS Report Query Language statement. This is a text-based representation of the report that both MITS Discover and MITS Report can understand.
To generate the MRQL while viewing the report, click the Export button in the menu of a 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.