CUSTOM SORT and SELECT using Cubes

Document created by Paul Wyatt - Avison Young on Apr 29, 2018Last modified by Paul Wyatt - Avison Young on Apr 30, 2018
Version 2Show Document
  • View in full screen mode

About the Solution/Project: 

About the solution


One of the first things I needed to do with a dataview was to control the order that entity members were shown in, not simply alphanumeric ascending but qualitatively, such as a given order of portfolios or regions.  Once set, the selections would persist after a data refresh but would have to be able to be removed immediately.  I needed to also restrict the display to show only specific members.  Whist an entitiy's sort order can easily be set to default to CODE or DESC ascending, this is no good when you want to set an order specifically to the needs of the analyst and have a need to change this order to suit other requirements.  Furthermore, restricting displayed information can be difficult when always having to re-configure the dataview or further down at the SQL load level.  A simpler, externally controlled and safe alternative would be welcome when immediate changes are required.


So, Here I've shared my method of SORTing and SELECTing members for display.


Solution/Project info


Both solutions use a dedicated SORT or SELECT cube which comprises of the target entity only.  This cube is first used in a dataview with data entry and only one row axis is used, that of the entity.  The SELECT dataview is further altered to use a checkbox.


Once the analyst has made their selections in either cube and saved it, the target dataview is modified to include the 2 cubes also.  These cubes are hidden.  accessing the dataview FILTERS tab, the SORT cube is used to SORT the dataview and the SELECT cube is used in  FILTER A with a value > 0.5


Once the target dataview is modified, it will show the entity members in the order stated and display only the members which have been ticked.


Attachments/Print Screens/Videos

Attached is a short video of how to implement the techniques.  I've included an image of a dataview simply to illustrate what exactly I'm referring to.


Takeaways/ Hints for other community Members:


MULTIUSER.  To implement this method for multiple users, one would have to incorporate database security with @User.  This is currently out of scope of this tip and will be covered in a future video. 


IMMEDIATE USE.  The techniques demonstrated here would be immediately useful to an analyst or manager that wanted to control what was seen by other users.  Alternatively, developers could use this technique in place of SQL filtering and dataview configurations.

Because the SELECT cube uses checkboxes, which resolve to 1's and 0's, the cube can be used in cube based selections.  This fact opens up number of flexible possibilities for dynamic reporting models; custom calendar entities and years can be used in the SELECT cube.   Month end / year end advancements could be easily controlled and rolled back for example.



VIDEO QUALITY.  I'm using new software; OBS for screen capture and SHOTCUT for editing.  I have not got the resolution quite right and so all BOARD screens are a bit blocky.  I'm sorry for this and will re-shoot the video once I know what I'm doing with the settings.  However, I felt that the video was watchable and so considered it more important to get techniques out there than wait until I've been able to polish my video skills.


FEEDBACK.  I welcome feedback regarding this technique.  Advancements on it or fiendishly simple alternatives are especially welcome.




Want to share additional material with other BOARDVille Citizens and the Community? Feeling like you would share a BOARD Capsule? Make sure information is anonymized and no sensible information is shared. Simplify and reduce the size of the application/solution, to the screens/procedures if necessary

NOTE: All inspirations will be reviewed before publication. Usual time to publication is 24h. You will be notified when the document will be published and contacted directly in case something requires your attention/intervention

3 people found this helpful