Progromatically choosing yesterday's date as a parameter.

I have a report setup against an analysis service cube. It runs fine. Users
choose a date from the parameter list and then it pulls out the data nicely.
I need to programatically have it select a parameter for yesterday's date.
Somebody suggested setting a default script for the parameter of
=DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
The problem is that my mdx date is in the format [Dimension Name].[All
Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
tax year. (i.e - today, where in the first quarter of 2006)
How can I convert the current date to the appropriate format?
Any help would be greatly appreciated.
Thanks,
MattI have done similar things in the past and I wrote a SQL query that gets the
current date then I use the datepart function to get the parts (i.e quarters)
If you want to reformat the way a part displays I put the datepart inside a
CASE statement.
Another approach is to turn it into a string and use the datepart to get
the parts you want. It may look something like this.....
SELECT 'Q' + CAST(DATEPART(qq, GETDATE()) AS varchar(255)) AS Quarter
There is probably a better way to do this but this is how I did it.
"Matt" wrote:
> I have a report setup against an analysis service cube. It runs fine. Users
> choose a date from the parameter list and then it pulls out the data nicely.
> I need to programatically have it select a parameter for yesterday's date.
> Somebody suggested setting a default script for the parameter of
> =DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
> The problem is that my mdx date is in the format [Dimension Name].[All
> Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
> tax year. (i.e - today, where in the first quarter of 2006)
> How can I convert the current date to the appropriate format?
> Any help would be greatly appreciated.
> Thanks,
> Matt