2 Replies Latest reply on Oct 29, 2013 3:32 AM by Israr Khan

    SSAS OLAP cube Dates as measures display

    Israr Khan

      Hi,

       

      I want to display report bases on MS SSAS cube to display dates as measures.

      Scenario is to show the cross tab call report to show the first and last call date for a given call.

       

      I created a calculated measure in the MS SSAS cube to calculate the max and min date for a call. The format for display is date. Tableau is showing #Error for that measure.

      If i use date as a simple measure with data type as date, then tableau does not show this measure as measure.

       

      is there a way to create a cross tab with dates as measures. the aggregation of date measures is Max or min.

       

      thanks

        • 1. Re: SSAS OLAP cube Dates as measures display
          . Indumon

          Hi Israr,

          I want to display report bases on MS SSAS cube to display dates as measures.

          Yes, It is possible to show dates as measures in Tableau while connected to SSAS cube.

           

          Scenario is to show the cross tab call report to show the first and last call date for a given call.

           

          In order to create 'Last call date' and 'First call date' as measures, I recommend you to follow the below steps in your SSAS cube editor. (I feel this method is simpler than MDX calculation)

           

          Step 1: Go to the Cube structure tab in the Visual studio solution editor and right click on the "Call measure group" then choose the "Call Date" field as a new measure. (In below example I used my cube so field names are different)

          Screen.JPG.jpg

           

          Step 2: Set the measure aggregation.

          Right click on the newly added date measure-> Properties then set the aggregation to MAX as below.

           

          Screen1.png

          ('Formatstring' and 'Name', you can choose as per your requirement)

           

          Step 3: Repeat step 1 to setup the Min Date as well

          Step 4: Set the aggregation to Min

          Screen2.JPG.jpg

          once you are done with the new measure creation then Process the cube.

           

          ------------------------Tableau Part Post Cube refresh-----------------------------------------------------------

           

          When you connect to the cube from Tableau, by default you can see Min and Max dates under measures.

           

          Screen3.JPG.jpg

           

          For reports: Right click on the measures and Convert to Discrete then place them on the rows or columns.

           

          example:

          Screen5.png

           

          hope this will help you.

          • 2. Re: SSAS OLAP cube Dates as measures display
            Israr Khan

            thank you very much. Excellent solution.