RE: percentual sales rates, dynamically

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brian Altmann (findme_at_thesignaturewebsite)
Date: 09/15/04 

Date: Wed, 15 Sep 2004 10:43:06 -0700

You can use an expression such as :

strtoset("axis(1)").item(0).item(0).dimension.currentmember

to replace

[Farben].CurrentMember

where axis(1) represents the rows axis.

Since this type of calculated member is designed with certain query patterns
in mind, you might want to consider temp members tied to those queries rather
than server-defined calc members, which might return errors or unexpected
results for certain queries.
HTH,
   Brian
   www.geocities.com/brianaltmann/olap.html

"Siegfried Münch" wrote:

> Hallo,
>
> i'm working with the Analysis Server of SQL Server 2000.
> I had created a sales cube within the Analysis Cube-Editor.
> The sales cube displays sales and there percental rates (sales per
> Colours).
> [Farben].CurrentMember means the dimension [colours].CurrentMember.
>
> Please look at my formula of the calculated member [sales_rate]:
> iif(isempty(([Measures].[VK_VK_NE], Ancestor( [Farben].CurrentMember,
> 1))), 1, [Measures].[VK_VK_NE] / ([Measures].[VK_VK_NE],
> Ancestor([Farben].CurrentMember, 1)))
>
> This formula works fine, but only, if i display the colours-dimension.
> If i change, within the cube-editor, the cube from sales per colours
> to sales per ware-group (drag and drop in the SQL-Editor), my formula
> must fail - i must change the formula to:
>
> iif(isempty(([Measures].[VK_VK_NE], Ancestor(
> [ware_group].CurrentMember, 1))), 1, [Measures].[VK_VK_NE] /
> ([Measures].[VK_VK_NE], Ancestor([ware_group].CurrentMember, 1)))
>
> No, my question:
> I want show the percentual rates for all selected dimensions. I'm
> searching
> for a formula, which is okay for all dimensions.
> Is there a function, which returns the current selected dimension?
> For example like this:
>
> iif(isempty(([Measures].[VK_VK_NE], Ancestor(
> currentDimension().CurrentMember, 1))), 1, [Measures].[VK_VK_NE] /
> ([Measures].[VK_VK_NE], Ancestor(currentDimension().CurrentMember,
> 1)))
>
> or is the another way to realize my idea?
>
> Thanks for all answers.
>
> Siegi
>

Relevant Pages

  • percentual sales rates, dynamically
    ... I had created a sales cube within the Analysis Cube-Editor. ... I want show the percentual rates for all selected dimensions. ...
    (microsoft.public.sqlserver.olap)
  • Re: Converting date entry from mm/dd to mm/yy
    ... bunch of sales agents. ... My problem is that some of these agents are members of a team, ... record for that agent, with the sales date you entered. ... You have a Date/Time field. ...
    (microsoft.public.access.formscoding)
  • Re: Needed Advice - Olap client tool
    ... or if the report display members not available to this user. ... When I sent the spreadsheet to another user, a sales person with actual ... I'll found a security group called "OLAP Administrators" ...
    (microsoft.public.sqlserver.olap)
  • Urgent:Dynamic Security using Member Properties
    ... I want have a Dimension named Sales Representative and i want to ... cannot see siblings or members other than that come under his ... I donot want to restrict top and bottom level i have put MDX ... Filter( ...
    (microsoft.public.sqlserver.olap)
  • Re: Constructive Dismissal
    ... > Considering that the company is in financial difficulty (as all sales ... > members aren't hitting targets and it looks like they're trying to ... > they're not going to pay above the statutory minimum or contract ...
    (uk.legal)