Filtering 2nd Row Values Based on Value in the 1st Row
Hi,
Here is my situation. I have 4 dimensions -- time, scenario, program and metric. Time is my column, scenario is my filter and my rows are program and metric. For this example my program members will be P1, P2 and P3. My metrics will be M1, M2, M3, ... M10. Some programs use all the metrics, some only use a handful. For example below is how I would like my model to appear (without the filter).
Jan Feb Mar April etc.
P1 M1
M2
M8
P2 M1
M2
M3
M4
P3 M1
M8
M10
If I pull both program and metric in as rows, I get partially there but for each program I see all the metrics that are setup and we don't want this as some metrics don't pertain to that particular program. And for internal user interface reasons, I cannot put program as a filter as I would like.
Suggestions?
Alan
Answers
- this is a long standing item that i've meant to blog about... last couple months I've slacked off on posts due to my regular work schedule...
If you have the assumption model setup as described you can do something like this which I've suggested internally with good results. In this case the assumptions model would only have dimensions Products and Territory:
Select {[Time].[Fiscal].[Year 2008].Children} on columns,
filter( {[Products].[All Members].AllMembers}*{[Territory].[All Members].AllMembers}, [Measures].[Linked Name]=0) on rows
from [Cube]
- I would start with a matrix that does everything you want except does not have the filtering. Then you do show mdx there and copy it to notepad. Then go to report properties and edit the mdx. Copy in first what was there originally to mak sure you get the hang of kind of weird way we use the dialog where some of the mdx is outside the boxes you edit.
Also you don't need to include the MDX relating to any page filters, that will be inserted automatically.
Then when that is working you should be able to wrap what is there for rows with something like this, where the linked_model_measure also needs to be updated to match your assumption model...:
filter( existing_mdx_for_rows, [Measures].[Linked_Model_Measure]=0) - Alan and I talked a little bit offline. Assumptions vs Associations were confused. After switching to the Assumption model strategy and a little MDX tweaking this solution is working for him. I'm refining my recipe and hope to post it soon...
All Replies
- There are a couple ways to do this. I generally tend to create an assumption model. Maybe with just Product and Metric as dimensions. Leave everything null except the valid intersections of Product and Metric. Put a 1 in those intersections.
Then with custom mdx in the matrix you can use the filter function to hide the intersections in the column axis that don't have a 1 in the valid intersections.... Peter,
I thought this was possible so thanks for you confirmation. Now I need a little more help.
Can you expand upon what you mention above? I need a little more guidance to get me going on this one. Maybe some sample MDX syntax for the 2nd part? Anything you can provide would help.
This provides a better solution for the client that will make our monitoring and analytics much cleaner later on.
Alan
- this is a long standing item that i've meant to blog about... last couple months I've slacked off on posts due to my regular work schedule...
If you have the assumption model setup as described you can do something like this which I've suggested internally with good results. In this case the assumptions model would only have dimensions Products and Territory:
Select {[Time].[Fiscal].[Year 2008].Children} on columns,
filter( {[Products].[All Members].AllMembers}*{[Territory].[All Members].AllMembers}, [Measures].[Linked Name]=0) on rows
from [Cube]
Don't you hate it when your work gets in the way of your blogging...
Thanks for the rest of the information. I think I see where you are going.
One question. I assume that in your example my assumption model had a time dimension set to only the year 2008 and I guess I would use whatever scenario I would want to call it.
- Right right, i just dug through my email to find the sample. In that case the modeler decided not to use another model and instead was just using NON EMPTY to do the filtering so my example MDX that I found didn't actually get tested and used.
In other scenarios you might use multiple time members so that you could have different combinations of products/territories etc in different time periods... So now I am working through this on a sample sight. The code you have referenced, I would put in the "User entered MDX" field under matrix properties in my target model (i.e., Program Metrics) -- correct?
As a note, I have two models -- a Program/Metric Association model where I say which program goes with which model via 1's and 0's which is associated with my final model which is entitled Program Metrics and where I need the filtering I was describing above.
MDX is my weakest point on this product and I don't pretend to fully understand it.
- I would start with a matrix that does everything you want except does not have the filtering. Then you do show mdx there and copy it to notepad. Then go to report properties and edit the mdx. Copy in first what was there originally to mak sure you get the hang of kind of weird way we use the dialog where some of the mdx is outside the boxes you edit.
Also you don't need to include the MDX relating to any page filters, that will be inserted automatically.
Then when that is working you should be able to wrap what is there for rows with something like this, where the linked_model_measure also needs to be updated to match your assumption model...:
filter( existing_mdx_for_rows, [Measures].[Linked_Model_Measure]=0) Peter,
This still isn't working for me and my ignorance of MDX, is a lot of the problem. Problem we now have on this project is soon we will be up to time crunches and the normal people I would have handle this are already swamped on other projects with just as critical deadlines.
Is this the type of thing I can put a support incident in and get someone who knows this stuff to do a go-to-assist type meeting and walk me through it online rather than via this support forum?
- Alan and I talked a little bit offline. Assumptions vs Associations were confused. After switching to the Assumption model strategy and a little MDX tweaking this solution is working for him. I'm refining my recipe and hope to post it soon...
A big THANKS to Peter for his help here on this issue. he went above and beyond and it will allow me to provide a much cleaner solution to the client!
Alan
Hi,
Which MDX do you enter in the user box, especially for rows ? I get an error telling me that my membersets (programs and metrics) are not of the same dimensionnality.
Thanks,
