Home » Developer & Programmer » Reports & Discoverer » Order a matrix by the cell content (RB 10g)
Order a matrix by the cell content [message #585843] Thu, 30 May 2013 05:37 Go to next message
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 #585889 is a reply to message #585843] Thu, 30 May 2013 13:25 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you set ORDER BY clause in report's query, as well as "Break order" property for the field (indicated by a blue arrow for that group Data model layout)?

I'm not sure what you wanted to do with a formula column, but the error you got says that - as you manually created it and tried to place it into a frame in the layout editor - you did it in a wrong manner. Although you probably put it - visually - into the right place, you need to check where it belongs to in Object Navigator. You might need to adjust its position by moving it (or the frame it is supposed to belong to) forward or backward (you do that in the Layout menu).
Re: Order a matrix by the cell content [message #585906 is a reply to message #585889] Thu, 30 May 2013 16:39 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hi,

thanks for Your reply.
The layout was created with the Wizard. If I set the Break Order to whatever than None in whichever column but my formula one, the error disappears. Of course the report is then not sorted the way the customer would Confused

Alberto
Re: Order a matrix by the cell content [message #585927 is a reply to message #585906] Fri, 31 May 2013 01:04 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
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.

If I got it right, the following matrix shows your current output (sorted by years / countries)
      Croatia   Italy   Slovenia
2010  100       400     200
2011   50       380     220
2012  150       200      80

Customer wants to sort it by production, not countries. How would that matrix look like, then?
Re: Order a matrix by the cell content [message #585945 is a reply to message #585927] Fri, 31 May 2013 04:40 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
You got it right, but columns and row are reversed:


2010 2011 2012

Croatia 100 50 150
Italy 400 380 200
Slovenia 200 220 80

So we should then sort by the Last Year's column, obtaining:


2010 2011 2012
Italy 400 380 200
Croatia 100 50 150
Slovenia 200 220 80


Re: Order a matrix by the cell content [message #585952 is a reply to message #585945] Fri, 31 May 2013 06:16 Go to previous messageGo to next message
Littlefoot
Messages: 19537
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):

./fa/10831/0/

Finally, the result:

./fa/10832/0/
Re: Order a matrix by the cell content [message #586073 is a reply to message #585952] Mon, 03 June 2013 05:28 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Many thanks indeed,

I had to modify the query a bit, as my data are calculated on the fly, and SQL-wise it seems to be working. Nonetheless I'm still getting the REP-1813 error saying that Object 'R_G_COUNTRY' too large to fit in matrix cell.
Actually R_G_COUNTRY is the group containing the country Confused

Re: Order a matrix by the cell content [message #586074 is a reply to message #586073] Mon, 03 June 2013 05:40 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle

REP-1813: Object '<object name>' too large to fit in matrix cell.

Cause: The data fetched into a matrix cell exceeds the capacity of the cell--e.g., the cell may contain a variable field, and the data may be longer than expected.

Action: Either enlarge your matrix cells until they are able to contain the data fetched into them, or redefine your data.
Re: Order a matrix by the cell content [message #586076 is a reply to message #586074] Mon, 03 June 2013 05:43 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hey,

what a fast answer... unfortunately that is not the case. If I simply remove the sort by production and order by country the error disappears... Confused
Re: Order a matrix by the cell content [message #586079 is a reply to message #586076] Mon, 03 June 2013 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If possible, create a VIEW (and to these "calculations" in there) so that it contains 3 columns: country, year, production. Then create a report based on a view and apply what I suggested earlier (rank countries).
Re: Order a matrix by the cell content [message #586116 is a reply to message #586079] Mon, 03 June 2013 11:08 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Done,

and it works. But I still get the error.
the attached file is actually a ZIP where You can find the view, the query, and the data model picture Confused

Many Thanks for Your help.
  • Attachment: data.txt
    (Size: 24.12KB, Downloaded 40 times)
Re: Order a matrix by the cell content [message #586125 is a reply to message #586116] Mon, 03 June 2013 15:03 Go to previous message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
it works. But I still get the error.

Hm, does it work or not, then?

What error do you get? The same REP-1813? If so, I'm out of ideas, I'm afraid.
Previous Topic: Select Query with minimum values
Next Topic: Oracle Discoverer
Goto Forum:
  


Current Time: Tue Sep 02 02:14:39 CDT 2014

Total time taken to generate the page: 0.13708 seconds