Hi Experts,

I have a calculated measure "On Promo", It will display as "True" or "False" while browsing the cube.

Below is the calculation we have used for "On Promo".

IIf( CoalesceEmpty(Measures.[Promo Count],0) > 0, 'True', 'False')

When I browse the measure in cube, it displays "True" in the Total or Grant Total Field.

My requirement is, I need to display as "NA" in the total or grand totals for the measure in the cube.

Please help me in implementing this.

Thanks in advance...

Re

Hi Siva, this is straightforward for Grand Total:

scope ( root() );
[measures].[On Promo]='N/A';
end scope;

It's a bit trickier with totals, though. Technically, any tuple with the 'All' member from any hierarchy can be considered a 'Total', and when slicing the cube, you usually have some 'All's in the coordinates for any cell displayed. You'll need to formulate how you define the Total that needs your measure redefined as N/A.

Hi Alexei,

Thanks very much for your reply...

I have added the below code into SSAS, but its not working as expected.  I want Total column to display "NA" for this measure.

CREATE MEMBER CURRENTCUBE.[Measures].[On Promo]
 AS IIf( CoalesceEmpty(Measures.[Promo Count],0) > 0, 'True', 'False'), 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Promotions'  ;  

scope ( root() );
[Measures].[On Promo]='N/A';
end scope;

Regards,

Hi Siva, why would you expect the code introduced as 'for Grand Total' to do anything to the 'Total column'? It only overrides the value for the Grand Total Field you mentioned in the original post.
Please note that what you have just posted in this comment is a calculated measure and a scoped statement - neither define any columns. Please try to understand the question I asked earlier about what exactly you call Total and answer it. This clarification is necessary to help you further with the Total.

Note also that CoalesceEmpty function call seems redundant. All it does is replaces Null with 0 on the left side of the comparison, and in SSAS '0>0' calculates the same as 'Null>0'.

Hi Siva_SSK,

According to your description, you want to have a measure show "N/A" on totals or grand totals level. Right?

For this kind of requirement, you can only use SCOPE statement to achieve it. In this scenario, I suggest you make the Measure on the outer SCOPE statement, and specify the different levels in nested SCOPEs.

scope ( [Measure].[On Promo] );
   scope ( level expression );
      this='N/A';
   end scope;
   scope ( level expression );
      this='N/A';
   end scope; 
   ...  
end scope; 

Regards,

Thank You Alex...

What I mean is the default Sub total and Grand Total which SSAS shows data needs to be customized.

Hi Simon,

Thanks very much for your Reply !!!

Yes, you are right I want to  show "N/A" on totals or grand totals level. 

Can you please explain what you mean in "Level Expression" in the below script.

  scope ( level expression );

Regards,

Let's try an example. Say you have sales value by product and date. A simple query for sales by years might return something like:

Year | Sales
2014 | 100
2015 | 200
Total | 300

There's just one grand total in the cube, 300. So is the 100 sales for year 2014 a 'total' value? Yes it is if you remember we also have products in the cube: it is a total of all product sales for 2014! If we forget the cube and only look at the table, though, 100 is not a total.

Hopefully this will help you understand why it is crucial that you precisely formulate your meaning of 'total' in your request.

Hi Alexei,

Thanks for spending time in helping me..

Please find the below simple example 

We have 3 products A,B and C for which On Promo measure is True,False and True.

When I view the measure in the cube as below, I want GrandTotal to show "NA".

Product	On Promo
Prod A	TRUE
Prod B	FALSE
Prod C	TRUE
Grand Total	NA

Thanks,

Hi Siva, so it appears that you want your measure to show 'N/A' when not sliced by products, whatever the contents of other dimensions. For this you'll only need one scope:

scope ( [Products].[Products].[.All] );
[Measures].[On Promo]='N/A';
end scope;

Note that with this you won't need a dedicated root() scope, as the grand total falls within this products scope.

Hi Siva, so it appears that you want your measure to show 'N/A' when not sliced by products, whatever the contents of other dimensions. For this you'll only need one scope:

scope ( [Products].[Products].[.All] );
[Measures].[On Promo]='N/A';
end scope;

Note that with this you won't need a dedicated root() scope, as the grand total falls within this products scope.

• Marked as answer by Simon_HouMicrosoft contingent staff, Moderator Monday, August 10, 2015 11:27 AM
• Unmarked as answer by Siva_SSK Monday, August 10, 2015 5:29 PM

