16 thoughts on “Designing Reports with Custom MDX Queries – part 3

  1. Thank u so much…….. i gained much knowledge for my interviews,
    plzzz update few more reports using different functions 🙂

  2. Greate post. I learned a lot. Thank you very much.

    The only problem I found in the MDX query is that, since you set LagMonths as integer, you have to use str(LagMonths) in the fx to add it as a string.
    In addition, ‘+’ works for me insead of ‘&’. It took me a while to figure this out. The dynamic MDX query for me is:

    =”with member Measures.[Selected Measure] as [Measures].[”

    + Parameters!MeasureName.Value & “] “

    + “member Measures.TrendValue as ([Measures].[”

    + Parameters!MeasureName.Value

    + “] , ParallelPeriod([Date].[Calendar].[Month], “

    + str(Parameters!LagMonths.Value) + “, [Date].[Calendar].CurrentMember)) “

    + “select “

    + ” {[Measures].[Selected Measure], Measures.TrendValue} on columns “

    + ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].+[”

    + Format(Parameters!FirstDate.Value, “yyyyMMdd”) + “] ) on rows “

    + “from “

    + ” [Adventure Works];”

    1. Thank you for the suggestion, Jenny. Generally, it is a good idea to explicitly convert non-string types to strings when doing concatenation. You can use a few methods and functions to do this including STR(), CSTR() and .ToString(). I think most .NET purists would argue in favor of the ToString() method.

  3. It seems like you truly understand plenty related to this particular subject
    and it all exhibits throughout this excellent blog, called Windows
    Blinds “Designing Reports with Custom MDX Queries
    – part 3 | Paul Turley’s SQL Server BI Blog”. Thanks a lot -Fawn

  4. Hi Paul
    I use Dynamic MDX query to make a flexible environment for end user, like steps you define in this step by step tutorial
    I have a new requirement that want to show dynamic measure in one plot, for example I want to show Tax , sell , and gain amount measure in date dimension. if it was static in number I could define a data-set with the require member name and then assign a parameter to each fields.
    but in my case it is dynamic in count, so I look for a solution that merge this measures into one measure and make a new dimension that show main measure name of each cell.

    in this thread :http://social.msdn.microsoft.com/forums/en-us/sqlreportingservices/thread/6213030c-acfe-4520-b2ad-f544496a4486
    Raymond Lee at his last post say that :
    “Since you are using cube, you can try to delete the two measures, and create calculated measure to combine the two measure values with prefixed name -type (‘DocEntry’, ‘Quantity’) . After that:
    Category: group on cast the prefixed name
    Series: group on year
    Data: group on cast the value from the calculated measure.”

    actually I couldn’t understand Raymond’s solution and ask you to make it clear for me.

    any help will appreciated.

  5. Hi Paul,
    Great post over all. I have been trying to accomplish report parameter to query parameter mapping all day and I have not had any success. I was wondering if you could shed some light or give me some pointers?

    1. Let the query designer do as much of the work as possible. There is a bit of a trade-off here (but both options are good news.) If you build the query using the graphical designer, it will generate the parameters with the necessary lookup datasets. The first trick is to use this to create all the necessary metadata. Then you can switch to text mode, copy and paste the query into SSMS and manipulate it.

      For more advanced designs, don’t bother with query parameters. Write and execute a simple MDX query that returns the members you need in the report (which it translates to dataset fields). Take that command text into the expression window and use the techniques described in this post to build a parameterized expression, referencing report parameters, not query parameters. Once I go that route, I normally don’t try to add my own query parameters. If you really need to, you can do that by copying the XML definition of an existing query parameter and using it as a template for the new one.

  6. Hi Paul

    I have a question about dataset fields for dynamic mdx queries.

    When the fields for an MDX query cannot be autogenerated, what’s the best way to include them in the dataset? I’m working with queries that will not run in design mode, and manually adding fields to a dataset for a dynamic MDX query seems tricky, especially when it’s not clear from the MDX query what the returned field name will be.

    1. Andrew, I’ve been where you are and I know what you’re asking but you shouldn’t have to do that.
      Write a simple verison of the query that returns all the necessary fields. Keep two copies on hand – the simple verison without parameters and the complex verison with parameters. If you need to add a member to the query (e.g. field to the report), modify the query and execute it to let the report designer add the field.

  7. Paul,

    This really helped me out. Thanks for posting.

    James Z. (frequent O -SQL-Dev attendee)

  8. Hi Paul,
    This is LVPRASAD , Now i am working with SSIS,SSRS ,
    i have one doubt in SSRS i.e
    How to create Calulate member for the change in two consecutive months sales amount ?
    Can you please give me the suggestions for how to write MDX query for that
    Thanks & Regards,
    Vara Prasad

    1. Are you looking for a member that returns the previous month’s sales amount? … or the difference between the past two months’ sales. Here’s a short sample. If I’m not on track, please provide a sample of the output you’re expecting.

      member Measures.[Prev Month Sales] as
      ([Measures].[Reseller Sales Amount], [Date].[Month of Year].CurrentMember.PrevMember)

      member Measures.[Prev Month Sales Diff] as
      [Measures].[Reseller Sales Amount] –
      ([Measures].[Reseller Sales Amount], [Date].[Month of Year].CurrentMember.PrevMember)

      {[Measures].[Reseller Sales Amount], Measures.[Prev Month Sales], Measures.[Prev Month Sales Diff]} on columns
      , LASTPERIODS( 6, [Date].[Month of Year].&[12] ) on rows
      [Adventure Works];

Leave a Reply

%d bloggers like this: