Prompt parameter name as a prompt

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.

Rendering reports in iFrames

There may be times where you want to embed your Cognos reports in other applications. The easiest way to handle this is with an iFrame, using the GET method. This is when you paste all of the parameters into the URL. For example:
http://../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2ffolder%5b%40name%3d%27Cognos%20Launch%27%5d%2freport%5b%40name%3d%27Chart%27%5d&ui.name=Chart&run.outputFormat=&run.prompt=false&cv.toolbar=false&cv.header=false

That would call the report Charts inside the folder “Public Folders\Cognos Launch”, run it in HTML without prompting, hiding the header and toolbar. The iFrame would stretch horizontally and vertically to fill the page. It is possible to pass parameters to the iFrames. &p_Year=2005 would send the value “2005″ to the parameter “Year”. Notice the p_ preceding the parameter name, if your parameter is p_Year, the URL parameter would then be p_p_Year.

This image shows a report consisting of a single graph sitting inside an iframe inside another report consisting solely of two prompts. When a prompt value is selected, the prompt page refreshes instantly, while the child report may slightly longer to run. It is possible to stack many report objects in a single prompt page with this, allowing the prompt page to run instantly, while the sub reports take longer to run.

The Year HTML item is a report expression with '&p_Year='+ParamValue('Year'). If the year parameter is empty, the Year HTML item won’t render. It is important to pass optional parameters in separate HTML items for this reason.

Now to complicate matters a bit, suppose there is a requirement to update the iFrame without refreshing the outer page. Another requirement is to stop using the GET method (in which parameters are taken from the URL) and to use POST. There are many reasons why POST is preferable to GET, but I’m not going into them now. Instead, let’s talk about the cognosLaunchInWindow method. The basic idea is the same, but we’re not passing the URL directly to the iFrame. We still need to pass all of the same parameters. Look at the following code:

function runReport()
{
cognosLaunchInWindow
(
'Report', "toolbar=no"
, 'ui.gateway' , cognosURL
, 'ui.tool' , 'CognosViewer'
, 'ui.action' , 'run'
, 'ui.object' , "/content/folder[@name='Cognos Launch']/report[@name='Chart']"
, "run.prompt" ,"false"
, "ui.toolbar" ,"false"
, "ui.header" ,"false"
, "cv.toolbar" ,"false"
, "cv.header" ,"false"
, "p_Year" , iYear
, "p_ProductLineCode", iProductLineCode
)
}


When this function is run, Cognos will attempt to run the report Chart under the folder Cognos Launch in the window or frame named “Report”. It will not prompt, but attempt to populate the prompts with the global JS variables iYear and iProductLineCode. The full script I used for this is:

