Drilling and Contexts

building reports desktopintelligence
1

Hello,

I have a report, with drilling, that I am trying to show a Grand Total at the top of. I initially created the report with the grand total cell at the top and everything is fine. I created a hierarchy to allow drilling on the report. I turned drilling on. The initial grand total is still fine, but when I drill down it now takes the grand total for that page not the whole report. Is it possible to create a formula that would show the grand total for the whole report even when drilling?

Thanks in advance,

Stephanie

Listserv Archives (BOB member since 2002-06-25)

2

Hello,

I submitted this question early this week, does anybody have any ideas?

Thanks again,
Stephanie
Happy St. Patrick’s Day

Hello,

I have a report, with drilling, that I am trying to show a Grand Total at the top of. I initially created the report with the grand total cell at the top and everything is fine. I created a hierarchy to allow drilling on the report. I turned drilling on. The initial grand total is still fine, but when I drill down it now takes the grand total for that page not the whole report. Is it possible to create a formula that would show the grand total for the whole report even when drilling?

Thanks in advance,

Stephanie

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 00-03-17 12:47:43 EST, you write:

I submitted this question early this week, does anybody have any ideas?

I was tempted to say “Yes”, and leave it at that. :slight_smile:

However, the answer is found at the end of the email after the problem statement.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Thanks again,
Stephanie
Happy St. Patrick’s Day

Hello,

I have a report, with drilling, that I am trying to show a Grand Total at the top of. I initially created the report with the grand total cell at the top and everything is fine. I created a hierarchy to allow drilling
on
the report. I turned drilling on. The initial grand total is still fine, but when I drill down it now takes the grand total for that page not the whole report. Is it possible to create a formula that would show the
grand
total for the whole report even when drilling?

Thanks in advance,

Stephanie

The reason you are getting the behavior described above is that a drillable report essentially uses Filters. Each time you drill you are invoking a new filter. The standard sum and context operations are affected by filters. That explains why your top level total does not stay a “top level” total as you drill.

The solution for this is to create a formula that ignores filters. The first step:

= Sum() In Report

… creates a formula to always provide the overall total. Adding the NoFilter() function:

= NoFilter (Sum () In Report)

… creates a formula that will always display the grand total, no matter what level of drilling (filters) has been invoked. The NoFilter() function is only available (to the best of my knowledge) in 4.1.1 or higher.

Dave

Listserv Archives (BOB member since 2002-06-25)

4

Thanks Dave, the NoFilter worked like a charm, glad you responded :slight_smile:

Stephanie

SOLUTION:

The solution for this is to create a formula that ignores filters. The
first step:
= Sum() In Report
… creates a formula to always provide the overall total. Adding the NoFilter() function:
= NoFilter (Sum () In Report)
… creates a formula that will always display the grand total, no
matter
what level of drilling (filters) has been invoked. The NoFilter() function is only available (to the best of my knowledge) in 4.1.1 or higher.
Dave

Listserv Archives (BOB member since 2002-06-25)