BAQ Report - Option Field for Date

I have created a BAQ report which uses Option01 and Option02 to filter dates from the OrderRel_ReqDate field. The problem I have is that although the data values for OrderRel_ReqDate show on the report in the correct format (dd/mm/yyyy for UK) if I add the Option fields to the report header from the BAQReportParameter dataset then the date shows as mm/yy/dddd.

We had the same problem with V8 \ Crystal and used a Formula field to format the data (see below) - is this still necessary in E10 as I am struggling to find an expression that SSRS accepts?

stringvar yyear;
stringvar mmonth;
stringvar dday;

dday := {BAQReportParameter.Option01}[4 to 5];
mmonth := {BAQReportParameter.Option01}[1 to 2];
yyear := {BAQReportParameter.Option01}[7 to 10];

date(tonumber(yyear),tonumber(mmonth),tonumber(dday))

Try using CDATE(Fields!..) around the date and then use the Date Formatting built into SSRS.

Thanks for replying… CDATE returns #Error so I am unable to use the date formatting.

I have also tried a few things that still return the date in mm/dd/yyyy format and also tried this:

=Format(First(Fields!Option01.Value, “BAQReportParameter”), “dd/MM/yyyy”)
but it actually returns “dd/MM/yyyy”.

Try just this:
=CDATE(First(Fields!Option01.Value, “BAQReportParameter”))

Then don’t use the Format function, instead set the property on the TextBox.

Thanks but I think :crossed_fingers: I’ve cracked it!
=Mid(CStr(First(Fields!Option02.Value, “BAQReportParameter”)),4,2) & “/” &
LEFT(CStr(First(Fields!Option02.Value, “BAQReportParameter”)), 2) & “/” &
RIGHT(CStr(First(Fields!Option02.Value, “BAQReportParameter”)), 4)

If that works, then great!

1 Like