Skip to main content

How to produce a simple drill-down dashboard like this ?

Answered
José Miguel Sánchez Jiménez

I'm struggling to produce, using B1 Dashboard or any other B1 tool, what I thought was a very simple drill down report.

Say the query returns this data:

Country Customer Code InvNumber InvDate InvBase InvTax InvTotal
USA C001 INV001 3/2/19 140 28 168
USA C003 INV002 8/2/19 160 16 176
USA C001 INV003 4/6/19 240 48 288
CANADA C002 INV004 18/6/19 250 50 300
CANADA C002 INV005 22/6/19 100 20 120
USA C001 INV006 8/8/20 190 19 209
CANADA C004 INV007 21/10/19 300 60 360
USA C003 INV008 23/10/19 175 35 210
CANADA C004 INV009 30/12/19 220 44 264

I'd like my first level (dimension) to be the country, so that the initial report view is the following:

Country InvTotal
CANADA 1.044,00
USA 1.051,00

When the USA row is clicked, it should drill down to the second level (dimension) which is Customer Code:

Customer Code InvTotal
C001 665,00
C003 386,00

 

So far so good. Now, when for example the first customer is clicked, I'd like all details of the underlying invoices for the clicked customer, like this:

InvNumber InvDate InvBase InvTax InvTotal
INV001 2/3/19 140,00 28,00 168,00
INV006 8/8/20 190,00 19,00 209,00
INV003 6/4/19 240,00 48,00 288,00

This seems not to be possible, because if I include InvNumber, InvDate, InvBase and InvTax as dimensions, they will be presented as subsequent drill levels instead of all at once as shown above.

How could this be achieved ? All my reports should have such a set-up: several dimension through which one can drill down and then, show all additional dimensions along with the measures without further drilling down.

Thanks in advance for any help.

 

0

Comments

4 comments

Date Votes
  • Official comment
    Rasmus Jensen
    • Edited

    Try to have a look at this sample (not 100% what your seeking but close): https://youtu.be/NGdWaiDLaws

     

     

  • Luca Zanni

    Hey Jose,

    I understand your issue. I managed to hide information on higher levels of a pivot table by applying a white text colour conditional formatting (matches the cell's white background colour) on the interested columns on all levels but the last one. This way the column headings will still be showing up, but the data will be invisible until you get to the last level of your pivot table.

    Just keep the text colour the same as the background colour and you'll be fine.

    Cheers

    Luca

  • Rasmus Jensen

    Alternative the final details could be in a sub-report you navigate to: https://youtu.be/fHMbYNJsz9s

  • José Miguel Sánchez Jiménez

    Thanks for the tips. I think I'll go for opening a new dashboard for the final details.

Please sign in to leave a comment.

Didn't find what you were looking for?

New post