Unable to add auto-filter on multiple columns while exporting to Excel (xlsx) JasperSoft Studio 5.61

Changed Status from New to Feedback Requested

---

Did you give it a try with a more recent version of JSS? Plus, can you please attach the report? Regards,Massimo.

Hello Massimo,

 

We are bound to this version because it is the only supported version for our core product CA PPM 14.2. I have attached the jrxml file for review.

Hello,

 

Autofilter settings provided in your sample seem to be correct. When applied, we should see all column headers decorated with the autofilter icon.

We tested the report sample in JR v.5.6.1 using an empty data source with 100 records, and were not able to reproduce the issue.

In these conditions we need more information about the export itself:

 

- is it an XLSX export, or an XLS?

- is the 'One page per sheet' option checked or not?

- could you estimate, roughly, how many records should we use, in order to get closer to the real use case? Maybe 100 are not enough here.

- are you using MS Excel to open the generated file, or other libraries, such as Open Office, Libre Office, etc?

 

Attached is the XLSX sample file we get in our test.

 

Thanks,

Sanda

Hi Sanda,

 

It is an XLSX (non-paginated) export. I haven't checked the one page per sheet, but only have 7 rows. Eventually, the report could contain 300-400 rows. The file is exported to XLSX and opened automatically by Microsoft Office/Excel 2010. I have attached the output I get by exporting directly from JSS.

 

thanks,

 

Michael

Hello Michael,

 

Still couldn't reproduce the issue after running the report as you described.

 

Thank you for posting the xlsx sample. Usually this kind of Excel file could be obtained when a net.sf.jasperreports.export.xls.auto.filter property is set for the static text at x="0", y="0" in the title band (see the attached jrxml). So, another useful check, in case your jrxml file went through consecutive modifications, would be to make sure that the proper (last modified) version of the jrxml is run.

 

If the issue still occurs after verification, if possible, please post a screencast that illustrates how the report design looks in your jss, how it runs and generates the Excel file. Maybe we could find some other helpful information this way.

 

I hope this helps,

Sanda

Hello Sanda,

 

I created a brand new report with just a basic header and detail. The problem still occurs. I am able to reproduce this on every single report I create including the new one. I have attached screenshots, excel output and jrxml file for your review.

 

I'm not sure why you mention the auto filter starting on static text, because I don't use that setting. I filter on columns and detail rows only.

 

Files attached.

 

thanks,

 

Michael

I created a fresh report. I removed all sections except for header and detail rows. I added only a "Start" and "End" auto-filter. It still doesn't display correctly. I don't set auto-filters on static text, and I'm pretty sure there wasn't one in the file I sent. I am able to reproduce this error everytime. It has never worked correctly. I attached a copy of the jrxml file, screenshots, and the xlsx output for your review.

 

thanks,

Michael

Hi Michael,

 

Following is a screencast with details about my environment and results I obtained while running the filter_test_v1.jrxml sample.

 

 

First I ran the report using JSS defaults for Excel export, then I checked "Detect Cell Type" option. In both cases, a correct Excel output was generated, as can be observed.

 

It looks like the issue might be related to the JSS installation itself. I'll keep you posted if I find out some other clues.

 

Regards,

Sanda

 

Hi Michael,

I tested the steps shown by Sanda in the screencast (comment #8) using as starting point the jrxml you provided in comment #7.

Both in development environment (last versions of JR and JSS) and 5.6.1 in MacOSX everything worked fine and I got the output as shown by Sanda. Combo in the correct position.

 

Could try to replicate the same steps and confirms it is the same for you?

Regards,

Massimo.

Hi Michael,

please give a try to this sample too.

You can run the sample JRXML against the SampleDB data adapter and select the export XLSX.

You should get something identical to reportOutput.xlsx

 

Let us know.

Regards,

Massimo.

Hi Massimo, the report compiles and runs but my filters are not in the right place. Again, they are being placed on the bottom row of the xlsx output. Screenshot attached.

Hi everyone,

 

I finally managed to reproduce the bug. Apparently it's caused by the 'Auto Filter' setting in the 'Exporter Parameters' pane.

Since the autofilter property is designed to work at element level only, we don't need an additional setting at exporter level, that might interfere with our own settings for elements in JRXML.

 

What could be done in this case: go to the 'Exporter Parameters' pane and make sure none of the 'Start' or 'Stop' options are set for Auto Filter. Choose the empty space in the related list box (see the attached image). Then run the report again. This time you should see the filters in the right places.

 

Please let us know about your results.

 

I hope this helps,

Sanda

Changed Resolution from Open to Fixed

Changed Status from Feedback Requested to Resolved

Changed Assigned User from - to @mrabbi

---

Fixed following Sanda's indications. The option was removed from the export panel. Moreover the labels like "Exporter Parameters" were updated to "Export Options".Regards,Massimo.

Thanks to all of you for helping me find the solution. Changing the parameters fixed the problem.

 

regards,

Mike

Changed Assigned User from @mrabbi to -

---