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

Respondida

José Miguel Sánchez Jiménez

• 20 de noviembre de 2020 07:06

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.

 

Vote

0

• Comentario oficial

  

  Rasmus Jensen

  • 30 de diciembre de 2020 06:04
  • Editado

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

   

   
• 

  Luca Zanni

  • 29 de diciembre de 2020 12:04

  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

  • 30 de diciembre de 2020 06:06

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

  José Miguel Sánchez Jiménez

  • 30 de diciembre de 2020 07:55

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

Iniciar sesión para dejar un comentario.