Active Report YTD calculation in Crosstab

[nishi02]

Hello People,

My query is about developing an Active report.

Cognos Version: 10.1
Data Source: MSAS Cube

Attached is a simple report studio crosstab report where I get values for both selected month and YTD based on user input month parameter. This works perfectly.

Now, I am trying to get similar results using active report and I have no clue on how to get that YTD? I need more ideas on how to proceed with this development and your help is appreciated.

Thank you.

[Jay_p]

If you have data drop button bar or any button bar for that fact where you are using year as a parameter to render your report, Go to Your Cross tab query and and drag data item for expression.. use that parameter year = year in your cross tab.. name that YTD.
If you also want to add another data item , add the parameter year from the data button bar =current_yuear-1

Hope it helps

[RKMI]

Hi,

Try this!

Thanks,
RK

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" expressionLocale="en-us" useStyleVersion="10" application="true"><!--RSU-SPC-0093 The report specification was upgraded from &quot;http://developer.cognos.com/schemas/report/7.0/&quot; to &quot;http://developer.cognos.com/schemas/report/9.0/&quot; at 2013-3-28. 12:40:6-->
<modelPath>/content/folder[@name='DAL']/folder[@name='UAT']/folder[@name='Packages']/package[@name='DAL_UAT']/model[@name='2012-10-25T09:02:33.057Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>

<query name="qry_crosstab"><source><model/></source><selection><dataItemMember label="Direct Written Premium - Actuarial" name="Direct Written Premium - Actuarial"><dmMember><MUN>[insurance_summary].[Measure Types].[Measure Types].[Measure Types]-&gt;:[M8].[[Measure Types]].[Measure Types]].&amp;[1]]]</MUN><itemCaption>Direct Written Premium</itemCaption></dmMember><dmDimension><DUN>[insurance_summary].[Measure Types]</DUN><itemCaption>Measure Types</itemCaption></dmDimension><dmHierarchy><HUN>[insurance_summary].[Measure Types].[Measure Types]</HUN><itemCaption>Measure Types</itemCaption></dmHierarchy></dataItemMember><dataItemMeasure label="Direct Written Premium - Actuarial" name="Measure Sum"><dmMember><MUN>[insurance_summary].[Measures].[Measure Sum]</MUN><itemCaption>Measure Sum</itemCaption></dmMember><dmDimension><DUN>[insurance_summary].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" output="no" value="9"/></XMLAttributes></dataItemMeasure><dataItem name="Current Month"><expression>roleValue( '_memberCaption', [Selected Month])</expression></dataItem><dataItem name="Selected Month"><expression>[insurance_summary].[Date Key Account].[Months].[Months]</expression></dataItem><dataItem name="Selected Year"><expression>ancestor ([Selected Month],[insurance_summary].[Date Key Account].[Months].[Years])</expression></dataItem><dataItem name="Selected MTD"><expression>periodsToDate([insurance_summary].[Date Key Account].[Months].[Years],[Selected Month])</expression></dataItem><dataItem name="YTD"><expression>aggregate (currentMeasure within set [Selected MTD])</expression></dataItem><dataItemLevelSet name="Transaction Type Desc"><dmLevel><LUN>[insurance_summary].[Policy Transaction Types].[Transaction Type Desc].[Transaction Type Desc]</LUN><itemCaption>Transaction Type Desc</itemCaption></dmLevel><dmDimension><DUN>[insurance_summary].[Policy Transaction Types]</DUN><itemCaption>Policy Transaction Types</itemCaption></dmDimension><dmHierarchy><HUN>[insurance_summary].[Policy Transaction Types].[Transaction Type Desc]</HUN><itemCaption>Transaction Type Desc</itemCaption></dmHierarchy></dataItemLevelSet></selection></query><query name="qry_month_prompt"><source><model/></source><selection><dataItem aggregate="none" name="Months"><expression>[insurance_summary].[Date Key Account].[Months].[Months]</expression></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>

<table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><appDataDropDownList width="200px" name="Data Drop-Down List1" refQuery="qry_month_prompt">
<appListItemDefinition><appListItemLabel refDataItem="Months"/><appListItemValues><appListItemValue refDataItem="Months"/></appListItemValues></appListItemDefinition><appOnSelectSetVariableValues><appSetVariableValueToDataItemValue refDataItem="Months" refAppVariable="Months"/></appOnSelectSetVariableValues><appContainerSelect><appCondition><appConditionDataItemComparison refDataItem="Months" refAppVariable="Months" operator="in"/></appCondition></appContainerSelect></appDataDropDownList></contents><style><CSS value="text-align:center"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><appDataDeck width="100%" name="Data Deck1" refQuery="qry_month_prompt">
<appDataCard>
<contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="qry_crosstab">
<crosstabCorner>
<contents><textItem><dataSource><dataItemLabel refDataItem="Measure Sum"/></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e2" refDataItem="Transaction Type Desc"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="Direct Written Premium - Actuarial"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Measure Sum"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e3" refDataItem="Selected Month"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e4" refDataItem="YTD"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><masterDetailLinks><masterDetailLink><masterContext><dataItemContext refDataItem="Months"/></masterContext><detailContext><dataItemContext refDataItem="Selected Month"/></detailContext></masterDetailLink></masterDetailLinks></crosstab></contents>
</appDataCard>
<appCardDefinition><appCardValues><appCardValue refDataItem="Months"/></appCardValues></appCardDefinition><appContainerSelect><appCondition><appConditionDataItemComparison refDataItem="Months" refAppVariable="Months" operator="in"/></appCondition></appContainerSelect></appDataDeck></contents><style><CSS value="text-align:center;padding-top:20px"/></style></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue>Selected Month and YTD Crosstab</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" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/></XMLAttributes><reportName>upgradeReport</reportName><appProperties><appVariables><appVariable name="Months"/></appVariables></appProperties></report>