Tag: prompts

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.

Javascript, Prompts

Quickie – Dynamic select and search size

| cognospaul

Generally the select box of the Select and Search prompt is wide enough to display all the information you need. But there are times where you need it to show everything.

Is the first option there Chaun Yang Sport Equipment Company? Org? IBM does have a way to fix this issue, but it involves modifying one of the internals, specifically the webcontentpromptingproperties.js file. In several of my clients, modifying any internal files is expressly forbidden. So I’ve written a quick and somewhat dirty javascript to handle it.

Drag an HTML item to the left of the prompt:

<div id="selectSearch">

Drag an HTML item to the right of the prompt:

</div>
<script>
  var e=document.getElementById('selectSearch');
  var myselect = e.getElementsByTagName('select')[0];
  if(myselect.childNodes.length>0){myselect.style.width=''}
</script>

This code will find the selectSearch div you wrapped the prompt with, find all of the select tags, and if there are any options the select will expand or shrink to fit.

And finally, as usual (when I remember) the report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</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><HTMLItem>
			<dataSource>
				<staticValue>&lt;div id="selectSearch"&gt;</staticValue>
			</dataSource>
		</HTMLItem><selectWithSearch parameter="Parameter1" refQuery="Query1"><useItem refDataItem="Retailer"/></selectWithSearch><HTMLItem>
			<dataSource>
				<staticValue>&lt;/div&gt;
&lt;script&gt;
  var e=document.getElementById('selectSearch');
  var myselect = e.getElementsByTagName('select')[0];
  if(myselect.childNodes.length&gt;0){myselect.style.width=''}
&lt;/script&gt;</staticValue>
			</dataSource>
		</HTMLItem></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="2011-06-09T13:50:33.233Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Retailer" aggregate="none"><expression>[Sales (query)].[Retailers].[Retailer]</expression></dataItem></selection></query></queries></report>

The XML is for Cognos 10, but this code was tested successfully on all the flavors of Cognos 8.4.