Visualising MDX with colored CM in OWC

[Whitebear]

Hello. I use SSAS2005 and have problems with coloring my cells in grid. Let's
consider simple MDX query to Adventure works:

with member [Measures].[Red Total Product Cost] as
'[Measures].[Total Product Cost]' , BACK_COLOR='Rgb(255,0,0)'
Select [Customer].[Customer Geography].Children
on Columns ,
[Ship Date].[Calendar Year].Children on Rows
from [Adventure Works]
where ([Sales Channel].[Sales Channel].&[Internet],
[Measures].[Red Total Product Cost])
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, BACK_COLOR

when I execute this query in Management Studio, I get grid with each cell
colored with red. When I execute this query in OWC, I get error "The query
could not be processed: The data provider didn't supply any further error
information". Is it my mistake or misunderstanding, or I should wait for OWC
12?

Thank you

 

[Jeff Moore]

Try:
'[Measures].[F Minutes]',back_color = 'iif(Measures.currentmember>=60,
iif(Measures.currentmember>=125,
4129023,4194303),iif(Measures.currentmember>=125,
4129023,16777215))',fore_color = 'iif(Measures.currentmember>=60,
iif(Measures.currentmember>=125, 0,0),iif(Measures.currentmember>=125, 0,0))'

This MDX comes from the BI Portal Addin which generates the MDX for you.

Jeff

 

[Whitebear]

What should I try? You give me the same syntax I use - it seems to be
unsupported in OWC or I missed something... OWC works only when I remove
back_color block from CM declaration. Can anybody tell me basing on my AW
sample how to change syntax to make OWC work? Or it's current version does
not support this MDX syntax?

Try:
'[Measures].[F Minutes]',back_color = 'iif(Measures.currentmember>=60,
iif(Measures.currentmember>=125,
4129023,4194303),iif(Measures.currentmember>=125,
4129023,16777215))',fore_color = 'iif(Measures.currentmember>=60,
iif(Measures.currentmember>=125, 0,0),iif(Measures.currentmember>=125, 0,0))'

This MDX comes from the BI Portal Addin which generates the MDX for you.

Jeff

Hello. I use SSAS2005 and have problems with coloring my cells in grid. Let's
consider simple MDX query to Adventure works:

with member [Measures].[Red Total Product Cost] as
'[Measures].[Total Product Cost]' , BACK_COLOR='Rgb(255,0,0)'
Select [Customer].[Customer Geography].Children
on Columns ,
[Ship Date].[Calendar Year].Children on Rows
from [Adventure Works]
where ([Sales Channel].[Sales Channel].&[Internet],
[Measures].[Red Total Product Cost])
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, BACK_COLOR

when I execute this query in Management Studio, I get grid with each cell
colored with red. When I execute this query in OWC, I get error "The query
could not be processed: The data provider didn't supply any further error
information". Is it my mistake or misunderstanding, or I should wait for OWC
12?

Thank you

 

[Peter Huang [MSFT]]

Hi,

Currently I am looking for somebody who could help you on it. We will reply
here with more information as soon as possible.
If you have any more concerns on it, please feel free to post here.


Thanks for your understanding!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Whitebear]

Thank you for your answer. I found that calculated members declared in cube
at designtime are colored and formatted correctly. I think OWC to incorrectly
parse "WITH MEMBER" clause in MDX query. For example, I found that if I
declare CM like

WITH MEMBER Measures.MyMeasure as 'expression' and there are line breaks in
expression, Management Studio execute this query fine, while OWC raise
unknown error, but when I remove all line breaks to fit expression to a
single line OWC works fine too.

Anyway I will be waiting for your response.

 

[Wei-Dong XU [MSFT]]

Hi,

From this, I assume you are using SQL server 2005 as the OLAP data source
of OWC. Currently as I know, the OWC in Office 2003 can only work with SQL
server 2000 OLAP. For 2005, this cannot work as expected.

For using SQL 2005 OLAP data, the new way is to use the contronl
CellSetGrid introduced at this article:
http://www.sqlserveranalysisservices.com/cellsetgrid/CellSetGridIntro.htm

Have a great weekend!

Best Regards,
Wei-Dong XU
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Whitebear]

Thank your for your answer, I suspected something like this.
I found temporary solution - to declare members at cube design. Of course I
cannot always use it. Also I get suggestions to "create session members" in
connection before attaching it to PivotGrid, but for this time we
experiencing trouble working in this way (MDX in PivotTable does not see
declare session member).

As I understang, CellSetGrid is THIRD PARTY, BETA ASP.NET component that
supports SSAS'2005. I need smart frontend client to integrate it into my
applications without expences (except of Office'2003 license). I think
CellSetGrid is not suitable for my tasks ( Proclarity can be an
alternative, but at least this time using it leads to additional license
expenses and it have not large community and really good support like MSDN.
Can you give me a hope at least that there will be OWC12, based on old OWC,
or aquired by Microsoft Proclarity engine? I need to make principal decision
to use OWC 11 with temporary solutions or not.

 

[Wei-Dong XU [MSFT]]

Hi,

I understand your scenario. So I think you can download this feature pack
from Microsoft:
Feature Pack for Microsoft SQL Server 2005 - November 2005
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-
9b91-9e8b9d835cdc&displaylang=en

This feature pack provides a service called "Microsoft SQL Server 2000
PivotTable Services" for SQL server 2000 and 2005 OLAP scenario.

And a component "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB
Provider" will be of value for your client component.

For the future version of OWC 12 supporting the SQL server 2005 OLAP, I can
really say nothing because it is still in beta and anything may be changed
soon.

Besides, there is an very nice article from codeproject.com introducing the
charting in ASP.net which should be of very help for your development:
ASP.NET Server-Side Charting with OWC11 By ravie gopal
http://www.codeproject.com/aspnet/owc11article.asp

In addition, this MSDN community is very active and its UI interface is
based on ASP.net web page. You can enjoy a lot there:
http://forums.microsoft.com/msdn

Have a nice day!

Best Regards,
Wei-Dong Xu
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
<https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469>

Others: <https://partner.microsoft.com/US/technicalsupport/supportoverview/>
If you are outside the United States, please visit our International
Support page:
<http://support.microsoft.com/default.aspx?scid=/international.aspx>.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.