Table Help

[NikkiG12]

Hopefully this is in the right place for this question. 

For the report I am creating, I want to use a table to do the following function:  collapse all columns so only the totals appear but the user can open up the table as needed. Is this even possible to do? 

I was thinking this might be done with a Matrix but not sure if I can collaspe the columns but the fields would be:

Rows: Vendor
Column: Product
Values: # of customers, # of purchased product, total $ of purchased, avg $ of purchased

Any ideas?

[parry2k · Accepted solution]

@NikkiG12 sure you can have value as "Vendor" and "Product" for the dummy columns. 

Can you share the screenshot of how you are using these columns?

[parry2k · Accepted solution]

@NikkiG12 for columns, make sure the drill on is changed to columns

[parry2k · Accepted solution]

@NikkiG12 If you have no hierarchy on rows then by default it will be columns, but if you have both, I don't think you can set anything default then the user has to chose.

[parry2k · Accepted solution]

@NikkiG12 for columns, make sure the drill on is changed to columns

[NikkiG12]

You are amazing! That worked for me. 

Final question, how to I ensure columns stays as the drill on so my users don't have to keep switching? If thats not possible thats fine. 

[parry2k · Accepted solution]

@NikkiG12 sure you can have value as "Vendor" and "Product" for the dummy columns. 

Can you share the screenshot of how you are using these columns?

[parry2k]

@NikkiG12 create a dummy column in your table, one for the rows and one for columns and in the matrix visual, use this dummy column before vendor and similarly use the dummy column above columns. It will create a hierarchy and by default, everything will be collapsed.

[NikkiG12]

Thanks for the quick reply @parry2k.

I just gave it a try so made a dummy row = 0 and dummy column  = 0, I see how it works for rows, but it doesn't seem to let me expand the columns to show the products. Are you able to show how this would work a bit more? I appreicate your time. 

Also silly question here, if I wanted to do row_dummy = Vendor will that work?