Keeping top level total at all Drill Levels - Formula Issue

building reports webintelligence
1

Hi,

I have a report that Drills down from Market Group to Market and I want to be able to see the Total Company Sales at all levels so I can perform a calculation. I’ve built the below formula and it gives me the right result, however my report has keept crashing since putting the formula in.

=NoFilter([Sales Exc Vat]ForAll([Market Group];[Market]);Drill)

I’ve tried using the formula on a new report that only has Market Group, Market (Dimensions) and Sales measure and it keeps crashing that too.

I’m using XI R2 so don’t have the ability to use the NoDrillFilters() function. Does anybody have any alternative formulas I could use or and explanation as to why the above causes issues with my report.

Thanks

Mike

M_Cherriman (BOB member since 2008-02-07)

2

How does it crash? Have you tried it as a variable as opposed to a formula? I doubt that will fix it. If it doesn’t try making the variable as follows.

v1=Sales Exc Vat]ForAll([Market Group];[Market])
v2=NoFilter([v1];Drill)

Unlikely but it may be enough to get it to work…

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Thanks Nick, that seems to have fixed the problem. Do you know why it was causing the problem in the first place?

M_Cherriman (BOB member since 2008-02-07)

4

I counted the parenthesis and maybe you needed:

=NoFilter([Sales Exc Vat]ForAll(([Market Group];[Market]);Drill))

In other words, maybe the drill should still be in parenthesis for the ForAll function?

bkaporch :us: (BOB member since 2008-12-02)