Pivot Table total issue.

[infosense_devel]

Hello All,

i have created one pivot table with hide/show functionality for its dimension and expression columns.

as per below.

Now we have requirement where we need to have sum for each expression, if i take example of above image, there should be total of actual and budget. only simple total same like we are usually having in simple table.

Any idea how to achieve it?

[YoussefBelloum]

Hi,

did you try to check the "Sub totals" on the dimensions ?

[infosense_devel]

thanks for your reply.

but i dont know what dimensions will user select from that dimension list box.

[YoussefBelloum]

No problem, it is called ad-hoc table, it is on demand.

so on the table, you should have all your dimensions already used on the dimension tab.

and for the display, it is dynamic with something like this:

=SubStringCount('|' & Concat(distinct ad_hoc_dim_field, '|') & '|', '|Model_dimension_field|')

so you can activate all the sub total for all the dimensions.

[infosense_devel]

but here my requirement is to have only one total you can call grand total. 

for each expression column i need one total.

[YoussefBelloum]

Yes, for each measure, just under the expression label, you will have a TOTAL line,

That's what you're trying to do ?

[infosense_devel]

Yes

[YoussefBelloum]

so the only option you have is the one I mentioned above.

Is it clear ?

[infosense_devel]

yes in that case if i will select three column from dimension i will have three same amount as total.

means three line for total with same value.

[YoussefBelloum]

you will have this with the selections made on the first screen shot (no matter how many dimension you will select, it depends only on the number of measures)