2 Replies Latest reply on May 22, 2013 12:31 PM by Mike J

    Format the Axis based on selection

    Mike J

      Hello All,


      I've created a workbook that plots points overtime on a bar graph; the time periods and the date granularity are driven dynamically by the user, I wonder if anyone knows a trick to controlling/formating the Axis tick labels dynamically.


      I need to control the way the date labels are displayed on the graph, Example; If the user selects, years, I want the labels to display "2010-2012-2013", quaters as "Q1-Q2-Q3",  months as "Jan-Feb-Mar" and so on.. I've included a picture with the highlighted fields I'm looking to format.

      Thanks for your help.



      Dynamic Axis Labels.PNG

        • 1. Re: Format the Axis based on selection
          Tracy Rodgers

          Hi Mike,


          Not sure it will get you exactly what you want, but you would create a parameter and a calculated field that will get you part of the way. Create a string parameter listing the different date parts. Then, create a calculated field similar to the following:


          case [Date Part]

          when "Week" then "Week " + datename("week", [Order Date])

          when "Month" then left(datename("month", [Order Date]), 3)

          when "Quarter" then "Q" + datename("quarter", [Order Date]) + " " + datename("year", [Order Date])

          when "Year" then datename("year", [Order Date])



          Place this on your columns shelf next to the date field. Right click on the date field and uncheck Show Header.


          Hope this helps!



          • 2. Re: Format the Axis based on selection
            Mike J

            Hi Tracy!,


            I tried your suggestion, unfortunatly the dates I use are continious and that method groups the data by "years,quarters,months....)  causing the following..

            Dynamic Axis Labels Rev2.PNG

            Thanks for your help, but unfortunaly that trick wont work for me.. 

            I'm just looking to change the way the labels on the axis are displayed without manually having to format them everytime I select a new date granularity