chetan_33

Thanks that worked. But its surprising that $P{} works in where clause or part before order by and so i never looked in such a detail in sample What is the secret behind '!'. Are there any such usage specifics at other places as well I will now go ahead by replacing it with variable defintion which is more manageable So will it also require $V{}?.

Giulio Can this be used to do page breaks by number of records for display on HTML page in a crosstab or any other form(group,subreports,etc)

I am using iReport 1.2.5, jasper set to 1.2.0,SQL Server and have successfully used a parameter as part of the where clause I wanted to conditionally include columns in order by clause (obviously the ones which are selected) but it fails with expressions. So I tried to simply substitute by a parameter with default values provided by iReport but it gives exception as follows java.sql.SQLException:ÂTheÂSELECTÂitemÂidentifiedÂbyÂtheÂORDERÂBYÂnumberÂ5ÂcontainsÂaÂvariableÂasÂpartÂofÂtheÂexpressionÂidentifyingÂaÂcolumnÂposition.ÂVariablesÂareÂonlyÂallowedÂwhenÂorderingÂbyÂanÂexpressionÂreferencingÂaÂcolumnÂname. ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628) I have following in my query ....... ORDER BY Name1,VKORG,Name1, PHier2, $P{PARAM_SORT_CLAUSE} ,JAHR, MONAT parameter has value of "PHier3 , MATNR There are 3 more parameter substitutions in whereby clause with which report worked fine till this new parameter is introduced.So cant understand what is 5 in exception. Is there any restriction on parameterising in order by clause and other parts of sql? I am trying to add capability to control sorting on a specific column and the sortorder as assc/desc Appreciate inputs

kjhuebner, lucianc Although I am new to this how about this solution which best fits your need. Y did Lucianc jump to subreport or subdataset? Considering you fetch the data in one go as a array list of array list in a single query with the data in certain columns (like employer, employee) repeating more (Sub reports bring in multiple queries--which affect performance) Then you can use Grouping feature of the Jasper (on employer, employee) and make it display as required I believe the only problem that can occur is if data it contains history for employee task to be displayed as in a crosstab format which can fail. Lucianc can comment more

Sukumesh that Doesn't help completely Check this query of mine http://www.jasperforge.org/index.php?option=com_joomlaboard&func=view&id=626&catid=8#626

ANYONE knowledgeable on this?

Hi All I have other parameters in SELECT part which gets substituted fine so to make rudimentary column sort work I tried with just a simple pre defined string parameter PARAM_SORT_CLAUSE = "PHier3 ASC,MATNR " and query part changed to order by Name1, VKORG, Name1, PHier2,$P{PARAM_SORT_CLAUSE} , JAHR, MONAT It gives me exception as below. Any idea why it is not gettign substituted? Also please answer to above 4 queries since this is not good approach ErrorÂfillingÂprint... java.sql.SQLException:ÂTheÂSELECTÂitemÂidentifiedÂbyÂtheÂORDERÂBYÂnumberÂ5ÂcontainsÂaÂvariableÂasÂpartÂofÂtheÂexpressionÂidentifyingÂaÂcolumnÂposition.ÂVariablesÂareÂonlyÂallowedÂwhenÂorderingÂbyÂanÂexpressionÂreferencingÂaÂcolumnÂname. ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)

Teodord I looked at the sample. UNlike you said -- have isStartNewPage was disabled on the BreakGroup . So how is it starting on new page after 5 records? The group expression will just help it to groups on every 5 records and nothing more. Also you said breaking on group is easy. So does breaking in other forms is complex. My layout is as defined in query at following URL. http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=8&id=311 Although I havent got complete solution to that layout query and just working with the crosstab as in Shipment, I want to know how to do pagination for that or Shipment sample?

I have a report where I want column sorting and so query defined in JRXML with ORDER BY section has a variable whcih acts on input paramters So wanted to know 1>> if this approach is fine or not. 2>> Me workign in iReport but it always moves variable tag after queryTag so is this causing problem for my query. 3>> Is point 2 a bug in iReport? 4>> I was getting "Parameter #8 not set" error if I use the expression of variable below direclty(tried both with and without double quotes) in query. there are only total 5 parameters in query. Can anyone tell me what is going wrong in that case or give me a valid code snippet for that part ################# Order by in Query looks like orderÂby Name1, VKORG, Name1, PHier2,($V{ORDER_QUERY})Â,ÂJAHR,ÂMONAT This variable to return me segment for 2 cols colA and colB basd on inputs as PARAM_SORT_ORDER (user inputs Boolean true or false) and PARAM_SORT_ON (user gives a code like A,B to specify the column header ). So effectively it has to return like colA Asc, colB colA Desc, colB colA , colB Asc colA , colB Desc So for variable ORDER_QUERY its initial expression part is using code like ( "PH3".equals($P{PARAM_SORT_ON}) ) ? ( "PHier3 " + ( (java.lang.Boolean.TRUE==$P{PARAM_SORT_ORDER_ASC}) ? (" ASC,MATNR ") : (" DESC,MATNR ")) ) : ( ("MAT".equals($P{PARAM_SORT_ON}) ) ? ( "MATNR "+ ( (java.lang.Boolean.TRUE==$P{PARAM_SORT_ORDER_ASC}) ? ( " ASC,PHier3" ) : (" DESC,PHier3 " )) ) : ( " PHier3, MATNR ") ) ###################################### But it gives error ]: java.sql.SQLException: Unrecognized SQL escape 'order' at line position 855. at java.lang.Throwable.<init>(Throwable.java) Post edited by: chetan_33, at: 2006/08/04 10:05 Post edited by: chetan_33, at: 2006/08/04 10:13

