Hi gurus/
I have a list report where, in some circumstances, I calculate a value and then need to save it and use it on the next line.
Any ideas on how to achieve this ?
regards, Andrew
If I'm understanding you correctly, you're looking for global type variables. These don't exist in ReportNet. If you happen to be coming from Crystal reports, its a big logical leap.
Yes I am.
Don't know Crystal Reports but lack of global variables sounds like a major limitation.
There is one software provided for this by cognos..Composite Information Server..this might be useful..I am still checking on it..
Srik
I am pretty sure you can create a calculated column
use your calculated field in a moving-total(calculated_field) function for your new field
change the aggregate type to calculated.
This should keep a running total of your calculated value for each row.
Thanks guys but I need to create a value on one line based on values on previous line.
I don't see how running totals will achieve this.
I think the best way may be to create a stored proc to do this task and call it in the expression editor.
It works because moving totals saves the value from the previous line. if you use ,2 rows and subtract the current row.
Here is a GO example:
Product name Actual quantity PreviousColumn
Aloe ReliefÃ, Ã, Ã, 14,786Ã, Ã, Ã, Ã, Ã, Ã, Ã, 0
Bear EdgeÃ, Ã, Ã, 28,140Ã, Ã, Ã, Ã, Ã, Ã, Ã, 14,786
<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en-us">
<BIQuery name="Query1">
<cube><factList><item refItem="Product name" aggregate="none"/><item refItem="Actual quantity" aggregate="none"/><item refItem="PreviousColumn" aggregate="none"/></factList></cube>
<tabularModel><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Product name]</expression></dataItem><dataItem name="Actual quantity" aggregate="total"><expression>[gosales_goretailers].[Product forecasts].[Actual quantity]</expression></dataItem><dataItem name="PreviousColumn"><expression>moving-total([Actual quantity],2)-[Actual quantity]</expression></dataItem></tabularModel></BIQuery>
</querySet>
<layoutList>
<layout>
<pageSet>
<page name="Page1">
<pageBody>
<list refQuery="Query1">
<listColumnTitles><listColumnTitle><textItem><queryItemRef refItem="Product name" content="label"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef refItem="Actual quantity" content="label"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef refItem="PreviousColumn" content="label"/></textItem></listColumnTitle></listColumnTitles>
<listColumns><listColumn><textItem><queryItemRef refItem="Product name"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Actual quantity"/></textItem><style><CSS value="text-align:right"/></style></listColumn><listColumn><textItem><queryItemRef refItem="PreviousColumn"/></textItem><style><CSS value="text-align:right"/></style></listColumn></listColumns>
<style>
<CSS value="border-collapse:collapse"/>
</style>
</list>
</pageBody>
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<table>
<tableRow>
<tableCell>
<textItem>
<expression>AsOfDate()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<text>- </text>
</textItem>
<textItem>
<expression>PageNumber()</expression>
</textItem>
<textItem>
<text> -</text>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<expression>AsOfTime()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableRow>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page></pageSet></layout>
</layoutList>
</report>
Cool. That'll be useful for me. Thanks.