Calling all PivotTable users: Let us know how you like to summarize your data...

As a reporting specialist I use the pivot functionality a lot.  With a little personal training I've shown Excel users the power of pivot tables and they are amazed at what they can do.  Something we've 'discovered' is that Excel is a very good reporting tool, it's just that people are poorly -- if ever -- trained to exploit this powerful tool!  Business people grow up with Excel ;-)

"% of Parent" would be a nice-to-have option.  I would like to have the option to place two or more data items together in a row or column instead of the additional items becoming subgroups.  Example using columns -

This is what's normal:

[Page Field]

............

............[data series 1]

............[data series 2]

[row series 1]....xxx

[row series 1]....xxx

This is what I would like:

[Page Field]

............

............[data series 1][data series 2]

[row series 1]...xxx.............xxx

Funky, I know, but I've run into instances where it'd just make my life easier if I could "join" two or more fields into one data field.  Such an option would be best if it was NOT a default but required the user to specify the action.

ACN: You can drag the 'Data' button to the column area to show the data items side-by-side.

The three things I would like to see are:

 Distinct Count

 Distinct Count

 Distinct Count

And the ability to have totals and grand totals on only some data fields. E.g. If I have a table with price and revenue by product, I want to show totals for the revenue but not the price. Or if I have Local-currency-revenue and Reporting-currency-revenue by currency and product, I want to be able to total them both for the currency sub-totals but only the Reporting-currency-revenue for the grand totals.

In the past I've added another column to show "% of total". I like to see the totals as well as the %.

Our users have not been able to find this functionality in general when accessing SQL 2K5 OLAP cubes. The only way to get at these options AFAIK is through the "Field Settings" dialog box. Even at that, you have to switch tabs to see "Show Values As" options. This is one area that is a step back from OWC, IMO.

In a cube context I think the OWC options & interface was quite good - context menu for "Show As", options for:

Percent of Row Total

Percent of Column Total

Percent of Parent Row Item

Percent of Parent Column Item

Percent of Grand Total

When pivoting on non-OLAP data I also think Distinct Count is high-priority / glaring omission.

Thanks!

Yes, I have used this functionality from time to time and it's so well hidden, every time I use it I keeping expecting that bells will go off and I'll be awarded a prize from Microsoft for actually finding and using it.  Even if you find it, it's not at all obvious how to use it.  For example, the user shouldn't have to drag multiple duplicate data fields into the table.

I often need to compare 2 columns of data (usually 2 years) with a difference column.  I use:

Show data as:  Difference from

Base field: Year

Base item: (previous)

This works but annoyingly always includes a blank column (because there is no prior year 2 to compare to) and unnecessary Total columns.  I hide all these manually.

I would LOVE it if there were a built-in easy way to simply change from the usual:

Column A | Column B | Grand Total (A+B)

to:

Column A | Column B | Difference (A-B or B-A)

without jumping through so many hoops.

To Air_Cooled_Nut:

I may be misunderstanding what you want, but I think the solution to putting your 2 data series in columns instead of rows is to simply drag the Data column header over to the right into the column area.

would be nice to have the option to summarize using a VBA / XLL udf function. Or is that already possible and I missed that feature?

1. Yes.

2. n/a

3. I do not "commonly" use pivot table because I am spoiled by a 3rd party "Excel friendly OLAP" product. That said, it offers great value as a poor man's OLAP. Of course the most common application is: quick and simple cross tabulation.

4. Jeff's enumeration of options for Percent-of-WHAT is right on point, although the average user might find the term "subtotal" or "group" more intuitive than "parent." I also vote for Count Distinct in the summary options. How about Median?

On a related, broader topic: The team needs to think hard about discoverability and intuitiveness. It drives me crazy that so many intelligent people will not undertake to learn to use pivot table at all, but it is not entirely their fault. In that regard, here is one small point for the features under discussion: "show values as" really does not convey much without further exploration to discover this is about relationships between data.

I would like to see enhancements to thepivot cache tools currently in place.  

-Ability perform *accurate* weighted averages (aka SumProduct) within the GrandTotal and Subtotals.

-The ability to group items without having to include them in the pivot table first.

-The ability to return text values from a  calculated field.