<script language="JavaScript" src="../cognoslaunch.js"></script>
<script language="javascript">
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
   { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

var cognosURL = window.location.pathname;
var iYear = '';
var iProductLineCode = '';
var YearList = fW._oLstChoices_Year;
var ProductLineList = fW._oLstChoices_ProductLine;
function runReport()
{
  cognosLaunchInWindow
  (
      'Report', "toolbar=no"
    , 'ui.gateway'  , cognosURL
    , 'ui.tool'  , 'CognosViewer'
    , 'ui.action'  , 'run'
    , 'ui.object' , "/content/folder[@name='Cognos Launch']/report[@name='Chart']"
    , "run.prompt" ,"false"
    , "ui.toolbar" ,"false"
    , "ui.header" ,"false"
    , "cv.toolbar" ,"false"
    , "cv.header" ,"false"
    , "p_Year" , iYear
    , "p_ProductLineCode", iProductLineCode
  )
}

YearList.onchange = new Function("iYear=YearList.value;runReport();");
ProductLineList.onchange = new Function("iProductLineCode=ProductLineList.value;runReport();");


</script>

Once a value is selected from either prompt, it will populate the associated JS variable with the value, and run the report. This allows for dashboards that do not be refreshed. Additionally, you could stick the function into another application to programmatically call Cognos reports. Just make sure of your license agreement before you start doing it on a widespread basis.

Both the iFrame source method, and the CognosLaunch method work with URL parameters. You can learn more about URL parameters through the admin and security guide, or by reading .

The XML for the frame report:

<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 Sales (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><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%;height:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><selectValue parameter="Year" refQuery="Years" required="false" name="_Year"><useItem refDataItem="Year"/></selectValue><selectValue parameter="ProductLineCode" refQuery="ProductLineCode" required="false" name="_ProductLine"><useItem refDataItem="Product line code"><displayItem refDataItem="Product line"/></useItem></selectValue><HTMLItem>
			<dataSource>
				<staticValue>&lt;script language="JavaScript" src="../cognoslaunch.js"&gt;&lt;/script&gt;
&lt;script language="javascript"&gt;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
   { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

var cognosURL = window.location.pathname;
var iYear = '';
var iProductLineCode = '';
var YearList = fW._oLstChoices_Year;
var ProductLineList = fW._oLstChoices_ProductLine;
function runReport()
{
  cognosLaunchInWindow
  (
      'Report', "toolbar=no"
    , 'ui.gateway'  , cognosURL
    , 'ui.tool'  , 'CognosViewer'
    , 'ui.action'  , 'run'
    , 'ui.object' , "/content/folder[@name='Cognos Launch']/report[@name='Chart']"
    , "run.prompt" ,"false"
    , "ui.toolbar" ,"false"
    , "ui.header" ,"false"
    , "cv.toolbar" ,"false"
    , "cv.header" ,"false"
    , "p_Year" , iYear
    , "p_ProductLineCode", iProductLineCode
  )
}

YearList.onchange = new Function("iYear=YearList.value;runReport();");
ProductLineList.onchange = new Function("iProductLineCode=ProductLineList.value;runReport();");


&lt;/script&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell colSpan="1"><contents><HTMLItem description="iFrame_Report">
			<dataSource>
				<staticValue>&lt;iframe
  id='Report'
  name='Report'
  src=''
  width=100%
  height=100%
&gt;
&lt;/iframe&gt;
&lt;script&gt; runReport();&lt;/script&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents><style><CSS value="height:100%;vertical-align:top;text-align:center"/></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="2011-06-09T13:52:15.420Z" output="no"/></XMLAttributes><queries><query name="Years"><source><model/></source><selection><dataItem name="Year" aggregate="none"><expression>[Sales (query)].[Time].[Year]</expression></dataItem></selection></query><query name="ProductLineCode"><source><model/></source><selection><dataItem name="Product line code" aggregate="none"><expression>[Sales (query)].[Products].[Product line code]</expression></dataItem><dataItem name="Product line" aggregate="none" sort="ascending"><expression>[Sales (query)].[Products].[Product line]</expression></dataItem></selection></query></queries><reportName>PromptsCognosLaunch</reportName></report>

And the XML for the chart report, just remember to save it under root\Cognos Launch\Chart, or to change the path in the function.

<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 Sales (query)']/model[@name='model']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<queries>
					<query name="Query1">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Month]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Month (numeric)" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Month (numeric)]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
					<detailFilters><detailFilter use="optional"><filterExpression>[Sales (query)].[Time].[Year]=?Year?</filterExpression></detailFilter><detailFilter use="optional"><filterExpression>[Sales (query)].[Products].[Product line code]=?ProductLineCode?</filterExpression></detailFilter></detailFilters></query>
				</queries>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
								<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
									<contents>
										<combinationChart showTooltips="true" maxHotspots="10000" refQuery="Query1" name="Combination Chart1">
								<legend>
									<legendPosition>
										<relativePosition/>
									</legendPosition>
									<legendTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="lx"/>
											</defaultStyles>
										</style>
									</legendTitle>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="lg"/>
										</defaultStyles>
									</style>
								</legend>
								<ordinalAxis>
									<axisTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</ordinalAxis>
								<numericalAxisY1>
									<axisTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<gridlines color="#cccccc"/>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</numericalAxisY1>
								<combinationChartTypes>
									<bar/>
								</combinationChartTypes>
								<style>
									<defaultStyles>
										<defaultStyle refStyle="ch"/>
									</defaultStyles>
								</style>
							<commonClusters><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Year"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers><chartNestedNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Month"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents><sortList><sortItem refDataItem="Month (numeric)"/></sortList></chartNodeMember></chartNodeMembers></chartNode></chartNestedNodes></chartNode></chartNodes></commonClusters><defaultChartMeasure refDataItem="Quantity"/></combinationChart>
									</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:52:15.420Z" output="no"/></XMLAttributes><reportName>Chart</reportName></report>

8.2: Export all report XMLs to file system

One of my clients decided they want to maintain a local copy of the folder structure and report XMLs. So if they had report “Sales” under “Public Folders\Sales\Western Europe\” they would want a copy at “c:\Cognos\Sales\Western Europe\Sales.xml”. They don’t have any of the third party tools like Meta Manager or Motio, so they need to do everything manually.

While I’m not a scripting expert, I have done some very minor work with vbscript. With vbscript I could open a spreadsheet, copy cell values, and create the file in the appropriate place.

The first thing to do is to create the report that will be used as the base for the script.

The report should be a simple list that contains the report path, report name and the XML. In order to do this we to create a datasource connection to the Cognos content store. Once that’s done a small change needs to be made to the database. Cognos stores report details with the folder ID it’s in. The folder ID contains a reference to the parent folder, but without creating a loop it will be impossible (not really, but I’m lazy) to get the full folder path.

Instead we can create a User Defined Function in the content store. The UDF is database dependent, so it will need to be modified when working with another database (like SQL Server or DB2) or when working against a different version (8.4, 10).

I found this UDF at Mogawad Blog:

CREATE OR REPLACE FUNCTION C8.getpath (p_pcmid IN NUMBER)
RETURN NVARCHAR2
IS
l_parentname   NVARCHAR2 (100);
l_pcmid        Number;
BEGIN
IF p_pcmid = 0
THEN
RETURN 'root';
ELSE
SELECT n.NAME object_name, o.PCMID
INTO l_parentname, l_pcmid 
FROM cmobjnames n, cmobjects o
WHERE o.cmid = n.cmid AND o.cmid = p_pcmid and isdefault=1;
END IF;

RETURN getpath (l_pcmid)||'\'||l_parentname;
EXCEPTION
WHEN OTHERS
THEN
RETURN sqlerrm;
END getpath;

With this UDF saved in the content store it is now possible to write the following SQL in RS:

SELECT n.NAME object_name, getpath (o.pcmid) path, c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = 'REPORT'
and getpath (o.pcmid) like 'root\Public Folders\Sales%'

Before we can run the report, it’s important to mention that some reports XMLs can be very long. The longest report at this client is roughly 650000 characters long. Some of my other clients have significantly larger reports. Because the vbscript uses Excel to access the data, we’d run into a problem. Excel can’t handle large fields – it would simply truncate them. Inside the query, it’s necessary to create seperate data items to split the xml. substring([SQL1].[SPEC],1,30000), substring([SQL1].[SPEC],30001,30000), substring([SQL1].[SPEC],60001,30000) and so on. The report I mentioned before took 22 of these fields.

Finally, inside the list itself, wrap each of the fields with quotes. Cognos delimited the fields with tabs, and considering that the XML might have tabs and newlines embedded inside it, it’s best to put a delimited that Excel can easily recognize.

Report XML below:

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
	<modelPath>/content/package[@name='Agg CN ISUP Quality 15 Min']/model[@name='model']</modelPath>
	<layouts>
		<layout>
			<reportPages>
				<page class="pg" name="Page1">
					<pageBody class="pb">
						<contents><list class="ls" refQuery="Query1">
								<style>
									<CSS value="border-collapse:collapse"/>
								</style>
								<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="OBJECT_NAME"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="OBJECT_NAME"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="PATH"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="PATH"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="SPEC1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="SPEC1"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec2"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec2"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec3"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec3"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec4"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec4"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec5"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec5"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec6"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec6"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec7"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec7"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec8"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec8"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec9"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec9"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec10"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec10"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec11"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec11"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec12"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec12"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec13"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec13"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec14"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec14"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec15"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec15"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec16"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec16"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec17"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec17"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec18"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec18"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec19"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec19"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec20"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec20"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec21"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec21"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec22"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec22"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec23"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec23"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec24"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec24"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Spec25"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Spec25"/></dataSource></textItem><textItem><dataSource><staticValue>"</staticValue></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
						</contents>
					</pageBody>
				</page>
			</reportPages>
		</layout>
	</layouts>
<queries><query name="Query1">
			<source>
				<sqlQuery name="SQL1" dataSource="Cognos CS">
					<sqlText>SELECT n.NAME object_name, getpath (o.pcmid) path, c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = 'REPORT'
and getpath (o.pcmid) like 'root\Public Folders\Sales%'</sqlText>
					<mdProjectedItems><mdProjectedItem name="OBJECT_NAME"/><mdProjectedItem name="PATH"/><mdProjectedItem name="SPEC"/></mdProjectedItems></sqlQuery>
			</source>
			<selection autoSummary="false"><dataItem name="OBJECT_NAME"><expression>[SQL1].[OBJECT_NAME]</expression></dataItem><dataItem name="PATH"><expression>[SQL1].[PATH]</expression></dataItem><dataItem name="SPEC1"><expression>substring([SQL1].[SPEC],1,30000)</expression></dataItem><dataItem name="Spec2"><expression>substring([SQL1].[SPEC],30001,30000)</expression></dataItem><dataItem name="Spec3"><expression>substring([SQL1].[SPEC],60001,30000)</expression></dataItem><dataItem name="Spec4"><expression>substring([SQL1].[SPEC],90001,30000)</expression></dataItem><dataItem name="Spec5"><expression>substring([SQL1].[SPEC],120001,30000)</expression></dataItem><dataItem name="Spec6"><expression>substring([SQL1].[SPEC],150001,30000)</expression></dataItem><dataItem name="Spec7"><expression>substring([SQL1].[SPEC],180001,30000)</expression></dataItem><dataItem name="Spec8"><expression>substring([SQL1].[SPEC],210001,30000)</expression></dataItem><dataItem name="Spec9"><expression>substring([SQL1].[SPEC],240001,30000)</expression></dataItem><dataItem name="Spec10"><expression>substring([SQL1].[SPEC],270001,30000)</expression></dataItem><dataItem name="Spec11"><expression>substring([SQL1].[SPEC],300001,30000)</expression></dataItem><dataItem name="Spec12"><expression>substring([SQL1].[SPEC],330001,30000)</expression></dataItem><dataItem name="Spec13"><expression>substring([SQL1].[SPEC],360001,30000)</expression></dataItem><dataItem name="Spec14"><expression>substring([SQL1].[SPEC],390001,30000)</expression></dataItem><dataItem name="Spec15"><expression>substring([SQL1].[SPEC],420001,30000)</expression></dataItem><dataItem name="Spec16"><expression>substring([SQL1].[SPEC],450001,30000)</expression></dataItem><dataItem name="Spec17"><expression>substring([SQL1].[SPEC],480001,30000)</expression></dataItem><dataItem name="Spec18"><expression>substring([SQL1].[SPEC],510001,30000)</expression></dataItem><dataItem name="Spec19"><expression>substring([SQL1].[SPEC],540001,30000)</expression></dataItem><dataItem name="Spec20"><expression>substring([SQL1].[SPEC],570001,30000)</expression></dataItem><dataItem name="Spec21"><expression>substring([SQL1].[SPEC],600001,30000)</expression></dataItem><dataItem name="Spec22"><expression>substring([SQL1].[SPEC],630001,30000)</expression></dataItem><dataItem name="Spec23"><expression>substring([SQL1].[SPEC],660001,30000)</expression></dataItem><dataItem name="Spec24"><expression>substring([SQL1].[SPEC],690001,30000)</expression></dataItem><dataItem name="Spec25"><expression>substring([SQL1].[SPEC],720001,30000)</expression></dataItem></selection>
		</query>
	</queries></report>

Run it as CSV. Depending on the number of reports you’re retrieving, I recommend stopping here to take a break. Get a coffee or something. Once it finishes running and you save it, check it to make sure it’s worked correctly. The first column should be the Report name, the second the full path. Every field onwards contains the xml.

Now for the script. As I said before, I’m not a scripting expert. Save it as whatever.vbs, and drag the csv export to it. It should loop through, creating directories and report xmls.

vbscript below:

Option Explicit



Dim objExcel, objWorkbook, objWorksheet, row, rows, col, msg, ThisTxt, ThisLen, ctrCells, objFSO, Path
Dim ipFN, objFileName, objTextFile, ipLen, opLen, WshShell, ReportFullPath
Dim i
Const swDebug = false

If Wscript.Arguments.Count = 0 then
  wscript.echo "Please drag a file to the icon!"
  wscript.quit
end if

msg = ""
i = 0

' Start the application
IpFN = trim(wscript.arguments(0)) ' Complete input file name
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.visible = false ' make Excel invisible
Set objWorkbook = objExcel.Workbooks.Open(IPFN)
Set objWorksheet = objWorkbook.Worksheets(1) '<---- Hardcode the worksheet number
rows = objWorksheet.UsedRange.Rows.Count


for Row = 2 to rows
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Path = strCleanPath( Replace(objWorksheet.Cells(Row,2),"root\Public Folders\",""))
  Path = "c:\" & Path
  CreateFolderTree(Path)
  ReportFullPath = Path & "\" & strClean(objWorksheet.Cells(Row,1).Value) 
  ReportFullPath = left(ReportFullPath,255)  
  objFileName = ReportFullPath & ".xml"
  Set objTextFile = objFSO.CreateTextFile (objFileName, True, True)
  objTextFile.WriteLine(objWorksheet.Cells(Row,3).Value&objWorksheet.Cells(Row,4).Value&objWorksheet.Cells(Row,5).Value&objWorksheet.Cells(Row,6).Value&objWorksheet.Cells(Row,7).Value&objWorksheet.Cells(Row,8).Value&objWorksheet.Cells(Row,9).Value&objWorksheet.Cells(Row,10).Value&objWorksheet.Cells(Row,11).Value&objWorksheet.Cells(Row,12).Value&objWorksheet.Cells(Row,13).Value&objWorksheet.Cells(Row,14).Value&objWorksheet.Cells(Row,15).Value&objWorksheet.Cells(Row,16).Value&objWorksheet.Cells(Row,17).Value&objWorksheet.Cells(Row,18).Value&objWorksheet.Cells(Row,19).Value&objWorksheet.Cells(Row,20).Value&objWorksheet.Cells(Row,21).Value&objWorksheet.Cells(Row,22).Value&objWorksheet.Cells(Row,23).Value&objWorksheet.Cells(Row,24).Value&objWorksheet.Cells(Row,25).Value&objWorksheet.Cells(Row,26).Value)
  objTextFile.close
next

objExcel.Application.quit ' Close the spreadsheet

Set objWorksheet = nothing
Set objWorkbook = nothing
Set objExcel = nothing

  wscript.echo "Done!"


'--------------------
'- Create Folder Path
'--------------------
Sub CreateFolderTree(strTempPath)
   If Not objFSO.FolderExists(objFSO.GetParentFolderName(strTempPath)) Then CreateFolderTree(objFSO.GetParentFolderName(strTempPath))
   If Not objFSO.FolderExists(strTempPath) Then objFSO.CreateFolder(strTempPath)
End Sub   


Function strClean (strtoclean)
  Dim objRegExp, outputStr
  Set objRegExp = New Regexp

  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+"
  outputStr = objRegExp.Replace(strtoclean, "-")

  objRegExp.Pattern = "\-+"
  outputStr = objRegExp.Replace(outputStr, "-")

  strClean = outputStr
End Function

Function strCleanPath (strtoclean)
  Dim objRegExp, outputStr
  Set objRegExp = New Regexp

  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "[(?*"",<>&#~%{}+_.@:\/!;]+"
  outputStr = objRegExp.Replace(strtoclean, "-")

  objRegExp.Pattern = "\-+"
  outputStr = objRegExp.Replace(outputStr, "-")

  strCleanPath = outputStr
End Function

Important to note: if the length of the report name plus path exceeds 255 characters, it will truncate it.

Cognos 10.1.1 bug: Charts breaking when switching between tabs

An annoying bug has been reported by several of my clients.

Any portal tab containing a chart will occasionally break when the user clicks away then returns:

If the user were to click on Public Folders, then return to the portal tab, suddenly the chart images are broken.

While I can’t explain why this is happening, I’m guessing that Cognos is flushing the image from the cache when the user leaves the page. When the user returns the image is no longer in the cache, so it’s returns a broken image.

Fortunately the refreshing the report will return the chart so this gives us a partial solution. If we can write a script that detects the state of the image (if it’s broken or not) we can have the portlet refresh.

First the report should be wrapped in a div with the display set to none. This will prevent users from seeing the broken charts. Second the chart itself should be wrapped in a div to give the function a place to start looking for broken images.

The function itself is fairly simple:

function checkImg()
{
var reportWrapper = document.getElementById('reportWrapper');
var chartWrapper = document.getElementById('chartWrapper');
var message = document.getElementById('message');
var img = chartWrapper.getElementsByTagName('IMG');
message.innerHTML = "checking image";
if (img[0].readyState == 'uninitialized') {refreshReport();message.innerHTML = "refreshing report";} 
else {reportWrapper.style.display="";message.innerHTML = "";}

}

It checks the image’s readyState attribute. If it’s unitialized, the image is broken and the report should be refreshed, otherwise everything is fine and show the report.

The refresh function is from one of the IBM knowledge base articles.

function refreshReport()
{
var intval;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)  
{

    fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );

}
var preFix = "";
if (fW.elements["cv.id"])
{

    preFix = fW.elements["cv.id"].value;

}    
var nameSpace = "oCV" + preFix;
if(intval!="")
{

    self.clearInterval(intval);
    intval="";

}
self["RunReportInterval"] = self.setInterval( nameSpace + ".getRV().RunReport()",'0' );
intval = self["RunReportInterval"];
}

Finally the checkImg function needs to be called. It can’t be called as soon as the page is loaded, because the chart image itself takes a moment to load. If the function is called before the chart is loaded, it will refresh the page in an infinite loop. Instead it’s best to wait a moment. I find that 1 second works well.

setTimeout("checkImg()",1000);

Now when the page loads it will wait 1 second and check the chart. If the image is broken it will refresh the page:

It’s worth mentioning that this is a band-aid solution (and not a great one either). The true fix needs to come from IBM. There is an APAR open for this issue, but so far no hotfix. Slower computers may take longer to load the image, and may result in the user being stuck in an infinite loop. Additionally the report will only display after the function has a chance to check the image, effectively increasing the load time of the report by however long is set in the setTimeout.

Report XML: (Remember to create a page and add this to a cognos viewer)

<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"/>
				<queries>
					<query name="Query1">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Gross margin" aggregate="automatic"><expression>[Sales (query)].[Gross margin]</expression><XMLAttributes><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
					</query>
				</queries>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
								<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
									<contents>
										<HTMLItem description="report Display None">
			<dataSource>
				<staticValue>&lt;div id="reportWrapper" style="display:none"&gt;</staticValue>
			</dataSource>
		</HTMLItem><HTMLItem description="Chart Wrapper">
			<dataSource>
				<staticValue>&lt;div id="chartWrapper"&gt;</staticValue>
			</dataSource>
		</HTMLItem><combinationChart showTooltips="true" maxHotspots="10000" refQuery="Query1" name="Combination Chart1">
								<legend>
									<legendPosition>
										<relativePosition/>
									</legendPosition>
									<legendTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="lx"/>
											</defaultStyles>
										</style>
									</legendTitle>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="lg"/>
										</defaultStyles>
									</style>
								</legend>
								<ordinalAxis>
									<axisTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</ordinalAxis>
								<numericalAxisY1>
									<axisTitle refQuery="Query1">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<gridlines color="#cccccc"/>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</numericalAxisY1>
								<combinationChartTypes>
									<bar/>
								</combinationChartTypes>
								<style>
									<defaultStyles>
										<defaultStyle refStyle="ch"/>
									</defaultStyles>
								</style>
							<commonClusters><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Product line"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></commonClusters><defaultChartMeasure refDataItem="Gross margin"/></combinationChart>
									<HTMLItem description="closing divs and script">
			<dataSource>
				<staticValue>&lt;/div&gt;
&lt;/div&gt;
&lt;div id="message"&gt;
&lt;/div&gt;
&lt;script&gt;

function checkImg()
{
var reportWrapper = document.getElementById('reportWrapper');
var chartWrapper = document.getElementById('chartWrapper');
var message = document.getElementById('message');
var img = chartWrapper.getElementsByTagName('IMG');
message.innerHTML = "checking image";
if (img[0].readyState == 'uninitialized') {refreshReport();message.innerHTML = "refreshing report";} 
else {reportWrapper.style.display="";message.innerHTML = "";}

}

function refreshReport()
{
var intval;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)  
{

    fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );

}
var preFix = "";
if (fW.elements["cv.id"])
{

    preFix = fW.elements["cv.id"].value;

}    
var nameSpace = "oCV" + preFix;
if(intval!="")
{

    self.clearInterval(intval);
    intval="";

}
self["RunReportInterval"] = self.setInterval( nameSpace + ".getRV().RunReport()",'0' );
intval = self["RunReportInterval"];
}

