Crosstab Data Sorting issue

Posted on November 6, 2012 at 3:00am
1

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.

iReport Designer
uouser's picture
uouser
61
Joined: Jan 25 2010 - 3:18am
Last seen: 3 years 3 months ago
This comment was blocked because Project Honeypot indicates it came from a suspicious IP address.
singh758 - 8 years 3 months ago
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
uouser - 8 years 3 months ago
add comment

5 Answers:

1
Posted on November 19, 2012 at 5:03am

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's picture
uouser
61
Joined: Jan 25 2010 - 3:18am
Last seen: 3 years 3 months ago
0
Posted on November 7, 2012 at 9:04am

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

Regards,

Lucian

lucianc's picture
lucianc
7357
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 10 hours ago
0
Posted on November 9, 2012 at 8:58am

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

uouser's picture
uouser
61
Joined: Jan 25 2010 - 3:18am
Last seen: 3 years 3 months ago
0
Posted on November 12, 2012 at 6:58am

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

lucianc's picture
lucianc
7357
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 10 hours ago
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 - 8 years 3 months ago

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.

uouser - 8 years 3 months ago

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

blosser - 8 years 2 months ago
add comment
0
Posted on May 8, 2014 at 5:20am

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.

Attachments: 
AttachmentSize
Office spreadsheet icon gopal.xls16 KB
gopalreddy436's picture
gopalreddy436
12
Joined: May 8 2014 - 4:48am
Last seen: 5 years 2 months ago

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.

uouser - 6 years 9 months ago
add comment
Feedback