Excel : Filter pivot data dynamically using macro
In this post I would like to explain very easy method to filter pivot data dynamically using macro.
To demonstrate this, I have used Adventureworks database AdventureWorksDW2008. Query used is to find the sales amount in each sales territory for each product in every year.
CREATE view [dbo].[v_Exceldashboard]
AS SELECT EnglishProductName,
CalendarYear,
d.SalesTerritoryGroup,
SalesTerritoryRegion,
SalesAmount,SalesAmount - TotalProductCost as profit ,
EnglishMonthName
FROM dbo.FactInternetSales a
INNER JOIN dbo.DimProduct b ON a.ProductKey = b.ProductKey
INNER JOIN dbo.DimDate c ON a.OrderDateKey = c.DateKey
INNER JOIN dbo.DimSalesTerritory d ON a.SalesTerritoryKey = d.SalesTerritoryKey
Connect Excel to this database and retreive the result of the above query into one sheet and we can create pivot data out of this result set as shown in Fig 01.
Above pivot data can be filtered by territory as per the user wish but I have received change request from the client to filter the pivot data by typing the required value in one text field rather than selecting from the pivot filter area as shown in Fig 02.
we can achieve this by implementing the below steps.
1. Create macro which capture the user entry from the field
2. Call this module at the change event of the field. Here E1 is the field where user can give the filter value.
When ever the change is happening to any of the filed in the worksheet happens this script also calls as it is given in the change event of the worksheet which is very performance degrade. So in order to avoid this situation we can add one more condition to check the change is happening to desired field or not by checking the address of the change event.
3. Final step is to hide the original filter so that user feel that only one option is there to filter data.
To demonstrate this, I have used Adventureworks database AdventureWorksDW2008. Query used is to find the sales amount in each sales territory for each product in every year.
CREATE view [dbo].[v_Exceldashboard]
AS SELECT EnglishProductName,
CalendarYear,
d.SalesTerritoryGroup,
SalesTerritoryRegion,
SalesAmount,SalesAmount - TotalProductCost as profit ,
EnglishMonthName
FROM dbo.FactInternetSales a
INNER JOIN dbo.DimProduct b ON a.ProductKey = b.ProductKey
INNER JOIN dbo.DimDate c ON a.OrderDateKey = c.DateKey
INNER JOIN dbo.DimSalesTerritory d ON a.SalesTerritoryKey = d.SalesTerritoryKey
Connect Excel to this database and retreive the result of the above query into one sheet and we can create pivot data out of this result set as shown in Fig 01.
![]() |
Fig 01 |
Above pivot data can be filtered by territory as per the user wish but I have received change request from the client to filter the pivot data by typing the required value in one text field rather than selecting from the pivot filter area as shown in Fig 02.
![]() |
Fig 02 |
we can achieve this by implementing the below steps.
1. Create macro which capture the user entry from the field
![]() |
Fig 03 |
2. Call this module at the change event of the field. Here E1 is the field where user can give the filter value.
![]() |
Fig 04 |
When ever the change is happening to any of the filed in the worksheet happens this script also calls as it is given in the change event of the worksheet which is very performance degrade. So in order to avoid this situation we can add one more condition to check the change is happening to desired field or not by checking the address of the change event.
3. Final step is to hide the original filter so that user feel that only one option is there to filter data.
Comments
Post a Comment