asked on

Default value in parameters linked to OLAP cube

I'm using SQL Server Reporting Services 2000 on an Analysis 2000 Cube.

I have a sales report which I want, by default, to show figures for last month. Thus I can setup subscriptions for the start of each month which will show the values for last month. The time dimension ([Time].[Fiscal]) is based on fiscal year, from 1st July - 30th June.

I've followed a tutorial from Databasejournal.com which establishes a date parameter as a string. Excluding the default value, this works fine.

In the default value I've entered the following formula:
=Month(DateAdd("m",-1,Now()))

I have very little knowledge of MDX and less still of VB Script. Do I need to change the formula to something more like "=[Time].[Fiscal].[" + Year(DateAdd("m",-1,Now())) + "].[" + Month(DateAdd("m",-1,Now())) + "]""? If so, how can I tell the Year to be a derivative of financial year rather than calendar year?

Yurich

If I got you right, you want to set the default value of your parameter in MS Reporting Services to the first of the last month, e.g. 1st of September, 2006 as of today.

You can use this formula for this:

=CDate( Year( Today()).ToString() & "/" & Month( DateAdd( DateInterval.Month, -1, Today())).ToString() & "/1" )

Good luck,
Yurich

bevco7

ASKER

Hi Yurich,

That's very close and certainly gives me something to work with. The report takes a month, in Analysis cube syntax ([Time].[Fiscal].[20xx].[Quarter x].[Monthname]). I'll modify your formula to return this string.

Thanks,

Sam

Yurich

To get the name of the previous month in Reporting Services for my first expression:

=MonthName( Month( CDate( Year( Today()).ToString() & "/" & Month( DateAdd(
DateInterval.Month, -1, Today())).ToString() & "/1" )))

Regards,
Yurich

bevco7

ASKER

Thanks Yurich,

So the formula I need is something like this?

="[Time].[Fiscal].[" & Year(DateAdd(DateInterval.Month, -1, Today())).ToString() & "].[Quarter " & Quarter(DateAdd(DateInterval.Month, -1, Today())).ToString() & "].[" & MonthName( Month( CDate( Year( Today()).ToString() & "/" & Month( DateAdd(DateInterval.Month, -1, Today())).ToString() & "/1" )))" & "]"

The only issue I see here is that Year and Quarter (I'm only guessing this function exists!) are based on calendar year. Can I make these based on financial year (starting 1st July)?

Thanks again,

Sam

ASKER CERTIFIED SOLUTION

Yurich

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial