6 thoughts on “Designing Reports with Custom MDX Queries – part 1

  1. Hi I am very new in MDX. I need to get the TotalSale-Monthly Sale.
    I have Sales data from which I can easily get Sales per month, but I need to find out totalSale-PeriodSale Data.
    I have below data
    Period Sale Amount
    200101 100
    200102 2500
    200103 3200
    200104 200
    200105 300
    200106 1500
    200107 6500
    200108 1500
    200109 8500
    200110 120
    200111 8652
    200112 755
    200201 655
    200202 241
    200203 455
    200204 655
    200205 855
    Total Sale 36688

    Need to find out below data In third column.

    Period Sale Amount Total Sale-Period Sale
    200101 100 36588
    200102 2500 34188
    200103 3200 33488
    200104 200 36488
    200105 300 36388
    200106 1500 35188
    200107 6500 30188
    200108 1500 35188
    200109 8500 28188
    200110 120 36568
    200111 8652 28036
    200112 755 35933
    200201 655 36033
    200202 241 36447
    200203 455 36233
    200204 655 36033
    200205 855 35833
    Total Sale 36688

    Could you please help me.
    Thanks in advance.

    Like

  2. Hi Paul,

    This is certainly a very informative post. I am new to MDX after using SSRS with T-SQL. The examples you have provided regarding passing dynamic values to fields and measures will undoubtedly save a lot of work in making the reports provided as dynamic as possible. Negating the need for the design and creation of multiple reports each with a different slant.

    I am, however, having a problem creating a dynamic management view. I have entered the following into the expression builder:

    Select Measures.[Take_on_Balance] from $System.MDSCHEMA_MEASURES WHERE CUBE_NAME = ‘NAME_OF_CUBE’

    ORDER BY Measures.[Take_on_Balance];

    N.B. NAME_OF_CUBE is supplemented with my organisations cube name

    However, the dataset does not populate and I receive the following error:

    “Could not create a list of fields for the query. Verify that you can connect to the datasource and that your query syntax is correct.
    Parser: The dot expression is not allowed in the context at line 1, column 8.”

    Any help would be greatly appreciated?

    Best Regards

    Carl

    Like

    • I don’t think you can create a calculated member from an DMV query. Calculated members work with MDX and not the specialized query syntax used for getting results from an DMV.

      Like

  3. Pingback: Designing Reports with Custom MDX Queries – part 3 « Paul Turley's SQL Server BI Blog

  4. Pingback: Designing Reports with Custom MDX Queries – part 2 « Paul Turley's SQL Server BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s