Actions

Information

16 responses

30 12 2013
Dave

Thanks. Your post is still bringing relief to those in need!

26 06 2013
raj

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

19 04 2013
Jenny Ke

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];”

23 04 2013
Paul Turley

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.

28 02 2013
Letha

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

12 01 2013
Abbas

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.
regards

23 01 2012
Umit C.

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?

23 01 2012
Paul Turley

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.

23 01 2012
Andrew

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.

23 01 2012
Paul Turley

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.

29 07 2011
James Zimmerman

Paul,

This really helped me out. Thanks for posting.

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

17 05 2011
LVPRASAD

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

23 05 2011
Paul Turley

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.

with
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)

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

4 05 2011
denglishbi

Outstanding series of posts Paul, great job as always:) Congrats on the change of jobs as well and best of luck!

13 05 2011
Paul Turley

Thanks. I’m pretty happy with the move so far. SolidQ is a great company.

4 05 2011
Designing Reports with Custom MDX Queries – part 2 « Paul Turley's SQL Server BI Blog

[...] next: part 3 – Handling Parameters [step-by-step tutorial]… [...]

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




Follow

Get every new post delivered to your Inbox.

Join 244 other followers

%d bloggers like this: