Component Solution Services REC02F Manuale Utente Scaricare il pdf (Pagina 51)

Tips Recipes Brio 2002

Section Type: Pivot section

Tip Name: Creating alternate sort order in a Pivot

Purpose / Use: Often, you want your Pivot labels sorted in an order other than

alphabetical. For example, you may want month names listed in calendar

order.

Sample File: Hidden sorting.bqy

Steps to Produce: Make use of a hidden fact to control the sort order of the labels.

1) Build your pivot as usual.

2) Add a new fact that corresponds to the desired sort order. In the sample BQY, a field

named “Month Number” contains the month number. If no such data column exists, add a

Computed Item to the Results section named “Month Number” with the following

formula:

if (Month_Name == 'January' ) {1} else {

if (Month_Name == 'February' ) {2} else {

if (Month_Name == 'March' ) {3} else {

if (Month_Name == 'April' ) {4} else {

if (Month_Name == 'May' ) {5} else {

if (Month_Name == 'June' ) {6} else {

if (Month_Name == 'July' ) {7} else {

if (Month_Name == 'August' ) {8} else {

if (Month_Name == 'September' ) {9} else {

if (Month_Name == 'October' ) {10} else {

if (Month_Name == 'November' ) {11} else {

12 }}}}}}}}}}}

Once created, add the Month Number field to the pivot’s facts.

3) Right-click on the sorting fact (Month Number in this example), and select “Hide Items”.

4) In the pivot’s sort line, select Month Name and set it to be sorted on Month Number using

Maximum in ascending order as shown below.

1 2 ... 46 47 48 49 50 51 52 53 54 55 56 ... 77 78

Nessun commento

Component Solution Services REC02F Manuale Utente Pagina 51