Category: Uncategorized

Uncategorized

Changing Report Studio defaults

| cognospaul

Report Studio has a number of annoying default settings, using legacy charts instead of the new and improved ones. While RS should save your settings in a cookie, in my experience it doesn’t always remember them. So instead of constantly changing the settings, I find it easier to change the XML sheet controlling the defaults.

Open ..webcontent\pat\profiles\profile_professional.xml

The file is unfortunately sparse. Most of the settings I change don’t appear. Fortunately, we can guess at them.

Unsetting the legacy charts is easy:

<setting name="UseLegacyCharting" defaultValue="false"/>

When working with dimensional data, Cognos has a nasty habit of aliasing MUNs. You drag a MUN into an exxpression and instead of showing you the full value ([Cube].[Dim].[Hier].[Level]->[all].[whatever]) it aliases it to [whatever] and creates a new data item on the report. To turn that off add the following line:

<setting name="AliasMUNs" defaultValue="false"/>

Also when working with dimensional data, if you drag in a member, or level or a set, it will create what’s called an “extended data item”. Once it’s created, you can’t modify it. To disable that behaviour on an existing report go to File->Report Properties-> uncheck Always create extended data items. Add the following line to disable it by default.

<setting name="CreateExtendedDataItemsForNewReports" defaultValue="false"/>

Animated windows annoy me as I’m often connecting remotely to help developers:

<setting name="EnableAnimation" defaultValue="false"/>

While I like the new charts, the preview is annoying:

<setting name="DisableDesignViewChartPreview" defaultValue="false"/>

The following settings are normally hidden. They may be hidden for a reason, but I use them constantly and have never had a problem. Maybe they’re features in development?

Another annoyance is the calculated member classes. When a query expression item is dragged into a crosstab, the node uses the class Crosstab member cell (calculated).

<setting name="UseCalculatedMemberClasses" defaultValue="false"/>

When building reports I occasionally need to switch between Page Design and Page Structure. Having to click on View then “Page Structure” takes too long; wouldn’t it be easier if there were buttons on the toolbar like the following: 

<setting name="ShowViewButtons" defaultValue="true"/>

Do you use table styles? Do you ever wish you could apply the styles to crosstabs?

<setting name="ShowCrosstabTableStyleOptions" defaultValue="true"/>

The next one isn’t incredibly useful, but nice if you like seeing icons in the property sheet:

<setting name="showLabelSmallIcons" defaultValue="true"/>

Forget the tiny icons, do you ever wish you could have the Business Insight style propertysheet in Report Studio?

<setting name="UsePropertySheet2" defaultValue="true"/>

As always, whenever modifying any Cognos internal file, make sure to back up the original file. You will need to delete your temporary files (remember to uncheck “Preserve Favorites website data”) before these take effect.

Uncategorized

Suppressing tree prompts

| cognospaul

This post uses the Sales and Marketing cube as a source. An example report XML is attached as usual.

A while back I wrote an article about why you should never use filters in OLAP. I mentioned there were only two cases where I permitted developers to use them. This is one of those cases.

When you use a tree prompt, Cognos will send a query to the database consisting of the use item, filters, slicers. Whenever you open a node (such as 2005) it will send another query containing children(2005), filters, and slicers. It will then append the results of that query into the tree prompt.

This behavior makes it difficult to control exactly what is returned. The general method of hiding members is to use the filter function. But since the filter is ignored to create the children function, we have to use something else. Slicers will also not work, as they effect the measures.

Instead, we can use detail filters. Cognos will apply the detail filters to the edge nodes, and since the edge node in a tree prompt consists solely of the hierarchy we want filtered, this will work nicely.

The first thing to remember is that Cognos will readily allow you to mix dimensional and relational functions. This is bad as it will result in local processing. So the filter if(substring(caption(currentMember([sales_and_marketing].[Time].[Time])),6,3)
=’Jan’) then (0) else (1) = 1 will result in:

MDX

WITH MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact] AS 'NULL', SOLVE_ORDER = 1, MEMBER_CAPTION = '' SELECT HIERARCHIZE(UNION({[Time].[].CURRENTMEMBER}, [Time]..[Year 2].MEMBERS, ALL)) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing] WHERE ([MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact])

SQL

with
Years4_0_oqpTabStream4 as
    (select
           Years4_0_oqpTabStream."Data Item11/_memberUniqueName"  as  Data_Item11__memberUniqueName,
           Years4_0_oqpTabStream."Data Item11"  as  Data_Item11,
           Years4_0_oqpTabStream."Year/_memberUniqueName"  as  Year__memberUniqueName,
           Years4_0_oqpTabStream."Year"  as  Year4
     from
           TABLE(_ROWSET("Years4.0_oqpTabStream")) Years4_0_oqpTabStream
    )
select distinct
       Years4_0_oqpTabStream4.Data_Item11__memberUniqueName  as  Data_Item1__memberUniqueName,
       case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end   as  Data_Item1,
       Years4_0_oqpTabStream4.Year__memberUniqueName  as  Year__memberUniqueName,
       Years4_0_oqpTabStream4.Year4  as  Year4
 from
       Years4_0_oqpTabStream4
 where
       (case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end  = 1)

It’s dumping the entire hierarchy into memory and processing it in SQL. If the hierarchy is large (I’ve got some clients with hundreds of thousands of members in their hierarchies) this can be problematic.

Instead, we need to use dimensional friendly functions. Let’s start with hiding all members where the Profit Margin is less than 0.405.

For this, it’s a very simple detail filter of [Profit Margin]<0.405. The results will then work on every level:

