DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

freadomfee

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--------------

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--------------

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--------------

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

---------------------------

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total ��11�� is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be 59�� for all professors). How could I get the right value to be shown in Grand Total

freadomfee

Any help would be appreciated as I'm very new to the MDX.

Deepak Puri

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

freadomfee

Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!

freadomfee

Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member

Thank You in advance.

Deepak Puri

Were you testing the KPI with the KPI Browser in BIDS If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

freadomfee

I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression) The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!

Deepak Puri

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

freadomfee

I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!

freadomfee

By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ��Amnt_Publications�� uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."

freadomfee

I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!

Deepak Puri

"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".

freadomfee

Thank You for answering. But could You be more specific (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!

freadomfee

OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!

Deepak Puri

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Re: DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)