index > SQL Server Reporting Services > How to get Now() to quarter in SSRS

How to get Now() to quarter in SSRS

Hello, I currently have this expression in the columns of a matrix in a report:

=iif(Fields!Fiscal_Quarter_Code.Value = "2008Q3", "YTD", Fields!Fiscal_Quarter_Code.Value)

I'm displaying the last 5 fiscal quarters in my report, and I want the current quarter to display as YTD instead of 2008Q3. This expression works, but I need to incorporate the Now() function into my report so I don't have to constantly update the string of the current quarter ("2008Q3" in this case) at the end of the quarter.

I have this as a filter where I am using the Now() and then looking at the parent to get to the fiscal code in my hiearchy..but I can't seem to do this same kind of logic as an expression in the report.

StrToMember ( "[Date].[Hierarchy].[Fiscal Month Code].["
+ Format(Now(), "yyyy")
+ "M"
+ Format(Now(), "MM")
+ "].Parent" ).Lag(4)
:
StrToMember ( "[Date].[Hierarchy].[Fiscal Month Code].["
+ Format(Now(), "yyyy")
+ "M"
+ Format(Now(), "MM")
+ "].Parent" ).Lag(0)

Any help would be appreciated. Thanks!

dcmander Monday, July 28, 2008 9:56 PM

Hi dcmander,

You could be doing that in your MDX query, by creating a member that evaluates to YTD for the current quarter, and the quarter name otherwise.

So by creating a "This Quarter" member you would have something like:

Code Snippet

with
member [Measures].[This Quarter] as
iif(
[Date].[Hierarchy].member_caption=strtomember("[Date].[Hierarchy].[Fiscal Date Code].&["+format(now(),"yyyy-MM-dd")+"T00:00:00]").parent.parent.parent.member_caption,"YTD",[Date].[Hierarchy].member_caption)

select
{measures.[This Quarter]} on columns,
{[Date].[Hierarchy].[Fiscal Quarter Code]} on 1
from
Sales

If you execute this query (I use a "Sales" cube but change to what ever your cube is called, and I have putthree times "parent." but change that to whatever number of levels you need to go from the date to the quarter), you'll get all the quarter names apart from the current quarter where you'll have "YTD". Then you would just need to get this member as part of your main MDX statement to be able to display what you need.

Let me know if that helps,

Regards,

Philippe

Philippe Harel Tuesday, July 29, 2008 11:55 AM