.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

MDX, count of rows with non empty measure

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


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?

View Complete Post

More Related Resource Links

SSAS Claculated Measure ignore empty rows issue



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:

count the num of rows in dataset



Anyone can tell me which part when wrong if my code is wrong ?

I'm using VS2010 have a dataset. Here is my code.

Dim temp_sec As Data.DataSet

temp_sec = ac.getTempTime("741", "10 July 2010")

suppose to have 13 rows yet i got my msgbox is 0 rows Undecided

Count of fact rows not being displayed correctly

I have the most generic measure - count of rows from the fact table. When I query the underlying fact table, example...select count(Primary_key) , I get accurate results. Also, I see my partition has exact same count for the rows. But, when I process the cube and view the same measure in the browser it shows less than the actual count . I never had this problem before. What could I be missing? I would appreciate your suggestions.   Rok

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

Measure - New Customer Count

I need to create a measure which counts the number of new customers for each time period. My fact table contains the customer number and its easy to create a distinct count of customers per month/week/year. I'm thinking I need to obtain the first order date for the given customer and compare if the select period is within the time frame, then include or exclude.  

Count how many rows are returned from a stored procedure

Hi,   I have written a stored procedure for my database which takes two varchar parameters and returns lots of rows of data. This data will be passed back to my .NET application with a reader for parsing.   Imagine this scenario of calling a stored procedure:   MyDatabase.dbo.sp_MyStoredProcedure 'String 1', 'String 2'   Let's say that this command returns 12,434 rows of data when executed in SQL Server Management Studio Express containing the data of 5 joined tables which contains 2 or 3 unions (depending on the input of 'String 2') and the data within each union is correctly sorted.   How do I get a row count from the execution of the above stored procedure command? I need to pass this back to my application for the progress bar to function correctly.   I don't want to replicate the function and modify it for counting rows because the stored procedure consists of approximately 150 lines of SQL code.   Thanks in advance.   Sean

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"  

MDX query to count non empty descendants


I have a calculated measure with the following definition,

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] /3

Instead of hard-coding the division value, I want to count how many non-empty descendants [Admit Date].[Date Hierarchy].[Quarter] has and divide the measure by that number. I wrote some Query like this, but didn't give me any result.

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] / COUNT(NONEMPTY(DESCENDANTS([Admit Date].[Date Hierarchy].[Quarter])))

How should I correct this query?




Count of Rows - Records between month - T-SQL 2005


Hi Below is my Scenario.

Record                       Start Date                           END Date

  1                              01/02/2008                          04/05/2009

  2                             01/25/2008                           06/19/2009

  3                              02/02/2008                   

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.


Count gives me the same answer regardless of the number of rows


I always get a result of zero from the following code sequence even as the row count changes and I just do not see what the error in my syntax is.




supercount As Integer

supercount = 0


Dim sqlkit As String

sqlkit =

new row and rows.count


Good morning to all

I am creating a simple demo for myself,in that i am intend to show top 3 rows from database,afterbinding with the help of DATASET,i am adding nul rows in dt and showing it into hte gridview on the base of no intered in textbox1,

after that now i want the total no of rows added in gridview rows.count ,suppose i added 3 null coloum so the total no of coloum now i have will be 6,but it is showing only three.


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns=false>
     <Columns >             
    <asp:TemplateField >
    <table >
    <tr> <td  style="font-weight: bold">

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!

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

GridView1.Rows.Count multiply by ten?


 I am displaying the below:

C# code behind

CCount.Text = GridView1.Rows.Count.ToString();


I need to multiply the GridView1 count by 10 and display in C10Count.Text

C10Count.Text = Convert.ToString( GridView1.Rows.Count  * 10 )


What is the proper syntax please?

Thank you



Same measure group for count and distinct count in SSAS


Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.



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