Using implicit hierarchy attributes for display/ordering

#1

I am building a data cube and its dashboard. The datacube will output only a few rows, which will be displayed almost as-is in a table in the dashboard.

Each row (about 15) will be of the form ‘event name’, ‘raw value’, ‘percent value’.

In that case, ‘event’ is found as an implicit hierarchy by Dundas, which is correct. One issue is to properly order those events in my dashboard. There is no logical/alphabetical order (eg. I want to see ‘sent’ before ‘open’ in my table, then ‘click’ and so on).
I can go in my dashboard to ‘custom sorting’ and basically add all values of ‘event’ as top override. It works, but it’s long, ugly, hard to update if I forgot one event in the middle, and I don’t know what will happen if an event name changes.

Is there a way to add an ‘order’ attribute to the event implicit hierarchy and then use it for sorting?
In the same way, could I add a ‘caption’ attribute to my event and use it as a caption in my dashboard table?

Cheers,

#2

Order by My order not Alpha or Numeric.
The only way I know is to manually do it.
An alternative to the top Override, assign a numeric value to each event name.
You could make a table in the Database and join it. add the column and hide it, but have it ordered by that.
Or you could add a derived column in the Data Cube.
Either way you are basically doing the same as top Override by manually making a list this event is in position X.
And if a new one shows up have an Else clause to assign it 0 or 99 to show at top or bottom of list.

if there is a better way I will be exited to hear about it myself.

2 Likes
#3

Hi Guillaume,

You can add both Order and Caption attributes in the data cube either using a calculated element where you assign these attributes for each event or by using a look up table with those values assigned. Sorting can then be done based on either of these attributes.

Does this help? Please let me know if you have any questions.

Regards,
Nikhil

#4

Hi James and Nikhil,

Thanks for your input. My question was not very well worded - adding order and caption is indeed very easy. My issue was about how to use those attributes once in the dashboard.

@James it looks like something I didn’t think of: sorting inside the SQL statement. I did try, but it is not enough, the sort order is not guaranteed to be the same outside the cube. Indeed, the sort transform inside a cube warns: ‘The transform allows various sorting rules to be applied to the input columns and copies the results to the output. The sorting applied is only applicable inside the data cube. The sort transform should never be the last transform before the process result.’

@nikhil If I understand well, you are suggesting the same as James? Otherwise, I would love more information about ‘Sorting can then be done based on either of these attributes.’

Thanks,

#5

No I was not saying adding an order by, add a column (int Value) you can sort on that is generated by a CASE statement like
CASE
When event = ‘First’ then 1
When event = ‘Second’ then 2
and so on.

#6

Thanks James, indeed. Hide the column in the dashboard but order by it. It does work perfectly and it is much easier to do it that way for my use case, thanks.

Now it looks like using the caption attribute of the hierarchy instead the actual hierarchy member is not really possible for implicit hierarchy. I’ll carry on looking for an alternative.