Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECTNONEMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ONCOLUMNS, NONEMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ONCOLUMNSFROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ONCOLUMNSFROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ONCOLUMNSFROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ONCOLUMNSFROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!