Challenge manipulate Pivot-table from cell ranges

[xluser2007]

Hi All,

I want to try and use Pivot tables to allow for flexible reporting to clients.

More specifically, i would like the user to change field names in cells, and have the pivot table update automatically.

To do this, I am experimenting with a simple dataset (sourced from here (http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553)), and am trying to understand how to manipulate further.

I want to build a really simple front end where the user selects from drop down lists for the page fields, row fields, column fields, data fields.

I have started building it.

Could anyone please, as a start show me how to make the pivot table change by allowing the user to change the cells L1-L4, in my attachment.

Any help appreciated,

[Bob Phillips]

What about the field in the area should that stay.

Maybe a better way would be to show the fields and the dropdown to say whether they appear in say Pagf, Row or Column.

[xluser2007]

What about the field in the area should that stay.

Maybe a better way would be to show the fields and the dropdown to say whether they appear in say Pagf, Row or Column.

Hi Bob, long time since we have spoken. Hope you are well.

With regards to your suggestion, I don't quite follow sorry. I am open for anything, as this is more an experimental thread, which i am hoping to convert to a live sample for work, depending on the options and suggestions.

The way I have it structured, is a cascading dropdown of the fields listed (as a pivot table can have each field used only once for each row, column, page or data section). I was hoping to for starters link these drop down menu's and when the user updates the dropdown list, the pivot table layout would change.

I.e. getting the user to effectively change the pivottable layout using a more user friendly dow menu is a one0line summary of my problem.

I;m sure you are 2 steps ahead of me though Bob, just wanted to reclarify my exact intention and seek clarification on your suggestion.

Thanks,

[Bob Phillips]

What I was thinking that if you align the fiedls to a PT region, you can only have one per region (what happens with more than 4 fields). Moving them also gets complex.

If you instead align the PT region to a field, the moving is simple, and you can have more than one per region.

Here is an example.

[xluser2007]

What I was thinking that if you align the fiedls to a PT region, you can only have one per region (what happens with more than 4 fields). Moving them also gets complex.

If you instead align the PT region to a field, the moving is simple, and you can have more than one per region.

Here is an example.


That is brilliant Bob!

A far better simplification of the convoluted way i was going about it (great to learn design).

Just one thing, I tried adding the field "Amount" in L4 and copy the validation down to M4. Everytime I keep changing the dropdown for amount and error appears. Please see attached. Any ideas what the issue may be?

Also If I wanted the Pivot table to change in Sheet2, and have the corresponding pivotchart change in sheet3 how would we amend the program for this?

Thanks again, I will try in the emantime to think of a slightly more complicated example to build on your technique.

regards

[Bob Phillips]

Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Dim PTArea As String
Dim PTFName As String

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Range("M1:M4")) Is Nothing Then

With Target

Select Case .Value

Case "Page": PTArea = xlPageField

Case "Row": PTArea = xlRowField

Case "Column": PTArea = xlColumnField

Case "Data": PTArea = xlDataField
End Select

PTFName = .Offset(0, -1).Value & IIf(.Offset(0, -1).Value = "Amount", "", " ")
With Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields(PTFName)
.Orientation = PTArea
.Position = 1
End With
End With
End If
End Sub

[xluser2007]

Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Dim PTArea As String
Dim PTFName As String

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Range("M1:M4")) Is Nothing Then

With Target

Select Case .Value

Case "Page": PTArea = xlPageField

Case "Row": PTArea = xlRowField

Case "Column": PTArea = xlColumnField

Case "Data": PTArea = xlDataField
End Select

PTFName = .Offset(0, -1).Value & IIf(.Offset(0, -1).Value = "Amount", "", " ")
With Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields(PTFName)
.Orientation = PTArea
.Position = 1
End With
End With
End If
End Sub

That works very well indeed Bob, many thanks :clap:.

Ok, I have been playing around with this, and I have a few slight issues to consider/ and thoughts on further development.

1. The data field can have the same variable mutiple times. That is you can put Amount variable in the row field and the data field. This is fine per say. But because this is a worksheet_change event, every time you keep changing the Data field on say amount, the Pivot table keeps adding the field again and again e.g. "Sum of Amount1", "Sum of Amount2" etc, which is not quite ideal.

2. I reckon the fact of having the user change the row field, column field for every data variable is a very clever and flexible idea, but does cause the above possible issue to surface, and also, the user probably won't get "row" and "column" per se in a front end context and may find it easier just to toggle variable names.


