One report, based on two DP’s. These two DP’s are linked on 3 dimensions. DP1 has less rows than DP2.
The report first shows all the matching rows and then the extra-rows of DP2 (with blank cells for information from DP1).
In order to get rid of the extra-rows with the blank cells, what I do is putting the same filter (=Not IsNull() And Not IsNull…) on the three linked dimensions.
But I don’t find this very handy , because if you want to change your filter, you have to change it on the 3 dimensions (OK, there’s the copy/paste, but still…).
Is there a more clever way to apply my filter ? Or another solution to get the result I want?
I donot understand why you need to filter on all 3 dimensions from DP1. Filtering on any one of the dimensions should give you the same result. Let me know if I am missing something.
I tried it, but if I apply my filter on just one dimension, then it does not work . The only way I found to make it work was to apply it on the 3 dimensions.
Have you tried putting the filter on a non-linked item from the data provider? It can be a non-linked dimension, a detail object, or a measure object. Whenever I need to change the “full outer join” behavior, I find this approach works best.
I first tried to apply it on a non-linked dimension. But it did not work. Then I applied it on the linked dimension (I had only one linked at that time), and it worked. So I figured out it was because the dimension was linked…
Afterwards I added new objects to my DP’s and linked them, and my filter did not work anymore. So I reapplied it on all the linked dimensions and then it re-worked… ouff
So I have a solution that works, but I’m not sure why it works and why it didn’t before…
Try creating a new dimension variable with the formula
=if isstring(<dim1>) then <dim1> else if istring(<dim2>) then <dim2> else .... else <dimN>
repeat the if istring test for however many dimensions you require.
Then apply your filter to the new dimension that is
=not isnull(<newdim>)
You can change the formula as you add/ remove new DPs and no changes to the filter in the reports.
(this also works for the linked universe alerter problem where the alerter doesn’t “apply” to a linked dimension If you chose the dimension from the first DP.)[/code]
[quote:1626f38105=“G.E.Davies”]Try creating a new dimension variable with the formula
=if isstring(<dim1>) then <dim1> else if istring(<dim2>) then <dim2> else .... else <dimN>
repeat the if istring test for however many dimensions you require.
Then apply your filter to the new dimension that is
=not isnull(<newdim>)
You can change the formula as you add/ remove new DPs and no changes to the filter in the reports.
(this also works for the linked universe alerter problem where the alerter doesn’t “apply” to a linked dimension If you chose the dimension from the first DP.)
[/quote]
Thanks Georges, but given that all my dimensions are strings, that should not have any effect.
Can you use a pre-run Report in this case i mean
you can create a Report with the 1st DP and the filter.
and insert one more table over the other with the 2nd DP and the filter.
In this case remove the header part of the second table.
Save the results as a text file and you will have results from both of them.
You will have problems only if ur using formulas
I used this in a similar scenerio to filter on different regions where the all the DP’s were text files.
let me know if this is useful to you
I wasn’t very clear in my original posting. You need the same dimension from each dataprovider
eg
if isstring(<linkdim1(dp name1)>) then <linkdim1(query 1 with universe> else if isstring(<linkdim1(dp name2)>) then <linkdim1(dp name2)> else <linkdim1(dp name3)>
Just one of the linked dimensions should be enough.
You may also need to plot the new dimension on the report. You can use hide cell so it doesn’t show. If the report is master/detail ie with sections you will need to use an aggregating function
Min() or Max()
in the hidden cell, assuming you haven’t sectioned the report using the new dimension.
I wasn’t very clear in my original posting. You need the same dimension from each dataprovider
eg
if isstring(<linkdim1(dp name1)>) then <linkdim1(query 1 with universe> else if isstring(<linkdim1(dp name2)>) then <linkdim1(dp name2)> else <linkdim1(dp name3)>
Just one of the linked dimensions should be enough.
You may also need to plot the new dimension on the report. You can use hide cell so it doesn’t show. If the report is master/detail ie with sections you will need to use an aggregating function
Min() or Max()
in the hidden cell, assuming you haven’t sectioned the report using the new dimension.
(edit to correct bad spelling)
[/quote]
I tried it too, but no success . Does this work with your reports? I still get the wrong same result .
U said ur filters r not global,so the idea would be to split ur Report into two reports.
The pre-run report and the Final Run.The pre-run report gets the results and computaions from dp1 and dp2, and the final run report displays results using the result from pre-run (text file as Data Provider).
In pre run u create a table with the first DP and the filter.Insert a second table above the first table itself and provide the columns from the second Data provider in this table and give the filter condition also.Remove the show header in second table after ur done.When u save this as a text file u will have results from both of them in 1 text file.This text file can be used as an input to the final run report. The only problem would be u cannot use any function or computation like in this case.Or else this works fine when you have different DP and just want to consolidate results.