locked
Syntax for the date range on weekly basis in report using data source as SSAS RRS feed

  • Question

  • Hi,

    I'm new to SSRS, I am creating a report such that to see the data on weekly basis

    I have a Date column and weeknumber column, how do I write the syntax to see only the latest week's data in that.

    the synatx is below which I am not able to write:

    [Dim%20Date].[Full%20Date%20Alternate%20Key].%26[2012-05-13T00%3A00%3A00]

    Can anyone let me know the correct sysntax for this.

     
    • Edited by K.Kalyan Sunday, May 20, 2012 7:54 PM edit
    Saturday, May 19, 2012 11:34 PM

All replies

  • I'm stll unable to figure this, any help is appreciated.
    Sunday, May 20, 2012 7:56 PM
  • Hi K.Kalyan,


    Please remember to mark the replies as answers if they help.

    Monday, May 21, 2012 2:59 AM
  • Hi Lola,
    Thanks for the links, with respective to the query in the link you mentioned, I have changed my query like:

    with member [dim date].[Full Date Alternate Key].[Full Date Alternate Key] as
      Aggregate(Tail(Descendants([dim date],[Dim Date].[Full Date Alternate Key].[Full Date Alternate Key]), 7.0))
    select NON EMPTY {[Measures].[Availability]
    } ON COLUMNS,
     {(
    [Dim Product].[Slot Alias].[Slot Alias].ALLMEMBERS,
    [Dim Agents].[Measurement Region].[Measurement Region].ALLMEMBERS,
    [Dim Date].[Full Date Alternate Key].[Full Date Alternate Key].allmembers
    )
    }
    ON ROWS
    from [Cube]
    where [dim date].[Full Date Alternate Key].[Full Date Alternate Key]

    BUT it gives me error:

    Executing the query ...
    The Full Date Alternate Key hierarchy already appears in the Axis1 axis.
    Execution complete


    Can you please let me know my  mistake.

    Monday, May 21, 2012 4:09 AM
  • with member [dim date].[Full Date Alternate Key].[Full Date Alternate Key] as
      Aggregate(Tail(Descendants([dim date],[Dim Date].[Full Date Alternate Key].[Full Date Alternate Key]), 7.0))
    select NON EMPTY {[Measures].[Availability]
    } ON COLUMNS,
     {(
    [Dim Product].[Slot Alias].[Slot Alias].ALLMEMBERS,
    [Dim Agents].[Measurement Region].[Measurement Region].ALLMEMBERS,
    [Dim Date].[Full Date Alternate Key].[Full Date Alternate Key].allmembers
    )
    }
    ON ROWS
    from [Cube]
    where [dim date].[Full Date Alternate Key].[Full Date Alternate Key]

    See the bolded sections, you cannot reference a hierarchy in the WHERE clause of an MDX statement that is already referenced on an axis. 

    Try using the following subselect instead

    with member [dim date].[Full Date Alternate Key].[Full Date Alternate Key] as
      Aggregate(Tail(Descendants([dim date],[Dim Date].[Full Date Alternate Key].[Full Date Alternate Key]), 7.0))
    select NON EMPTY {[Measures].[Availability]
    } ON COLUMNS,
     {(
    [Dim Product].[Slot Alias].[Slot Alias].ALLMEMBERS,
    [Dim Agents].[Measurement Region].[Measurement Region].ALLMEMBERS,
    [Dim Date].[Full Date Alternate Key].[Full Date Alternate Key].allmembers
    )
    }
    ON ROWS
    from (SELECT [dim date].[Full Date Alternate Key].[Full Date Alternate Key] ON COLUMNS 

            FROM [Cube]

            )
    I'm not sure what it actually achieves though, if you use NONEMPTY on your column axis, the calculated member you are using should carve out the correct area of the cube... 

    Also, try asking your MDX questions in the Analysis Services forum


    Josh Ash

    Monday, May 21, 2012 6:23 AM