Hi Alex,

I have used your code there is no changes, I have tried the below script still its not working. 

Please let me know is there any corrections.

CREATE MEMBER CURRENTCUBE.[Measures].[On Promo]
 AS IIf( CoalesceEmpty(Measures.[Promo Count],0) > 0, 'True', 'False'), 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Promotions';   

SCOPE ( [Measures].[On Promo] ,
    [Dim Prod].[Product Hierarchy].[All]
        );                                                                        
THIS = (null);  

Thanks,

Hi Siva,

What exactly do you mean by "there is no changes"? After adding the scoped assignment I suggested to cube's code, deploying the cube, and creating a report with products on rows and the 'On Promo' measure on columns, you should have seen output as in your sample screenshot.

Your scope statement covers the same cells (where the measure is 'On Promo' and product hierarchy is the All member), but assigns NULL value to these cells. This will cause them to be suppressed by the usual NON EMPTY that a query contains, effectively removing the total when slicing by products and potentially returning empty resultset when not.

Hi Alex,

I have tried the same steps as you mentioned above.

our requirement is grand total should not show any values, but after implementing the scope as you suggested still the grand total is displaying values.

I have used the code in the Calculation tab in SSAS.

Thanks,

At this point, barring typos and other scoped assignments later in the Calculations, I'd say that the issue is probably with the hierarchy chosen for the scope statement, [Dim Prod].[Product Hierarchy].[All] vs the queries you issue against the cube.

For the code you posted last, the below query should get you a list of TRUEs and FALSEs for products and notably a NULL for the All member of the hierarchy.

select [Measures].[On Promo] on 0,
[Dim Prod].[Product Hierarchy].members on 1
from <your cube name here>

If in your scope you do THIS='NA' instead of (null), then the query should display 'NA' for the All member. At least it duly does in my test environment...

Hi Alex,

I have used the below code in my cube, Still the grand total is displaying value.

SCOPE ( [Measures].[On Promo] ,
    [Dim Prod].[Product Hierarchy].[All]
        );                                                                        
THIS = 'NA';   

Thanks,

Hi Siva, do you have the "end scope;" closing line in your cube script? If not, it's the typo that is ruining it all!

Alex,

I have tried with end scope; still the grand total display value.

SCOPE ( [Measures].[On Promo] ,
    [Dim Prod].[Product Hierarchy].[All]
        );                                                                        
THIS = 'NA';    
end scope;

Well, this is quite unusual. Are you saying that with this scoped assignment, not overridden in the calculations by subsequent assignments, the query 

select [Measures].[On Promo] on 0,
non empty [Dim Prod].[Product Hierarchy].members on 1
from <your cube name here>

shows either 'True' or 'False' for the All member (we can use non empty because All is assigned 'NA' and not NULL)?

Siva, matters will not be helped by repeating your requirement that has been stated already, which unfortunately is the only content of your latest comment.

I'm unable to help you further unless you either run the suggested test query to see what may be wrong with the scoped assignment in your environment, or alternatively provide any new information showing why a straightforward and tested scoped assignment fails in your case.

Alex,

I have executed the below query, the All is displaying a value as "TRUE".

select [Measures].[On Promo] on 0,
non empty [Dim Prod].[Product Hierarchy].members on 1
from <Cube Name>

Thanks,

Siva, if this result came in from a cube that had the scope above deployed, it is totally strange. Given the sensitivity of your cube, I suggest you study the approach with the 'AdventureWorksDW2012Multidimensional-EE' one. I chose the [Gross Profit] calculated measure and two hierarchies, [Product].[Product Categories] and [Product].[Product Model Lines].

A straightforward pivot of gross profit by categories looks like this:

Next the scoped assignment gets added to the very end of the script as follows (the purple line denotes where the original MS script ended)

With this assignment saved and deployed, the original pivot changes to

If we slice by another hierarchy, the value depends on whether a single category applies to the cell

I'm still at a loss what's going wrong in your cube, but am hopeful that you can first replicate the solution for AdverntureWorks and next transfer it to your cube.

• Proposed as answer by Ed Price - MSFTMicrosoft employee, Owner Saturday, August 15, 2015 12:50 AM

Alex,

Thanks very much for putting your effort in helping me...

As you said there might be a problem with the way that my cube has been built. But I could not find where the issue is.

I have tried with a measure which is Integer data type, still the totals are displaying values for the measure.

Re