setTimeout("checkImg()",1000);
&lt;/script&gt;
</staticValue>
			</dataSource>
		</HTMLItem></contents>
								</pageBody>
								<pageHeader>
									<contents>
										<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
											<contents>
												<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
													<dataSource>
														<staticValue>This works</staticValue>
													</dataSource>
												</textItem>
											</contents>
										</block>
									</contents>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="ph"/>
										</defaultStyles>
										<CSS value="padding-bottom:10px"/>
									</style>
								</pageHeader>
								<pageFooter>
									<contents>
										<table>
											<tableRows>
												<tableRow>
													<tableCells>
														<tableCell>
															<contents>
																<date>
																	<style>
																		<dataFormat>
																			<dateFormat/>
																		</dataFormat>
																	</style>
																</date>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:left;width:25%"/>
															</style>
														</tableCell>
														<tableCell>
															<contents>
																<pageNumber/>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:center;width:50%"/>
															</style>
														</tableCell>
														<tableCell>
															<contents>
																<time>
																	<style>
																		<dataFormat>
																			<timeFormat/>
																		</dataFormat>
																	</style>
																</time>
															</contents>
															<style>
																<CSS value="vertical-align:top;text-align:right;width:25%"/>
															</style>
														</tableCell>
													</tableCells>
												</tableRow>
											</tableRows>
											<style>
												<defaultStyles><defaultStyle refStyle="tb"/></defaultStyles>
												<CSS value="border-collapse:collapse;width:100%"/>
											</style>
										</table>
									</contents>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pf"/>
										</defaultStyles>
										<CSS value="padding-top:10px"/>
									</style>
								</pageFooter>
							</page>
						</reportPages>
					</layout>
				</layouts>
			<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:50:33.233Z" output="no"/></XMLAttributes><reportName>Test 1</reportName></report>

