Solved

Default value in parameters linked to OLAP cube

Posted on 2006-11-26
5
497 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:bevco7
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Yurich
ID: 18023480
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
0
 

Author Comment

by:bevco7
ID: 18040898
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
0
 
LVL 21

Expert Comment

by:Yurich
ID: 18041467
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
0
 

Author Comment

by:bevco7
ID: 18041500
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
0
 
LVL 21

Accepted Solution

by:
Yurich earned 500 total points
ID: 18049022
hmm... it can grow pretty complex... In this particular situation if you still want to have everything dynamic (not hardcoded), then I'd create another parameter, say prmFiscalYear, and the formula for this one is:

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

Then instead of Today() in your main formula, I'd use

Parameters!prmFiscalYear.Value

Another thing - do you need to convert it into the << ="[Time]..."  >> format? Can't you just pass a date to your Cube?

Regards,
Yurich

0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS - First row gets hidden under Column header 2 85
SSRS report builder 3.0 6 41
VB RDLC Report 1 100
SSRS troubles 4 51
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question