I will check out the sample you mentioned and post updates. However as a quick update I am interested in breaking every N record on page which can be across groups and not in a specific group as u said. Also I am considering this for crosstab where records span across different row groups How about that?

If anyone knows please reply I need urgent inputs on this I believe this should be possible since jasper is able to give a normal page break based on dimension without cutting the last row!!

I would like to know about any tricks to place Tool Tip on the elements or rows displayed.? Also is there any way by which we can add some metadata to each of the element based upon what type of export format it is .. Like if export is HTML then I would like to add a HTML tags in the textfield and not otherwise?

Hi All, I am eager to know how jasper report fits into a portal environment. I am new to both of this and request all to point to give inputs /any good resource links which explains them. I am not able to understand how following are addressed 1> Hrefs : Typically in a portlet environment the namespace has to be Portlet api is to be used to generate hrefs for managing the namespace in hrefs. So how to prepare the URLS in case of drill down reports where the hrefs are created during fill operation and i think doesnt have access to portal/portlet APIs? 2> Managing personalisation - In portal environment the look and feel is managed through themes and skins. and the report output in HTML should be able to adapt that. I use iReport and from that understand that there are styles that can be defined. But for personalisation to work I guess I need ability to define CSS for elements. So how can it be done... or is there other solution? 3> Are there any other such factors which needs to be taken care of in portal environment for jasper?

Hi All, I working with jasper and ireport 1.2.5 with compatibility set to jasper 1.2.0 and currently working on cross tab report. I see option of Ignore pagination in iReport which when selected gives me all the results without any page breaks. When I disable it there are breaks happening in the report pages as expected. But I would like to know how can i control(in iReport and programmatically as well) the number of Data rows after which the page break can happen. My report is mainly for HTML pages where we normally show pagination like 10/30/50 records per page.In ireport i dont see any such option of defining number of records per page. Also how does it behave when considering data across group, & in crosstabs?

Hi All, I want to implement column sorting in my Jasper report. User should be able to click a column header, and the report sorts on that column I have a query as a datasource and not able to understand how to get sorting implmented. Please provide inputs on this. Also I am having crosstab and sorting is to be done for crosstab. I see a crosstab property "Data is presorted" but how does it help me?

I would like to know more about Reset Type and Increment Type which is present in crosstab and how it affects crosstab display. So far just using the defaults of Reset Type report. Also if anyone can verify my understanding of the different properties for element :- Print when details overflow, Stretch with Overflow ,and StretchType. Print when details overflow : -- continues printing the elment in allocated dimensions even if data overflows. This can cause text overlapping if any element is below it. Stretch with Overflow :-- This causes the cell dimension to change with increse in data content(horiz or vertically??). other adjoining elements in relateive direction will move if Float is enabled else again will lead to overlapping elements StretchType: - This is applicable only if stretch is selected. **"Relative to tallest object" will stretch the element to same height as the tallest object anywhere in report gets stretched (which also shud have stretch selected)!! **What happens if Stretch is not selected and "Stretch with Overflow" is selected?? **"Relative to band height" will stretch this element height as much as the band expands!!

lucianc thanks again that was really a simple trick I didnt understand the second option you told. Can you please explain me. Also I noticed that with my original cross tab the label headers doesn't come properly above the data of the row group .The width of the row col groups seems to change even if I dont have stretch Type or stretch overflow defined. Any help on that.

My second query is resolved now.But would appreciate any help on first query from the forum Post edited by: chetan_33, at: 2006/08/04 10:30

Hi All, I have a cross tab wherein I want the data in the row group 1 to be repeated for all the rows which are generated for row group2 and so on. To explain considering the sample ShipmentsReport.pdf located at "jasperreports-1.2.5demosamplescrosstabs" OR "jasperreports-1.2.5docssamples" In this the first row headers are for region and second is for city. In sample region is shown once for all cities belonging to same region. I want to repeat the value of region for all cities in the first column instead of just displaying it one time. How to do this. Also am looking for layout solution posted at http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=8&id=311