Using OLAP functions without a DMR model

This is on Cognos 10.1.1 with the GO Data Warehouse (query) package.

I was asked a somewhat difficult question. On a relational model the report shows a crosstab years with another attribute nested in rows and measures in columns. How can the growth % of the years from the previous year to the current year be displayed?

Normally I would create data items for each measure for each year that needs to be included in the expression. Something along the lines of
case when [Year] = extract(‘year’,current_date) then [Measure] else 0 end
The issue with this is that this is a crosstab – how can you make it accommodate different measures on different edges?

A better solution would be one that mimics dimensional functionality. I want to divide 2011 by 2010, with all of the measures acting accordingly.

In a dimensional query I would simply do [Current Year]/[Previous Year]. If I didn’t have any calculated members in the cube, I might try something like this:

item(filter([Year Level],[Year Caption] = #sq(timestampMask(‘yyyy’,$current_timestamp))#),0)

The filter returns a set of all of the years that match the current year, and the item takes that set and returns the first member in it.

Because this is a relational model, OLAP functions shouldn’t work.

(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1

But they do.

My guess is that Cognos is implicitly converting the [Year] field into a level, like it would be in a DMR model. The odd thing is, that while it works, I don’t see it reflected in the SQL of the xtab.

Cognos SQL:

select 
       SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN  as  Order_method_typekey,
       GO_TIME_DIM.CURRENT_YEAR  as  Yearkey,
       XSUM(SLS_SALES_FACT.QUANTITY  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Quantity,
       XSUM(SLS_SALES_FACT.SALE_TOTAL  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Revenue,
       XSUM(SLS_SALES_FACT.GROSS_PROFIT  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Gross_profit
 from 
       great_outdoors_warehouse..GOSALESDW.SLS_ORDER_METHOD_DIM SLS_ORDER_METHOD_DIM,
       great_outdoors_warehouse..GOSALESDW.GO_TIME_DIM GO_TIME_DIM,
       great_outdoors_warehouse..GOSALESDW.SLS_SALES_FACT SLS_SALES_FACT
 where 
       (GO_TIME_DIM.CURRENT_YEAR in (2006,2007)) and 
       (SLS_SALES_FACT.ORDER_METHOD_KEY = SLS_ORDER_METHOD_DIM.ORDER_METHOD_KEY) and 
       (SLS_SALES_FACT.ORDER_DAY_KEY = GO_TIME_DIM.DAY_KEY)
 group by 
       SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,
       GO_TIME_DIM.CURRENT_YEAR

Running a trace in SQL Server shows that it’s not doing anything different:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN" AS "Order_method_typekey", "GO_TIME_DIM"."CURRENT_YEAR" AS "Yearkey", sum("SLS_SALES_FACT"."QUANTITY") AS "Quantity", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "Revenue", sum("SLS_SALES_FACT"."GROSS_PROFIT") AS "Gross_profit" from "GOSALESDW"."SLS_ORDER_METHOD_DIM" "SLS_ORDER_METHOD_DIM", "GOSALESDW"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM"."CURRENT_YEAR" in (2006, 2007) and "SLS_SALES_FACT"."ORDER_METHOD_KEY" = "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" and "SLS_SALES_FACT"."ORDER_DAY_KEY" = "GO_TIME_DIM"."DAY_KEY" group by "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN", "GO_TIME_DIM"."CURRENT_YEAR"',1
select @p1

So it must be doing the calculation locally. Local processing is frowned upon, so you should try to find another way to handle this. Ideally building a real cube and using real OLAP functionality.

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><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1" rowsPerPage="999999">
			<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>
		<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="2007 %" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="2007 %"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles><dataFormat><percentFormat/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e2"><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><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Gross profit" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></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="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Order method type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method type]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales fact].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales fact].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Gross profit" aggregate="total"><expression>[Sales (query)].[Sales fact].[Gross profit]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="2007 %"><expression>(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_relationalExpression" value="true" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Year] in (2006,2007,2008)</filterExpression></detailFilter></detailFilters></query></queries><reportName>test</reportName></report>

Why detail filters should never be used in OLAP

The following post is be based on the Sales and Marketing cube on Cognos 10.1.1, but it should be true for all versions of Cognos.

One of the most common questions I am asked is why I am so intensely against detail filters on OLAP queries. Official Cognos guides show examples of detail filters on OLAP, and it’s much faster to develop when using detail filters, why shouldn’t I allow them?

To begin detail filters prevent authors from answering complex business questions. What is the profit margin per year for every product that had a profit margin exceeding 60% in the years 2006 and 2007? A filter of [Profit Margin]>=0.6 doesn’t allow us to specify which years. Let’s make the business question even easier: What is the profit margin, per year, for each project where the profit margin for all the years exceeds 60%?

These are the results I expect to receive:

The rows node has the following expression:
filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]>=0.6)

Native MDX for the report:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m1], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m1] AS 'AGGREGATE([Time]..[Year 2].MEMBERS, [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m1]' SELECT UNION([Time]..[Year 2].MEMBERS, {[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, [MEASURES]..[Profit Margin] >= 0.6) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]

The summary column makes it a bit complex, but otherwise the MDX is easy to read.

Let’s remove the filter function and apply the [Profit Margin]>=0.6 as a detail filter

MDX:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_INT_m1], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m3] AS 'AGGREGATE(FILTER([Time]..[Year 2].MEMBERS, NOT ISEMPTY([Product brand]..[@MEMBER].[COG_OQP_INT_m2])), [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m3]' MEMBER [Product brand]..[@MEMBER].[COG_OQP_INT_m2] AS 'IIF(([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6, 1, NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = '' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin] AS '[MEASURES]..[@MEMBER].[COG_OQP_INT_m1]', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_INT_m1] AS 'IIF([MEASURES]..[Profit Margin] >= 0.6, [MEASURES]..[Profit Margin], NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' SET [COG_OQP_INT_s1] AS 'CROSSJOIN([Time]..[Year 2].MEMBERS, [Products]..[Product 4].MEMBERS)' SELECT UNION(FILTER([Time]..[Year 2].MEMBERS, COUNT(FILTER([Products]..[Product 4].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0), HEAD({[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, IIF(COUNT(FILTER([COG_OQP_INT_s1], ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0, 1, 0)), ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, COUNT(FILTER([Time]..[Year 2].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]

Suddenly we have 16 products instead of 13. The values are also artificially inflated for some of the products that are in the correct crosstab. What’s going on? The detail filter is being applied on intersections of the year and product. Any intersection that is below 60% is being suppressed, so the summary is showing the values for the unsuppressed years for each product. Any product that had any year exceeding 60% is displayed, and the summary column would indicate that the calculated total for all the years also exceeds 60%. Bad business decisions might be made because of this faulty data.

Have a report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us" interactivePageBreakByFrame="true">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2010-06-24T18:14:00.360Z']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<queries>
					<query name="Query1">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]&gt;=0.6)</expression></dataItem><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><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection>
					</query>
				<query name="Query2">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product]</expression></dataItem><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><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection>
					<detailFilters><detailFilter><filterExpression>[Profit Margin]&gt;=0.6</filterExpression></detailFilter></detailFilters></query></queries>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
								<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
									<contents>
										<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
											<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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>
												<defaultStyles>
													<defaultStyle refStyle="xt"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse"/>
											</style>
										<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab>
									<crosstab refQuery="Query2" horizontalPagination="true" name="Crosstab2">
											<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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>
												<defaultStyles>
													<defaultStyle refStyle="xt"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse"/>
											</style>
										<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></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="2010-06-24T18:14:00.390Z" output="no"/></XMLAttributes></report>

