locked
Scope Statement Issue RRS feed

  • Question

  • Hi ,

    I have a cube  . We recently got a new requirement  to display currency dimension data in report .

    Eg: 

    Currency Name                    DeveloperNetAmountUSD

    UsDollar  

    Pound

    Riyal

    Rupee

    Grand Total  : $89.08 

    In the above  manner i should get the o/p . For each individual currencies it should not display measure value  . But for grand total it should display the grand total . To achieve this i wrote a scope statement  as below .

    scope(EXCEPT([Currency Code].[Currency].Children, [Currency Code].[Currency].[All]),
    {
    [Measures].[Developer Net Amount USD]
    });                    
    this='';                    
    end scope;
    
    scope([Currency Code].[Currency].[All],
    {
    [Measures].[Developer Net Amount USD]
    });                    
    this=sum([Currency Code].[Currency].[All],[Measures].[Developer Net Amount USD]);                    
    end scope;

    By the above query , i am not getting the grand total  value  . I am only getting value for dimension members to empty  as specified above.

    The O/p i am getting as below  :

    Currency Name                    DeveloperNetAmountUSD

    UsDollar  

    Pound

    Riyal

    Rupee

    Grand Total  :

    Could any one please get me out this  . Thanks in advance .

    Thursday, October 25, 2012 7:43 PM

All replies

  • Waiting for  your valuable suggestions .Fast response would be greatly appreciated . Its very urgent requirement of our project  .
    Friday, October 26, 2012 12:19 AM
  • You need to FREEZE calculations on the ALL member before setting the values of all children. See if the script below works for you.

    SCOPE(
    	[Measures].[Developer Net Amount USD]
    );
    	SCOPE(
    		[Currency Code].[Currency].[All]
    	);
    		FREEZE;
    	END SCOPE;
    	SCOPE(
    		[Currency Code].[Currency].[Currency].Members
    	);
    		THIS = NULL;
    	END SCOPE;
    END SCOPE;
    

    HTH, Martin

    http://martinmason.wordpress.com

    Friday, October 26, 2012 1:29 AM
  • Thanks your very  much for your  valuable  response . Martin by using the above scope statement my problem has solve partially . 

    By this  scope statement i am getting desired o/p as required above . But it is showing effect on other dimensions when dragged on to report .

    Eg: Suppose if i dragged Currency Dimension and Payout Status Dimension on to report (Both are related to DeveloperNetAmountUSD  Measure Group) .

    The o/p is below in the screen shot . 

    Even though it is having data for USD currency it is not showing any data because  we used SCOPE statement for currency members .It is only showing the grand total for all currencies and  Payout Status . But that should not happen .

    My requirement in the first post , Should only occur when i drag only Currency dimension and NetAmountUSD measure . But that scope should not fire when i used multiple dimensions along with Currency Dimension With NetAmountUSD measure .

    In this scenario can you suggest me way out of this  .

    Note : I have changed the "THIS= NULL ;" in  the above scope statement to "THIS ='' ;" as to display empty data for all currencies in EXCEL.

    Friday, October 26, 2012 6:31 AM
  • Wouldn't recommend you change the THIS=NULL statement to THIS=''. When you do so, sparse cells that would not normally have data, will now have data and you'll get a whole lot more cells back than you really intended to get.

    Not sure what's going on without seeing you're entire solution. Placement of calculations within the Calculations tab of the cube editor are important. If the Freeze statement proceeds other statements that manipulate the Developer Net Amount USD measure, those statements will now not be applied. Therefore, to resolve the issue you may try moving the modifications suggested above so that they execute later on in the Calculations script.

    HTH, Martin


    http://martinmason.wordpress.com

    Friday, October 26, 2012 12:57 PM
  • Thanks martin  for  your reply. The reason i put "THIS =''  " ; because when i select payout Status and currency dimensions on to report ,it is only showing data for Payout status dimension  members  .i.e. it is displaying cumulative total of all currencies of each payout status member . But i want display the data for currencies under each payout  status member , Not  the cumulative total of all the currencies under each payout status member .Please  find the below screen shot for this . This is the result of keeping "THIS =NULL;" 

     But even though i keep "THIS='' ;" i am not getting the data for currencies under each payout status dimension  , because i am assigning currency members to NULL OR '' (EMPTY) as below 

    SCOPE(
    		[Currency Code].[Currency].[Currency].Members
    	);
    		THIS = NULL;

    Hope i made you clear on this  . 

    Note : I don't have any members with scope statements in cube above or below this script  . This is only one scope statements in my cube  .

    Could please guide me in this scenario .   I want to run the scope statement only when Currency and NetAmountUSD is dragged ,but not when it has multiple combinations with NetAmountUSD  . i.e. when user drags Currency and PayoutStatus along with NetAmountUSD .
    Friday, October 26, 2012 6:10 PM