Report Bursting

We are going to use the mdx “Subset” functionality which generates a subset base on the knowing the starting element and the amount of members from a group set.

{ Subset ( { group set}, first element, amount of elements ) }

Before going deeper in this subset it will be necessary to address another specification, if the dynamic subset is going to be user specific we will need to get values from the “}ElementAttributes_}Clients” cube. For that reason we will need to add three elements in this cube: “Selected Element, Pagination Step, Amount Elements”.

{ group set} :

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Journal] )} ,
LOOKUPCUBE (“}ElementAttributes_}Clients”,”(
 [}Clients].["+USERNAME+"]
, [}ElementAttributes_}Clients].[Selected Element])”))}

This group set gets the string value from the “}ElementAttributes_}Clients” cube, and filters by level, which means that in this example the group set will have the 0 level element members from the “Selected Element” .

First Element:
( LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Pagination Step])”) *
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”) ) -
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”)

The “First element” in the subset will be created mathematically getting values from the “}ElementAttributes_}Clients” cube. In this case:

( LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Pagination Step])”) *
1 *
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”) ) -
3 -
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”)
3
(1*3) – 3 = 0

Which means that the first element in this case will be 0.
The idea is that the pagination step will drive the subset through this concept:
( Pagination Step * Amount Elements ) – Amount Elements  =    First Element
( 1 * 3  )  - 3   = 0
( 2 * 3  )  –  3   =  3
( 3  * 3  )  - 3   =  6
( 4  * 3  )  - 3   =  9
…

Amount of elements:

The “Amount of elements” in the subset will be created mathematically getting values from the “}ElementAttributes_Clients” cube. In this case:

LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”)

Which means that the amount of Elements for the subset will be 3 in this case.

Full mdx Statement for mdxFilterSubset:
{ SUBSET (
/* SELECTED ELEMENT */
{ TM1FILTERBYLEVEL ( {TM1DRILLDOWNMEMBER (
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Journal] )} ,
LOOKUPCUBE (“}ElementAttributes_}Clients”,”(
 [}Clients].["+USERNAME+"] , [}ElementAttributes_}Clients].[Selected Element])”))}, ALL , RECURSIVE ) } , 0 ) },
/* Paginatation step */
/* this parameter is just to know the starting point */
( LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Pagination Step])”) *
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”) ) -
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”),
/* Amount of Elements */
LOOKUPCUBE (“}ElementAttributes_}Clients”,”([}Clients].["+USERNAME+"] ,[}ElementAttributes_}Clients].[Amount Elements])”)
)}

 

WebSheet

Finally we will add some cells in the websheet to be able to input the values in the “}ElementAttributes_}Clients” cube. For example to input values for the “Selected Element” we will write:
=DBR(rngServer&”}ElementAttributes_}Clients”,rngUser,”Selected Element”)
Where rngServer is the cell with the server name and rngUSer is the cell with the user name.

When working with Cognos 8 and TM1 if you have several database connections within Framework manager C8 asks for credentials.  When entering credentials you are unable to login to TM1.  The reason for this is the Max Connections are set below the number of TM1 database connections within Cognos.  To resolve this issue set the administrator or client setup in Framework Manager for you TM1 DB connections to 100 or more.  This number should be greater than the number of cubes in your C8 model.

Considering the Month dimension as we see below:

+ Year
+ 1 Quarter
       Jan
       Feb
       Mar
+ 2 Qarter
       Apr
       May
       Jun
+ 3 Quarter
       Jul
       Aug
       Sep
+ 4 Quarter
       Oct
       Nov
       Dec

ANCESTORS

In this case we  see the immediate ancestors of Feb.

{ ANCESTORS ([dimension].[member], ancestor level ) }

{ ANCESTORS ( [month].[Feb], 1 )) }

Subset member: 

1 Quarter 

{ ANCESTORS ( [month].[Feb], 2 )) }

Subset member: 

Year

BOTTOMCOUNT

This example filters the Bottom 5 Order element values. Which Means:

Feb => 0, Dec => 0, Apr => 0,  Aug => 0, Jun => 0

{ Order( {BottomCount( {TM1SubsetAll( [month] )}, 5.000000, [Filter].([Filter].[Order]))}, [Filter].([Filter].[Order]), BASC) }