Essbase Global Variables and DQM

One of my clients was running into a problem with migrating their reports to the DQM. The reports took 15 seconds to run in Compatible Mode, but in DQM failed with the XQE-PLN-0001 error:

Even by Cognos standards this is a bit obscure.

After a bit of troubleshooting I narrowed the problem to how they were dealing with Essbase Global Variables. When global variables were placed in the slicers, they functioned correctly and the mdx looked clean. Cognos put them into tuples exactly as I would expect. When nested in crosstabs they also worked. They can’t be put into tuple expressions. tuple([cube].[Measure],[cube].[Global Variable]) will return an error.

In the compatible mode the client found a work-around with item([cube].[Global Variable],0). This essentially turns the variable into a member. This worked and the report took 15 seconds.

Dynamic Query Mode has a much stricter parser. Since the global variables are neither sets or members, it is very difficult to use them. We can, however, trick Cognos into using them as members by converting them to calculated members with a specified hierarchy.

Changing

tuple(
    [Fm4].[Scenario].[Scenario]->:[DO].[Scenario].[Actual]
  , item([Fm4].[CED_ActualVer_Reports],0)
  , item([Fm4].[CED_ActCurrentYear],0)
  , item([Fm4].[CED_ActualRecentlyClosedQtr],0)
)

to

tuple(
    [Fm4].[Scenario].[Scenario]->:[DO].[Scenario].[Actual]
  , member(total(currentMeasure within set [Fm4].[CED_ActualVer_Reports]), 'a','a',[Fm4].[Version].[Version])
  , member(total(currentMeasure within set [Fm4].[CED_ActCurrentYear]), 'b','b',[Fm4].[Year].[Year])
  , member(total(currentMeasure within set [Fm4].[CED_ActualRecentlyClosedQtr]), 'c','c',[Fm4].[Period].[Period])
)

completely fixed the problem. As a benchmark test, the client took the changes I made and applied them to the Compatible Query Mode. In the CQM the report took 10 seconds (from 15). The DQM ran the report in under a second. It took a few trials to convince some people that the report was actually running and not being retrieved from cache.

The member function takes the parameters
1. value expression (a number, a measure, a total within a set.
2. ID (must be unique in the entire query)
3. Caption (what is displayed if the calculated member is used in an edge)
4. Hierarchy (generally optional, this explicitly sets the hierarchy of the calculated member. If no hierarchy is selected it wil default to the measures.

It’s worth noting the summary function I used was total(). When working with non-additive measures, it is recommended to use the aggregate function or the explicit summary function for the particular measure.

When using this method to create tuples, it is very important to correctly plan the query. A tuple cannot have two parameters from the same hierarchy, this includes measures. Conceptually it is easy to understand that, as you cannot find the intersection between two points on the same line. As mentioned before if no hierarchy is selected in the member function, it will default to the measures. In the case of a tuple, measures are considered part of a hierarchy and the same limitation applies. You cannot find the intersection between Quantity of Sales and Profit Margin, for instance. As such, some analysis of the query and Global Variables must be done before you can create the calculated member.

I created a test crosstab with the various Global Variables, and looked at the MDX (Tools–>Show Generated SQL/MDX). The crosstab has Actual Results (a member), CED_ActualVer_Reports (Global Variable), CED_ActCurrentYear (Global Variable), and CED_ActualRecentlyClosedQtr (Global Variable) nested in the columns and the measure Net Revenue in the rows.

SELECT
{([Scenario].[Actual], [Version].[OutsidePresentations], [Year].[FY11], [Period].[YearTotal].[Q4])} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(0),
{[Account].[Chart of Accounts].[ac_NET_INCOME].[ac_INC_AFTER_TAX].[ac_INC_BEFORE_TAX].[ac_INC_OPERATING].[ac_GROSS_PROFIT].[ac_SALES]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(1)
FROM [CED.Fm]
WHERE ([ActivityType].[at_TotalActivityTypeDim], [Geography].[mk_TotalMarketDim], [Entity].[Global BUs], [Company].[co_GP_CFO])


Using this I can see that CED_ActualVer_Reports comes from the Version hierarchy, CED_ActCurrentYear from the Year and CED_ActualRecentlyClosedQtr from the Period. The calculated members are then created accordingly and Cognos treats them as proper members from the hierarchy.

The same crosstab, when using the member function, looks like this:

WITH
MEMBER [Version].[XQE_V5M_a_CM2] AS '([Version].[OutsidePresentations])', SOLVE_ORDER = 4
MEMBER [Year].[XQE_V5M_b_CM1] AS '([Year].[FY11])', SOLVE_ORDER = 4
MEMBER [Period].[XQE_V5M_c_CM0] AS '([Period].[YearTotal].[Q4])', SOLVE_ORDER = 4
MEMBER [Scenario].[XQE_V5M_Act Current Year_CM4] AS '([Scenario].[Actual], [Version].[XQE_V5M_a_CM2], [Year].[XQE_V5M_b_CM1], [Period].[XQE_V5M_c_CM0])', SOLVE_ORDER = 4
SELECT
{[Scenario].[XQE_V5M_Act Current Year_CM4]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(0),
{[Account].[Chart of Accounts].[ac_NET_INCOME].[ac_INC_AFTER_TAX].[ac_INC_BEFORE_TAX].[ac_INC_OPERATING].[ac_GROSS_PROFIT].[ac_SALES]} DIMENSION PROPERTIES [Original], LEVEL_NUMBER, GEN_NUMBER, MEMBER_ALIAS, ANCESTOR_NAMES ON AXIS(1)
FROM [CED.Fm]
WHERE ([Company].[co_GP_CFO], [ActivityType].[at_TotalActivityTypeDim], [Geography].[mk_TotalMarketDim], [Entity].[Global BUs])

It is important to note that family functions will not work correctly with calculated members. If they work at all, without returning an error, you may get unpredictable results.

Quickie – Dynamic select and search size

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 \webcontent\prompting\properties.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.

Accessing Tree Prompts with Javascript (on Cognos 10.1.1)

There are many reasons why you may want to interact with a Tree Prompt with JavaScript. Maybe you want to enable the finish button if a member on the bottom level is selected, or to select the last member, or to ensure only 5 members are selected.

This post isn’t to detail every possible scenario, but to detail the some functions available and how to use them. It’s important to note that I am hardly a JavaScript expert, so there may be better ways to do anything I say here.

First you need to identify your tree prompt. Unlike most prompt controls, where the identifier changes based on the type viewer being used, the tree prompt can be called using window.treePROMPTNAME. Unfortunately we can’t apply an onmousedown event to the tree prompt, so we have to wrap it in a div.

Createa tree prompt and give it the name “Time”. Drag an HTML item to the left of the tree prompt

<div id='myTree'>

and an HTML item to the right

</div>

.

Now we can attach an event to capture the clicks:

<script>
document.getElementById('myTree').onmousedown=function(){runTree('Time')};
</script>

Any click inside that div will now trigger the runTree function passing ‘Time’ as an argument.

Because there are a number of JavaScript functions are run upon selecting an element we can’t immediately get the value of the element. So we can use the setTimeout function to wait 200 milliseconds before getting the data.

<script>
function runTree(id)
{
 t=setTimeout('checkTree("'+id+'")',200);
}
</script>

After 200 milliseconds the checkTree function will run, also passing Time as the argument.

<script>
function checkTree(id)
{
  selectedTreeNode  = window['tree'+id].getLastSelectedNode();
  if(!selectedTreeNode) {return}
  alert(selectedTreeNode.getName());
  alert(selectedTreeNode.getValue());
  alert(selectedTreeNode.getLevel());

}
</script>

The checkTree function will now alert the selected elements Name, MUN, and Tree level. Note the Tree Level is from the tree prompt, not the member’s hierarchy level. But knowing these, we can then call other functions. You could check the level number of the selected element and enable or disable the tree prompt while popping up a message.

You can programmatically set the default value of the tree prompt using JavaScript. Unfortunately it appears it is only possible to do this on the first level.

<script>
var node = window.treeTime.getRootNode().getChildren()[window.treeTime.getRootNode().getChildren().length-1];
node.setSelected(true);
node.updateNodeSelection();
node.updateParent();
window.treeTime.setLastSelectedNode(node);
</script>

This will only effect the prompt after the page has been loaded. Prompt pages should be fine, but prompts on the report page will need to have a default value set in the prompt macro.

I learned about these functions by going through the js file associated with tree prompts. Check out ..\webcontent\prompting\CTreeIE5NS6.js for more Tree Prompt functions.

It worth noting that these functions are written by IBM, and are liable to change on upgrade. I’d be interested in hearing if these work in any of the previous versions of Cognos.

Bug: In Cognos 10 new packages don’t inherit permissions

For the past week I’ve been researching a fix to an issue where new packages don’t inherit permissions.

Take the following scenario:

My client has a policy where all packages are saved in specific folders according to the associated project.

All projects are saved in the “Projects” folder with their own specific permissions.

Admins are granted full permission, the project admins are responsible for creating and maintaining the reports, and for granting rights to other users besides the default users for the project. Users are granted only read, execute, and traverse rights. Meaning that they can run any report, but not save anything to the folder. Admins can override this, of course, but that’s their own problem.

In theory, every new item should inherit these settings. Admins can override, but the general practice is to always allow the inheritance. Unfortunately there is a bug in Cognos in which newly created packages overwrite the inherited permissions.

Admins have their execute permissions removed, meaning they can’t run any of their reports, while users have been granted write rights. If the admins don’t notice and fix it immediately, this leaves a big hole in the security.

Fortunately IBM support is hot on the case. There response is that it’s a known issue, but the work-around is to modify the FM.ini file in the configuration directory.

To fix it, backup and open ..\cognos\c10\configuration\fm.ini in your text editor of choice.

Find the line

<Preference Name="SetPolicyPackage">FALSE</Preference>

and replace it with

<Preference Name="SetPolicyPackage">TRUE</Preference>

and save the file.

As this is not an XML file, no restart of Cognos is necessary. You probably need to restart FM if it’s open. All new packages will now inherit correctly.

My thanks to the IBM rep who helped me solve it, and to Noam at Libi who opened the ticket for me.