.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 query to count non empty descendants

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

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?




View Complete Post

More Related Resource Links

Count Query Execution


I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use ?


We have the following query:   SELECT H.VEHICLE_ID,                 COUNT(ISNULL(H.HISTORY_ID, 0))            FROM VEHICLES V LEFT OUTER JOIN VEHICLE_HISTORY H              ON V.VEHICLE_ID = H.VEHICLE_ID           WHERE H.SPEED >= 65             AND CONVERT(DATE, H.GPS_TIME) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))        GROUP BY H.VEHICLE_ID;   Unfortunately, if there inst a record for a vehicle for the current day in VEHICLE_HISTORY, or there inst a record over 65 in VEHICLE_HISTORY, the query simply does not include the result. How can we include all results (i.e. return 0)? What we want is the number of rows returned to match the number of rows in the vehicles table. I thought doing the LEFT OUTER JOIN would take care of this, and return a result for all vehicles.   Thanks.

WPF DataGrid does not show new row on empty linq query

I'm using Linq to Entities and bind a linq query to the WPF 4 DataGrid datacontext.  The linq query look something like this:                  var query = from s in this.db.Customers                         orderby s.Name                         select s;             private ContextEntities db = new ContextEntities();             private BindingListCollectionView View;             this.View = ((BindingListCollectionView)CollectionViewSource.GetDefaultView(query));             this.DataContext = this.View; If my Customers table does not have any record, the DataGrid does not show any row for user to add.  I have CanUserAddRows set to true.  I found another post that address this issue but it say to create a new entity type.  http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/cdc0fb6f-2335-42e2-98d7-4bd73ffffc8c My project

tfs mdx query(cumulative count)

I need cumulative count of bug,risk work item per day first, i made this query WITH MEMBER [Measures].[Date Value] AS     [Date].[Date].CURRENTMEMBER.MEMBER_VALUE   MEMBER [Measures].[Active Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Active], [Measures].[Cumulative Count])   MEMBER [Measures].[Resolved Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Resolved], [Measures].[Cumulative Count])   MEMBER [Measures].[Closed Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Closed], [Measures].[Cumulative Count])   SELECT {     [Measures].[Date Value],     [Measures].[Active Cumulative Count],     [Measures].[Resolved Cumulative Count],     [Measures].[Closed Cumulative Count] } ON COLUMNS, (     Filter(         [Date].[Date].[Date],         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE >= CDate(@StartDateParam) AND         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE <= CDate(@EndDateParam)     ) ) ON ROWS FROM [Team System] WHERE (     {[Work Item].[System_WorkItemType].&[Bug],[Work Item].[System_WorkIt

Problem with SELECT COUNT query and parameters

Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

how to count transactions done on your database by T-SQL query?


Dear All,

I have multiple database and I want to know how many transaction that are happening per database using T-SQL, can anyone help me?





Karim Alaa

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?

SSRS MDX Cross Join Query - Cross join Non Empty Set with Empty Set



I'm pretty new to MDX so please go gentle :)

I'm writing a SSRS report which will show employees (in a hierachy) and the number of different events which occured for them each day over a week.

The problem i'm having is that i want to show ALL employees in the hierachy regardless of whether they have any events but also ONLY show those event columns for events which have happened that day.

As i understand it so far SSRS does cross joins to flatten out the data, so i'm guessing i need to crossjoin the Employees set (including empties) against the events set ensuring NON Empty is used, however i'm struggling to get this to work

Heres what i've trued so far

NON EMPTY{[Measures].[Event Count] } ON COLUMNS, 
{DESCENDANTS([Employee].[Reports To].[Regional Director].ALLMEMBERS)} ON ROWS 
FROM [DataWarehouse]


Event                  Count
Employee1            28
Employee2            28
Employee3            (null)
Employee4            28

query - generate month, year (count) for blog archive


hi guys.

from my sql blogs table, I want to generate blog archive section on my master page. like;

january, 2010 (20)

fabruary, 2010 (35)

I cannot come up with a linq to sql query on this. do you have any idea how it can be done ?

Problem to write a query..using COUNT()...


I am facing problem to get desired output..

i m using query as written below.........

SELECT  studentprofile.RollNo, AttendanceMaster.ScholarID, studentprofile.StudentName 

COUNT(AttendanceMaster.AttendanceStatus) AS Expr1

FROM         AttendanceMaster AS AttendanceMaster INNER JOIN
                  studentprofile AS studentprofile ON                    AttendanceMaster.ScholarID=studentprofile.ScholarID


(studentprofile.Section = 'A')

AND (studentprofile.Class = 'Nursery')

AND (studentprofile.SchoolCategory = 'PRE SCHOOL')

AND (studentprofile.SchoolType = 'XYZ School')

AND (AttendanceMaster.AttendanceStatus = 'Present')

AND (AttendanceMaster.Date BETWEEN '11/1/2010 12:00:00 AM' AND '11/30/2010 12:00:00 AM')
GROUP BY studentprofile.RollNo,AttendanceMaster.ScholarID, studentprofile.StudentName

this query gives output propery for those students who comes(AttendanceStatus='Present') atleast ne day........but unable to show the the re

caml query - group by column and get count of records



I want to group by on column name and get the count of records returned.



SQL query nested use of count and join


Here is the problem/issue:  Table A can have multiple record associations to Table B.  Table B has a 1:1 record relationship with Table C.  Table C can have multiple record associations to Table D.  I want to find all the records in Table A that have exactly one entry in Table B, which have only one entry in Table C (which is always true as noted above), which have 0 or 1 entry in Table D.

Here is what I have now:

use Database
Select *
From TableA as TA
Where TA.MedRecID In
    select TableB.MedRecID
    from TableB,TableC
    where TableB.AccountSourceIdentifier In
        select TableC.IncidentSourceIdentifier
        from TableC, TableD
        where TableC.IncidentID In
            select TC.IncidentID
            from TableC as TC
                left outer join TableD as TD

SQL query to get count of fields



I have a table like this




Third Approval



MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

How to check for null/empty strings on dynamic objects?


What is the proper way to check for null or empty strings on dynamic objects? This is the only solution I've found so far:

if (admin.PasswordHash.GetType() == typeof(DBNull) || admin.PasswordHash == string.Empty)

If the field is null, the object returned is DBNull.Value, but if the field is empty string, it's return as a System.String object. This makes it impossible to do regular "admin.PasswordHash == DBNull.Value" check, as that will fail whenever the field has been set to empty string. Is my example the right (and only) way to go?

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