How to produce a simple drill-down dashboard like this ? Answered
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.
Comments
4 comments
Try to have a look at this sample (not 100% what your seeking but close): https://youtu.be/NGdWaiDLaws
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
Alternative the final details could be in a sub-report you navigate to: https://youtu.be/fHMbYNJsz9s
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.