筛选来自Olap立方体的数据透视表中标题中的日期 [英] Filter for Dates which are in Caption in PivotTables which come from Olap cube
问题描述
我的代码到目前为止:
pre $ p $ Set cvField ([DimTime]。[Year-Quarter-Month-Day])
'将项目添加到报表过滤器
pvtField.CreatePivotFields
pvtField.Orientation = xlRowField
With ActiveSheet.PivotTables(1).PivotFields([DimTime]。[Year-Quarter-Month-Day]。[Day])
Debug.Print end_date'return格式为dd.mm.yyyy的日期,它是一个有效的日期
Debug.Print start_date'返回格式为'dd.mm.yyyy'的日期,这是一个有效的日期
Debug.Print .PivotItems(1).Caption'返回例如星期一,2006年9月4日,这只是返回,如果我点击数据透视表中的加号(见附图)
Debug.Print .PivotItems(1).Value' [DimTime]。[Year-Quarter-Month-Day]。[Day]。& [2006-09-04T00:00:00]
Debug.Print .Name'返回例如[DimTime]。[Year-Quarter-Month-Day]。[Day]
.ClearAllFilters'清除所有过滤器
.CubeField.IncludeNewItemsInFilter = True
.PivotFilters.Add2类型:= xlCaptionIsBetween,Value1:= start_date,Value2:= end_date'在这里获取错误
'.PivotFilters.Add2类型:= xlDateBetween,Value1:= start_date,Value2:= end_date'同样的错误
End With
错误讯息是:
$ b
运行时错误438:对象不支持属性或方法。
手动打开加号(在代码中引用):
更新
好的,到目前为止我已经算出来了:
<
$ $ p $
For Each I In .PivotItems
Debug.Print i.Caption它打印例如星期一,九月04 2017
下一个我
我无法使用.IsDate这个字段或所以它不是一个日期?
如果我格式化我的start_date这个格式我得到Montag,2017年9月4日(德国版本,因为我使用德国电脑)。这是相关的我的问题?
更新2:
.PivotFilters.Add类型:= xlCaptionIsGreaterThan,Value1:=格式(start_date,dddd,mmmm dd yyyy)'getting这里错误
结果是,星期三选择了start_date = 2017年8月23日星期三(我已经将我的系统设置从德语更改为英文)
结果问题 - >我可以将我的PivotField转换为日期吗?
Update3
我使用以下for循环转换了Olap Date,现在得到一个新错误:
1004:您输入的日期不是有效的日期。
For Each I In .PivotItems
Debug.Print i .Caption
Debug.Print CDate(CStr(i.Caption))
p = i.Caption
u = Split(p,,)(1)
Debug.Print CDate(u)'例如04/09/2017
i.Caption = CDate(u)
下一个i
'Debug.Print .PivotItems(1).IsDate
.PivotFilters.Add类型:= xlDateBetween,Value1:= start_date,Value2:= end_date'出现错误
在CDate(u)和start_date / end_date中也试过用Cdbl,并得到这个错误信息:
lockquote
<5>无效的过程调用或参数。
启动宏记录器,设置DateBetween过滤器,然后看看它记录的代码。然后把这个代码和上面你正在做的事情进行比较,你可能会找到你错误的地方。当我做到这一点,我得到这个:
$ b $ pre $ ActiveSheet.PivotTables(PivotTable3)。PivotFields([Range]。[日期],[日])。 _
PivotFilters.Add2类型:= xlDateBetween,Value1:=1/02/2017,Value2:= _
2017/8/08
请注意,宏记录器不关心日期格式为dddd,mmmm dd yyyy格式...它只是使用d / mm / yyyy格式适合我的位置(新西兰)。这可能是你错了。
I want to Filter Dates which stands between two Dates within a Olap Cube.
My code so far:
Set pvtField = pt.CubeFields("[DimTime].[Year-Quarter-Month-Day]")
' Add item to the Report Filter
pvtField.CreatePivotFields
pvtField.Orientation = xlRowField
With ActiveSheet.PivotTables(1).PivotFields("[DimTime].[Year-Quarter-Month-Day].[Day]")
Debug.Print end_date 'return Date in the format 'dd.mm.yyyy', it is a valid Date
Debug.Print start_date 'return Date in the format 'dd.mm.yyyy', it is a valid Date
Debug.Print .PivotItems(1).Caption 'returns e.g. Monday, September 04 2006, this is just returned, if i click on the plus sign in the pivot table(see attached picture)
Debug.Print .PivotItems(1).Value 'returns e.g. [DimTime].[Year-Quarter-Month-Day].[Day].&[2006-09-04T00:00:00]
Debug.Print .Name 'returns e.g. [DimTime].[Year-Quarter-Month-Day].[Day]
.ClearAllFilters 'Clear All The Filters
.CubeField.IncludeNewItemsInFilter = True
.PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:=start_date, Value2:=end_date 'getting here the error
'.PivotFilters.Add2 Type:=xlDateBetween, Value1:=start_date, Value2:=end_date 'getting here the same error
End With
The error Message is:
Runtime Error 438: Object does not support property or method.
Manually opened plus signs (referenced in code):
Update
Ok, what i fount out so far:
With:
For Each i In .PivotItems
Debug.Print i.Caption 'it prints e.g. Monday, September 04 2017
Next i
I cant use .IsDate on this Field or the items, so it is not a date?
If I Format it my start_date to this Format i get Montag, September 04 2017 (the german version, because i am using a german pc). Is this relevant for my problem?
Update2:
i have tried:
.PivotFilters.Add Type:=xlCaptionIsGreaterThan, Value1:=Format(start_date, "dddd, mmmm dd yyyy") 'getting here the error
and the result is, that just Wednesdays are selected with start_date = Wednesday, August 23 2017 ( i have changed my System settings from German to English)
Resulting Question--> Can i convert my PivotField to a Date?
Update3
I converted the Olap Date with the following for loop and getting now a new error:
1004:The date you entered is not a valid date. Please try again.
For Each i In .PivotItems
Debug.Print i.Caption
'Debug.Print CDate(CStr(i.Caption))
p = i.Caption
u = Split(p, ",")(1)
Debug.Print CDate(u) 'e.g. 04/09/2017
i.Caption = CDate(u)
Next i
'Debug.Print .PivotItems(1).IsDate
.PivotFilters.Add Type:=xlDateBetween, Value1:=start_date, Value2:=end_date 'getting here the error
Also tried that with Cdbl in CDate(u) and start_date/end_date and getting this error message:
5: Invalid procedure call or argument.
Fire up the macro recorder, set the DateBetween filter, then take a look at the code it recorded. Then compare that code to what you are doing above, and you'll probably be able to find where you went wrong. When I did it, I got this:
ActiveSheet.PivotTables("PivotTable3").PivotFields("[Range].[Date].[Date]"). _
PivotFilters.Add2 Type:=xlDateBetween, Value1:="1/02/2017", Value2:= _
"1/08/2017"
Note that the macro recorder didn't care that the dates were formatted in dddd, mmmm dd yyyy format...it just used the d/mm/yyyy format appropriate for my location (New Zealand). That's probably where you are going wrong.
这篇关于筛选来自Olap立方体的数据透视表中标题中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!