Hi All, I have a cross tab wherein I want the data in the row group 1 to be repeated for all the rows which are generated for row group2 and so on. To explain considering the sample ShipmentsReport.pdf located at "jasperreports-1.2.5demosamplescrosstabs" OR "jasperreports-1.2.5docssamples" In this the first row headers are for region and second is for city. In sample region is shown once for all cities belonging to same region. I want to repeat the value of region for all cities in the first column instead of just displaying it one time. How to do this. Also am looking for layout solution posted at http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=8&id=311

My reason for looking at it is because of following requirements http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&id=311&catid=8 If you can answer that please thanks for your interest

Hi lgarcia I have recently configured on my laptop connection to sqlserver2005 for iReport through 2 approaches 1: using JDBC odbc bridge 2: using JTDS driver from sourceforge FOR OPTION 1 I made a connection in SYSTEM DSN to the server using the "Control PAnel < ODBC data source". You must be knowing this and in iReport Database connections properties do followign driver---sun.jdbc.odbc.JdbcOdbcDriver enter ur server address and database name, user, pwd as applicable to which u want to connect. and then clikk wizard button of the dialog URI should be like --- jdbc:odbc:[sYSTEM_ODBC_NAME] FOR OPTION 2 download the JTDS driver from sourceforge place its jar file jtds-1.2.jar to lib of iReport extract 2 .dll files within downloaded zip file into the root of iReport and then restart the iReport Select driver --- net.sourceforge.jtds.jdbc.Driver enter ur server address and database name, user, pwd as applicable to which u want to connect. and then clikk wizard button of the dialog URI should look like --- jdbc:jtds:sqlserver://[server naem]/Data;instance= Hope this helps

Hi lgarcia I have recently configured on my laptop connection to sqlserver2005 for iReport through 2 approaches 1: using JDBC odbc bridge 2: using JTDS driver from sourceforge FOR OPTION 1 I made a connection in SYSTEM DSN to the server using the "Control PAnel < ODBC data source". You must be knowing this and in iReport Database connections properties do followign driver---sun.jdbc.odbc.JdbcOdbcDriver enter ur server address and database name, user, pwd as applicable to which u want to connect. and then clikk wizard button of the dialog URI should be like --- jdbc:odbc:[sYSTEM_ODBC_NAME] FOR OPTION 2 download the JTDS driver from sourceforge place its jar file jtds-1.2.jar to lib of iReport extract 2 .dll files within downloaded zip file into the root of iReport and then restart the iReport Select driver --- net.sourceforge.jtds.jdbc.Driver enter ur server address and database name, user, pwd as applicable to which u want to connect. and then clikk wizard button of the dialog URI should look like --- jdbc:jtds:sqlserver://[server naem]/Data;instance= Hope this helps

Hi All I have a cross tab report which is very similar to "jasperreports-1.2.5demosamplescrosstabsShipmentsReport.jrxml" (jasperreports-1.2.5docssamplesShipmentsReport.pdf) I have 2 queries for my report(and need help badly to optimise the report before i get into another set of issue of applying CSS for end user):- ---------------------------- INFORMATION ABOUT REPORT ---------------------------- The only difference is I have 2 more levels of nesting above the Country shown in that report. So it will be LEVEL1 containing multiple LEVEL2 which in turn contains records for multiple COUNTRIES and rest all then continues as as in the shipment report All this data is available in same table (FK_LEVEL1, FK_LEVEL2, FK_COUNTRY, FK_REGION, FK_CITY, YEAR, MONTH, and 2 MEASURES to be displayed). --------------------------- QUERY1 --------------------------- I need to show the LEVEL 1, LEVEL 2 and Country similar to the way Country is shown in the sample report (i.e. as a GROUP HEADER) But I want to have reduced query i.e. just one instead of multiple if I follow approach as in sample(two in the ShipmentReport ) and still be able to achieve the given display How to do that . is it possible? --------------------------- QUERY2 --------------------------- I have currently done similar levels as in Shipment but with 2 different measures being displayed. The database has data for 2 months only. So effectively there should be 2 measureCols for month JAN and 2 measureCols for month FEB shown and then 2 cols for total of all measures across months. But I am gettign 2 extra columns repeated for month JAN and FEB but with different data. The query is right and I am exploring where I have gone wrong in report definition. Mostly looks my problem but since used iReport wizard to make it I am posting it to see if there is something else that I need to consider?? ######################

Thanks lucianc By changing to jasper 1.2.0 it now works save my another day.