.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Display NULL value for all the dimension for a particular measure at the Aggregate(All) Level.

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Hello All,

Please help me on this:

I have to display a measure value for all the dimensions as NULL at the aggregate/top/All level. For all the other dimensional levels, this measure value should be displayed. I used the following MDX, but it will hide the measure value for all the other dimensions(because while drilling through the other dimension, this condition will be satisfied by default):

SCOPE([abc].[abc].[abc].members, [MEASURES].[measure1]);      

 this = NULL;      




View Complete Post

More Related Resource Links

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Need to display Blank Member as a NULL in one of the column Level


 Hi ,

  1. select NON EMPTY {[Measures].[Balance], [Measures].[Peso Equiv]} ON COLUMNS,
    from [depositlib_22]
    where [REPORT DATE].[All Report Date]
    |                                                   | Balance     | Peso Equiv     |
    | All Particulars |        |                  |                | 8,286,706,398.76 | 373,066,873,125.05 |
    |         | RESIDENTS   |                  |                | 8,279,166,150.24 | 373,059,101,772.19 |
    |         |        | GOVERNMENT            |                |      68.38 |       68.38 |
    |         |        |                  | NATIONAL

How to display a NULL value (of SQL DateType) as 'N/A' ?


hi all

My SQL field 'modified' is a DateTime type and if the value is NULL in SQL I want to display a 'n/a' string.
I am using FormView templates to display the value.

If I use this statement -> ISNULL(Items.modified,'') AS Modified; I get '1/1/1900 12:00:00 AM' in return.

If I use this statement -> ISNULL(Items.modified,'n/a') AS Modified; I get 'Conversion failed when converting datetime from character string.'

I could go ahead and change my class from "public DateTime Modified" to "public String Modified" but I want the field to represent its real value which is DateTime.

I am not inserting the date value back into the table since the sprocs all take care of that for me. I just want to display the 'n/a' string.

So, is there any way to display a SQL DateTime type as a string when its value is NULL inside the table?

ps: I would prefer not to change any code inside the FormView Templates if possible :)

Thank you!


Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

Display Null if Column is empty

is it possible to display Nullif my sql data is null while binding it with gridview in asp.net<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" Height="185px" Width="244px"> <RowStyle BackColor="#EEEEEE" ForeColor="Black" /> <FooterStyle BackColor="#CCCCCC" ForeColor="Black" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="Gainsboro" /> </asp:GridView> string command="select * from user1"; DataSet ds1 = new DataSet(); ds1 = ob.getall(command); GridView1.DataSource = ds1; GridView1.DataBind(); for example like this

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

if NULL in database table - display empty string?


I have a table in an Sql Server Database from which I want to fetch data to a form. 

[Headline] [varchar](max) NULL,
[Text] [varchar](max) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

[Headline] [varchar](max) NULL,

[Text] [varchar](max) NULL,

I want to check both Headline and Text to see if they are null, and if so display an empty string each for them. I've looked at some tutorials but don't yet understand really how to use it. DBNull? DataRow.IsNull? Something else? Can I check both Headline and Text at the same time or do I need do check them separately?

RSS - Is there a way to display all new content across several subsites on a top level home page ?


I have an Intranet site that contains several departmental intranet sites.

Is it possible to have a web part on the homepage of the top level intranet that displays all recently added content to the various sub sites ?



Please Help with Defining Calculated Measure based on Dimension Members



The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:



case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Displaying measure names on rows under the dimension attributes in SSRS report.


I'm very new to Reporting and this is my first assignment. I have to create a report out of Cube. I did pulled out the necessary Dimensions and Measures in the data set. However, I do have a problem generating the structure of the report. I have two dimensions and two measures. DimRegionName, DimDate, Measures.NewCustomers and Measures.RevisitMembers. The Year out of DimDate should be in columns and RegionName on Rows. On the top, the requirement is to display the names of the measures in rows under every RegionName and their values in the data field. Something as below - I did try to produce this using Matrix report but not sure how to display the measures on rows under the RegionName.  


                                 Year 2010        Year 2009

Bay Area

  New Customers              100                  150

  Revisit Members             50                     30

South Ca

Warning: Null value is eliminated by an aggregate or other SET operation.



When i run a Pivot query, i receive this error. It looks not harmfull, because query executed successfully and the results are partially ok. But for some columns, i get NULL values surprisingly. Because i have carefully checked all data in the source for the null values. All NULL values converted to ZERO, so there is no reeason for such error.

I do not understand why this error for?

Processing orphan dimension records with NULL parent foreign keys

<head> <style type="text/css"> .style1 { font-family: Arial, Helvetica, sans-serif; } .style2 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0px; } .style3 { font-size: x-small; } .style4 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0; } .style5 { color: #0000FF; } .style6 { font-family: "Courier New", Courier, monospace; } .style7 { color: #0066CC; font-weight: bold; font-style: italic; } </style> </head>

This link Handling Data Integrity Issues in Analysis Services 2005 is a classic paper on handling data integrity. The snowflaked data structure decribed in it is:

Product Class (dimension)  <--  Product (dimension)  <--  Sales (fact) where some Products do not have a Product Class

My situation is similar, but a bit more complex.   I don't actually have referential integrity problems . . . orphans are valid.

I have two fact tables TableA and TableB. They are related in a [0-or-1] to a [0-or-many] structure.

  • That is, each TableA record may have zero, one, or many TableB records associated with

How do display null rows returned from my table valued function?

DECLARE @techNumLoop VARCHAR(25)
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL

SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
INSERT @myTemp
FROM ufn_Find_Some_Stuff(@techNumLoop)


how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Compare the values of a measure with reference to a time dimension


Hi experts, i am a novice on Analysis Services and i have this necessity:

I want to create a calculated member that compare the values of a dimension with reference to a time dimension.

Example :

Sales Year 2009   Sales year 2010   %Var

I have found this model of calc below but i not know well Analysis Services.

How can i implement this function? My time dimension named [Tempo].[Anno].[Anno] and my measure named [Measures].[Importo Neg]


// Test for current coordinate being on (All) member.



[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend