Crosstab Data Sorting issue

[uouser]

Hi,

I am using crosstab in my report and I am having problem with the data it displays. It repeats the column on the report when I use 'Data is pre-sorted' flag on crosstab. Please check the image attached.

 

 

When I uncheck the 'Data is pre-sorted' box, I get the columns correctly but the rows are ordered alphabetically whereas I want rows in the order shown in the image.

The data passed to the crosstab via Dataset is:

 

The sorting order I am using at the moment is:

ORDER BY display_order ASC, block_schedule_ID ASC;

Can anyone please help. Thanks.

[singh758]

This comment was blocked because Project Honeypot indicates it came from a suspicious IP address.

[uouser]

I tried the option of concatenating the display_order value with service_name column to get the sorting done correctly. But it takes whole as string and does the sorting as:

 

22 - Electricity

25 - Accountancy Fees

26 - Management Services

......................

......................

4 - General Building Repairs/Works

 

where as I need the service name to displayed in the order of display_order field

[lucianc]

What JasperReports version are you using?  Could you upload a report JRXML and a CSV with the data?

Regards,

Lucian

[uouser]

Thanks for your reply lucianc

Here are the files attached. The report file is attached as .TXT file as the forum didn't allowed me to upload the JRXML format file. I have also attached the CSV data files which is used with main report SQL and sub-dataset SQL. Hope to hear soon. Thanks.

/sites/default/files/files/Report%20File.txt

/sites/default/files/files/Sub%20Data%20Set%20CSV.csv

/sites/default/files/files/Main%20report%20CSV(1).csv

[lucianc]

We have identified a bug that affects crosstabs with isDataPreSorted set.  Please log it in the trackers.

You can simulate the presorted flag by wrapping group bucket values in objects that preserves the original order of the values.  See attached Java class.  To use it, reset the isDataPreSorted flag and then use something like new OrderedValue($F{service_name}, $V{REPORT_COUNT}) as bucket expressions for the groups where you want the order preserved.

/sites/default/files/files/OrderedValue_java.txt

Regards,

Lucian

[uouser]

Thank you lucianc for your reply. I have raised the ticket on tracker which is:

 

http://community.jaspersoft.com/ireport-designer/issues/2584

 

I will try the solution you suggested. Will let you know how it went. Thanks

[uouser]

I managed to get this working. I realised that the dataset returned via my SQL queries were not having entry for each columns e.g. I was having a row returned for  COLUMN 1 and COLUMN 2 for ROW1 but then onwards I was either having a row returned for COLUMN 1 or a row returned for COLUMN 2 by my SQL. Something like

                                                    General Expenditure             Internal Only

General Repairs                             5                                                 5

Cleaning                                           5

Accountancy                                                                                        5

Insurance                                          5

and so on ....

 

I changed my SQL query so it returns 0 if there is no data to be retrieved for COLUMN 1 or COLUMN 2 and that made it working in the sorting order I want havin the 'Data is pre-sorted' flag checked for the crosstab. LIKE

                                                    General Expenditure             Internal Only

General Repairs                             5                                                 5

Cleaning                                           5                                                0

Accountancy                                      0                                                5

Insurance                                          5                                                0

Hope this is helpful for others.

[uouser]

I am unable to try this as we are using Jasper with our PHP application and I have linux server ... I will have issues getting this working on our server .... but I managed to get this working after tweaking my SQL query that I use to retrieve my data. Thanks for your help anyway.

[blosser]

same problem was 1,5 year ago. i cant open page http://jasperforge.org/plugins/mantis/view.php?id=5281

[gopalreddy436]

Hello frds,
Iam new @jasper Report.....i have one requirement ,,,iam enable to get any blog for this so plz help meeeeeee

My requirment is .......i want to do SubTotal for more than 2 rows .So iam unable to get this .Soooo plz help and send step how to do this...

Plz check below attached Excel File like tht i want output............Iam waiting for Your Reply.

[uouser]

You can use two sub reports each handling its own section in your main Driver report e.g. a sub report for section A and a sub report for section B

 

Or you can use report groups to achieve same as your Excel spreadsheet.