Outlining/Pivoting/Cascading/Hierarchy Data

JonRambo

 

So, we've been working to get some areas of the business switched over from Tableau to Domo. In the process of that, folks are asking that some parts of their reporting be represented in the exact same way as it was in Tableau since their teams have become accustomed to those views. That being said, I'm having a small issue figuring out how to outline duplicate records in a ticketing system.

Here's an example of what it should look like:

Order # | Ticket # | Details |

AAAAA  | 000001 | Detail 1 |

             | 000002 | Detail 2 |

             | 000003 | Detail 3 |

BBBBB  | 000001 | Detail 1 |

             | 000002 | Detail 1 |

             |              | Detail 2 |

             |              | Detail 3 |

             | 000003 | Detail 1 |

             |              | Detail 2 |

CCCCC | 000001 | Detail 1 |

             | 000002 | Detail 1 |

             | 000003 | Detail 1 | 

 

So basically the data is pivotted/cascaded/hiearchied out without aggregation in Tableau. I tried to do something similar in Sumo Mode but was unable to get the results that were needed.

nalbright

This is related to the topic I posted on the same subject here:

 

http://dojo.domo.com/t5/Data-Sources/Hierarchical-Relationship-Between-Columns-In-Data-Sources/m-p/1807

 

It would be nice to see a way to deal easily with hierarchical data like your instance and mine has.  That said, there are some ways you can deal with this in beast mode at least (which does not work with sumo cards, for whatever reason).

 

First, you would need to have an order and ticket number for all details, like AAAAA 00002 Detail 2, for example. Then, you can make sure that every unique detail in your ticketing system would have a unique concatenate for order #, ticket #, and detail # (a calculation you can make in Beast Mode with a bit of SQL).  You could then click through data via first order # and then ticket # to see the details underneath.  From what I can gather, at least, one deals with hierarchical layers in cards most easily by constructing drill paths according to the structure of the hierarchy.

JonRambo

This is a pretty good idea, @nalbright! Unfortunately, the business has requested that there be no click throughs or drill downs for this particular visualization. They're insistent that all the data should be laid out upfront in the prescribed way. This way in particular....

 

nalbright

Are you looking to put it in tabular form then?  You can still duplicate the order & ticket numbers for all the the details and look at it in a table, and then do "count unique" order numbers and ticket numbers to determine the number of those, but it might be a matter of visual appeal as well for something like this.

JonRambo

So that was my initial approach. I just had SQL do all of the work from Domo Workbench and dumped the data into a Sumo Card like so:

 

select

*

from

(

select

mrtitle as Order_ID
,mrid as Ticket_ID
,mrsubmitdate as Submit_Date
,mrassignees as Assignees
,mrstatus as Status
,Submitted__bin__bPB__bby as Submitted_In_PB_By
,count(*) over (partition by mrtitle) as Tickets

from

footprints.dbo.master16

where

mrtitle in

(

select

mrtitle

from

footprints.dbo.master16

where

mrstatus = 'Open'
and
not ltrim(rtrim(lower(mrtitle))) like 'n%a'
and
disposition <> 'Abandoned__fDenied'
)

) a

where

tickets > 1

order by

Tickets desc
,order_id desc

,submit_date desc

 

;

 

 

 

The only problem was that the presentation was not acceptable to the business unit since they're so used to the Tableau formatting.

nalbright

Impressive work there.  I suppose you could suggest an idea for a card that mimicked the look of Tableau for cascading/hierarchical data.  Do you know how to get to the ideas page:

http://dojo.domo.com/t5/Ideas-How-would-you-make-Domo/idb-p/Ideas

JonRambo

That's a good a idea!

 

 

Thanks for the info!

 

Jon

nalbright

You're very welcome .