SSRS BAQ Report - Option Field Not Getting Converted to dd/MM/yyyy format

Kinetic ERP
1

In SSRS BAQ report , I am using Option01 from the GLJrnDtl.JEDate field to filter the records. When i run the report, the BAQ report screen shows the date format in “dd/MM/yyyy” format… But when it goes to report, it is getting displayed as “MM/dd/yyyy” format… Even if I try to convert, it is not getting converted. Let me know how can I convert this to “dd/MM/yyyy” format??

2

Check the expression for the field in the report. Most of the time, the formatting of the date is a part of the expression. I always delete that and use the field properties to do my formatting. If you post the expression for that field, we can confirm if the formatting is being done in the expression.

3

Format(Cdate(First(Fields!Option01.Value, “BAQReportParameter”)),“dd/MM/yyyy”)… I am trying the expression like this… when i run the report, i am getting error for this expression

4

Even If drag and drop the Option01 field to report , and in text box properties if i try to do the custom conversion to “dd/MM/yyyy” format, it is not working…

5

Change

=Format(Cdate(First(Fields!Option01.Value,“BAQReportParameter”)),“dd/MM/yyyy”)

To

=First(Fields!Option01.Value, “BAQReportParameter”)

And let me know if that fixes it.

6

if i place this expression, I am not getting any error but I am getting date in “MM/dd/yyyy” format…

7

Sorry, I don’t have access to any SSRS, but I will try and explain what to do.

Now that you changed your expression, right click on the field and go into the properties. You should be able to change the format to whatever you want on one of the tabs in the dialog. Best thing I can compare it to is when you open a format menu in Excel.

8

In properties , if i try to change it is not getting changed… but in same case if i place any date field from BAQRepotResult, it is getting changed as expected… but if i check that for BAQParameter field, its not working…

9

OMG, just had a flashback to having this same issue once. Unfortunately, I cannot remember if I fixed it.

The issue is that the BAQParameterField is in DateTime and that is why SSRS does not like it. Try converting the field to Date only and see if that fixes it.

10

Hi Kane… I have achieved this two ways… First one is just splitting the option field by date , month and year, I am getting the result in “dd/MM/yyyy” format.

=Mid(CStr(First(Fields!Option01.Value, “BAQReportParameter”)),4,2) & “/” &
LEFT(CStr(First(Fields!Option01.Value, “BAQReportParameter”)), 2) & “/” &
RIGHT(CStr(First(Fields!Option01.Value, “BAQReportParameter”)), 4)

Secondly in the data set expression query , I converted the option01 as convert(varchar(10),convert(datetime,[Option01]),103) as [Option01]

This too gives the result in dd/MM/yyyy format.

1 Like