By using this website, you agree to our Terms of Use (click here)
I am trying to display a chart showing values over time by month. I'm using the period field which is formatted 01-2018, 02-2018, etc. I want them to sort chronologically, but instead they are sorting like this. 01-2017, 01-2018, 02-2o17, 02-2018. Is there any quick ways via the generic inquiry to get this to sort correctly?
Brian, we have made it a standard practice to add two columns to any GI whose result set may need sorting, grouping, or filtering by year or month. You can use the functions =Year(PostingDate) and =Month({PostingDate}) to get the data you need. This greatly eases sorting and filtering by date component.
If you want to use the 2018-01 format so things will sort correctly, you could use the following formula (just replace [SOOrder.OrderDate] with your table/column):
=CStr(Year([SOOrder.OrderDate]))+'-'+Right('0'+CStr(Month([SOOrder.OrderDate])),2)
The reason for the Right portion of the formula is to get the leading zero in front of the month.
I really wish that the Format formula which is available in Report Designer was also available in Generic Inquiry, but, alas, it's not. If you think it's a good idea, go ahead and vote on the Make Format formula from Report Designer also available in Generic Inquiry idea (click here) that I just created.