DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

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?

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

|||

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])

|||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!!!
|||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.
|||

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].&

|||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!
|||

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];

|||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!
|||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."
|||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!
|||"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".|||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!
|||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!
|||

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)

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?

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

|||

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])

|||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!!!
|||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.
|||

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].&

|||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!
|||

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];

|||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!
|||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."
|||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!
|||"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".|||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!
|||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!
|||

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];

sql

distinct count with multiple partitions?

We analyze much of our data using distinct measures. Some of the underlying sources - i.e. daily page views are heavy and have millions of rows a day. Because of this I've created daily partitions to only process the incremental data that's arrived. However, I'm curious - how does distinct count perform when it needs to rollup over multiple partitions?

IE Say it's Dec 25th, and I have 25 unique partitions for each day of December thus far. Internally, how do the distinct measures correctly accumulate the unique instances of my measure? Are there any significant performance concerns to be aware of?Distinct Count measure will work fine with multiple partitions. AS keeps the distinct count values inside the partitions, and therefore it can correctly aggregate across them. Partition per day should be OK too.|||Arjun: Make sure you are on at least SQL 2005 SP1 QFE rollup, preferably SP2 CTP2. There were a couple of bugs fixed for distinct count measures over multiple partitions.|||Good to know, thanks! Jeff, do you have any details on the nature of the issues with distinct counts over multiple partitions?

Distinct Count Measures Spanning Partitions

