Help with displaying data

[hopkinsc]

Hi,

I need help with how my data is displayed...

I am wring an app to report on WASTE and REDUCTIONS. there are several WASTE / REDUCTIONS reason descriptions. I am displaying the results in a STRAIGHT TABLE and I have a Drill-Down group with 5 drilldown functions.

DEPT

CAT

SUB CAT

PRODUCT

RETAILER

The problem i am facing is that if a product has been sold at a reduced price for multiple reasons

e.g. 1 bottle of coca cola was sold reduced (manually) , and 1 bottle of coca cola was sold reduced (by scanning a reduced to clear label)

this gives 2 reasons for the same product description. So when the end user has selected DEPT from the drill-down group, there will be 2 instances of SOFT DRINKS.

I would want the DEPT, CAT, SUB CAT to only show 1 instance . then when the user selects PRODUCT, this would display both reasons for the user to analyse.

Can anyone tell me how i could achieve this?

[pover]

Is there a reason why you don't use a pivot table?

Also, you could use the concat() function to combine reasons in one row. You might even be able to do an if that is like the following:

if(Drilldown_Group='PRODUCT',REASON,concat(REASON,','))

Regards.

[hopkinsc]

Thanks Karl, i will try that.

The reason i havn't used a pivot table, is that although this would solve that problem, the column REASON is hidden until the user selects PRODUCT from the drill-down. If i use a pivot table, this would take the ability to conditionally hide columns. 😞