May 18, 2004 at 7:20 am
I've been creating a variety of complex calculated members for a cube I'm working on, and don't know if I can create the following type of calculated member.
Scenario: Imagine having an olap output where time (year/months) are listed on the rows, and some given measure (call it "money") is listed on the column.
Is there a way to have the total at the Year level only sum the data being *displayed* at the month level. For example, if the total for all of 2003 = $1,200 (with each month = $100), is it possible to have only $300 display at the year level if only Jan, Feb, and March are displayed (for example, in an Excel pivot table) rather than $1,200 showing up at the year level?
I feel like the answer is obvious, but yet elusive.
Any suggestions?
--Pete
May 19, 2004 at 2:18 am
No You cannot.
An accumulated sum measure measure might provide some of the functionality you require. Also You could add a "quarter" level in your time dimension.
/rockmoose
You must unlearn what You have learnt
May 19, 2004 at 5:11 am
I have to disagree, if you set the Visual Totals (to 'on') within a role that is used to access the cube, this will cause any totals to display only the total of those items you have on the report. Look for Visual Totals on the 'Common' tab of the 'Dimension Security' within the Role.
Steve.
May 19, 2004 at 2:16 pm
Visual Totals will sum the members visible to the role. And does not have anything to do with what members are visible on the report.
So still again, I don't think you can achieve the functionality that peterzeke wants to accomplish ( In AS ).
Some client tool might incorporate this kind of "dynamic conditional sum", but none that I know of.
/rockmoose
You must unlearn what You have learnt
May 20, 2004 at 6:15 am
Interesting... I've come across a function called VisualTotals(), but not sure it could meet my needs. Never used it so I'm not sure what it can/can't do or quirks it may have. Do either of you have experience with this function?
--Pete
May 20, 2004 at 7:04 am
I have only limited experience of the VisualTotals function. We have dabbled with roles and VisualTotals in some cubes, but are not currently using it anywhere.
The conditional sum that You are trying to achieve is something that some of our users also have asked for. In our case the end users are using ProClarity and if they want to Sum only the members visible on the report they use the Sum functionality in ProClarity.
Just like summing the cells on an Excel spreadsheet.
And we have provided them with variuos Accumulating measures.
/rockmoose
You must unlearn what You have learnt
May 20, 2004 at 7:12 am
Say Rockmoose, tell me about ProClarity. Good, bad? I know this inquiry is outside the realm of my initial post, but I'm also in charge of investing useable OLAP reporting tools in addition to creating the cubes and doing the MDX thing. Excel pivot tables are limiting, and quirky when it comes to calculated members. I've seen a demo on Novaview by Panorama Sftwre (nice), but certainly I've also read good things about ProClarity.
--Pete
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply