Introduction.
Data can be displayed in ascending as well as in descending order using charts. If we want to show top or bottom entries using charts (to display Top Region wide sales), we can change the property of the chart to display sales in ascending or descending order (under behavior enable sorting). We might require only top 5 entries to be displayed on the screen; this functionality is not available with Xcelsius Components.
This Blog is intended to show steps, required to display top 5 entries with the help of standard visual components.
Note: As we cannot achieve this with standard Xcelsius components we need to do some formatting on the Excel level.
System Requirements.
Xcelsius 2008 SP3 (SAP Crystal Dashboard Design).
Excel Formatting.
Suppose we have region wise sales for 12 regions (coming from Database), we want to display Top 5 region out of all. (We suppose that the data is already there inside the Excel)
Below figure shows a sample data set that we are going to use for explanation.
Note: We will be using three Excel formulas for this example:
MAX(range) : this function is used to find the maximum out of a given set of numbers.
MATCH(lookup value, Search array, [Match Type]) : Is used to search the row number of a particular search item.
IF(logical_test , value_if_true , value_if_false) : condition to eliminate the maximum out of a set of values of values.
INDEX(array , cell no(row) , column_num) : It is used to pick data at a particular cell
Now on the adjacent cell we manipulate the data set we have. Below is the snapshot of the formulas used for manipulation. (Starting from E2)
The formulas on those cells will end up calculating the maximum at E3, location at cell E5 and F3:F14 shows the data set except the maximum (E3 which is $7,643.00).
Cell E3 becomes the topmost data out of all. Range F3:F14 will now act as the new data set for 2nd topmost data. So now we will repeat the same formula as above for the calculation of 2nd height data.
Cell K3 will calculate Maximum out of I3:I14 (i.e. $4,567.00), cell K5 calculates cell number of the maximum data (i.e. 10) and L3:L14 will give the new data set excluding $4,567.00.
Output will look like below:
Note: Value $4,567.00 is excluded from the list.
Now L3:L14 will become new data set for 3rd height data calculation. Same process is repeated for 3rd, 4th and 5th position (as below).
Now take the entire formatted data on a single place, so that we can display that data set with the help of Xcelsius visual components (link spread sheet, Grid etc).
The above data set displays the Top5 records. This data set we are going to use for Display.
To show the dynamic nature, we will be using Radio button for Month selection and a month wise data set (as below).
Xcelsius Activity.
Step1: Import (Ctrl + Alt + I) Excel sheet prepared in previous steps inside Xcelsius.
Step2: Drag and drop a Radio button component inside the design canvas. It will display Months, select cell no C26:N26 as Label for the radio button.
Step3: Add a series in the data insertion tab (radio button property), select column from the Insertion Type dropdown.
Step4: Select range C27:N38 as the Source Field and C3:C14 as Destination (so now on selection of radio button the data set will change). Set the Selection Item as Label1.
Step5: Drag and Drop two List Views inside the design canvas and map the first one (the original records) to cell A2:C14 and the second one to A17:C22 (the result set).
So now when we select a month from the Radio Button it will copy new data set for each month and Internal Excel formulas will make sure the calculation of Top 5 Region sales.
3 Comments
