We had an epicor consultant for a few years when we first started using epicor and one of their employees created a couple of BAQ’s and dashboards for us. The method she used to create these is far beyond my knowledge of baq’s and im having some trouble modifying her baq.
the date field that she added in sorts the field from left to right by number. The list would look something like this:
12/31/2021
12/31/2020
12/30/2021
When i took a look at the BAQ i found the calculated fields that are associated with that column. She has them setup as nvarchar which right away tells me thats why the sorting is screwed up (correct me if im wrong).
The problem is, the calculated field looks like this:
There is a second calculated field in another subquery that looks identical. I cannot find SODD or SODD1 used in any other calculated field.
I did try changing the type to DATE instead of nvarchar but that just coughed up an error. Any ideas on how i can fix this? I have attached the query phrase incase that might help. Let me know if you need anything else uploaded. Thanks in advance!
I looked at the query text that you attached. It looks like you have three different queries with a UNION between the three. The way UNIONs work is want the field types match up on each of the queries. So, if field #10 on your first query returns a date field, then field #10 on your second query and third query should also return a date field.
The “SODD” field (the 10th in the query) is a varchar (space) on your first and third query, and it’s the OrderRel.DueDate converted to varchar (format 101 which is MM/DD/YYYY) on the second query. That makes the 10th field a varchar field on all three queries.
The easiest fix would be to change your convert to use format code 111 instead of 101. That would change it to YYYY/MM/DD, and if you’re sorting by that column it will then sort in date order.
that seemed to do the trick for my end-user. Thank you so much for helping with this.
and yes i will admit im no where near and epicor expert or advanced user but even i cant understand why our consultant did things this way. Our tracker views are setup so weird for other fields when a simple date drop-down would have done the job. thank you again!
Hi Joe,
you can get any date from any date field then format it the way you want cast it to any calculated field in BAQ using this SQL command, for example
FORMAT(OrderRel.DueDate, 'DD/MM/YYYY')
you can add time and time calculation as well if you want
