| Order a matrix by the cell content [message #585843] |
Thu, 30 May 2013 05:37  |
 |
amauri
Messages: 18
Registered: May 2013
Location: Italy
|
Junior Member |
|
|
Hi,
I have a matrix report that shows production per year(horizontal) and per country(vertical).
The customer's willing to see the data ordered by production, and not per country.
I tried to add a CF containing the production in the country group and order by that one, but I'm getting a message saying that the field object cannot be contained into the frame.
Both the field and the containing frame are set to Variable.
May anyone suggest either how to get rid of the error or how to achieve the main goal of ordering by the cell content?
Thanks
Alberto
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Order a matrix by the cell content [message #585952 is a reply to message #585945] |
Fri, 31 May 2013 06:16  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
OK, here you are.
A test table:SQL> select * from test;
COUNTRY YEAR PRODUCTION
-------------------- ---------- ----------
Croatia 2010 100
Croatia 2011 50
Croatia 2012 150
Italy 2010 400
Italy 2011 380
Italy 2012 200
Slovenia 2010 200
Slovenia 2011 220
Slovenia 2012 80
9 rows selected.
SQL>
In Reports, I used Wizard and chose matrix layout. Query looks like this:
with sorter as ( select country,
rank () over (order by production desc) rn
from test
where year = (select max (year) from test)
order by production desc
)
select s.rn,
t.country,
t.year,
t.production
from test t, sorter s
where t.country = s.country
The idea is: rank countries by production in the last (MAX) year (column RN returns that order). Then, join these two tables (SORTER and TEST).
Data model layout looks like this: RN column must be in a group that contains COUNTRIES; set its break order to "ascending" (because - in SORTER table - I used "order by production desc". If you want, you can use "ascending" in the ORDER BY clause, but then you'd have to change break order in Reports to "descending"; the result is the same, anyway):
Finally, the result:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
