February 27, 2015 at 1:46 pm
Hi,
i have the following MDX query that uses parameters from an SSRS report to produce a result set:
WITH MEMBER PREM AS
StrToMember("[Measures].[" + @PremiumType+ " Premium]")
SELECT NON EMPTY
{PREM}
ON COLUMNS,
NONEMPTY
(Filter({ ([Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
[Broker].[Account Broker Name Previous - Big 6].[Account Broker Name Previous - Big 6].ALLMEMBERS *
[Broker].[Account Broker Name - Big 6].[Account Broker Name - Big 6].ALLMEMBERS)}
,[Broker].[Account Broker Name - Big 6].CurrentMember.name <> [Broker].[Account Broker Name Previous - Big 6].CurrentMember.name
AND ([Broker].[Account Broker Name Previous - Big 6].Members = "Marsh" OR [Broker].[Account Broker Name - Big 6].CurrentMember.name = "Marsh")))
ON ROWS
FROM ( SELECT ( - { [Broker].[Account Broker Name Previous - Big 6].&[] } )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@Operations, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT( STRTOSET(@Division, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@DateQuarter, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@ProductLineGroup, CONSTRAINED) )
ON COLUMNS
FROM [Broker Premium])))))
the query gives me what I need, however, in the filter clause there is an OR statement that checks if the value of either the 'Account Broker Name Previous - Big 6' or the 'Account Broker Name - Big 6' = "Marsh". Instead of hard coding the value, i want to check a multi-valued parameter that will store the names pf the brokers. I need to somehow replace the OR statement to filter the result set based on what is coming in from the parameter to see if either field has the value(s) from the parameter.
so i basically need to replace the filter clause like this:
[Broker].[Account Broker Name Previous - Big 6].Members = STRTOSET(@Broker) OR [Broker].[Account Broker Name - Big 6].CurrentMember.name = STRTOSET(@Broker)
can this be done using MDX?
March 2, 2015 at 8:34 am
Yes, it can be done. Use the StrToSet() function in your where clause to filter, with the values of your dropdown...you may have to format these a little.
March 2, 2015 at 9:45 am
Hi,
When I change the query to use a where clause:
WITH MEMBER PREM AS
StrToMember("[Measures].[" + @PremiumType+ " Premium]")
SELECT NON EMPTY
{PREM}
ON COLUMNS,
NONEMPTY
(Filter({ ([Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
[Broker].[Account Broker Name Previous - Big 6].[Account Broker Name Previous - Big 6].ALLMEMBERS *
[Broker].[Account Broker Name - Big 6].[Account Broker Name - Big 6].ALLMEMBERS)}
,[Broker].[Account Broker Name - Big 6].CurrentMember.name <> [Broker].[Account Broker Name Previous - Big 6].CurrentMember.name))
ON ROWS
FROM ( SELECT ( - { [Broker].[Account Broker Name Previous - Big 6].&[] } )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@Operations, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT( STRTOSET(@Division, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@DateQuarter, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@ProductLineGroup, CONSTRAINED) )
ON COLUMNS
FROM [Broker Premium])))))
WHERE (
{STRTOSET('[Broker].[Account Broker Name Previous - Big 6].&[Marsh]')}
)
I get the following error:
"The Account Broker Name Previous - Big 6 Hierarchy already appears in the Axis 1."
Is it because I am selecting the field and using in the where clause? Is there a different way to write the query to make it work?
thanks
Scott
March 2, 2015 at 2:58 pm
Yes, you cannot use the same hierarchy in the where-clause and the "Rows" axis.
You have 2 options in this case:
1. Instead of using it in the where-clause, create a filtered set. Similar to the calculated member, you'll use "with set... as..." and then use that set on the axis.
2. If this member is part of a hierarchy, use the dimension attribute directly to filter. If there is no hierarchy, you could create one if it makes sense.
A few other observations if I may:
* Using the StrToSet() function in calculated members/sets usually slows the query quite a bit. Avoid that if you can, by building up the query dynamically with an expression. Not pretty, but could be significantly better than the poor performance.
* Your query is a good example of the bad MDX that the designer or query builder generates. You should be able to move the sub-queries either to the where-clause or create similar calculated sets. It'll make the query easier to read as well.
March 2, 2015 at 9:08 pm
Thanks Martin,
Being new to MDX, i usually start off with the Designer then modify the query as needed. Probably not best practice...
In regards to creating the named set, i understand what you are saying, but How do i create a named set that will get me the records where either of the 2 fields has the value I am looking for?
Again, i would need the set to do something like [Account Broker - Big 6] = "Marsh" OR [Account Broker Previous - Big 6] = "Marsh" and then only return the records that match that criteria. But I also want to show both those fields as columns.
Scott
March 3, 2015 at 7:39 am
To create a named set, the syntax will look something like the following:
with set [Filtered Customers]
as
{
{[Retail Customer].[Gender].&[Male]}
* {[Retail Customer].[Marital Status].&[Divorced]}
}
select{[Measures].[Units Sold]} on 0
,{[Filtered Customers]} on 1
from[Sales]
The above will create a named set which contains only divorced males, and the select query will return those two members as well as the units sold for them. Essentially, this is the intersection of those 2 members and the measure.
Incorporating the StrToSet() function to account for the variables you want to pass is just as simple:
with set [Filtered Customers]
as
{
{strtoset('[Retail Customer].[Gender].&[Male]')}
* {strtoset('[Retail Customer].[Marital Status].&[Divorced]')}
}
select{[Measures].[Units Sold]} on 0
,{[Filtered Customers]} on 1
from[Sales]
The important part here is to ensure that whatever is returned from your variable or parameter is in the right format as the above (everything between the single quotes).
Hope this helps.
March 3, 2015 at 7:48 am
To add to my previous response:
If you want to create an "OR" scenario and display both members separately, include the "All" member from each attribute hierarchy:
with set [Filtered Customers]
as
{
{strtoset('{[Retail Customer].[Gender].&[Male],[Retail Customer].[Gender].[All]}')}
* {strtoset('{[Retail Customer].[Marital Status].&[Divorced],[Retail Customer].[Marital Status].[All]}')}
}
select{[Measures].[Units Sold]} on 0
,{[Filtered Customers]} on 1
from[Sales]
March 3, 2015 at 9:45 am
Martin,
thanks for your help on this.
i changed the query to include the filtered set using the "OR" logic:
WITH MEMBER PREM AS
StrToMember("[Measures].[" + @PremiumType+ " Premium]")
SET FilteredBrokers AS
{STRTOSET('{[Broker].[Account Broker Name - Big 6].&[Marsh], [Broker].[Account Broker Name - Big 6].[All]}')}
* {STRTOSET('{[Broker].[Account Broker Name Previous - Big 6].&[Marsh], [Broker].[Account Broker Name Previous - Big 6].[All]}')}
SELECT NON EMPTY
{PREM}
ON COLUMNS,
NONEMPTY
({ [Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
{FilteredBrokers} })
ON ROWS
FROM ( SELECT ( STRTOSET(@Operations, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT( STRTOSET(@Division, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET('[Date].[Quarter].&[2013]&[2]', CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@ProductLineGroup, CONSTRAINED) )
ON COLUMNS
FROM [Broker Premium]))))
However, it seems to be creating many (null) records for the filtered set and doesn't seem to be giving me the correct data back.
see sreenshot attached.
what i need to do is check to see if the Account Broker Name - Big 6 is not equal to the Account Broker Name Previous - Big 6 for any given quarter.
March 3, 2015 at 10:01 am
scottcabral (3/3/2015)
Martin,thanks for your help on this.
i changed the query to include the filtered set using the "OR" logic:
WITH MEMBER PREM AS
StrToMember("[Measures].[" + @PremiumType+ " Premium]")
SET FilteredBrokers AS
{STRTOSET('{[Broker].[Account Broker Name - Big 6].&[Marsh], [Broker].[Account Broker Name - Big 6].[All]}')}
* {STRTOSET('{[Broker].[Account Broker Name Previous - Big 6].&[Marsh], [Broker].[Account Broker Name Previous - Big 6].[All]}')}
SELECT NON EMPTY
{PREM}
ON COLUMNS,
NONEMPTY
({ [Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
{FilteredBrokers} })
ON ROWS
FROM ( SELECT ( STRTOSET(@Operations, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT( STRTOSET(@Division, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET('[Date].[Quarter].&[2013]&[2]', CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@ProductLineGroup, CONSTRAINED) )
ON COLUMNS
FROM [Broker Premium]))))
However, it seems to be creating many (null) records for the filtered set and doesn't seem to be giving me the correct data back.
see sreenshot attached.
what i need to do is check to see if the Account Broker Name - Big 6 is not equal to the Account Broker Name Previous - Big 6 for any given quarter.
The <null> values you are seeing in SSRS are really the [All] members. This is how SSRS displays the [All] member names (as empty strings), and there are many because you are retrieving all possible combinations of the [All] members with your other dimension members on the axis.
Create a filter in SSRS to exclude the <null> values (or empty strings). If this does not work, then I am afraid I am failing to see exactly what your expected output is here. You'll need to provide some examples of your expected outcome (and current output) if that is the case.
March 3, 2015 at 11:12 am
Martin,
Basically, this is an SSRS report that sits over an SSAS cube. What the report is supposed to do is show which accounts for a given Quarter, Operations, Division, and ProductLineGroup (all parameters) have a different value in the "Account Broker Name Previous - Big 6" and "Account Broker Name - Big 6" fields. Basically show the accounts where the previous value was not equal to the current value. Both the previous value and current value are fields that exist in the cube.
the query the i have so far which gives me the accounts using the parameters given where both fields don't match is this:
WITH MEMBER PREM AS
StrToMember("[Measures].[" + @PremiumType+ " Premium]")
SELECT NON EMPTY
{PREM}
ON COLUMNS,
NONEMPTY
(Filter({ ([Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
[Broker].[Account Broker Name Previous - Big 6].[Account Broker Name Previous - Big 6].ALLMEMBERS *
[Broker].[Account Broker Name - Big 6].[Account Broker Name - Big 6].ALLMEMBERS)}
,[Broker].[Account Broker Name - Big 6].CurrentMember.name <> [Broker].[Account Broker Name Previous - Big 6].CurrentMember.name))
ON ROWS
FROM ( SELECT ( - { [Broker].[Account Broker Name Previous - Big 6].&[] } )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@Operations, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT( STRTOSET(@Division, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@DateQuarter, CONSTRAINED) )
ON COLUMNS
FROM ( SELECT ( STRTOSET(@ProductLineGroup, CONSTRAINED) )
ON COLUMNS
FROM [Broker Premium])))))
AS you can see, i am filtering the data set to only give me the records where the Account Broker Name Previous - Big 6 <> Account Broker Name - Big 6. This will give me the records where both of the fields have different values, which is what the user is looking for, by Quarter, Operations, Division, and ProductLineGroup. I am also removing and blank values for the Account Broker Name Previous - Big 6, since the year 1 for any account will not have a previous value.
However, the trick here is there is another parameter called @Broker. This parameter is a multi-select parameter and will allow the user to select the actual broker that they want to see the differences for. So for example, if they select "Aon", then the report should show the accounts where either the Account Broker Name Previous - Big 6 was equal to "Aon" and the Account Broker Name - Big 6 is not equal to "Aon" OR where the Account Broker Name - Big 6 is equal to "Aon" and the Account Broker Name Previous - Big 6 is not equal to "Aon". I need to check going both ways on the 2 fields. This will tell the user that either the account was Aon before and now is something else OR the account was something else before and now it is Aon.
Once the user selects the Broker from the @Broker parameter, i need to somehow alter my MDX to use the value in the parameter and check if either the Account Broker Name - Big 6 OR the Account Broker Name Previous - Big 6 fields have the value from the parameter.
I create the @Broker parameter and have the value set to the [Broker].[Account Broker Name - Big] member. I will probably have to create another parameter, maybe called @BrokerPrevious to store the value in the [Broker].[Account Broker Name Previous - Big 6] member. Then all i need is to alter the mdx to see if either field has the value that was selected by the user.
let me know what you think
thanks
Scott
March 3, 2015 at 11:41 am
Ok...I think it makes a little more sense (to me) now.
I think a union will work in this case:
NONEMPTY
(
{
Filter
(
{
(
[Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
StrToSet(@PreviousBroker) *
[Broker].[Account Broker Name - Big 6].[Account Broker Name - Big 6].ALLMEMBERS
)
}
,[Broker].[Account Broker Name - Big 6].CurrentMember.name <> [Broker].[Account Broker Name Previous - Big 6].CurrentMember.name
)
}
+
{
Filter
(
{
(
[Prospect Client].[Account Number].[Account Number].ALLMEMBERS *
[Prospect Client].[Account Name].[Account Name].ALLMEMBERS *
[Prospect Client].[Division].[Division].ALLMEMBERS *
[Prospect Client].[Department].[Department].ALLMEMBERS *
[Prospect Client].[Section].[Section].ALLMEMBERS *
[Broker].[Account Direct Broker Code Previous].[Account Direct Broker Code Previous].ALLMEMBERS *
[Broker].[Account Direct Broker Code].[Account Direct Broker Code].ALLMEMBERS *
[Broker].[Account Broker Name Previous - Big 6].[Account Broker Name Previous - Big 6].ALLMEMBERS *
StrToSet(@Broker)
)
}
,[Broker].[Account Broker Name - Big 6].CurrentMember.name <> [Broker].[Account Broker Name Previous - Big 6].CurrentMember.name
)
}
)
MDX Union Reference: https://msdn.microsoft.com/en-us/library/ms146030.aspx
March 3, 2015 at 1:24 pm
Yes, finally!!!
thanks so much for your help. You were a great source for information.
Scott
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply