COGNOiSe.com - The IBM Cognos Community

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.

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

Hi,

Try this!

Thanks,
RK

<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>