We have a large cube partitioned now by week, whereas before it was one big
dumb partition. The problem is I don't know how to get DISTINCT COUNT
measures to behave properly. If I create a named set and do a SUM or an
AGGREGATE function of all the partitions in that set, it literally adds the
distinct counts from the partitions instead of doing a distinct count for al
l
the partitions in the set.
Is this even possible?Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
measures, whether the cube is partitioned or not. So it's not clear why
partitioning the cube would worsen problems with distinct count measures
- can you give a specific example of what happened?
http://groups-beta.google.com/group...erver.olap/msg/
5b7f7bedfe8d147e[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "Sean Boon [MS]"
Date: Thu, 9 Oct 2003 13:02:57 -0700
Subject: Re: Distinct Count
Hi Chet,
Distinct count will work with MDX's WHERE clause. What doesn't work is
attempting to aggregate across sets that consist of multiple members.
In
other words you can't use the AGGREGATE() function or a calculated
member
based on the AGGREGATE() function in the where clause. It's a
limitation of
the distinct count aggregate type. There is a way around this though and
it's outlined in the following whitepaper.
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnol.
.
Sean
Sean Boon
SQL Server BI Product Unit[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Sorry for not being more specific.
Calculated members based upon named sets that span what the cube is
partitioned by is what causes the problems.
Let's say there's a cube that is partitioned by month with this sample Fact
Data:
CustID TimeID
1 1
1 32
1 33
1 61
1 64
2 1
3 1
4 1
5 32
6 32
7 62
8 62
The TimeID's from 1-31 are Jan, 32-59 Feb, and 60+ are March, all of 1998.
I'm looking to get Distinct Count of Customers (CustID).
When querying the cube at the natural Year, Quarter, and Month levels, the
correct results are returned:
Year Distinct Count = 8
Quarter 1 Distinct Count = 8
Jan Distinct Count = 4
Feb Distinct Count = 3
Mar Distinct Count = 3
As you can see, drilling up and down with native members of dims in the cube
is just fine, regardless of partitioning.
However, we do a lot of dynamic rolling analysis. Various front ends will
generate a named set or calculated member, or both, for "the last N months."
Let's say I created a simple calculated member called "LastTwoMonths":
[Time].[1998 01] + [Time].[1998 02]
This returns 7, which is incorrect. It does the distinct count of each
partition and adds them together, which is not "correct" in the sense of tru
e
distinct counting. With the above data, the distinct count for those two
months should be 6.
I can't figure out how to get the distinct count to work properly when the
cube is partitioned by the same dimension that many calc members are based
upon.
Thanks for giving this matter the attention. I really appreciate it!
"Deepak Puri" wrote:

> Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
> measures, whether the cube is partitioned or not. So it's not clear why
> partitioning the cube would worsen problems with distinct count measures
> - can you give a specific example of what happened?
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b7f7bedfe8d147e
> Newsgroups: microsoft.public.sqlserver.olap
> From: "Sean Boon [MS]"
> Date: Thu, 9 Oct 2003 13:02:57 -0700
> Subject: Re: Distinct Count
> Hi Chet,
> Distinct count will work with MDX's WHERE clause. What doesn't work is
> attempting to aggregate across sets that consist of multiple members.
> In
> other words you can't use the AGGREGATE() function or a calculated
> member
> based on the AGGREGATE() function in the where clause. It's a
> limitation of
> the distinct count aggregate type. There is a way around this though and
> it's outlined in the following whitepaper.
> http://msdn.microsoft.com/libr-ary/...ry/e-n-us/dnol.
> ..
> Sean
> --
> Sean Boon
> SQL Server BI Product Unit
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Based on your detailed description, I would re-iterate that, with AS
2000, you won't be able to roll up Distinct Count measures dynamically
in a calculated member. However, this should work in AS 2005 (Yukon), if
that's an option.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Distinct count issue

Hello,
I have made a cube the following calculated member (with as parent member Measures):
COUNT(NONEMPTY( {[Parsed Logs Visitors].[Parsed Logs Visitors Dimension Table].[Parsed Logs Visitors Dimension Table]} *
{[Measures].[Parsed Logs Count]}))

Here I try to make a unique visitor count using the primary key in a non empty crossjoin with the fact table. When I query it with other dimension attributes it works fine, but it gives the total amount of unique visitors when I use (other) attributes of the same dimension.
How can I avoid that?

Thanks in advance,

Eyso Zanstra

You could try applying "existing" to the primary key attribute set:

COUNT(NONEMPTY( {Existing [Parsed Logs Visitors].[Parsed Logs Visitors Dimension Table].[Parsed Logs Visitors Dimension Table]} *
{[Measures].[Parsed Logs Count]}))

|||Yes, that did the trick.
Thank you!

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Displaying traffic lighting images using MDX query

Hi All,

I want to display traffic light images in jpivot table depending upon the measures . I can be able to display color in the jpivot table. How to display images?

Thanks in advance.

Hi Rajha,

you have to create a KPI in your cube.

Edit your cube in BIDS and then go to KPI's tab. Create a new KPI and complete the form with MDX script you need for Value, Goal, Status and Trend. There you can also set Status indicator e Trend indicator (traffic light, gauge, standard arrow and so on).

|||

Hi Francesco,

Thank you very much for your help. I'm a newbie to mdx. Actually I am using some open source database and cube designer which doesnt have KPI property. Can I manually add that code to my mdx query? If so, can u tell me where I can found related documents.

|||

Hi Rajha,

I really don't know if there's a way to obtain the same in MDX but I'm thinking not.

What you can easily do is to color cell depending on the value in it like this:

WITH MEMBER MEASURES.DEMO AS

'[Measures].[Internet Sales Amount]' ,

FORE_COLOR = 'IIF ( MEASURES.DEMO < [Measures].[Reseller Sales Amount] * 0.7,

RGB(0,255,0),

RGB(255,0,0)

)'

SELECT

{MEASURES.DEMO} ON COLUMNS,

NONEMPTYCROSSJOIN([Date].[Calendar].[Month].Members,[Department].[Departments].[Department Level 02].MEMBERS) ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR

At the moment I'm not able to find documentation about this but you can check on the web

Let me know if it's what you're looking for and please check my answer if you think if it was helpful anyway.

P.S. what product are you using that supports MDX?

Francesco

|||

Hi Francesco

Thanks for your reply. As I said in my first thread I can be able to show color depending upon the conditions but i cant bring images in the table.

I'm evaluating Pentaho which uses mdx for drill down reports.

Displaying inividual fact details in report body

I have a dimension for account and measures for time taken for editing etc. which provide a sum of minutes taken to edit all documents in each account.

The requirement is on clicking on the account link to be able to get details regarding each document in every account. for each document, I would need to display editor name, time taken, document type etc.

How do I display details that are not an aggregation along the rows but individual details of each document?

I would suggest using the drillthrough mdx command - more details @. BOL: http://msdn2.microsoft.com/en-us/library/ms145964.aspx