Report count in regressive order

[metafurionx]

Hi,

I'm trying to get the row counting in my report in reverse order.

 

The query for the report gets the data in descending order (ORDER BY column DESC).

So far I thought of that basic formula "total rows - actual row + 1 = descending count".

I know that if I set a Text Field Expression as $V{REPORT_COUNT} and its Evaluation Time as Now I get the actual row, and if I set Evaluation Time as Report I get the total. But I can't join both in a formula.

Is there any way to build this formula or some alternate solution for my goal?

 

I'd appreciate any reply, even if some attempt. Thanks in advance.

[patobarrientos]

well... you can't join the variables because one of them is evaluated at the end of the report and the other one is evaluated on each row.

So I can recommend use another field as TotalRows, you can try lenght method.

Then you have "QueryResult.length() - actualRow"

where QueryResult is the resultset with all your data and actual row is the same you had before.

I hope it works for you.
 

Cheers!

Post Edited by patobarrientos at 05/23/2011 16:23

[metafurionx]

patobarrientos, thanks for your response!

 

That was one of the ways I tried, but I got stuck when trying to obtain the result for the field.

I went through the Add Dataset option, redoing my SELECT with a COUNT() function already, attributing the result on an AS "TotalRows" so I got my textField for it.

But then I can't reference it from anywhere in my report, because its use is limited to a Chart or a Crosstab element.

 

Is there a way to use it in a textField expression, or did I go the wrong direction to obtain my query result for the field?

Post Edited by metafurionx at 05/23/2011 17:26

[patobarrientos]

can you post your code or a sample to see how are you getting the TotalRows field?

It did work how I told you but I'm getting data from a XML file.

[patobarrientos]

I tried this.

SELECT Field1, Field2, (SELECT COUNT(*) FROM TABLE ) AS TotalRows FROM TABLE

it isn't an elegant solution but it works bringing the TotalRows as field, anyways.. this way execute the 2nd select query as many times as rows you have in your table.

good luck!

[toddbrook]

if you're using oracle as your data source then re-code the query suggested above as something like

 

select field1, field2, count(field1) over (partition by 1 order by 1) total_count from table

 

this will avoid having to repeat the count(*) for each row.

i presume you can write analytic queries in other dbs but i don't use them so you'd have to look up the syntax

[metafurionx]

Thank you guys so much, both ways worked out!

 

Yes, I'm using Oracle as my data source. I tested

SELECT Field1, Field2, (SELECT COUNT(Field1) FROM table) AS "TotalRows" FROM table

and

SELECT Field1, Field2, COUNT(Field1) OVER (PARTITION BY 1 ORDER BY 1) "TotalRows" FROM table

with success. So, to avoid repeating the execution, I'll stick with the second suggestion.

 

And I edited my textField expression as ((Integer)($F{TotalRows}-$V{REPORT_COUNT}+1)).toString(), getting me a regressive counting.

 

I'm grateful for the time and knowledge shared.

Interesting solution, because I won't need an additional dataset. I didn't know those notations, gonna do some research on them.