4 Replies Latest reply on May 27, 2014 9:15 AM by Matt Lutton

    DataName Calculated Field with Parameter for Yearly, Quarterly and Monthly Values

    Ed Kukec

      Hi All,

       

      I have a date field called Discharge Date in my table and I want to create a parameter that allows a user to rollup/drill down volumes by Year, Year/Quarter, Year/Month.  Let's assume I don't want users to be using the little rollup/drill down button that comes with any time dimension field. I've created a parameter Called Time Dimension with values Yearly, Quarterly, Monthly. The data gets rolled up to Yearly,Quarterly or Monthly volumes depending on what the user selects.  I also created a Time Dimension Calculated Field with the following "If" condition:

       

      IF [Time Dimension] = 'Yearly' then DATENAME('year',[DISCHARGE_DT]) elseif

         [Time Dimension] = 'Quarterly' then DATENAME('year',[DISCHARGE_DT])+" "+DATENAME('quarter',[DISCHARGE_DT]) elseif

         [Time Dimension] = 'Monthly' then DATENAME('year',[DISCHARGE_DT])+" "+DATENAME('month',[DISCHARGE_DT]) elseif

      end

       

      This seems to work ok but the problem is that our fiscal year starts on April 1st and ends on March 31st. I've set the fiscal year start on the DISCHARGE_DT already but for some reason when I use the calculated field in my group by clause instead of the discharge_dt it reverts back to Calendar Year so the numbers/volumes are off.

       

      Is there a way to fix this or rewrite my if condition to get the numbers our properly.  I wasn't sure whether I needed to use DateName or DataTrunc, etc.  For Yearly values...I just want the 4 digit year. For Quarterly, I would like it to look like "2010 Q1, 2010 Q2". For Monthly, I would like the format to look like "2010 Apr, 2010 May....".

       

      Any help would be appreciated.

       

      Thanks,

      Ed