How to use the next row value

[andrewmadrid]

Hi all,

I have a report which I export to CSV. After that, I have to manually calculate a field based on four values, let's say: A1, B1 (which are on the same row and I can use), and A2-B2 (which are in the next row). The formula is something like this:

=IF(Y(G1=G2;K1=K2);"no";"yes")    --- incremented by 1 every row, obviously. This would be the example of row 1.

And I want to do this automatically from the report, and get the CSV with those values calculated. But I have no idea of how to get G2 and K2 in the Expression Definition. Any guess?

Thanks in advance!

[pricter]

You should create a data item to have the row number

For example ( RN)

Code Select Expand

running-total(1)

Copy the original query and then join the two queries on the data item with the following expression

[Original Query][RN]=[Next Query].[RN]-1

In the newly created query from the join perfom you comparison.

[andrewmadrid]

Ok, let me try it! I'll tell you. Thank you.

[andrewmadrid]

Hi pricter.

I'm stuck. When I do the join, I think I'm doing it right, but when adding the elements of the join query to the report, just after that I cannot validate and it says "RSV-VAL-0004 Unable to find query information for the item QueryItem1"

I've checked all, but everything seems to be OK. I've even tried to start from scratch again, but the same error arises again :( Before adding any element to the report, it validates correctly.

What might I be doing wrong?

Thanks again.

[pricter]

Apparently you have done a mistake and it can not find the QueryItem1.

Below is a report specification based on Go Sales(Query) sample package

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en">
<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>

<joinOperation>
<joinOperands>
<joinOperand cardinality="1:1"><queryRef refQuery="CurrentRow"/></joinOperand>
<joinOperand cardinality="0:1"><queryRef refQuery="NextRow"/></joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[CurrentRow].[CurrentRow] = [NextRow].[NextRow]</filterExpression>
</joinFilter>
</joinOperation></source>
<selection><dataItem name="Product line"><expression>[CurrentRow].[Product line]</expression></dataItem><dataItem name="QuantityCR"><expression>[CurrentRow].[Quantity]</expression></dataItem><dataItem name="QuantityNR"><expression>[NextRow].[Quantity]</expression></dataItem><dataItem name="Bool"><expression>if
([QuantityCR]&gt;[QuantityNR])
then
('YES')
else
('NO')</expression></dataItem></selection>
</query>
<query name="CurrentRow">
<source>
<model/>
</source>
<selection><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="CurrentRow"><expression>running-count ([Product line])</expression></dataItem></selection>
</query><query name="NextRow">
<source>
<model/>
</source>
<selection><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="CurrentRow"><expression>running-count ([Product line])</expression></dataItem><dataItem name="NextRow"><expression>[CurrentRow]-1</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>
<list refQuery="Query1" horizontalPagination="true" name="List1">



<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="QuantityCR"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="QuantityCR"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Bool"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Bool"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<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"/></XMLAttributes></report>

[andrewmadrid]

Hi pricter!

Thank you so much for your help. Now, I get results I want to. But now, I'm facing a new problem... When exporting the report, with te information I need, if I do it in HTML it shows greatly the info, but I need it in an Excel spreadsheet, so if I try to export in Excel (any version ) or CSV the output is empty (just the headers and in the A2 the current date).

What is happening? Can't I export the report? That would be offputting!!!

[pricter]

I tried to run that repot that I have attached in this topic and works fine with excel.

Could you try to run the report that I have attached?

Could you provide your report specification to have a look at it?