kirannandedkar
asked on
MDX issue
I tried to write the scope statement using different approaches, however haven’t been able to overcome the issue. An example of how the scope statement is rewritten :
SCOPE ([dOrganisation].[Organisa tion Hierarchy].[Reporting Unit].&[BELUX HYPERION]
,[dDataType].[Category Data Type].&[Forecast]);
THIS = [dOrganisation].[Organisat ion Hierarchy].[Reporting Unit].&[BELUX HYPERION_];
END SCOPE;
Attached is the document with the issue description. Thanks for your help in Advance!
000TD-Issue--1-.docx
SCOPE ([dOrganisation].[Organisa
,[dDataType].[Category Data Type].&[Forecast]);
THIS = [dOrganisation].[Organisat
END SCOPE;
Attached is the document with the issue description. Thanks for your help in Advance!
000TD-Issue--1-.docx
ASKER
Please help me
Your scope expression will not work as you are referencing CurrentMember values for [dDataType].[Category Data Type]. Unless these are included in the query there will be no current member for them. You could try nested Scopes (rather than explicitly testing the current member values) but I think this might have the same problem. I will try to find an alternative for you later.
Tim
p.s. as an aside, rather than using:
[dDataType].[Category Data Type].CurrentMember.Name = "Budget"
use
[dDataType].[Category Data Type].CurrentMember is [dDataType].[Category Data Type].&["Budget"]
as it's more efficient
Tim
p.s. as an aside, rather than using:
[dDataType].[Category Data Type].CurrentMember.Name = "Budget"
use
[dDataType].[Category Data Type].CurrentMember is [dDataType].[Category Data Type].&["Budget"]
as it's more efficient
ASKER
No this doesnt work either. Please help
Did you try nested scopes?
i.e.
SCOPE ([dOrganisation].[Organisa tion Hierarchy].[Reporting Unit].&[BELUX HYPERION]);
SCOPE ({[dDataType].[Category Data Type].&[Budget], [dDataType].[Category Data Type].&[Forecast]});
THIS = ([dOrganisation].[Organisa tion Hierarchy].[Reporting Unit].&[BELUX HYPERION_]);
END SCOPE;
END SCOPE;
I have tried this in an equivalent calculation and it works with the value only in the slicer.
Tim
i.e.
SCOPE ([dOrganisation].[Organisa
SCOPE ({[dDataType].[Category Data Type].&[Budget], [dDataType].[Category Data Type].&[Forecast]});
THIS = ([dOrganisation].[Organisa
END SCOPE;
END SCOPE;
I have tried this in an equivalent calculation and it works with the value only in the slicer.
Tim
ASKER
Still no luck... Same result...
ASKER
Here is how I think the scenario got closer. I created a MEMBER like below:
CREATE MEMBER CURRENTCUBE. [Measures].[000TD_FC_TEST] AS
(([dOrganisation].[Organis ation Hierarchy].[Reporting Unit].&[BELUX HYPERION_]
,[dDataType].[Category Data Type].&[Forecast]),[Measur es].[Balan ce])
And then create a Scope Statement:
SCOPE ([dOrganisation].[Organisa tion Hierarchy].[Reporting Unit].&[BELUX HYPERION]);
THIS = [Measures].[000TD_FC_TEST] ;
END SCOPE;
And this solves some of the problem as it gives me the desire result when slicing using “Forecast”. However it messes up the solution if I put “Budget” or any other Category in the slicer, it still gives the same results like “Forecast”.
Then I tried something like below:
SCOPE ([dOrganisation].[Organisa tion Hierarchy].[Reporting Unit].&[BELUX HYPERION]);
SCOPE ([dDataType].[Category Data Type].&[Forecast]);
THIS = ([Measures].[000TD_FC_TEST ]);
END SCOPE;
END SCOPE;
But this one won’t work either. Any suggestions on this. Thank you!
CREATE MEMBER CURRENTCUBE. [Measures].[000TD_FC_TEST]
(([dOrganisation].[Organis
,[dDataType].[Category Data Type].&[Forecast]),[Measur
And then create a Scope Statement:
SCOPE ([dOrganisation].[Organisa
THIS = [Measures].[000TD_FC_TEST]
END SCOPE;
And this solves some of the problem as it gives me the desire result when slicing using “Forecast”. However it messes up the solution if I put “Budget” or any other Category in the slicer, it still gives the same results like “Forecast”.
Then I tried something like below:
SCOPE ([dOrganisation].[Organisa
SCOPE ([dDataType].[Category Data Type].&[Forecast]);
THIS = ([Measures].[000TD_FC_TEST
END SCOPE;
END SCOPE;
But this one won’t work either. Any suggestions on this. Thank you!
ASKER
anybody expert there to help
Hi,
Your calculated member isn't really closer to sorting your problem out - you have simply created a tuple that explicitly returns the value for Belux hyperion_ sliced by Forecast. But it won't be sliced by anything else - it's not an approach that will resolve your problem.
What I don't understand is why the nested scope solution I provided didn't work - in my recreation of the problem it worked fine.
Your calculated member isn't really closer to sorting your problem out - you have simply created a tuple that explicitly returns the value for Belux hyperion_ sliced by Forecast. But it won't be sliced by anything else - it's not an approach that will resolve your problem.
What I don't understand is why the nested scope solution I provided didn't work - in my recreation of the problem it worked fine.
I have replicated the problem using Adventure Works: the idea being that for June and July, the measures for 'Bikes' is set to be the same as the values for 'Clothing'. Calculation is:
SCOPE ([Product].[Category].&[1] );
SCOPE ({[Date].[Month of Year].&[6],[Date].[Month of Year].&[7]});
THIS = ([Product].[Category].&[3] );
END SCOPE;
END SCOPE;
MDX queries to check this:
For June Bikes = Clothing:
SELECT ({[Measures].[Internet Sales Amount],[Measures].[Intern et Order Count]}) on rows,
NONEMPTY ([Product].[Category].[Cat egory].MEM BERS) on columns
FROM [Adventure Works]
WHERE ({[Date].[Month of Year].&[6]})
Accessories Bikes Clothing Components
Internet Sales Amount $65,200.93 $35,206.48 $35,206.48 (null)
Internet Order Count 1,723 778 778 (null)
(note that month is only included in the where condition)
For August, Bikes has it's own value:
SELECT ({[Measures].[Internet Sales Amount],[Measures].[Intern et Order Count]}) on rows,
NONEMPTY ([Product].[Category].[Cat egory].MEM BERS) on columns
FROM [Adventure Works]
WHERE ({[Date].[Month of Year].&[8]})
Accessories Bikes Clothing Components
Internet Sales Amount $52,056.61 $1,824,214.67 $23,335.39 (null)
Internet Order Count 1,323 932 519 (null)
Isn't this what you are trying to achieve?
Tim
SCOPE ([Product].[Category].&[1]
SCOPE ({[Date].[Month of Year].&[6],[Date].[Month of Year].&[7]});
THIS = ([Product].[Category].&[3]
END SCOPE;
END SCOPE;
MDX queries to check this:
For June Bikes = Clothing:
SELECT ({[Measures].[Internet Sales Amount],[Measures].[Intern
NONEMPTY ([Product].[Category].[Cat
FROM [Adventure Works]
WHERE ({[Date].[Month of Year].&[6]})
Accessories Bikes Clothing Components
Internet Sales Amount $65,200.93 $35,206.48 $35,206.48 (null)
Internet Order Count 1,723 778 778 (null)
(note that month is only included in the where condition)
For August, Bikes has it's own value:
SELECT ({[Measures].[Internet Sales Amount],[Measures].[Intern
NONEMPTY ([Product].[Category].[Cat
FROM [Adventure Works]
WHERE ({[Date].[Month of Year].&[8]})
Accessories Bikes Clothing Components
Internet Sales Amount $52,056.61 $1,824,214.67 $23,335.39 (null)
Internet Order Count 1,323 932 519 (null)
Isn't this what you are trying to achieve?
Tim
ASKER
Please see attachment and reply me ASAP
mydoc.doc
mydoc.doc
Hi,
As I understand it, if you have custom rollups then they will take precedence - I don't think you'll be able to work around them by attempting to provide other calculations (as you've been attempting). You would need to modify the custom rollups themselves to 'build in' the mapping between the Belux hyperion members.
Tim
As I understand it, if you have custom rollups then they will take precedence - I don't think you'll be able to work around them by attempting to provide other calculations (as you've been attempting). You would need to modify the custom rollups themselves to 'build in' the mapping between the Belux hyperion members.
Tim
ASKER
can you show how to do it please
Maybe - but I haven't used custom rollups myself. You would need to post the calculations to begin with...
ASKER
Please can somebody give me the solution as i am now tired of this.
I don't think you can expect a solution if you don't post the requested information. Can you post the custom rollup code?
ASKER
Please find attached code. Please change extension of attached file to .mdx
Copy-of-Formula.txt
Copy-of-Formula.txt
Looks like you will have to replicate the logic in the scope statements into these calculations.
You may be able to place these calcs inside the scope statement to simplify the checks on Forecast and Budget but modifying these calculations will be necessary.
You may be able to place these calcs inside the scope statement to simplify the checks on Forecast and Budget but modifying these calculations will be necessary.
ASKER
but cant you give me logic for it?
I'm not going to rewrite your query for you - it will take too much time. You have the logic, it's just plugging it into the existing calculations.
ASKER
Thanks for your help again! I did try modifying the calculations just like I did in the Scope statement, however it looks like we cannot achieve this simply within our cube. I've got to do this at the Report Level.
In the scope statement we cannot specify the SubCube query (the slicer pane in the SSAS Browser). We opened up a case with MS and got the above conclusion.
In the scope statement we cannot specify the SubCube query (the slicer pane in the SSAS Browser). We opened up a case with MS and got the above conclusion.
ASKER
ok close it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER