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?
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?
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.
Thanks Dave, the NoFilter worked like a charm, glad you responded
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