locked
How to Setup Cube for Queries between 2 Dates RRS feed

  • Question

  • I have setup a simple cube for school admissions data and the end user will be using Excel or SSRS Reporting Manager to access the data. 

    The question I have is how to calculate applications (for example) between two date ranges so that the end user can do the same. 

    I am not sure if I should be using MDX to create a calculated member or if I should go about it a better or easier way.

    Here is some MDX code that I have been playing and seems to work...

     WITH MEMBER [MEASURES].[COUNT] AS 
    Sum([Dim Date].[Date].[Full Date].[2012-03-01 00:00:00.000]:[Dim Date].[Date].[Full Date].[2013-11-24 00:00:00.000], [Measures].[DISTINCTAPPLICANT])
    SELECT {[Measures].[COUNT] }ON COLUMNS, 
    {[Dim Applications].[APPL START TERM].&[2013AY]} ON ROWS
    FROM [FACT_ADMISSIONS]

    Unfortunately, if it's the calculated member that I should be using how would I set it up 1) to have date and start term parameters and 2) how would I set it up so that an end user would be able to pick the date ranges say in Excel?

    Thanks.

    Monday, November 25, 2013 7:15 PM

Answers

  • Hi,

    that query is belongs to ssrs.first you have to create startdate and enddate in ssrs parameter window.

    then pass above query in ssrs dataset window.

    it will work.

    if you want in ssms, please check below query.

    select

    {[Measures].[Internet Sales Amount]

    } on columns,

    {[Date].[Date].members} on rows

    from(

    select

    (

    ([Date].[Date].&[20050706]:[Date].[Date].&[20070906])

    ) on columns

    from [Adventure Works]

    )

     


    indu

    • Marked as answer by lewk3000 Wednesday, November 27, 2013 1:32 AM
    Tuesday, November 26, 2013 4:05 PM

All replies

  • Hi Lewk3000,

    According to your description, you want to add two parameters to your query so that users would be able to pick the date ranges dynamically, right? Here is sample query about date range for your reference.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
    ) on columns
    from [Adventure Works]
    ) 

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, November 26, 2013 2:34 PM
  • Hey Charlie.

    Thanks for the response.    I tried playing with the script in the query window of SSMS but it asked me to declare the Startdate parameter.

    As you can probably tell, I am pretty new to MDX and I was wondering where I put this code?  Is the SSAS cube through BIDS or at a report level (SSRS, Excel)?

    Thanks for the help.

    Tuesday, November 26, 2013 3:07 PM
  • Hi,

    that query is belongs to ssrs.first you have to create startdate and enddate in ssrs parameter window.

    then pass above query in ssrs dataset window.

    it will work.

    if you want in ssms, please check below query.

    select

    {[Measures].[Internet Sales Amount]

    } on columns,

    {[Date].[Date].members} on rows

    from(

    select

    (

    ([Date].[Date].&[20050706]:[Date].[Date].&[20070906])

    ) on columns

    from [Adventure Works]

    )

     


    indu

    • Marked as answer by lewk3000 Wednesday, November 27, 2013 1:32 AM
    Tuesday, November 26, 2013 4:05 PM