Subset members: 

Feb, Dec,  Apr,  Aug , Jun 

CHILDREN

In this example we see the immediate children of the selected member.

{dimension.[member].CHILDREN}

{ month[1 Quarter].CHILDREN }

Subset members:

Jan, Feb, Mar

COMMENTS

You can use // or – at the end of a mdx statement to comment its own mdx statements.
You also can use /* Comment */ prior or during a line. In this example the comments are in blue fond.

/* This filter elements whose name starts with alphabet values falling in a range. */
{Filter ( {TM1SUBSETALL( [month] )}, /* select all elements to filter */
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME > “A” AND
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME < “J”
)} – End of Filter
// another comment example

 <set>.CURRENTMEMBER

According to the Analysis Services Books Online, “returns the current member along a dimension during iteration.” In this example we can see how to filter elements whose name starts with alphabet values falling in a range.

{Filter ( {TM1SUBSETALL( [month] )},
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME > “A” AND
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME < “J”
)}

Subset members: 

Feb, Apr, Aug, Dec

DESCENDANTS

Displays the selected member and children.

{DESCENDANTS (dimension.[member])}

{DESCENDANTS ( month.[1 Quarter])}

Subset members:

1 Quarter, Jan, Feb, Mar

DRILLDOWNMEMBER

Displays the immediate children level of the selected member.

{DRILLDOWNLEVEL ({[dimension].[member]})}

{DRILLDOWNLEVEL ({[month].[Year]})}

Subset members:

Year, 1 Quarter, 2 Quarter, 3 Quarter, 4 Quarter

EXCEPT

Displays all the elements except the selected member

{EXCEPT ({From}, {[dimension].[member to except]})}

{EXCEPT ( { LASTPERIODS  (6, [month].[Jun]) } , {[month].[Feb]} ) }

Subset members:

Jan, Mar, Apr, Apr, May, Jun

FILTER

{ Filter ( [members to filter], [cube].([dimension].[member] , [dimension].[member] ) = Condition )}

{FILTER ( {TM1SUBSETALL( [month] )}, [Filter].([Filter].[Filter])=1)}

Subset members:

Jan, Mar, Apr, Apr, May, Jun, Jul, Sep (the member with the value 1 in the Filter cube)

Another example we can see how to filter elements whose name starts with alphabet values falling in a range.

{Filter ( {TM1SUBSETALL( [month] )}, 
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME > “A” AND
{TM1SUBSETALL( [month] )}.CURRENTMEMBER.NAME < “J”
)}

Subset members:

Feb, Apr, Aug, Dec

FIRSTCHILD & LASTCHILD

FIRSTCHILD: Returns the First Child of the selected Member.

{dimension.[member].FIRSTCHILD}

{month.[1 Quarter].FIRSTCHILD }

Subset members:

Jan

LASTCHILD: Returns the Last Child of the selected Member.

{dimension.[member].LASTCHILD}

{month.[1 Quarter].LASTCHILD }

Subset members:

Mar

FIRSTSIBLING & LASTSIBLING

FIRSTSIBLING: Returns the First Sibling in the same group set that shares a parent.

{ [dimension].[member].FIRSTSIBLING }

{ [month].[Feb].FIRSTSIBLING }

Subset members:

Jan

{ [month].[Dec].FIRSTSIBLING }

Subset members:

Oct

LASTSIBLING: Returns the Last Sibling in the same group set that shares a parent.

{ [dimension].[member].LASTSIBLING }

{ [month].[Feb].LASTSIBLING }

Subset members:

Mar

HEAD

Returns the first element inside the group set.

{ HEAD (Group Set ) }

{ HEAD ( {month.[1 Quarter].CHILDREN } )}

Subset members:

Jan

TAIL

Tail returns the final member of the group set.

{TAIL ( Group set ) }

