Designing a new table structure

(8 posts) (2 voices)
  1. nsepetys, Member

    Hello myDBR Representative,

    How are you doing? I am attempting to build a summary view that pulls stats into one frame/table. What would be the easiest way to approach the below design-

    The primary features that are distinct from current table design is the possibility of multiple value "headers" within each table. I understand that in the picture above the table called "Service Quality" is much like the pageview (which is super easy to use- so thank you for providing that option). However, what I would like is the table structure like the "Activity Summary" table. This provides multiple columns for value name info (Column A and Column C being the examples) and multiple columns (adjacent to the name info) for values. You could almost say this is a multi-columned pageview because it seems to essential just connect two or more pageview tables together (for the purpose of grouping similar metrics while taking advantage of the horizontal real-estate of a client's viewing space. What would you say is the best way to achieve that?

    For the time-being I will use the "keeptogether" command to maximize the horizontal real-estate of multiple pageview datasets.

    Thanks for your help,

  2. myDBR Team, Key Master

    is the structure a pageview where each row's value (B / D) come from different table/column and can even have different datatype or are the A and C the same, as B and D. If the number of rows is dynamic, where does the data come from?

    myDBR Team

  3. nsepetys, Member

    If I understand what you're asking you want to know how the data is presented to myDBR before it is rendered as a table? If that is what you're asking the easiest way to approach the problem in the back-end is to permit multiple datasets to be retrieved and then merged together with the number of rows total being equal to the greatest number of rows returned between the grouped query results and the number of columns equaling 2 x the number of queries.

    From my end I plan to have one query for each grouping (ie- query 1 for Column A/B and query 2 for C/D). What would be beautiful is if the multi-columned pageview would simply just group together multiple pageviews. I think if you view it that way it'll make sense.

    SELECT 'dbr.pageviewgroup'
    SELECT 'dbr.pageviewgroup.header', 'Activity Summary' SELECT routed AS 'Work Orders Routed[routed]'
    , pending AS 'Work Orders Pending[pending]'
    , processed AS 'Work Orders Processed[processed]'
    , acceptance AS 'Acceptance[acceptance]'
    FROM orders
    WHERE user = p_userid; SELECT avghittime AS 'Average Hit Time[avghittime]'
    , mdnhittime AS 'Work Orders Pending[mdnhittime]'
    , avgclosure AS 'Average Closure[avgclosure]'
    , avgpendingtime AS 'Average Pending time[avgpendingtime]'
    , areascovered AS 'Areas Covered[areascovered]'
    , processreuse AS 'Process Re-use[processreuse]'
    FROM orders_metric
    WHERE user = p_userid; -- potentially more queries below that would add another series of column/values SELECT 'dbr.pageviewgroup.close'

  4. myDBR Team, Key Master

    why not just use dbr.pageviews with dbr.keepwithnext? What is the advantage that the result sets would be in same table?

    myDBR Team

  5. nsepetys, Member

    The advantage is aesthetics and cohesion between datasets. It also matches the client-facing summary that we're phasing out so I'm trying to avoid changing the styling as much as possible.

  6. myDBR Team, Key Master

    You can do quite a bit with just some HTML and CSS while still keeping all the functionality that myDBR offers out from the box.

    See demo.

    myDBR Team

  7. nsepetys, Member

    That's what I thought. My HTML and CSS skills are fairly limited so even this type of (what I'm sure was for you a quick throw together) stuff is pretty time consuming. Thanks for your help though. I think that demo will be useful for me.

  8. nsepetys, Member

    For anybody that is looking for a solution to somewhat quickly designing a custom table structure- I ended up using MS excel to create the tables I was going for visually. From there I exported to an .htm and copied the HTML and CSS into the stored procedure. While the stored procedure looks a little more overwhelming with all the html, css, and mysql code living in one doc it helped me nearly duplicate the original design I was going for in a fraction of the time. I suppose there are other applications that will export to HTML/CSS without having to spend what, for me, would be hours tweaking it but excel was by far the easiest and most accessible for me based solely on my personal experience with it.


You must log in to post.