Convert DateTime to Date for Display and Filter

[premece]

I'm hitting a very familiar issue for which there are lot of solutions, but wanted to know whether that is the correct way to do it.

Our DB is SQL Server 2012.

I have a DateTime field coming in from the DB and user wants to see the Date in MM/DD/YYYY format. He wants to display the date as well as prompt as Date. He does not want to change the Data format as well. In this Tableau age all he wants to do is Drag and Drop :)

When I do a cast as date, the DateTime becomes Date in YYYY-MM-DD format. In this case it works perfectly as a Date for Date prompt, but the Display is not as the user expected.

When I do a convert(varchar(10), DateTimeField, 101), the DateTime becomes Date in MM/DD/YYYY format. In this case it works perfectly for Report Display, but then the prompt filter is not working as this is a varchar and not a Date.

What I've currently implemented is have 2 fields, one for Prompt with cast and one for Display with convert and users are happy with it. But I wanted to make sure that this is the best practice.

Is there a solution where I can achieve this by a single calculation so that
(a) I'll have one field instead of two and give user the real Drag Drop experience he is requesting?
(b) and also minimize the fields in my FWM model

Thanks in Advance,
premece

[cognostechie]

First of all, I don't know how your user is doing 'drag & drop' in a Report Studio report. Do they have access to RS and are making the report themselves or did you mean
that they are using Query Studio or Workspace? This question should really have been in the FM forum.

You do not have to do either one of what you did. FM allows changing the format for display purpose.

1> Go to the Business Layer.
2> Click on the date column of your Date Query Subject
3> In the 'properties' section on the bottom right, you will see a property called 'Format'
4> Click on that and from the 'Format Date', select 'Date'
5> From the 'Date Type' select 'Short'

Publish the package and see the results. Go back to the FM model and play around with the rest of the settings like Date Ordering, Display years , Display Months etc.

[premece]

Thank you very much sir. I was thinking too much that I missed the obvious

Because of the recent licensing changes with IBM Cognos, everyone has Report Studio.
But they want to use  it like Query Studio  :D