jmurray

I forgot to mention that in some databases (like Oracle) it is not possible to carry out DML statements within a function. This is rather annoying if you want to call a stored procedure that updates some data based on input paramater settings and returns the number of rows affected (or an error code). If it is a requirement that your function insert, update or delete data then you may need to force it to operate as an autonomous transaction. In Oracle you would need to suppliment the function declaration with PRAGMA AUTONOMOUS_TRANSACTION.

You could pore over the source code or simply use the colour picker in an image editor program, like paint, to determine the palette.

The solution to your problem will be very simple, but a resolution will be difficult without your report design and a page of output marked up to show what is going wrong

JasperReports was originally designed to satisfy printed output solutions and evolved to cater for html, pdf, csv, excel, word and other formats. It hasn't had a focus on screen based outputs because most of the non-print based solutions provide their own handling of screen output. So there are no elements that allow you to freeze panes. The closest you will get is to output your data to CSV or EXCEL and apply the freeze pane function in MS Excel.

Using a single report to provide different solutions based on user parameter settings or source data is a clever way to implement business solutions. It involves overlaying elements and controlling which one is displayed using printWhenExpression settings on each element. You first need to decide what is common and what will need to change depending on parameters or source data (eg. a field value). Common elements normally appear in report sections that don't change much like headers and footers and would include things like logos, report titles, copyright notices, company addressing or web site addresses, and so on. These are put in place in the main report and are shared between each of the different forms. Elements that are displayed or hidden depending on parameter settings or field values need to be treated in one of two ways: where there are only one or two elements that change then place them on the main report individually and give each element their own printWhenExpression formula to display and hide them. if there are large number of elements in the same report section that change then place them in a subreport, bind them to a field in the main report, and apply printWhenExpression formulae to each subreport (ie. you display or hide entire subreports.Deciding on which of these methods to use is based purely on managing complexity. To keep things simple you need to make a decision about when there are too many elements overlaying each other to manage easily. This number is usually quite small, and subreports provide a very simple way to manage the complexity. For some reports you may need to use a mixture of both methods to get the best compromise between functionality and management.

It's documented in Chapter 6 of the iReport Guide.

hahagal Wrote: Hi, Seems like it still doesn't give me the results. Is there any way to store value to a variable and then compare it with the next record? Then display if it is not the same and hide if its the same. It might be time to post your report design and a page of output marked up to show what is going wrong

lknueve Wrote: seems like your Variable Expression: new java.lang.Integer(1) should be Variable Expression: new java.lang.Integer(0) for the variable that you want to reset to zero for each new group - otherwise it would reset to 1 Hi lknueve, that isn't correct. The value that you want the cariable to reset to is contained in the Inital Value Expression. When the variable type is defined as Count then it increments away from the Initial Value Expression each time the Variable Expression is not null. You can use any static, non-null value to force the counter variable to increment every time a detail record is filled, or you can get funky with it and reference a field that may contain nulls. In that case the counter will only increment when the field is not null.

hahagal Wrote: textfield to display when above 2 variables is 1 <textField evaluationTime="Auto" isBlankWhenNull="true"> <reportElement x="100" y="0" width="63" height="12"/> <textElement textAlignment="Left"> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[( (!$V{IntercoCount}.equals(new Integer(1)) && !$V{CcyCount}.equals(new Integer(1))) ? " " : $V{CTPY_Type_Text} )]]></textFieldExpression> </textField> Try this for your conditional statement: <textField evaluationTime="Now" isBlankWhenNull="true"> <reportElement x="100" y="0" width="63" height="12"/> <textElement textAlignment="Left"> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[ ( ( $V{IntercoCount}.intValue()==1 && $V{CcyCount}.intValue()==1) ? $V{CTPY_Type_Text} : "" ) ]]></textFieldExpression> </textField> or this alternate method: <textField evaluationTime="Now" isBlankWhenNull="true"> <reportElement x="100" y="0" width="63" height="12"> <printWhenExpression>new java.lang.Boolean( $V{IntercoCount}.intValue()==1 && $V{CcyCount}.intValue()==1 )</printWhenExpression> </reportElement> <textElement textAlignment="Left"> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression class="java.lang.String"> <![CDATA[ $V{CTPY_Type_Text} ]]> </textFieldExpression> </textField> Both provide the same result in different ways. The first example uses java to solve the problem, the second uses the text element's prinWhenExpression to control whether the textbox is visible or not. Post Edited by jmurray at 13/07/2011 13:00

Your printWhenExpression is still incorrect. Remember that it is a snippet of embedded Java, so you have to be very explicit about what you are doing. If there's one thing Java excels at it's semantics. new java.lang.Boolean( $F{Method}.compareTo("Third Party")==0 )

Please allow me to restate your post for clarity: There is a hyperlink in one report that points to a second report. The hyperlink also passes an id to the second report that shapes the displayed result set. However, if no data is available for the id that is passed then there is no point opening the second report, so the hyperlink should not be visible in the first report. How can i do this? You will need to execute an SQL statement to determine whether there are any rows returned for a given id. To do that you could embed an invisible subreport in the detail section of the first report to execute a simple query and return the number of rows. The subreport does not need to have any visible elements on it. It just needs to accept the id into a subreport parameter and return the count of matching rows in a variable. The SQL statement for the subreport could be as simple as SELECT count(1) as id_recs FROM table WHERE id=$P{subreport_id} The main report will need to use the value returned from the little subreport in the printWhenExpression of the hyperlink field

This won't address the underlying issue with your report design but will give you the results you want: force the database to always return a value in that field. eg. SELECT field1, field2, nvl(problemfield,0) as problemfield FROM table..... Note: The equivalent to Oracle's nvl() in MySQL is ifnull(), in postgres and db2 it's coalesce(), in MS-SQL it's isnull()

The problem is that $P{startDate} is not defined when you try to set the default value of $P{startDate}.getTime() / 1000L to parameter startDateLong. Use a variable instead, and use BigDecimal rather than Integer: <variable name="startDateBD" class="java.math.BigDecimal"> <variableExpression><![CDATA[new java.math.BigDecimal($P{startDate}.getTime() / 1000)]]></variableExpression> <initialValueExpression><![CDATA[new java.math.BigDecimal(0)]]></initialValueExpression> </variable>

Why not just do something like SELECT criteria, count(*) FROM table GROUP BY criteria and create a group in your report definition based on the criteria field? At its simplest you could just implement a simple ungrouped query with all the detail fields you need. Then create a group in you report definition and implement a counter variable to get the count as detail rows are filled. Check the sample report defs at http://jasperforge.org/uploads/publish/jasperreportswebsite/trunk/samples.html. The first sample contains an example of grouping.

Libraries are missing. Check your iReports classpath is complete. This normally doesn't need to be done unless you've installed a new version of iReports

