I have a to allow the user to pick a date range. My fact table records have the year field (yyyy) and the month field (mm). So the UI should present to the user a way to pick 10/2011, 11/2011,12/2011, and 1/2012 for example. So I defined a list box with the following expression:
=Month & ' / ' & Year
and I sort by the this expresson:
Year + Month/100
as you can see in the attached sample app. The problem with this solution is that as more data is loaded with more Years (currently I only have two years worth of data), the control will have to show scroll bars and just becomes less user friendly to interact with.
Is there a more elegant way to accomplish this? Maybe an expandable tree view listbox of Year/Month hierarchy. Something like below:
+2011
+2012
and when user clicks on a +, the list expands for that year and shows all the months with a check box next to each month for selection. Then user can collapse that year and expand another year and select desired months for that year.
