SEMI SOLVED: Crosstab sorting

[vnug]

 Hi:

I am trying to avoid the default sorting of Crosstab data by passing sorted data. But, it uses in-built data sorting and the resulting output is "atds-what-I-get.jpg". What I would like to get is in "atds-what-I-need.jpg". I am also enclosing the data set for this example. I have also tried the checking the option of "pre-sorted data". Then, I got lot of rows with "null" and some data. Even the data is incomplete. I would appreciate any pointers to fix this issue. 

Ideally I would like to use the "serial_no" column to order the data set and be able to get the crosstab output as in "atds-what-I-need.jpg". Thanks in advance.

 

best regards,

vnug

Post Edited by vnug at 04/06/2010 03:29

[kchaudhry]

Eventhough "Data is Presorted" has always worked for me. One way of doing this is to create a new column and sort based on that column. For example you can use sort/decode like this

select case when tablename.fieldvalue = 'Sample1' then 1

                      when tablename.fieldvalue = 'Sample2' then 2

                      when tablename.fieldvalue = 'Average' then 3 end orderfield

from tablename

order by  orderfield

[vnug]

Hi kchaudhry:

 

Thanks for your response. Unfortunately, I do not know the contents of row_name and param_name .... as they are dynamic. That was the reason I have a column "serial_no" (hoping to sort by this column to get the data in the order that I would need).  Unless I am missing something that is too obvious .... in your explanation, your query would not work in my case because I wouldn't know the dataset.

 

 

[vnug]

Hi Kchaudhry:

I am trying to get your above example to work .... unfortunately, it is still not displaying data properly (assuming that I know the rownames and columnnames). Would it be possible for you to give me a jrxml file for the dummy data posted in the original post? Thank you.

Post Edited by vnug at 03/24/2010 10:30

[kchaudhry]

Try the attached jrxml.

[vnug]

 Thanks, Kchaudhry for the sample file.

I am attaching  the output for my efforts. I have tried 3 different versions of iReport .... 2.0.0, 3.7.0, and 3.7.1.

Whenever I use pre-sorted data in the crosstab setup panel, I end up getting null values in the cells as shown. Here is my select statement -

select *, case when row_name='Sample1' then 0  when row_name='Sample2' then 1 when row_name='Average' then 2 end as order_ from data_values  order by order_

The select output gives me the output as in excel file (which is also enclosed). I am enclosing the output received for reference. I got the order needed for rows but the output contains unnecessary "null" values. Is there something I am not configuring??? Appreciate any pointers.

[deepthynair]

Even I want to disable crosstab sorting.I want the datas to be shown in the order the data is filled itself?

Is this possible?

[vnug]

 Howdy all -

Thanks everyone for the helpful responses. 

The sorting can be done outside of what iReport forces. Unfortunately, it comes at a cost .... we need to know what the data contains. So, the steps if you know the data before:

1) Setup the SQL query with "case" statements to get the proper ordering

2) use pre-sorted data in the crosstab properties

3) Create hidden row and column (depending on whether you need private sorting rather than iReport sorting). These hidden fields will get you the sorting order you need. This step is "very much" needed, otherwise the output will contain lot of unnecessary "null"s.

Hope this helps.

[wichogg]

Code:

Thank you for the semi solution, it worked for me.

[andreasX]

see my post here for various rather good solutions: http://stackoverflow.com/questions/8172301/crosstab-sorting-in-jasperreports/21632977#21632977