(i.e. If('Revenue'>1000,"Over", "Under").

- The abilility to create Calculated Items in a pivot table where averages and grouped items are being used.

I'm sure someone will tell me this is possible...but here goes

I'd like to be able to do more totalling in columns:

eg at the moment it is:

Group1       Group2         Grand Total

Data1 Data2  Data1 Data2

What I'd like is

Group1       Group2         Total        Grand

Data1 Data2  Data1 Data2    Data1 Data2

Does that make sense?

I would love to see

a) % of Actual total

i.e If I chose to filter data for a field

The % of total readjust according to the filtered result.

What I would like is a % of the Total without the filter

eg :

A : 20%

B : 30%

C : 40%

D : 10%

Tota : 100%

Lets say I remove c and d

I would like A and B to continue showing 20% and 30% w.r.t Actual total

In addition to some useful functionality already mentioned, I'd like to see the following PivotTable enhancements:

1) A calculated field feature that works reliably under all conditions without calculation errors.  Why bother having the feature otherwise?  In most instances, the calculated field could be added to the source data but there are circumstances where it'd be useful to add a calculated field to the PivotTable e.g. some instances involving complex subqueries or where the end user would not have access to the source data.

2) A more useful multiple range consolidation feature.  After you specify the multiple ranges (or Tables) for consolidation, the PivotTable field list would simply contain the column headings from the ranges or Tables.  The actual PivotTable will be summarizing the data across the multiple ranges or Tables based on the fields included in the PivotTable.

3) An option to have one set of common headings when elements in a hierarchy have similar titles (easily done in Crystal Reports) e.g.

Part Number Description  Quantity

1000-3900 xxxxxxxxx 1

 2001-3700 xxxxxxxxx 2

   3001-4200 xxxxxxxxx 4

   3001-4300 xxxxxxxxx 4

 2001-3800 xxxxxxxxx 2

 2001-4700 xxxxxxxxx 4

   3001-4500   xxxxxxxxx 8

The data for the above report comes from an external database.  The information can be brought into Excel in multiple tables or can come from multiple database table views.  To get to the lowest part level requires two self-joins on a table.  Each resultset has the same field names and includes a field with the associated parent part number.

4) Last n Months/Years date filter (also for ranges/Tables)

5) Formula based filter (also for ranges/Tables)

6) Ability to apply number formatting to multiple data fields simultaneously

My mistake with terminology :-(  Instead of using "[data series x]" I should've written "[column x]" or "[field x]" -- please substitute accordingly.  The three little x's in my example would be considered the aggregate Data values and they are of no concern.  Let's try this again.  Current way:

[Page Field]

______________[Field 1]

______________[Field 2]

[row series 1]___xxx__

[row series 1]___xxx__

[row series 2]___xxx__

[row series 2]___xxx__

What I would like to do:

[Page Field]

______________

______________[Field 1][Field 2]

[row series 1]___xxx______xxx___

[row series 2]___xxx______xxx___ ...

Thanks to Excel MVP Stephen Bullen for helping me see my error :-)

First, not to get too far off track, but I need to express my thanks.

Thanks to the Excel team past and present for their work on pivot tables. It's largely because of their work on the mysterious and, to some, intimidating nature of the tool that I have my current Reporting Analyst position. I, thankfully, took the time to learn and understand what other Excel users ran from.

I've been using pivots since '97. I've led training sessions at our office with dozens of fellow staff so that they can make good use of the tool too. Skill with pivots has earned me a fair bit of $$, so, sincerely, "Thanks!".

In response questions:

1 - Yes.

2 - N/A

3 - Almost alway one of % of row, % of column, or % of total. Usually used for budget or sales analysis.

4 - % of parent would be exceedingly useful. Would've loved to have had that at times before instead of jumping through hoops. But making it easy takes all the fun out of it, right?

Maybe this feature exists, and I can not find it.  What my users want is a similar column grouping/composite function that Excel OLAP cubes provide, e.g. A is B + C + D, and E is F + G.  In essence it lets you do structured drill down on the columns.

This should be possible n-levels down, e.g. need to nest this functionality.

Should be able to assign different colors to A and E than to the other columns.

We would like to expand and collapse A and E, and when A is expanded, A, B, C, D should show.

When all columns show and you select across one row to see a sum,totals should not show double (e.g. select A, B, C, D, E -> sum is A + E.

It would be fine with me if this could only be achived programmatically, since we generate the pivot tables programmatically anyways...

Thanks!!!

axel