Default value in parameters linked to OLAP cube
Posted on 2006-11-26
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:
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?