Static and dynamic excel formula in iReport 3.1.4

[shummel]

Hi,

i'am getting into trouble using dynamic excel formula in iReport 3.1.4.

 

Working fine for static formula:

<property name="net.sf.jasperreports.export.xls.formula" value="SUM(I3+1,00)" />

within the <reportElement>

 

Not getting to work in any way for dynamic formula:

<property name="net.sf.jasperreports.export.xls.formula" value="SUM(I+$V{REPORT_COUNT})" />

or

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA[("I" +$V{REPORT_COUNT}]])"> </propertyExpression>

and so on.

 

Could anyone help me out if i made a code misstake or is this function not yet implemented into v.3.1.4 ??

 

Many thanks in advance

Sven

[szaharia]

Hi,

If you just want to have the I3 + 1.00 formula expression, just use:

1)      <property name="net.sf.jasperreports.export.xls.formula" value="SUM(I3;1.00)" />

If you want to have a more complex sum, like I1 + I2 + I3 +1.00, try to use:

2)      <property name="net.sf.jasperreports.export.xls.formula" value="SUM(I1:I3;1.00)" />

If you have to determinate dynamic values for the expressions above, try to use:

1)      <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(I" + $V{REPORT_COUNT} + ";1.00)"]]></propertyExpression>

or

2)      <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(I1:I" + $V{REPORT_COUNT} + ";1.00)"]]></propertyExpression>

 

Hope this helps,

sanda

 

[shummel]

Hi Sanda,

 

many thanks for your quick reply.

 

I have tried your examples and they will work if i change the char ";" into "+" like:

<property name="net.sf.jasperreports.export.xls.formula" value="SUM(I3+1.00)" />

 

 

 

I have also tried a more complex one with success like:

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM((I" + $V{REPORT_COUNT} + "+1.00/100)/100)"]]></propertyExpression>

I think i now understand the syntax and hope any body else will find those examples helpfull as well.

 

 

May i can ask you one more detail about the build in variable REPORT_COUNT ?

For most of my formula i need to know the row number of the actual row.

In my above example the row number is always one number less.

The header band isn`t counted and i don´t know why or how to overcome that.

To avoit missunderstanding:

ROW 1 = Header

ROW 2 = Header (REPORT_COUNT VALUE = 1)

ROW 3 = Header (REPORT_COUNT VALUE = 2)

 

Any idear on that or alternative ?

 

Thanks a lot,

Sven

[shummel]

Hi Sanda,

 

i found the solution myself

new Integer($V{REPORT_COUNT}.intValue()+1)

 

For example:

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA[$F{ART3_A3VK}.compareTo(new BigDecimal(0.00))==0 ? null :"SUM(I" + new Integer($V{REPORT_COUNT}.intValue()+1) + "-K" + new Integer($V{REPORT_COUNT}.intValue()+1) + ")"]]>

 

Will give me the formula

=SUMME(I2-K2) for the second row.

=SUMME(I3-K3) for the third row, aso.

 

thanks again for your help

Sven

[Ram Ayanavilli]

Hi All,

Would you please explain for what this is. Actually I want to display the data in excel sheet s like sheet1, sheet2,... sheetn. As per my requirement I will get Large amount of data and i need to split the data into different tabls if the records exceeds the excvel records limit and for that tabs I need to give name like. ABC1,ABC2,...ABCn. ABC is the static name and I have two subreports in my report.

 

 

Thanks,

Naresh