I need urgent Excel Help

A

Achuks

New Member
Joined
Apr 27, 2015
Messages
26
Hello All,
I appreciate your great contribution to this forum, and in recognition of your wealth of knowledge I write to seek your assistance on an issues I am having. I have pivot table with data from cube, I need to filter the table based on the value of two date field Begin and end date. I have tried some of the script you used to assist in some issue but no success. I have sample of the data and Macro recorded filter script below. Thank you


Sub FilterMacro()
'
' FilterMacro Macro
' Date FilterMacro
'


'
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Date Dimension].[Fiscal Month].[Fiscal Month]").VisibleItemsList = Array( _
"[Date Dimension].[Fiscal Month].&[2013\14]&[5]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[6]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[7]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[8]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[9]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[10]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[11]", _
"[Date Dimension].[Fiscal Month].&[2013\14]&[12]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[1]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[2]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[3]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[4]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[5]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[6]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[7]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[8]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[9]", _
"[Date Dimension].[Fiscal Month].&[2014\15]&[10]")
End Sub

[TABLE="class: cms_table, width: 1225"]
<tbody>[TR]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]1/01/2010[/TD]
[TD][/TD]
[TD]This Data is from MS SQL Cube , I want to filter using Begin date and End Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD="align: right"]30/06/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Jan-2010[/TD]
[TD]Feb-2010[/TD]
[TD]Mar-2010[/TD]
[TD]Apr-2010[/TD]
[TD]May-2010[/TD]
[TD]Jun-2010[/TD]
[TD]Jul-2010[/TD]
[TD]Aug-2010[/TD]
[TD]Sep-2010[/TD]
[TD]Oct-2010[/TD]
[TD]Nov-2010[/TD]
[TD]Dec-2010[/TD]
[TD]Jan-2011[/TD]
[TD]Feb-2011[/TD]
[TD]Mar-2011[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (Central)[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (North East)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (South East)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (South)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (Sydney Metro)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC (West)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF (Central)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF (North East)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF (South East)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF (South)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF (Sydney Metro)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Which is the first month of each fiscal year?

Would the code need to test for missing months or are you sure that for the date span selected, there is data for each fiscal month?
 
Upvote 0
The first month of each fiscal year is July. which spin between previous year and current year .
 For instance the full year is July 2014 to June 2014. There is always data for each fiscal month.

Thank you Brother.
 
Upvote 0
Here's some code for you to try. I'm not able to test it since I don't have access to the OLAP cube you are using.

Copy all this code into the Sheet Code Module of the Sheet that has the Pivot Table and cells that will hold the Start and End dates. To get to the Sheet Code Module, Right-click on that Sheet's tab > View Code

Modify the cell addresses in the code to match the cell addresses of your Start and End dates.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'--filters a pivottable with and OLAP source to show the
'    pivotitems for each fiscal month between two dates

'--the dates are entered by the user in this worksheet
'    and the pivottable is filtered each time those
'    dates are changed.

 Dim dtStart As Date, dtEnd As Date, dtCurr As Date
 Dim lFilterItemCount As Long, lNdx As Long
 Dim sErrMsg As String, sMemberKey As String
 Dim vFilterArray As Variant
 Dim pvf As PivotField
 
 '--modify to match cell addresses in your sheet
 Const sSTART_ADDR As String = "$B$3"
 Const sEND_ADDR As String = "$E$4"
 
 On Error GoTo ErrProc
 '--modify to match the OLAP pivotfield name
 Set pvf = Me.PivotTables("PivotTable2").PivotFields( _
   "[Date Dimension].[Fiscal Month].[Fiscal Month]")
 
'--validate inputs
 If Target.CountLarge > 1 Then GoTo ExitProc
 If Target.Address <> sSTART_ADDR And _
   Target.Address <> sEND_ADDR Then GoTo ExitProc
 
 dtStart = Me.Range(sSTART_ADDR).Value2
 dtEnd = Me.Range(sEND_ADDR).Value2
 If Not (dtStart > 0 And dtEnd > 0) Then GoTo ExitProc
 
 If dtEnd < dtStart Then
   MsgBox "End date cannot be before Start date"
   GoTo ExitProc
 End If
 
 '--adjust start and end dates to first of month
 dtStart = DateSerial(Year(dtStart), Month(dtStart), 1)
 dtEnd = DateSerial(Year(dtEnd), Month(dtEnd), 1)
 
  '-calculate number of fiscal months in date span
 lFilterItemCount = Year(dtEnd) * 12 + Month(dtEnd) - _
   (Year(dtStart) * 12 + Month(dtStart)) + 1
 
 ReDim vFilterArray(1 To lFilterItemCount)
 
 dtCurr = dtStart
 '--step through each month to build visibleitemslist array
 Do While dtCurr <= dtEnd
   sMemberKey = sGetMemberKey(dt:=dtCurr)
   lNdx = lNdx + 1
   vFilterArray(lNdx) = sMemberKey
   '--increment dtCurr to first of next month
   dtCurr = Application.EDate(dtCurr, 1)
 Loop
 
 '--apply filter
 Application.EnableEvents = False
 pvf.VisibleItemsList = vFilterArray
 
 '--for debugging only
 Debug.Print vbCr & "Span: " & dtStart & " --> " & dtEnd
 For lNdx = LBound(vFilterArray) To UBound(vFilterArray)
   Debug.Print vFilterArray(lNdx)
 Next lNdx
 
ExitProc:
 On Error Resume Next
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub

Function sGetMemberKey(ByVal dt As Date) As String
'--returns a string representing the OLAP member key
'    corresponding to the input date

 Dim lFiscalMth As Long, lFiscalYear As Long
 Dim sReturn As String
 
 '--last month of fiscal year is June
 Const lFY_END As Long = 6
 Const sITEM_FORMAT As String = _
   "[Date Dimension].[Fiscal Month].&[YYYY\FY]&[FM]"
 
 lFiscalYear = IIf(Month(dt) <= lFY_END, Year(dt), Year(dt) + 1)
 lFiscalMth = (lFY_END + Month(dt) - 1) Mod 12 + 1
 
 '--substitute each value in format string
 sReturn = Replace(sITEM_FORMAT, "FY", Right(CStr(lFiscalYear), 2))
 sReturn = Replace(sReturn, "YYYY", CStr(lFiscalYear - 1))
 sReturn = Replace(sReturn, "FM", CStr(lFiscalMth))
 
 sGetMemberKey = sReturn
End Function
 
Upvote 0
Hello Jerry,
I thank you very much for your effort in helping me. I am relatively new to Excel VBA but once I get this done I will give it more attention. I did exactly what you said I should do, I modified the cell addresses in the code to match the cell addresses of Start and End dates and as well copied all the code into the Sheet Code Module of the Sheet that has the Pivot Table. After doing this and press the run button, it requested for me to save it instead of running. Upon saving, it creates a blank sub which runs without result. Is there anything I can do further?

Thank you once again for your effort.
 
Upvote 0
Hello Jerry,
Bravado, you are great it is working you saved me a lot of headache. Brother Jesus my Lord will bless you.
 
Upvote 0
I'm glad that helped you. :)

It sounds like you initially tried to create a macro button to run that. As you've figured out, the code doesn't require a button-it's triggered whenever a user changes the start or end date.

One downside of that approach is that if the user wants to change both start and finish dates, it will update after each- and possibly give an error message if the end date is temporarily before the start date. If you prefer the code could be modified to run by a button instead.
 
Upvote 0
Yes Brother I think that will be a better approach and I noticed that some filtering and calculations I am doing outside the Pivot table are not changing in response to the data being filtered. What is the approach of using button I will like that.
Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pivot Slicer Help
Replies
2
Views
204
Trevor G
Trevor G
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
163
Tetra201
Tetra201
S
  • Question Question
Need Help in Dates
2 3
Replies
27
Views
1K
Dave Patton
D
F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
231
floW_5
F
F
  • Question Question
Filtered by checkboxes
Replies
4
Views
204
floW_5
F

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top