Creating Month-Day Dates for Reports vs Year-Month-Day

Document created by MarkR on Jun 25, 2010
Version 1Show Document
  • View in full screen mode

Currently when you create a chart that has a date field the date is displayed as YYYY-MM-DD.



Rarely will the year be an important piece of data. You also have to angle the date because of the length. This, to me, is distracting. What I prefer is something like the following.





This requires a few variables; namely getMonth and getDay. The issue is these variables return integers so for example 2010-06-08 would return 6 and 8 respectively. You could take the next step and use the variable to convert a number to a string and then concatenate the results. The problem is when you go to sort the concatenated variable you will end up with a sorted order like this:







The solution is a bit roundabout but at least the Active List is reusable. I am not sure how much 5.0 with global variables will help; hopefully it will take a few steps out.


The first thing to do is create an active list that won't expire with two fields: integer and string. Index the integer field. Populate the list along the following lines:


Int | String

1, 01

2, 02


31, 31


In your query do both the getMonth and getDay variables but now use the getActiveListValue for each. In this variable link the integer from the getMonth/Day to the integer in the Active List. Then you can create a variable set to concat the string values from those links as the string field will show up in the dropdown list (within the concat variable). Something like this...though my variable names leave something to be desired.




All that is left is to select the last concatenated variable in the field selection tab. You will end up with a list that can be sorted like the following: