.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

SSAS Claculated Measure ignore empty rows issue

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server


I have a calculated measure [Revenue Varaiance] which is basically a variance of current year and lastyear Revenue.

Last year Reveue Measure name is [Pyr Revenue Actual] and for current year  is [Revenue Actual ]

Equation for my Revenue Varaiance is given below.

IIF([Measures].[Pyr Revenue Actual]=0,IIF([Measures].[Revenue Actual]=0,0,1),([Revenue Actual]-[Pyr Revenue Actual])/[Pyr Revenue Actual])

Out put:

View Complete Post

More Related Resource Links

MDX, count of rows with non empty measure



I have a fact table with 8 measures that aren't always all available. I'd like to aggregate them with AVG() so I tried to create a calculated member:
where I1 is the measure (aggregated by sum) and #Indexes is a count of rows automatically created by SSAS. But obviously the #Indexes includes all rows, also the ones with empty measures.

M1   M2 ...
null   5
10    6

I have to have 10 as AVG rollup of M1 in this small table and now I'm getting 5.

how could I solve this?

SSAS 2008 attritube hierarchy doesn't group records and repeat rows

We are having problems with the dimension attributes in lower level hierarchies not grouping under 1 single level. Here is the hierarchy: VP    College      Department         Departmental Course            Course Level               Course Number The top 3 levels are grouping correctly without duplicate rows and they don't have compoud keys. The lowest 3 levels are not grouped correctly and they have compounds keys because the attributes are not unique by themself. The result of the hierarchy looks like this: VP Academic Affairs    Business School of Management        International Business School            Mgm101                 Lower                       Course Number 123            Mgm101                Upper                

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

SSAS DB migation issue from SQL 2000 to SQL 2008

While  running migration wizard  to migrate sql 2000 Analysys service DB from 2000 to  2008  Validation step  failing with  datatype mismatch.Please assist me here

SSAS 2008 - I cannot seem to have semi-additive and normal additive measures in the same measure gro

I have a measure group with both conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty). The cube processes them quite happily, and I can view and analyse (slice) them fine, but when I analyse them both together, then the conventionally aggregated measures (aggregation type SUM) always dissapears in favour of the semi additive measures. This is not the case if I analyse conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) from different measure groups.Why does this happen, and is there a way to overcome this by pref not creating seperate fact sources for the conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) currently in the same fact source.

SSAS Data Member Issue


Hi all,

I am using SQL 2005 - SSAS- Calculations tab to create data member. I am using two numeric columns from the fact table to create a data member, after that I use calculation properties "associated measure group' to link with the fact table. Now it is showing in perspective tab at the bottom.

But for some reason it's NOT SHOWING IN THE FACT TABLE in  browser tab where I am viewing some resutls.

Please let me know what I did wrong? Or if I missed something?




SSAS - Measure sign flipping of FormatString


Hi all

I was wondering what the best way is to flip the sign of a measure's FormatString (not the value).

I need to flip the sign of my measures for display purposes only for certain scenarios (for example for the scenario Actual vs Plan). The calculation of the roll-up must take place using the "normal" unflipped sign.

I was thinking to do it in a similar way than the following currency flipping example


AS [Measures].[Fact Value],

FORMAT_STRING = “Currency”,


SCOPE([MEASURES].[Currency Test]);

SCOPE([Account].[Account Structure].&[2]);



SCOPE([Account].[Account Structure].&[4]);




Is there a better way to do it. Many thanks in advance


SSAS - MDX sent by excel returns empty set


Hi All

when I try to browse a cube using a PC (Parent-Child) account dimension, the query generated by Excel returns an empty set. Reason:

Excel uses the following expression:





empty Hierarchize({DrilldownLevel({[Date].[Fiscal].[All]})}) DIMENSION

Hide the empty rows on dispform.aspx using javascript


Guys, can I hide the empty rows on display form using javascript?

If you know anything, Help me.


SSAS for heavy network data to serve around 12-15 million rows per day


I am a fresher with the analytics.

Presently, working on a system which tracks the data from Network giants like Bing, Google, Yahoo, Facebook etc. depending upon the profile of customers using our system.

So we our tracking data of around 5-6 million rows in a day. Apart from that, dimensions like Search Keywords and all also contain data of around 50-60 million of rows. Although, daily addition will be low in dimension tables, but it will keep on increasing as customers will increase. Tracking data is expected to grow beyond 15 million.

To provide analytics, I must have minimum of 3 months of data in a cube. To provide trends, it should increase to around 1-4 years (in our future road map).

Presently, I am suffering with fatal issues while processing a cube, like, "The operation cannot be completed because the memory quota estimate xxxx exceeds the available system memory yyyy" - while processing dimensions

Please guide with some good thought process how to manage such a huge data and provide analytics over it. As well throw some light over the approach to follow for testing cubes and data loading packages of ssis.

Please provide the links of articles/videos/blogs & name of books to discuss and decide design the analytics for data-extensive systems. Any kind of reading mater

GridView empty rows.


I bind data to GridView from SqlDataSource. GridView have <19 rows. But i want that all 19 rows are written. If Rows in SqlDataSource are empty it's must be empty in GridView but must be there!

SSAS Excel Drill Thru : Number of Rows Returned don't match up


Hi, I have created an excel report which connects to my cube and have created drill thru action. When I double click on data cell in excel, what I would expect is to give details for that cell. For example, I have a cell in excel that represents 1000 Sales made. When I double click on it, it should only show me thousand rows, however for some reason it gives me more than 1000 rows. So basically the cell counts don't tie with drill thru row counts.....

Any idea on how I can rectify this?

Measure aggregation issue


Hi All,

I have a cube with quarter partitions and a measure group which is structured in DSV as it picks only latest record per member and stores in that measure by partition scheme. There are count and sum measures depending on this measure group. My cube is used for reports in SSRS.

When I am looking the reports, If the time frame is in one partition range the numbers look right and if the time frame is across multiple partitions, the numbers are wrong. It's over counting things coz, If a member is counted in one partion, I don't want that to be counted in other partion when the time frame is across multiple partitions.

Any help is greatly appreciated



Measure Group Shows Empty


I've searched this forum and read a few posts similar to mine, but they either weren't answered, or the answers didn't work.


I have an Analysis Services database with about 8 measure groups and about 25 dimensions.   We just released our new version into our production environment today, and we have one measure group that shows up completely empty.  All functionality worked in development, was tested in stage, but now this one measure group doesn't work.  I can check all connections to db, data source, dsv, to cube, to measure group, and everything is fine.  From within BIDS, I can right click a fact table and explore data, and data is there.  Data is also there in the fact table inside the database engine.  When I try and browse the cube, or query with MDX, just the one measure group shows all null.  Any thoughts?

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

Issue with Measure aaddition


HI All


We have an SSAS cube with a measure group. When I add a new measure to the measure group, it automatically creates a dimension for me in the list of dimensions that display in the lower keft hand corner. Any idea what’s wrong? Thanks in advance

With Excel pivot table client using SSAS cube, how to change the measures to rows?

I want the measures as rows instead of columns in Excel pivot table on a SSAS 2008 cube. How do I do that? Thanks.
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