Jump to content
Changes to the Jaspersoft community edition download ×

Crosstab Data Sorting issue


uouser
Go to solution Solved by uouser,

Recommended Posts

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.

 

crosstab-issue.png.e7fb3b6b55db610a604ad2b1116a8f1b.png

 

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:

 

crosstabresultset.png.06feceeb085cffa14842ab8d0a5888a0.png

The sorting order I am using at the moment is:

ORDER BY display_order ASC, block_schedule_ID ASC;

Can anyone please help. Thanks.

Link to comment
Share on other sites

  • Replies 11
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 1 month later...
  • 1 year later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...