In the preceding image, March of 2004 is missing from the tree prompt as the Profit Margin for that month was 40.7%. Q1, 2005 is also missing, as the Profit Margin for the entire quarter was 40.7% as well.

Next, we can suppress an entire level. Note that this will not allow you to skip a level; you can’t find the children of a member which isn’t rendered. For this, all you need to do is to filter on the ordinal or levelNumber of the member. roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) < 3

And finally we let’s try suppressing Januaries.
if(roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue(‘_memberCaption’,currentMember([sales_and_marketing].[Time].[Time])) contains ‘Jan’) then (0) else (1) = 1. In this case, it checks that the current level is 3, and that the caption contains ‘Jan’ and returns 0. The MDX is valid:

WITH SET [COG_OQP_INT_s1] AS 'FILTER([Time]..[Year 2].MEMBERS, IIF(([Time].[].CURRENTMEMBER.PROPERTIES("LEVEL_NUMBER") = 3 AND CONTAINS([Time].[].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), "Jan")), 0, 1) = 1)' SELECT [COG_OQP_INT_s1] DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing]

And the result:

In each case, the tree prompt will behave exactly as expected, while suppressing unwanted members.

Report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1">
								<style>
									<defaultStyles>
										<defaultStyle refStyle="pg"/>
									</defaultStyles>
								</style>
								<pageBody>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pb"/>
										</defaultStyles>
									</style>
									<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No suppression</staticValue></dataSource></textItem></contents><style><CSS value="white-space:normal"/></style></tableCell><tableCell><contents><selectWithTree parameter="Parameter1" refQuery="Years"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Only members with Profit Margin &lt; 0.405</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree parameter="Parameter2" refQuery="Years2"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No months</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years3" parameter="Parameter3"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No Januarys</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years4" parameter="Parameter4"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" rowsPerPage="99999" refQuery="Data">
			<crosstabCorner>
				<contents/>
				<style>
					<defaultStyles>
						<defaultStyle refStyle="xm"/>
					</defaultStyles>
				</style>
			</crosstabCorner>

			<noDataHandler>
				<contents>
					<block>
						<contents>
							<textItem>
								<dataSource>
									<staticValue>No Data Available</staticValue>
								</dataSource>
								<style>
									<CSS value="padding:10px 18px;"/>
								</style>
							</textItem>
						</contents>
					</block>
				</contents>
			</noDataHandler>
			<style>
				<CSS value="border-collapse:collapse"/>
				<defaultStyles>
					<defaultStyle refStyle="xt"/>
				</defaultStyles>
			</style>
		<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Profit Margin" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table></contents>
								</pageBody>
							</page>
						</reportPages>
					</layout>
				</layouts>
			<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><queries><query name="Data"><source><model/></source><selection><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Quarter]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Month]</expression></dataItem></selection></query><query name="Years"><source><model/></source><selection><dataItem name="Year" aggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection></query><query name="Years2"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Profit Margin]&lt;0.405</filterExpression></detailFilter></detailFilters></query><query name="Years3"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Level Number"><expression>roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time]))</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression> [Level Number] &lt; 3</filterExpression></detailFilter></detailFilters></query><query name="Years4"><source><model/></source><selection><dataItem name="Data Item1"><expression>if(roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue('_memberCaption',currentMember([sales_and_marketing].[Time].[Time])) contains 'Jan') then (0) else (1)</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Data Item1] = 1</filterExpression></detailFilter></detailFilters></query></queries><reportName>suppressing tree prompts</reportName></report>
Uncategorized

Prompt parameter name as a prompt

| cognospaul

Recently I was presented with an interesting problem. The developer made a chart report which allowed the user to define the x-axis through a radio button. In order to do this the developer dragged all the fields he needed into the query, and used the following expression to change the axis:
#sb(prompt('Category','token'))#

This means if Month was selected in the radio, the x-Axis would be [Month].

Next he set up a drill through to a list report. The user would click on one of the bars, and it would filter the detail report based on the category selected, and the bar clicked. The optional filter is:
#sb(prompt('Category','token'))# = #prompt('Value','string')#
Resolving to [Month] = ‘Jan/2010’ or maybe [Product] = ‘Glowsticks’

The target report is also designed as a standalone, with a dozen prompts on the page. And now we get to the problem. If the user drilled on Country = UK, and then selects France and Belgium, the report will return no results. This makes sense as the country filter is [Country] in (‘France’,’Belgium’) and the Category filter is [Country] = ‘UK’. The expected behaviour is slightly different. The user expects to override the category filter with whatever is selected in the regular prompts. It also has to be accomplished without JavaScript.

I’d welcome ideas on different ways to accomplish this, as the solution I’m about to present is difficult to understand and maintain.

First I changed all of the prompts on the page to match the values the categories were passing. So the filter [Country] = ?p_dest_country? was changed to [Country] = ?Country? and [Month] = ?p_Month? was changed to [Month]=?Month?

The categories filter was changed to:
#sb(prompt(‘Category’,’token’,’1′))# in (#promptmany(prompt(‘Category’,’token’),’string’,sq(prompt(‘Value’,’string’,’1′)))#)

Assuming the user drilled on Country = UK, this will resolve to:

[Country] in (#prompt(‘Country’,’string’,sq(‘UK’))#)

Since the Country parameter in the report is null, it will default to UK, ultimately resolving to:
[Country] in (‘UK’)

Since the drilled value is only in the default parameter of the macro, selecting any value from the prompt will always take precedence.

Please feel free to leave a comment if you can think of another way of handling this.