The attached pdf is a sample that contains dynamically generated cricket graphs. Page 1 is just a title page. Page 2 contains three images that are dynamically created by an external rendering tool. Page 3 contains 4 subreports that generate jasperreport graphs from data for specific timeframes. The following query from the main report provides base textual data for the left hand side of page 2 and the URI strings that are used for the weekly, monthly and yearly cricket graphs on the right hand side of page 2. <queryString><![CDATA[select DISTINCT a.*, b.tail_fqhostname , 'http://public.nms.det.nsw.edu.au/weathermap/proxy.php?' || 'url=http://cricket.nms.det.nsw.edu.au/?type=png;target=' || substr(rrdpath,14,length(rrdpath)-17) || ';inst=0;dslist=ifHCInOctets%2CifHCOutOctets;range=604800;rand=763' as weekly , 'http://public.nms.det.nsw.edu.au/weathermap/proxy.php?' || 'url=http://cricket.nms.det.nsw.edu.au/?type=png;target=' || substr(rrdpath,14,length(rrdpath)-17) || ';inst=0;dslist=ifHCInOctets%2CifHCOutOctets;range=2592000;rand=763' as monthly , 'http://public.nms.det.nsw.edu.au/weathermap/proxy.php?' || 'url=http://cricket.nms.det.nsw.edu.au/?type=png;target=' || substr(rrdpath,14,length(rrdpath)-17) || ';inst=0;dslist=ifHCInOctets%2CifHCOutOctets;range=31536000;rand=763' as yearly from planned_bw a, rrd_paths_path b, planned_bw_consumption c where a.HOSTNAME || '.net.det.nsw.edu.au'= c.hostname and A.HOSTNAME || '.net.det.nsw.edu.au'= b.tail_fqhostname and a.hostname='cabr01' and ( c.consumption_key = $P!{consumption_key1} or c.consumption_key = $P!{consumption_key2} or c.consumption_key = $P!{consumption_key3} or c.consumption_key = $P!{consumption_key4} ) order by A.REGION, A.SITE_NAME]]>[/code] The URI strings contain a URL for the wrapper cgi script, a rendering URL for the cricket grapher, the path to the interface to be graphed and the parameters required by the cricket grapher to render the correct counters for the right period (in this case one week): wrapper cgi: 'http://public.nms.my.hidden.web.site/weathermap/proxy.php?'render URL: 'url=http://cricket.nms.my.hidden.web.site/?type=png;target='target path: substr(rrdpath,14,length(rrdpath)-17)parameters: ';inst=0;dslist=ifHCInOctets%2CifHCOutOctets;range=604800;rand=763' as weekly[/code]The assembled URI might look something like this: http://public.nms.my.hidden.web.site/weathermap/proxy.php?url=http://cricket.nms.my.hidden.web.site/?type=png;target=/routers/offices/swsy/cabr01/gigabitethernet0_0;inst=0;dslist=ifHCInOctets%2CifHCOutOctets;range=604800;rand=763 The URI is then used in an image element like this: <image hyperlinkType="Reference"> <reportElement key="image-1" x="395" y="10" width="385" height="160"/> <imageExpression class="java.lang.String"><![CDATA[$F{WEEKLY}]]></imageExpression> <hyperlinkReferenceExpression><![CDATA[$F{WEEKLY}]]></hyperlinkReferenceExpression></image>[/code]Hope this helps if you were considering using an external rendering tool.

The reason why it didn't work is that my solution contained a typo (due to a copy and paste tragedy). Apologies. Change the zero in the variable expression to a one: Variable Expression: new java.lang.Integer(1) If that doesn't work please post the relevane sections of xml.

Check recent topic http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=89442 as it may contain the answer you need

Naveen, can you post the xml relating to your variable declaration and the section of the report where the variable is is used please?

The best thing to do right now would be to post a sketch of what you want your report to look like along with the source xml. That way people can see what needs to be done and where the report design might be letting you down.

If the pics are stored locally on the report server then you can reference them directly, but if they're on (say) a remote web server you'll need to pass the pic url to an external image rendering tool (for example cricket grapher can render network utilisation graphs on the fly given the correct URI, and the png output files can be embedded in the report).

Place the check mark image where you want it to be displayed, then set the printWhenExpression appropriately: eg. ( $F{Method}.compareTo("Third Party")==0 ) If you aren't positive about whether the case will always be the same then force the strings on both sides of the comparison to either upper or lower case. If you can't be sure that source string won't contain leading or trailing whitespace then use the .trim() function to be absolutley sure.

Apologies, I realised after I went to bed that my post.(equals() is ... ... use .compareTo() instead:) was incorrect, so I've deleted it. Post Edited by jmurray at 07/11/2011 20:52 Post Edited by jmurray at 07/11/2011 20:57

One way to guarantee that you subreport runs after you main report is to nest the main report inside your subreport as a second level subreport. There's a reason why it sounds odd to do it that way. :D You may need to cascade report parameter values through the subreport to the main report (and back again if you need to access return values) A second method can be used if you don't need the results of the subreport to be displayed in the detail section. The main query runs as normal to fill the detail section and the subreport is placed in either the summary section or last page footer.

At it's simplest you either make the stored procedure a function or wrap it in a function. Then you can use it within a simple SQL statement like this: SELECT getRowsUpdatedByMyProcedure( $P!{startDate}, $P!{EndDate} ) FROM DUAL; If you place that query into a simple subreport and put the subreport into the report title [or a page header with a printWhenExpression of new java.lang.Boolean( $V{PAGE_NUMBER}.intValue()==1) ] then the report will update records in the backend data before the rest of the report is filled using the parameters passed in from the user (or default values).