|
| |
• Up • AutoFilter • Count words • Sumproduct • Pivot Tables •
Excel - Pivot Table with Parameters
Connecting Excel to mainframe or major databases can provide the missing
link between a dedicated Data Warehouse, and a stand alone Excel sheet which
is maintained through copy and paste. The approach is highly scalable, and I
have successfully used these techniques in businesses with turnover ranging
from 5 Bn per annum to a few hundred thousand.
Using Pivot Tables allows you to exceed the 65,000 row limit, and to store
huge amounts of data in Excel. All that is needed is to ensure that the
resultant report to be displayed, is itself small enough to fit into Excel.
Even though Excel 2007 has no row limits, it is still preferable to use a
pivot table for the sake of speed and flexibility.
The one major element missing from Pivot tables, is the ability to allow a
user to select and change one or more parameters in a query. This would
allow you to tailor your queries so that you can extract smaller data sets,
which can turn a 30 minute data dump into a 10 second query instead.
The easiest way to do this, is to perform a search and replace on the pivot
table cache - which is where the query is stored.
To ensure that the process is repeatable, you have to store the results of
the last query replace in a cell, and then you can use the worksheet change
event to trigger a replace and refresh whenever a new value is input by a
user. A larger portion of the Period cache string must be defined to ensure
the replace function works with a unique string, otherwise the new period
number replaces part of the year. For example, if we start with 2008, and
period 2, and then select period 3, the search and replace function will
look for all 2's and replace them with 3, resulting in a query that is now
defined as Year 3008, Period 3.
I have used two common variables used in business reports as parameters:
Year and Month.
This exercise has made use of: Named Ranges, Data validation (for the drop
down selection lists), External Data Query, and Pivot table with Parameters.
The database is a csv file, but you can link to just about any database, and
to most accounting programs as well, using External Data Query and ODBC.
The end result is a tight, clean and seamless experience for the user.
The The Excel Sheet demonstrating this approach is here :
Pivot Table with
Parameters.zip
And the code follows here:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
Call Pt1
End If
End Sub
Private Sub Pt1()
OLDYR = Application.ActiveSheet.Range("A4").Value
OLDVAL = Application.ActiveSheet.Range("A5").Value
NEWYR = Application.ActiveSheet.Range("C4").Value
NEWVAL = "Period=" & Application.ActiveSheet.Range("C5").Value
Sheet1.PivotTables(1).PivotCache.BackgroundQuery = True
Sheet1.PivotTables(1).PivotCache.CommandText =
Replace(Sheet1.PivotTables(1).PivotCache.CommandText, OLDYR, NEWYR, , 1)
Application.CommandBars("PivotTable").Visible = False
Sheet1.PivotTables(1).PivotCache.CommandText =
Replace(Sheet1.PivotTables(1).PivotCache.CommandText, OLDVAL, NEWVAL, , 1)
Application.ActiveSheet.Range("A4").Value = NEWYR
Application.ActiveSheet.Range("A5").Value = NEWVAL
Application.ActiveSheet.Range("C5").Select
Application.CommandBars("PivotTable").Visible = False
End Sub | |
|
