mwarner_2

Yes I can enter it in a property name field, but still it does NOT give the name to the Excel tab. No matter where I entered ...export.xls.sheet.name... it still Never named any Excel tab. So please tell me where to enter it so that it works.

I want to export to Excel. My summary page goes on a separate tab. My data goes in the tab(s) before the Summary tab. I don't really care if those tabs are named Page 1, Page 2, etc. Don't know how many tabs like that I will have. What I do care about is naming the Summary tab. I have read that there is a "net.sf.jasperreports.export.xls.sheet.name" property to set the name of a tab. But using Report Studio, when I go to the Summary band, Properties, Advanced, Properties, ..., Add there that Property is not available in the dropdown list. Even if I go to the Source XML tab, and enter "property name="net.sf.jasperreports.export.xls.sheet.name" value="Summary"/> it has no effect on the sheet names. So what am I missing?

These are the tips I've learned on how to create a report that is expected to be exported to Excel. Reports need to be designed differently depending on how the user is going to view/use them. I find PDF is easier than Excel. Excel has taken some trial and error to get satisfactory results. With Excel you have to make certain all the columns line up, so you don't get a whole bunch of different sized columns in Excel. Export a poorly laid out report to Excel, and this is clearly seen. This post doesn't cover that effort. For exporting to Excel, my objective is to keep things clean and tidy. This is because the user wants the data in Excel so that they can slice, dice, and sort, group, filter, and play with it until they see/get what they need. They don't want to do clean up before they can do that. In Excel, I have one tab for data, and another tab for footnotes. In the data tab I have one row for the report name / company logo. The next row is for column headers. Then the rows for data. Now in JS.. The Page Header section has the report name and the company logo graphic. They are both the same height, and their widths match exactly to the columns in the Column Header. The Column Header section is where I place the table for the data. Having the data in a table, allows the user when viewing the data in HTML to sort the data, etc. The data table, has one row for column headers, one row for data. In the Summary section, I show the user all their input selections, explain any abbreviations or calculations, show who ran the report and when, etc. Place a Break Palette Element at the very top of the Summary. Mine is at x: 0 px, y: 0 px, Position Type: Fix Relative To Top. I also have a No Data section, which is a copy of the Summary page with the addition of a big "There was no data found for the selections made" notice, and the addition of the report name and company logo. With this No Data section, if the user made selections that have no resulting data, they see a notice telling them that, and they can see their selections. Often times an empty report, to a user, is seen as the system or the report not working. So I want to tell them that it was their choices that led them here. The Main Report has to have the same dataset query as the table, for this to work. In Main Report, Report properties, Page Format (for this one example report I am looking at), my Page Format has the following properties: Format: CustomWidth: 1350Height: 540Units: pixel.Page Orientation: LandscapeMargins: 0So I have a very wide report (like 18.75 inches), which is fine in Excel. I've had other reports that are like 3200 pixels wide. I do this because you don't want the user to get a column-squished report and expect them to like right-sizing the columns. So I make the columns wide enough so that most data is seen without wrapping. The height of the report is not really important. If there are thousands of rows, on screen, they will just have to scroll down to see them all. In Excel, thousands of rows is the expected norm. Also, in Report properties, I have made these selections: When No Data Type: No Data SectionSummary On A New Page: checkedSummary with Page Header and Footer: checked --I could have this unchecked, and in the Summary section, I would have to add the report name and the company logo.Ignore Pagination: checkedAlso, in the Main Report Properties, in Advanced, Properties, I have added these (which end up near the top of the Source XML): <property name = "net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/> Without the above, JS only puts as many data rows on a single tab as fit within the Page Height. <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> The above gets rid of one row with a very large row height at the end of the data tab. Don't understand why that one very high row is created, but this is needed to get rid of it. Of course having this means that you can't (easily) have row spacing in the footnotes. <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/> The above shows the Excel grid lines, otherwise you get solid white, which kind of ruins the look of Excel (for a data dump). <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> The above tells Excel to treat dates as dates, numbers as numbers, instead of all being treated as text when it is set to "false" (which is the default). Note: Java's custom date formats are different from Excel's custom date formats, so a date format might look good in Excel but bad in Java/HTML, or vis-a-versa, so take care with custom date formats. <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/> The above tells Excel to not delete the company logo graphic. "true" (which is the default) deletes graphics. <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/> The above, as far as I know, has to be typed or cut-n-pasted directly into the Source XML. I have not found a place to select it under any Properties. I am not certain why it is needed, but it is. <property name="net.sf.jasperreports.export.xls.sheet.names.all" value="Data/Footnotes"/> The above tells Excel, to name the first tab "Data", and the second tab "Footnotes". Instead of "Data", I would recommend something more descriptive, maybe the report name. <property name="net.sf.jasperreports.export.xls.freeze.row" value="3"/> The above tells Excel to freeze the top 2 rows. Once in Excel therefore, the report name/company logo row, and the column headers row is frozen, and the user can scroll through thousands of records and still see them. Without this, the user would have had to do it themselves. <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> The above keeps JS from truncating values wider than the explicity set colunmn widths. In Excel this is seen as wrapping. When looking at your report, the look in Preview is different than the look in JasperReports Server. Also I've had the best luck using Save As XLSX Paginated (compared to the other options). These are my observations. I welcome yours.

