2 Replies Latest reply on Jan 29, 2013 4:26 PM by Tracy Rodgers

    Parameter Setting -- Month & Year

    Benjamin Creydt

      Good Day,


      Within our report, we have a parameter to select a break-out by dates.  Worked perfectly with the following formula: Datename('month',[sales date]).  However, I completely forgot about the the new year...

      With the old method, it would read:





      New year -- I know have: January - October - November - December.

      To address the issue, I added:  Datename('month',[sales date]) + " " + Datename('year',[sales date])  Format it works perfectly -- sorting, not so much.

      Now our data is sorted alphabetical.

      Anyone had a pretty good solution for address the sort within a Date & Year Parameter?


      Thank you in advance for the support & assistance.

        • 1. Re: Parameter Setting -- Month & Year
          Dean Hewitt

          Hello Benjamin,


          I think there are several misunderstandings here.

          1.  When creating a parameter, you can manually sort the values.  This will determine the order they are displayed when showing the parameter control.

          2.  The underlying dimension (Sales Date) from which the parameter was created from is used to sort data on various worksheets.  To display in date order use Sales Date and format to custom => MY.  This is done by dragging the Sales Date pill to the rows or columns shelf.  Then right click and select the discrete more.  This is the first one.  Then select custom => Month / Year.  This will show the date in the format you are looking for.


          I hope this helps.  Let me know if you need more.



          • 2. Re: Parameter Setting -- Month & Year
            Tracy Rodgers

            Hi Benjamin,


            Another way you can do this is to place the Sales Date on a worksheet by itself. Right click on it and select More>Custom>Month/Year. Then, go to Worksheet on the main menu and select Copy>Crosstab. Now, create a string parameter with a list and click the Paste from Clipboard option.


            Hope this helps!