locked
First and last dates in query RRS feed

  • Question

  • My cube reports on web site activity around ad clicks. Each ad has multiple records in the fact table. One dimension key is Date Reported, which is a date when an ad was active.

    In my report, I need to have a row for each ad and 3 columns. Column A specifies the number of ad clicks in the time period (entered as a parameter). Column B needs to display the first date in the reporting period for which the ad was active. Column C needs to display the last date in the reporting period for which the ad was active.

    I cannot seem to find the MDX to calculate the first/last dates in the dataset query. I have tried

                   

     WITH MEMBER [Measures].[First Day Reported] AS
    
            TAIL(
    
            NONEMPTY(
    
                            {[Date].[Date].MEMBERS},
    
                                    ([Measures].[Clicks])
    
                                            )
    
                    ,1
    
                    )
    
    

     

     

    But this returns #error in the column. The error message is “The function expects a string or numeric expression for the argument. A tuple set expression was used.”

    Any suggestions would be greatly appreciated.

    Thursday, March 24, 2011 4:57 PM

Answers

  • Tail returns a Set. As your count parameter to Tail function is 1, it looks like you are getting only one member. So you can do 

    TAIL(..............).Item(0).Member_value


    vinu
    • Marked as answer by Challen Fu Friday, April 1, 2011 12:34 PM
    Thursday, March 24, 2011 5:58 PM
  • You can also have date measures. So consider putting a datetime column in your fact table and building both an AggregateFunction=Min and Max measure off it. This will perform much better than any MDX approach
    http://artisconsulting.com/Blogs/GregGalloway
    • Marked as answer by Challen Fu Friday, April 1, 2011 12:34 PM
    Sunday, March 27, 2011 3:02 PM

All replies

  • Tail returns a Set. As your count parameter to Tail function is 1, it looks like you are getting only one member. So you can do 

    TAIL(..............).Item(0).Member_value


    vinu
    • Marked as answer by Challen Fu Friday, April 1, 2011 12:34 PM
    Thursday, March 24, 2011 5:58 PM
  • You can also have date measures. So consider putting a datetime column in your fact table and building both an AggregateFunction=Min and Max measure off it. This will perform much better than any MDX approach
    http://artisconsulting.com/Blogs/GregGalloway
    • Marked as answer by Challen Fu Friday, April 1, 2011 12:34 PM
    Sunday, March 27, 2011 3:02 PM