ps - I am finding out that the internal name of the input controls HAVE to match the name of your parameters. For instance, if your parameter is named Select_City, then your input control can NOT be internally named City_Single_Select. If they don't match (even though in Jasper Reports Server, in the Repository, for this report, you have editted the Controls & Resources, for Select_City to point at City_Single_Select), then the report ignores any selection you made (as if you left Select_City blank). Maybe the documentation is full of warnings about this, but until you've made this error a few times, you won't learn it.

I have a Main Report, with a query string of Select null as test. So the Main Report's Report_Count is always 1. There is a table in the Main Report. If the table is empty, then I want to display the No Data Section (with a message like "There was no data for your input selections". Normally when I don't have a subreport or a table, and the inputs don't have any returned data, then it is easy to set the When No Data Type: No Data Section, and add the band and add my desired text. But with a table, the Main Report's Report_Count is always 1, so the report is never "empty", and the No Data Section never triggers. Is there a way I can get the Table's Report_Count and use it in the Print When Expression of the No Data Band?

Some significant progress. Maybe even a solution! Remember the problem is that a multi-select query won't work when the user-made selections have a commulative tolal of more than 128 characters. Like you can't select 10 cities. I created a new parameter, called StringCity, of type java.lang.String. Is For Prompting is unchecked. CityString has a default value expression is ${City}.toString().replace("[","").replace("]",""). This is doing two things. toString() is converting City from a collection to a string. replace is stripping out square brackets. I don't have to do that stripping in my stored procedure (which I was doing above). If you just want this multi-select to work in the Main Report, and don't need it in a table, then create the above string parameter and change the sql query from EXEC get_table_data @city = $P!{City} to EXEC get_table_data @city = $P{StringCity}. If you want this to work in a table, with an empty or other query in the Main Report, then create the above string parameter, and then in the table dataset, in Table, Dataset, Parameters map Name: Table_City to Expression $P{StringCity}. My sql query is EXEC get_table_data @city = $P{Table_City}. Table_City is java.lang.String. Here is one url that was a help to me (but I wasn't smart enough to understand it the first 4 times I read it): http://alexandru-ersenie.com/tag/sending-collection-parameter-in-jasper-report/

When you Preview the report in Studio, does it run successfully there?

I use Jaspersoft Studio Professional 6.1, and JasperReports Server 6.1. I don’t use iReports (though I have it). I want to use a table in my report (so as to take advantage of the column sort, column filter, and column resize on-screen features that a table offers). I also want to have a multi-select query input control, so that the users can select as many values as they want to use in the report. Let’s say that my report will show data about selected cities. I create a new report, with a Main Report sql query of ‘select null as test’. This query doesn’t do anything useful but at least establishes the database connection. I have an input parameter of City, which I have defined as being java.util.collection. I add a table to the report. I add a parameter of ‘Table_City’, which I defined as being java.util.collection. And I have a sql stored procedure of “EXEC get_table_data @city = $P!{Table_City}”. I am required to use this stored procedure. In the Table, Dataset, Parameters, I have created (or what I call ‘mapped’) Name: Table_City to Expression:$P{City}. I put the desired fields from the query into the table, and save the report. Then I ‘Publish Report to JasperReports Studio’. In Server, I created a root, Public, Controls, input control called “City”, which I defined as being a multi-select Query. Let’s say the SQL query for this is “EXEC get_cities”. Back in the Server Repository, I have edited the report, Controls & Resources, Input Controls for “City” by ‘Select an Input Control from the repository’ and choosing ‘/public/Controls/City’. Then I run the report and if I select one city, it works perfectly. If I select three cities, it works perfectly. If I select six cities, it works perfectly. But when I select all, or if I select too many cities, then I get an error message like: Caused by: java.sql.SQLException: [TIBCO][sqlServer JDBC Driver][sqlServer] The identifier that starts with ‘Atlanta, Alberta, Albuquerque, Alpharetta, Chicago, Memphis, Minneapolis, Saint Louis, Toronto, Paris, Hong Kong, Tokyo, San Fra’ is too long. Maximum length is 128. So I have used the collection, $P! method. But it doesn’t work in a table when the multi-selections, when taken as a string, are more than 128 characters. To try to get around this, I tried defining the parameters as java.lang.String, but there are two problems with this. 1. The multi-select query encloses the user’s selections with square brackets, like ‘[Atlanta]’. And when [Atlanta] is passed in the query, there is no matching [Atlanta] in the city database. To try to solve that, I had the ‘get_table_data’ stored procedure changed such that it would strip any square brackets in the passed-in parameter values. Perhaps this could be done in the XML java, but I don’t know how or where to do this. 2. I get an error message of Caused by: java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String. Maybe there is some way in the XML java to do this conversion between the ArrayList (of the multi-select query) and String parameter, but I don’t know how or where to do this. Can you tell me what to do such that users can select more than 128 characters worth of multi-selections? ps – When I do the multi-select query in the Main Report, and define the City parameter as a String, and I have the stored procedure strip out the square brackets, then I am not limited to 128 characters in my selected cities. I can select all cities and it works perfectly. Of course the problems with this is that I: 1. Don’t get the benefits of the table. 2. Have to have the stored procedure do the stripping of the square brackets. 3. Can’t show the user-selected cities in my footnotes page because I get an error of java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String. Normally on the summary section I would have a StaticText field of ‘Selected Cities: ‘ and then have a TextField with the expression of $P{City} Thank you ahead of time!