{ TAIL {( { month.[1 Quarter].CHILDREN } )}

Subset member:

Mar

HIERARCHIZE

This function reorganizes the selected set under a hierarchy.

{HIERARCHIZE ({TM1FILTERBYPATTERN ( {TM1SUBSETALL( [month] )}, “*e*” ) }) }

Subset members:

Year, 1 Quarter, Feb, 2 Quarter, 3 Quarter, Sep, 4 Quarter, Dec

INTERSECT

Intersect returns only the elements that appear in both sets.

{INTERSECT ({Group Set 1}, {Groups Set 2})}

LASTPERIODS ( 6, [month].[Jun])}         {month.[1 Quarter].Children}

Jan                                                      

Feb

Mar                                                             Mar

Apr

May

Jun

{ INTERSECT ( { LASTPERIODS (6, [month].[Jun]) }, { TAIL ( {month.[1 Quarter].CHILDREN } )} )}

Subset member:

Mar

LAG & LEAD

Lead (n) is the same as Lag (-n) they only return a single member.

{[dimension].[member].LAG(n) }

{ [month].[Mar].LAG(2) }
 
Subset member:

Jan

{ [dimension].[member].LEAD(n) }

 { [month].[Feb].LEAD(2) }

Subset Member:

Apr

LASTPERIODS

turns the n elements preceding and including the selected member.

{ LASTPERIODS (n , [dimension].[member] ) }

{ LASTPERIODS(6, [month].[Jun]) }

Subset members:

Jan, Feb, Mar, Apr, May, Jun

LOOKUPCUBE

This example shows how to Look Up the zSetup cube to get the desire value to Filter By Patter,

{ TM1FILTERBYPATTERN ( {TM1SUBSETALL( [month] )},
LOOKUPCUBE (“zSetup”, “([SetupMeasures].[CurrentMonth], [Setup].[String])”))}

Subset member:

Value in zSetup => Aug.

NEXTMEMBER & PREVMEMBER

Similar to LAG or LEAD but it only moves along 1 element

{[dimension].[member].NextMember }

{ [month].[Feb].NextMember }

Subset Member:

Mar

{ [dimension].[Member].PrevMember }

{ [month].[Feb].PrevMember }

Subset Member:

Jan

ORDER

Order the subset base in a reference to another cube, in this example we just order the months which values are higher than 0.

{ Order ( selected dimension members , cube dimension reference , BASC – BDESC }

{ Order ( Filter ( {TM1SUBSETALL( [month] )} ,[Filter].([Filter].[Order]) <> 0 ), [Filter].([Filter].[Order]) , BASC) }

PARENT

Parent Function displays the parent of the selected member.

{ dimension.[member].PARENT }

{ month.[May].PARENT }

Subset member:

2 Quarter

SIBLINGS

These functions displays the siblings or the elements at the same level of the selected member it is really similar to TM1FilterByLevel.

{ dimension.[member].Siblings } 

{month.[1 Quarter].SIBLINGS }

{[dimension].[member].Level.Members}

{[month].[1 Quarter].Level.Members}

Subset member:

1 Quarter, 2 Quarter, 3 Quarter, 4 Quarter
  
SUBSET

Subset functions generates a subset base in the first element and the an amount of members from a group set, in this example it will show all the elements starting on February and counting 10 elements more until November,

{ SUBSET ( {group}, first element, amount of elements ) }

{SUBSET ( {TM1FILTERBYLEVEL (TM1SUBSETALL ( [month] ) , 0)}, 1, 5)}

Subset members:

Feb, Mar, Apr, May, Jun

TM1FILTERBYLEVEL

This function shows all the elements by filter by level in a determinate group.

{ TM1FILTERBYLEVEL ({Group}, n,n…)}

{ TM1FILTERBYLEVEL ({TM1DRILLDOWNMEMBER ({[month].[Year]}, ALL, RECURSIVE) }, 1, 2)}

Subset members:

Year, 1 Quarter, 2 Quarter, 3 Quarter, 4 Quarter

TM1FILTERBYPATTERN

This function returns the pattern matching the set.

TM1FILTERBYPATTERN ( {TM1SUBSETALL( [month] )}, “Ju*”)}

Subset members:

Jun, Jul

TM1DRILLDOWNMEMBER

This function is equal to the function Descendants.

{TM1DRILLDOWNMEMBER ({Descendants(month.[1 Quarter])}, ALL, RECURSIVE) }

equal

{ DESCENDANTS ( month.[1 Quarter])}

Subset members:

1 Quarter, Jan, Feb, Mar.

TM1SORT

This function sorts <set> alphabetically

TM1SORT ( { LASTPERIODS (6, [month].[Jun]) } , ASC ) }

Subset members:

Apr, Feb, Jan, Jun, Mar, May.

TM1SORTBYINDEX

This function sorts <set> by the index value of the members.

TM1SORTBYINDEX (<set>, ASC|DESC)

{ TM1SORTBYINDEX ( { LASTPERIODS (6, [month].[Jun]) } , DESC ) }
  
TM1SUBSETALL

This function returns all the elements of the dimension.

{TM1SUBSETALL ( [dimension] )}

{TM1SUBSETALL ( [month] )}

TM1SUBSETTOSET

Returns the member(s) of pre-existing month subset, there is also another way to write return a subset.

{TM1SUBSETTOSET ( [dimension] , “Subset name” ) or [dimension].[subset name]

{ TM1SUBSETTOSET ([month] , “Union”) }  equal to [month].[Union]

In this case Union subset members are “Jan” and “Mar”.

TOPCOUNT

This example filters the Top 5 Order element values. That means:

Sep => 5, Nov => 6, 4 Quarter => 6, 3 Quarter => 9, Year => 21

{ ORDER( {TOPCOUNT( {TM1SUBSETALL( [month] )}, 5.000000, [Filter].([Filter].[Order]))}, [Filter].([Filter].[Order]), BASC) }

UNION

Union joins two sets together returning the values from both sets.

{UNION ({Group 1}, {Group 2 } ) }

{UNION ( {HEAD (TM1SUBSETALL( [month] ))} , { TAIL ( {month.[1 Quarter].Children} )} )}

Subset members:

Jan, Mar

The supported environment matrix for TM1 (through 9.4 MR1 FP2) does not include support for IIS 7.   In most cases, attempting to install TM1Web to an IIS 7 web server will fail altogether.   The reason for this is that TM1Web relies on the IIS metabase – the centralized configuration store used by IIS 5 and IIS 6.   With IIS 7, the metabase has been replaced by a new delegated configuration system based on a hierarchy of distributed XML configuration files.

In order to successfully install and configure TM1Web to work on IIS7, the Metabase Compatibility component of IIS 7.0 needs to be installed on the machine. This component is not installed by default.  On Windows Server® 2008 builds, you can use the Server Manager tool (Start->Administrative Tools->Server Manager), and check the “IIS 6.0 Management Compatibility” component to install it.  For Windows XP, Vista, or Server 2003 you can use the Microsoft Web Platform Installer – a free tool available on the Microsoft IIS site    [url]http://www.microsoft.com/web/downloads/platform.aspx[/url]    Install and launch the Web Platform Installer, and select the “Web Platform” tab.  Next to the “Web Server” item, click “Customize” to display the available IIS configuration options.   Scroll to the group of items labeled “Computability”, place a check in the box beside “IIS Metabase Compatability”, and click the Install button.

Once the Metabase Compatibility feature is enabled, you should be able to complete the install of TM1Web on your IIS7 machine.  Once TM1Web is installed, there’s one additional configuration step.  Start the Internet Information Services (IIS) Manager (typically available via Control Panel -> Administrative Tools).  Browse the folders in the left pane and select the item TM1Web (typically found under “Machine Name” -> Sites -> Default Web Site).   In the right panel, click the icon for “Default Document”. Add an entry “TM1WebLogin.aspx” if it is not already defined.  You may need to restart the default web site.

Verify that TM1Web is working properly by browsing to http://yoursite/TM1Web
The TM1Web login screen should appear.

Welcome!   The ARCS team is pleased to launch our all new blog.  With over a quater century of combined experience delivering Business Performance Management (BPM) solutions that employ leading edge technologies such as IBM Cognos TM1 and Cognos8, our goal for this blog is to share the accumulated  knowledge, best practices and insights with the online community.  Your comments on any article published to this blog are both welcomed and appreciated.

For interactive and threaded discussions pertaining to TM1,  C8, Executive Viewer, and other IBM Cognos Business Intelligence software, please visit our all new ARCS Solutions Forum.  

Thanks for visiting.  Please check back often.