Showing posts with label current. Show all posts
Showing posts with label current. Show all posts

Monday, March 12, 2012

How to use MDX script in RS dimension filter (dataview)

Hi, I need to create filter which retrieves data for current moment. [time].[date].lastchild is not an option because the calender in database is generated till following year (2008).

I guess script goes like..

[time].[date].currentmember

but it does not work.

I'm green with these MDX scripts.. I'd be very pleased if some one of you gurus could help me.

Best Regards,
Eeva

You can use StrToMember() for this, e.g.:

StrToMember ("[Date].[Calendar Date].[2003].[H" +

CStr(IIf(Month(Now()) <= 6, 1, 2)) + " CY 2003].[Q" +

CStr(DatePart("q", Now())) + " CY 2003].[" + Format(Now(), "MMMM") + "].[" +

Format(Now(), "MMMM") + " " + CStr(Day(Now())) + "]")

assuming Year-Half Year - Quarter - Month - Day user-defined hierarchy. If you use a special format for the Date dimension key, e.g. YYYYMMDD and you should, then you can simplify the above syntax and use the Day attribute hierarchy directly, e.g. [Date].[Day].[Day].&[20070323]

Friday, March 9, 2012

How to use Fields or ReportItems value in charts

Hi, all. I am doing a financial report needs to show last 10 yrs history data. The end year is the last year of the current year. How can I set the minumum and maximun with Year(Now) fucntion for the x-axis, to represent the years? Is there any way to use Fields value for the chart minumum and maximun?

Yes this is possible in RS 2005 (but not in RS 2000). Axis settings such as min/max/crossat/major interval/minor interval can be expression-based. Just type in the expression as e.g. =Year(Now)

-- Robert

|||Thanks Robert. Actually I want to put the Year(Now)(2006) as maximun and Year(Now) -10(1996) as Minimun. However, the data I have are bewteen 1998 and 2002, so the chart still shows data from 1998 to 2002. Can I set exactly 10 years on the chart? Even thought there're no data at 2006 and 1996?|||

This should work fine if the x-axis is set to use "numeric / time-scale values" and the category grouping expression evaluates to either integer values or DateTime objects.

-- Robert