Thao, please help: Date hierarchy - Urgent

[Hans Riis]

I've posted this problem before but nobody seems to have the answer and now
I'm past my deadline. My problem should in theory be quite simple if I only
understood the structure of a date hierarchy but I can't find anything in
the documentation or here in the newsgroup. I've tried all sorts of things
but nothing seems to give the right result.

I have a dropdown menu where the user's supposed to choose to view the
previous x days in a chart. The date hierarchy is defined as year, month,
day.
The code below returns strange dates. That is, when run on October 22 I get
May 9 and September 10-22.

Dim i, datevar, yearvar, monthvar, dayvar, datearray(13)

Set c=ChartSpace1.Constants
ChartSpace1.SetData c.chDimCategories, c.chDataBound, "Date"
Set objPFS = ChartSpace1.InternalPivotTable.ActiveView.FieldSets("Date")
Set objPC = ChartSpace1.InternalPivotTable.Constants

objPFS.AllIncludeExclude = objPC.plAllInclude
objPFS.AllIncludeExclude = objPC.plAllExclude

Set objPF = objPFS.Fields("Day")

For i = 0 To 13
datevar = date()-i
yearvar = cstr(year(datevar))
monthvar = monthname(month(datevar), False)
dayvar = day(datevar)
datearray(i) =
objPFS.Member.ChildMembers(yearvar).ChildMembers(monthvar).ChildMembers(dayv
ar-1).value
Next

objPF.IncludedMembers = datearray


I've solved the problem in JavaScript but our company policy is to use only
VBScript when accessing OWC-components.
The below snippet works:

for(i=13;i>=0;i--) {
var datevar = new Date();
datevar.setDate(datevar.getDate() - i );
var dayvar = datevar.getDate();
var monthNumber = datevar.getMonth();
var monthvar = monthToMonthString(monthNumber + 1);
var yearvar = "" + datevar.getFullYear();

datearray =
datearray.concat(Array(objPFS.Member.ChildMembers(yearvar).ChildMembers(mont
hvar).ChildMembers(dayvar - 1)));
}
objPF.IncludedMembers = datearray;


Thanks in advance,
Hans Riis
Bang & Olufsen, Denmark

 

[Thao Moua [ms]]

I ran your date parsing code only and it does print the correct date for me.
You might want to verify your parsed date data are mapped to the correct
objPFS.Members. And also check your date format. This should not be an
issue but you should verify anyway. I am using US date format (mm/dd/yyyy.)

----------------------------------------------------------------------
Thao Moua
OWC Chartspace Support

This posting is provided "AS IS" with no warranties, and
confers no rights. Use of included script samples are
subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Hans Riis]

Hi Thao.

Thanks for looking at my problem. It's getting very critical now as my boss
and my client are fighting over what is reasonable to provide besides the
standard functionality.

I get the right values for year/month/day when running

SetLocale("en-us")

For i = 0 To 13
datevar = date()-i
yearvar = cstr(year(datevar))
monthvar = monthname(month(datevar), False)
dayvar = day(datevar)
MSGBOX(yearvar & " " & monthvar & " " & dayvar)
datearray(i) =
objPFS.Member.ChildMembers(yearvar).ChildMembers(monthvar).ChildMembers(dayv
ar-1).value
MSGBOX(datearray(i))
Next

The result of the first Msgbox is 2004 October 16 - 2004 October 29. The
second Msgbox yields only the numbers 16-29.
Both our SQL Server and Analysis Manager are English versions so the
SetLocale should ensure the correct date values.

To me it seems like the problem lies in one of these lines:

datearray(i) =
objPFS.Member.ChildMembers(yearvar).ChildMembers(monthvar).ChildMembers(dayv
ar-1).value
objPF.IncludedMembers = datearray

Thao would you please please please help me solve this problem before I get
caught between my boos and my client?

Thanks again, Hans Riis