3 Replies Latest reply on Jul 21, 2015 9:39 AM by Kelly Sedlak

    Date Parameter in Microsoft OLAP cube(Analysis services) ???

    Vinoth kumar Balakrishnan

      I am trying to create a Date Parameter from the Day attribute of Time Hierarchy in Microsoft OLAP cube(Analysis services) and pass that parameter to the same Day attribute.

       

      Below is the steps I have followed:

      1. Right clicked the Day Attribute and created the Parameter.

      1.jpg

      2.Screen shot of the parameter before creating

      2.jpg

      3.Now I hit ok to create the parameter.

      4. Then Dragged the same Day Attribute to the Row shelf, I got all the dates.

      3.jpg

      5. Then Dragged the same date attribute to filter and selected the condition tab to apply the date parameter as the input.

      4.jpg

      4.jpg

      6. After hitting ok, all the dates listed disappeared. Then selected the option to show parameter control and then tried to change the selected date in the parameter control, which doesn't show the selected in the row shelf.

       

      Anyone have Idea of achieving this??? Inputs are welcome to help me understand how this works.

        • 1. Re: Date Parameter in Microsoft OLAP cube(Analysis services) ???
          Kasper Ulvedal

          I have had a similar issue with a date parameter that should be compared to members in a SSAS OLAP Cube.

          To solve this, I took the same approach as you do above - however, I formatted the parameter as date and compare this to the attribute in the Date dimension.

           

          I am having the following:

          - A dimension in the cube with an attribute named [Date]

          - The parameter [Select Date] that is formatted as date and includes a range setting minimum and max date with the step size of 1 month.

           

          The "Date" attribute is set as filter in the sheet.

          The condition of the filter includes this formula: date([Date]) <= date([Select Date])

           

          By setting this, you will get all dates older than the date set in the parameter.

           

          Cheers,

          Kasper

          • 2. Re: Date Parameter in Microsoft OLAP cube(Analysis services) ???
            Benoit Cocheteux

            Hi Kasper,

             

            Thanks for the tip, it works with my worksheet!

             

            However, I encouter a query problem while trying to use a measure. Here is the error text:

             

            Analysis Services database error 0x00000001: The '1024' locale is not supported.

            Analysis Services database error 0x80004005: Query (12, 67) The following system error occurred:  Type mismatch.

              ; The query: WITH

            MEMBER [Measures].[Parameter_Fin File Date Parameter] AS

              'CDate(41828.781124803238)',

              SOLVE_ORDER = 65535

            MEMBER [Measures].[Tableau Constant Measure] AS

              '1',

              SOLVE_ORDER = 65535

            SELECT

            {[Measures].[Tableau Constant Measure]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,

            Hierarchize(Filter(

              [Fin File Date].[Fin File Date].[Fin File Date].AllMembers,

              (IIF(IsDate([Fin File Date].[Fin File Date].CurrentMember.NAME),CDate([Fin File Date].[Fin File Date].CurrentMember.NAME),NULL) <= DateValue(CDate(41828.781124803238))))) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS

            FROM [FinancialFile]

             

            Unable to calculate the filter for the field 'TEMP(Unroll)(290714814)(0)'.  The display may not show correct data.

             

            Would you be able to tell me what's going on?

             

            Many thanks,

             

            Benoît

            • 3. Re: Date Parameter in Microsoft OLAP cube(Analysis services) ???
              Kelly Sedlak

              Benoit - did you ever figure out this issue? I am unable to work with date filters/parameters with my SSAS dates! it is very frustrating! i get the same error as you (above)Tableau TEMP(Unroll)(290714814)(1)