So what kind of comprimise method can we use? I was thinking the pivot table that would be created would have a fairly limited flexibility/ modification scope (becasue it doen't really make sense to have the user have adjust all variables as page fields for example, as the pivot chart, the main output from all of this would look a little meaningless - if this application gets used in a business context, Else this complete flexibility method in itself is trerrific).

What I mean by the above is, On the side fix just 2 fields for row 2 fields and similarly for column and page. And fix the maximum number of fields for data i.e. there are 4 fields in this case, so there should be 4 field selections for data.

For the row, column and page fields, the user should select the variables from a drop-down to match these Pivot Fields. They should be as a consequence be built as a cascading query. As in my first example. As there can only be one variable per these fields.

For the data field variable selection, this should again be a cascading query, but the user should be able to select all variables again, as the data field can allow duplicate entries per field i.e. Amount in the row field and sum of amount in the dat field. The onle issue is if it is a worksheet_change event, can we reset the data field every time to prevent the issue#1 identified above.

I have attached a samnple workbook, with your already aweome working version, but with anew sheet with the suggested menu as above.

Please let me know what you think. Really keen to hear your thoughts.

regards

[Bob Phillips]

What you saya is true, but you could also make the opposite argument. Why shouldn't the Sum appear twice in a pivot, sometimes this is wanted to get a summary/alternate view.

Also, whilst this migt be made to 'work' for a few fields, it will become very cumbersome with many fields.

Personally, I think this whole approach is wrong. If you think you need to do this because the user can make a mess of a pivot, you can bet your life they can also mess up your UI. I would take half an hour and teach them how to use pivots, then its up to them. You can take a horse to water ...

[xluser2007]

What you saya is true, but you could also make the opposite argument. Why shouldn't the Sum appear twice in a pivot, sometimes this is wanted to get a summary/alternate view.

Also, whilst this migt be made to 'work' for a few fields, it will become very cumbersome with many fields.

Personally, I think this whole approach is wrong. If you think you need to do this because the user can make a mess of a pivot, you can bet your life they can also mess up your UI. I would take half an hour and teach them how to use pivots, then its up to them. You can take a horse to water ...


Bob, very fair points - all good discussion for such a thread.

I have had more of a think and here is what I still feel.

Forgetting all about Pivot Tables, the way I am seeing this being used is allowing the user to dynamically drill down a dataset.

As I work in finance and not programming, it is easier for me to try and explaoit Excel's built in feature for such events i.e. Pivot Tables, hence the reason for such an approach.

Now based on my previous thread, I mentioned severly restricting the felxibility of the pivot by the user. You very fairly pointed out that this is not necessarily ideal, as the user may want to Sum the same variable twice in the data field, for example. I guess what I want to say is that from my perspective, I really don't want to allow this for the user. Not becasue it is wrong, but this should just allow very basic sum, or count of a single field at a time, for the purposes of the report I am thinking of applying this to (again I know I am "changing the game" or so to speak, but that is because I am understanding it better as we go along ;)).
My purpose is not to hide anything from the user per se, but just want them to chart in a particulkar way for a report. And having a restricted PT template and a PivotChart that links of it would be ideal for this exercise, as it would save having to create every combination of graph using VBA, and save memory and confusion that way.If we wanted to add in a functionality to do 2 sums for a single variable, again, I would like to put a drop down next to the datafield drop diown that has 1, 2, 3 which gives the user the ability to sum once, twice or thirce respectiuvely (of course the VBA may be a bit tricker)

The end game is, just creating a single sturdy template for the pivot table, but allowing that template to be controlled by a clean easy to use drop down menu. Where it appears that even for the given template, the flexibility is lacking, it should just be modified by adding more drop-downs.

My coding is really not that flash, and the kinf of event code you whipped up in 5 mins, would have taken me weeks to come up with.

As such, for the sake of experimenting, is it possible for you to please show me how we could do the slight variant of your method as suggested in my previous post. I don't mean to bother, but I am learning lots from this thread.

Also your point about having many fields making this approach cumbersome making this approach cumbersome is also valid. At this stage though, I am just so keen to see how it works for small fields and seeing if we can build in more functionality for more if this works.

Thanks sincerely for your advice and help Bob - hope you stick around a bit longer on this experiment!

[Bob Phillips]

I think that a simple dropdown is not ideal because you can never convey in that DD what the effect of their selection will be. You can do it in a user guide but then you might as well show them how to use a PT properly!

A better way IMO is to have some an array of selection criteria that covers all of your preferred views. This is the essence of a dashboard.

I also do not like pivot charts (I am not the greatest fan of pivots per se, but certainly not pivot charts). I would provide a chart dashboard, which when clicked on shows the expanded chart.

I am off for a bike ride now, but I will take a crack at this on my return.

I am quite happy to continue this thread as long as you like, in whatever direction you want.

[xluser2007]

I think that a simple dropdown is not ideal because you can never convey in that DD what the effect of their selection will be. You can do it in a user guide but then you might as well show them how to use a PT properly!

A better way IMO is to have some an array of selection criteria that covers all of your preferred views. This is the essence of a dashboard.

I also do not like pivot charts (I am not the greatest fan of pivots per se, but certainly not pivot charts). I would provide a chart dashboard, which when clicked on shows the expanded chart.

I am off for a bike ride now, but I will take a crack at this on my return.


Hi Bob,

Thanks for the update. Actually the one-word description you have put is the perfect summary for this i.e a Dashboard. I should have said that earlier when I we were discussing using drop-down to drill down a pivot table.

I'm also not a big user of PivotCharts. If they are based on a Pivot Tbale, they always move dynamically with the change in the pivot table format (as they are designed). I would prefer if they could be adjusted separately to the pivot that they are linked to, but for this example, i thought they would be ideal as we want the user to change the pivot and have the resultant chart as per the exact format of the pivot. This was my motivation for the inital suggestion.

As such, I am really keen and open to this new dashboard interface you are suggesting.


I am quite happy to continue this thread as long as you like, in whatever direction you want.
This is a really kind offer Bob, and I really do appreciate it. It's one thing to write up code for someone, but it's another thing to so persistently help others that is the mark of a true teacher. Thanks heaps.

regards,

[Bob Phillips]

Okay, here is my first crack at it.

[xluser2007]

Okay, here is my first crack at it.
This is awesome stuff, way more advanced than what i was thinking :clap:.

I'm going to play with this over the next couple of days and given that you have it to this point, I;ll try to come up with a work example on a larger dataset (i.e. larger dataset would mean that more complex combinations of variables that may arise).

Thanks!