Creating a custom report

The easiest way to create a custom report is to identify an existing report and copy it to a new report.

First create a row in the ContentObjs table for the new report. Specify a new unique ID and a Name.

Copy the values from an existing report. This sample code duplicates all fields (except the name) from the report with ID of 8 into a report with ID of 37.

 Update ContentObjs Set
ContentObjStyleID		= (Select ContentObjStyleID from ContentObjs where ID=8),
MDX 															= (Select MDX from ContentObjs where ID=8),
UIState												= (Select UIState from ContentObjs where ID=8),
UIObject										 = (Select UIObject from ContentObjs where ID=8),
ContentObjGroupID	 = (Select ContentObjGroupID from ContentObjs where ID=8),
ImageURL										 = (Select ImageURL from ContentObjs where ID=8),
EditStructure						= (Select EditStructure from ContentObjs where ID=8),
EditData 										= (Select EditData from Contentobjs where ID=8),
Title 													= (Select Title from ContentObjs where ID =8),
XslID													 = (Select XslID from ContentObjs where ID=8),
AllowedRoles						 = (Select AllowedRoles from ContentObjs where ID=8)
Where ID=37

The ContentObjStyleID is an entry in the ContentObjStyles table. It refers to the colors and fonts that are used to display the report as defined in the Set Style menu option.

MDX and UIState control the contents of the report.

ContentObjGroupID is an entry in the ContentObjGroups table.

ImageURL is the image to display in the navigation bar.

Title is the title to display in the report.

XslID is an entry in the XslInfo table (used for extracting the report).

AllowedRoles is discussed above.

Where does the data come from?

The data for the report comes from the MDX in the MDX field. The MDX defines the members across and down in the report. You can also create calculated fields in the MDX as in the example for the Performance Results report. You can include members in the MDX that are not immediately visible in the report, but are available to be included for customizations.

You must include the special comments that are at the beginning of the report if you require the selected members to "follow along". This is an example of these comments from the Performance Results report.

/* Substitution Definitions */
/*===================================================================*/
/*id "caption", "dimension name", allowed selections, follow
flag, verify set, {from set} id*/
/*1 "Unit", "[Unit]", 1, true, false, {[Unit].Members} 1*/
/*2 "Period", "[Period]", 1, true, false,
{Except([Period].Members, {[Period].&[-2]})} 2*/
/*3 "Accumulation Method", "[Accumulation Method]", 1, true,
false, {[Accumulation Method].Members} 3*/
/*4 "Scenario", "[Scenario]", 1, true, false,
{[Scenario].Members} 4*/

The first line of the definition indicates how to read the next lines. They contain the ID, a caption, the dimension name, the number of allowed selections (member select button) (1 off-grid member or -1 multiple on-grid members), whether selections should follow along, and the set of members to choose from for the selection.

Within the MDX, there are comments embedded around member lists. These comments denote the beginning and end of possible member selection as is specified in the Substitution Definition comments section. For example, the following code indicates that the member between /*4B*/ and /*4E*/ should be substituted according to the rules for ID of 4 in the substitution definition section.

/*4B*/[Scenario].DefaultMember/*4E*/

Defining the columns to display

The information about how columns are displayed is contained in the UIState field. This field contains information in XML. The part to pay attention to is the AllColumns section. The VisibleIndex attribute indicates what index a column is displayed in the report. A VisibleIndex of -1 means it will not display. It is possible to include a member in the MDX (weight for example) that does not initially display in the report, but it must be available if the user chooses to display it through the Add or Remove Columns menu option.

The MDX and UIState for a report are first retrieved from the UserContentObjStates table. If you make changes to these fields in the ContentObjs table, you must remove them from the UserContentObjStates table for the changes to appear.

The MDX and the UIState values for all of the default reports can be found in the COUpdate.sql file in the same directory as SMAppWiz.exe. You cannot copy these straight into Enterprise Manager or SQL Server Management Studio as there is a limit of 255 characters for copying and pasting. You can use these to create your own SQL for updating the fields.