Dynamic Excel formulas and crosstabs

[glassner]

 

Does anyone know how to generate Excel formula dynamically when using a crosstab ?

This post is very helpful in decribing how to generate Execl formulas dynamically

in the post above, we set the following property:

net.sf.jasperreports.export.xls.formula

"=SUM(B2:B"+($V{REPORT_COUNT}.intValue()+1)+")"

But in the context of a crosstab, does anyone know how to refer to the number of records in a group, or the number of columns in a group ? How does one replace "B2" with something that indicated the first column, the first row ...

Or dynamic Excel formulas and crosstabs do not mix ?

[glassner]

Any suggestions ?

[teodord]

Hi,

 

I think you could create another measure in the crosstab, that performs a distinct count with the same expression as the group's bucket expression. This measure should evaluate to the number of rows in that crosstab group.

I have not tried it, but let us know how it goes.

 

I hope this helps.
Teodor

 

[glassner]

returns null ...

This is the measure RowCount using the same bucket expression of the group courseProgramCode

<measure name="RowCount" class="java.lang.Integer" calculation="DistinctCount">
      <measureExpression><![CDATA[$F{courseProgramCode}]]></measureExpression>
     </measure>

 

<rowGroup name="courseProgramCode" width="58" totalPosition="End">
      <bucket>
       <bucketExpression class="java.lang.String"><![CDATA[$F{courseProgramCode}]]></bucketExpression>
      </bucket>

(...)

[lucianc]

Where are you trying to write this formula?  Measures values are only available in data cells..

Regards,

Lucian

[glassner]

Yes, I was using data cells.

It is not clear whether there is an easy way to create Excel formulas with Jasper especially if we are using crosstabs. If there is a way, please let me know.The documentation on this issue is non-existent.
 

 

[glassner]

I am also finding other issues when converting to Excel format:

 

The entire header of my crosstab does not appear on my report if I export it in Excel, but it does appear in any other format (say, PDF).

I converted the Excel report into XML and then opened it with a text editor: the header of the report is just not there !

As I said, the header appears in any other type of export

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=76165&topid=76234

[lucianc]

glassner
Wrote:

It is not clear whether there is an easy way to create Excel formulas with Jasper especially if we are using crosstabs.

No, in the general case there is no easy way to create Excel formulas that reference other cells in the report.

The entire header of my crosstab does not appear on my report if I export it in Excel, but it does appear in any other format (say, PDF).

 What does header mean exactly?  Which report band is it?  Also, how/where from are you exporting to Excel?

Regards,

Lucian

[glassner]

 

Solved the issu of the column header not showing on Excel following Teoror suggestion

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=76165

 

Any plans on allowing JasperReport to facilitate the creation of Excel formulas ?

[lucianc]

glassner
Wrote:

Any plans on allowing JasperReport to facilitate the creation of Excel formulas ?

Log a feature request for that.  Concrete ideas on how it should work would help.

Regards,

Lucian