very very important....how to add a row in pivot table

[#1]

hi

i am creating a pivot table in which i am taking a column from database as row. from database i am getting six different rows but i want to add 2 more rows showing the total of these 8 rows and their count.

how can i do that.....plz help me

[Anonymous]

I don't see how to add two total rows, but you can show both sum and count in one row, for example by using expression like this:
if(rowno()>0,
sum(Price)
, 'sum=' & sum(Measure) &';  ' & 'count=' & count(Measure))

[#3]

could u be more specific plz...tell me where to write that expression and how to show which is count and which is sum???

[#4]

in addition to it i want to know whether i can add values in the dimension fetched from database,while using it in the pivot table.

in mycase i need to sum of amount of top10 and for others....

[Anonymous]

Attaching an example.

Chart 1 contains expressions for sum (total) and count.  Chart 2 - only sum, but both "total" and count are in the total row.

It is not adding two rows, but providing both totals in one row.

[#6]

hi Michael

this is not what i am looking for...in my case i am applying logic on every value of the dimension to get the desired results in the columns...

regards

Vitul

[Anonymous]

Every value? - than the chart 1 is what you need.

Or maybe chart 3 - see attched.

[#8]

hi Michael

thnx for your help....

this is not what i am liikong for....let me explain in more detail....for that i am goin to attach an image..it wud be easier for u to understand that....

here in image...i am using a column 'provider' as a dimension from the database and i want the top 10 provider on the basis of another column 'amount'.

after that i have to show the sum of top 10 in the pivot table and sum of the rest of providers in another row..tell me how to achieve it plz???

[#9]

hi michael and theses values jp,rs,shine etc are coming from database and it will keep on changing on the basis of the 'amount'....

regards

Vitul

[Anonymous]

Vitul,

This is certainly possible, with a minor exception - "all other" is above "total of top 10".
Here is how to implement it:
1. Your dimension should be a calculated dimension, using rank() function based on the sum of Amount (or whatver expression you need), which filters out dimensions values with rank<10.  It is dynamic, changes after reload as data changes.  Do not check "Suppress When Value Is Null" - it will keep your "all others".
2. On the Presentation tab, replace default "Null symbol" with the text "all others"
3. Expressions will be conditional, based on rank, and different for total.  The logic is like this:
If it is not "total" line, use expression as is (e.g. sum(Amount))
if it is the total line, the expression is conditional and includes only values if dimension has rank>10 (e.g. sum(<rank-based conditions using if or set analysis> Amount)).
I don't have time for an example now.  Will get to it later, unless somebody else gives you an example before that.

Regards,
Michael

Edit: See atachment, chart 4.  I think it is very close to your requirement.