Hi,
This is an MDX question.

How can get hold of today's date and use it in my MDX query to slice on the
time dimension? I think I need to use a VBA function but I'm not sure what it
is. I've previously used some VBA functions along with the STRTOSET function
but that was years ago and I've forgotten how to do it.

By the way:
1) The format of my time member is "YYYY-MM-DD HH:MI:SS" and HH:MI:SS is
always '00:00:00'
2) I am using Panorama Novaview as my client tool.

Any help much appreciated.

Regards
Jamie Thomson

use VBA!Now

Regards,
Ilona Shulman
Senior Development Consultant, DBA
SSE Inc
http://www.sseinc.com


"Jamie" <Jamie (AT) discussions (DOT) microsoft.com> wrote

Thanks Ilona,

What do I need to install on my server in order to get access to the VBA
function library? I'm guessing it doesn't come bundled with Windows right?

Regards
Jamie Thomson
http://www.conchango.com


"ilona" wrote:

Jamie,
VBA libraries do not count as user-defined external functions and they are
automatically loaded when your PivotTable Service starts up. So you do not
have to load anything --here is an example on how to use them in MDX - I
altered Query#3 in MDX Sample Application which works against foodmart:

with member [Measures].[Store Sales Last Period] as 'VBA!Now()'
select
{[Measures].[Store Sales Last Period]} on columns,
{TopCount([Product].[Product Department].members,5, [Measures].[Store Sales
Last Period])} on rows
from Sales
where ([Time].[1998])

Regards,
Ilona Shulman
Senior Development Consultant, DBA
SSE Inc
http://www.sseinc.com


"Jamie" <Jamie (AT) discussions (DOT) microsoft.com> wrote

Thanks Ilona, that worked great!

Now I'm trying to get them to work for me how I need them to.

The following executed fine:
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where [Week Ending Date].[2004-10-01 00:00:00]

Then I tried this which also executed fine (and returned the same results:
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where StrToTuple("[Week Ending Date].[2004-10-01 00:00:00]")

Then I tried this which errored:
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where StrToTuple("[Week Ending Date].[" + VBA!YEAR(VBA!NOW()) + "-" +
VBA!MONTH(VBA!NOW()) + "-" +VBA!DAY(VBA!NOW()) + " 00:00:00]")

Can you see what I'm trying to do here? I'm trying to build up a string that
looks like
"[Week Ending Date].[2004-10-01 00:00:00]"

The error I got was:
snytax error - token is not valid: "StrToTuple("[Week Ending Date].[" +
^VBA!YEAR^(VBA!NOW()) + etc....

So its complaining about my call to VBA!YEAR even though I have successfully
used this syntax in the following way:
with member Measures.Test as 'VBA!YEAR(VBA!NOW())'
select
{Measures.Test} on columns,
{Company.[All Company]} on rows
from [Timesheet Reporting]
which simply returns "2004".

Any ideas?

Thanks
Jamie



"ilona" wrote:

Additionally, when I put VBA!STR into it like this:
with member [Week Ending Date].MyWeek as'StrToTuple("[Week Ending Date].["
+ VBA!STR(VBA!YEAR(VBA!NOW())) + "-" + "10" + "-" + "01" + " 00:00:00]")'
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where [Week Ending Date].MyWeek
It doesn't cause an error on execution although it DOES cause an error value
(#ERR) to be returned

Why do some VBA functions cause it to error and some not?

Would appreciate any help cos I'm floundering a bit here!

Regards
Jamie


"Jamie Thomson" wrote:

I think you're half way - you were right that you needed to cast the returned
value of VBA!NOW() to a string to make it work when you're using the +
operator to build strings, but in the example below I think you've got an
error which is nothing to do with your original problem. Can you click on the
cell with ERR in MDX Sample App and tell me what the error message you see
is? My guess it's something to do with the Billable Hours calculated measure
not liking the fact you have another calculated member in the WHERE clause.
Does

select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where (StrToMember("[Week Ending Date].["
+ VBA!STR(VBA!YEAR(VBA!NOW())) + "-" + "10" + "-" + "01" + " 00:00:00]"))

....work?

Regards,

Chris


HTH,

Chris


"Jamie Thomson" wrote:

OK,
I executed your query Chris and got the following error on execution:
Formular error - cannot find dimension member ("[Week Ending
Date].[2004-10-01 00:00:00]") - in a name binding function

I executed the following (which I posted up here yesterday further up this
thread):
with member [Week Ending Date].MyWeek as'StrToTuple("[Week Ending Date].["
+ VBA!STR(VBA!YEAR(VBA!NOW())) + "-" + "10" + "-" + "01" + " 00:00:00]")'
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where [Week Ending Date].MyWeek
which executed OK but returned #ERR. Double clicking on the error produced
exactly the same error message as above.

SO...I obviously have the same problem occurring here. I can build my string
correctly OK but for some reason it doesn't like it. I can promise you that
this member does exist.

Any ideas???

TIA

Regards
Jamie

"Chris Webb" wrote:

FOUND THE PROBLEM!!! (And ooo its made me angry

I took a closer look at the error message I was getting and noticed that the
string that it was building was:
[Week Ending Date].[ 2004-10-01 00:00:00]")
whereas it SHOULD have been:
[Week Ending Date].[2004-10-01 00:00:00]")

Spot the subtle difference???

I have no idea why VBA!STR(VBA!YEAR(VBA!NOW())) produces " 2004" instead of
"2004" but its easily remedied by wrapping it all up in:
VBA!RIGHT(<exp>, 4)

Strange strange strange!

Anyone any ideas as to why this may be happening?

Regards
Jamie Thomson
http://www.conchango.com



"Jamie Thomson" wrote:

And in case anyone out there is still reading this, here's the final
solution. Not pretty but it works:
with member [Week Ending Date].MyWeek as
'StrToTuple("[Week Ending Date].[Date].[" +
VBA!TRIM(VBA!STR(VBA!YEAR(VBA!NOW()))) + "-" +
VBA!RIGHT(VBA!CHR(48) + VBA!TRIM(VBA!STR(VBA!MONTH(VBA!NOW()))), 2) + "-" +
VBA!RIGHT(VBA!CHR(48) + VBA!TRIM(VBA!STR(VBA!DAY(VBA!NOW()))), 2) +
" 00:00:00]")'
select
{Company.[All Company]} on rows,
{Measures.[Billable Hours]} on columns
from [Timesheet Reporting]
where [Week Ending Date].MyWeek

If anyone fancies trying to make this a bit neater...be my guest!!!

Thanks to Chris and Ilona for all your help.

Regards
Jamie Thomson
http://www.conchango.com


"Jamie Thomson" wrote: