Hello! I cannot find any information if I can visualize results of my MDX
queries in PivotTable of Excel'2007... OWC have such functionality, but since
it is now obsolette technology, I'm trying to replace it with Excel'2007, as
Microsoft recommends...

Thank you

Hello,

I am not sure what's result for runing MDX you want to see in the Excel.

Since you use the PivotTable to show the SSAS data, you could not modify
the dimension and measure group configuration.

Sincerely,

Wei Lu
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.

Hello!

OWC'11 have ability to visualize custom MDX:

myOWC.DataMember = <Cube name
myOWC.CommandText = <MDX Query
myOWC.Refresh

Some time ago I asked in corresponding newsgroup about plans on OWC. I've
got an answer, that it is obsolette technology, and in future versions of
Office I should use Excel Pivot Table instead, writing my application with
VS'2005 Office Tools. Now I'm trying to migrate to Pivot Table from OWC, and
I think I found this loss of functionality - I cannot run my custom MDX query
in Pivot Table (MDX property is read-only).
So I have an application that get MDX queries from storage, execute them
and vizualize in OWC. Now I want to make it using PivotTable instead of OWC.
But I can't execute MDX query (or maybe I don't know HOW TO). Anyway I want
to ask Microsoft's representative or any competent professional, how should I
develop solution, that stores definition of very custom presentations of cube
data and visualize them in Office 2007...

Thank you

Hello,

Did you ant to load MDX query dynamically in the application or just use
Pivot Table control with MDX query? If you may take following article to
see if it will help:

http://www.microsoft.com/msj/0999/mdx/mdx.aspx

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

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

Hello!
I have an application that uses OWC 11. Interface of an application is a
quite simple: user selects a "report" (i.e. my custom MDX Query), press "Run
report" button and OWC 11 PivotTable is populated with data. Then, user
slice-n-dice PivotTable as it wants. This application have some limitations,
related to OWC 11 PivotTable, that was designed BEFORE SSAS'2005:
-Cannot arrange dimension attributes and measures into folders
-MANY bugs with calculated members in MDX (SSAS'2005) query (do not display
color correctly, this is only one example)
-VERY slow execution
-et cetera

On my questions in Microsoft's newsgroups I received a simple answer: OWC 11
is an obsolette technology, that works with SSAS'2005 incorrectly, you should
develop your application with VS'2005 Office Tools and write Excel'2007
add-in. I'm trying to follow this recommendations, but I found that I cannot
execute my custom MDX query in Excel'2007. Yes, I can go another way: I can
design my custom presentation in Excel'2007 PivotTable (except of writing
MDX), and store ready Excel'2007 file in my "reports storage" (except of
storing MDX queries). But I'm facing with another problems in this
front-line. For example, you may read another post I created in this
newsgroups:

http://msdn.microsoft.com/newsgroups...b-0c3315213d7b

Shortly, all my "reports" use calculated members. If I add calculated
members to PivotTable with Office 2007 VBA, they have incorrect number
format. Also, I cannot add color and font expressions for my calculated
members (while in custom MDX I can). At last, this solution have excess
complexity: I have to add calculated members with help of VBA and than I have
to design Pivot in Excel.

Now I've found a workaround:
-I declare ALL calculated members on the server-side (in BI studio, while
designing of my AS Solution)
-Design pivot report in Excel'2007 PivotTable
-Store Excel'2007 workbook with pivot report in database

When user requests any report, application load Excel'2007 workbook with
this report from database storage, open report and refresh it.


This solution DO NOW SATISFIES me in long term:

-Declaring server-side calculated members means that I cannot distribute my
cube separately from application. If I have several cube installations, and I
release new version of my BI-application, or simply add a new report, I have
to include in at a new cube design or execute some MDX-statement once during
setup, that add new calculated member on every installation. Of course, it's
quite hard to maintain this system.
-If I design reports in Excel instead of writing custom MDX queries, MDX
queries produced by Excel are not as effective as I can write in MDX myself,
because PivotTable is UNIVERSAL product, while my reports are special and do
not use all functionality simultaneously.



Also I'm confused with a lack of OLAP clients for SSAS'2005, provided by
Microsoft. As I understand, there are only 3 ways to display multidimensional
data using Microsoft products, each with it's own disadvantages:
1. Excel'2007. All my problems are written above. It is very impressive, but
too raw product for BI needs. Besides, I cannot embed it's PivotTable in my
application and distribute it separate from Office'2007, as I could do with
OWC. I cannot write my application in Borland Delphi, for example - I have to
use VS'2005 office tools and work in Office'2007 interface. It is a really
great disadvantage, in some cases.
2. Reporting Services 2005. It's my opinion, that as far as this technology
is amazing for relation database reporting, as it is miscarried for
multidimension analysis. Flattening MD-data into relation dataset and then
re-pivotting it in report - it look very funny for beginners, not for
enterprise-scale application. Also, I can slice my data with parameters, but
how can I drillthrough hierarchies, replace attributes on axes, etc?
SSRS'2005 do not know anything about my UDM features - this is why reporting
services provide me with so poor functionality.
3. Proclarity software. Proclarity is acquired by Microsoft, as I
understand. Your support said me last year, that I will be able to download
it's application from my MSDN Universal downloads site at July'2006... It is
still not available today. I just cannot download trial version of it's
software... :(

I'm totally surprised, that from year 2005, when SSAS'2005 (that is really
impressive and feature-rich OLAP engine) was released, I have not convenient
and functional technology to deliver analytical data to end-user for today.

Thank you

P.S. The article you provided is about OWC. It is obsolette together with
OWC :(

I will be extremely thankful for any answer.
I have created a cube and if I create a pivot from it using all the
dimensions in page field , I am getting correct values.
I have a tsk which requires me to populate all the dimensions in List
views and then generate the report using VBA abd MDX query.
I am not getting that right.Could you pl point me to any sample or
write any sample MDX for the scenario.

Thanks a alot
Regards,
Shailesh

On Feb 25, 6:01 pm, Whitebear wrote:
Hello!
I have an application that uses OWC 11. Interface of an application is a
quite simple: user selects a "report" (i.e. my custom MDX Query), press "Run
report" button and OWC 11 PivotTable is populated with data. Then, user
slice-n-dice PivotTable as it wants. This application have some limitations,
related to OWC 11 PivotTable, that was designed BEFORE SSAS'2005:
-Cannot arrange dimension attributes and measures into folders
-MANY bugs with calculated members in MDX (SSAS'2005) query (do not display
color correctly, this is only one example)
-VERY slow execution
-et cetera

On my questions in Microsoft's newsgroups I received a simple answer: OWC 11
is an obsolette technology, that works with SSAS'2005 incorrectly, you should
develop your application with VS'2005 Office Tools and write Excel'2007
add-in. I'm trying to follow this recommendations, but I found that I cannot
execute my custom MDX query in Excel'2007. Yes, I can go another way: I can
design my custom presentation in Excel'2007 PivotTable (except of writing
MDX), and store ready Excel'2007 file in my "reports storage" (except of
storing MDX queries). But I'm facing with another problems in this
front-line. For example, you may read another post I created in this
newsgroups:

http://msdn.microsoft.com/newsgroups...px?dg=microsof...

Shortly, all my "reports" use calculated members. If I add calculated
members to PivotTable with Office 2007VBA, they have incorrect number
format. Also, I cannot add color and font expressions for my calculated
members (while in custom MDX I can). At last, this solution have excess
complexity: I have to add calculated members with help ofVBAand than I have
to design Pivot in Excel.

Now I've found a workaround:
-I declare ALL calculated members on the server-side (in BI studio, while
designing of my AS Solution)
-Design pivot report in Excel'2007 PivotTable
-Store Excel'2007 workbook with pivot report in database

When user requests any report, application load Excel'2007 workbook with
this report from database storage, open report and refresh it.

This solution DO NOW SATISFIES me in long term:

-Declaring server-side calculated members means that I cannot distribute mycubeseparately from application. If I have severalcubeinstallations, and I
release new version of my BI-application, or simply add a new report, I have
to include in at a newcubedesign or execute some MDX-statement once during
setup, that add new calculated member on every installation. Of course, it's
quite hard to maintain this system.
-If I design reports in Excel instead of writing custom MDX queries, MDX
queries produced by Excel are not as effective as I can write in MDX myself,
because PivotTable is UNIVERSAL product, while my reports are special and do
not use all functionality simultaneously.

Also I'm confused with a lack ofOLAPclients for SSAS'2005, provided by
Microsoft. As I understand, there are only 3 ways to display multidimensional
data using Microsoft products, each with it's own disadvantages:
1. Excel'2007. All my problems are written above. It is very impressive, but
too raw product for BI needs. Besides, I cannot embed it's PivotTable in my
application and distribute it separate from Office'2007, as I could do with
OWC. I cannot write my application in Borland Delphi, for example - I have to
use VS'2005 office tools and work in Office'2007 interface. It is a really
great disadvantage, in some cases.
2. Reporting Services 2005. It's my opinion, that as far as this technology
is amazing for relation database reporting, as it is miscarried for
multidimension analysis. Flattening MD-data into relation dataset and then
re-pivotting it in report - it look very funny for beginners, not for
enterprise-scale application. Also, I can slice my data with parameters, but
how can I drillthrough hierarchies, replace attributes on axes, etc?
SSRS'2005 do not know anything about my UDM features - this is why reporting
services provide me with so poor functionality.
3. Proclarity software. Proclarity is acquired by Microsoft, as I
understand. Your support said me last year, that I will be able to download
it's application from my MSDN Universal downloads site at July'2006... It is
still not available today. I just cannot download trial version of it's
software... :(

I'm totally surprised, that from year 2005, when SSAS'2005 (that is really
impressive and feature-richOLAPengine) was released, I have not convenient
and functional technology to deliver analytical data to end-user for today.

Thank you

P.S. The article you provided is about OWC. It is obsolette together with
OWC :(

Hello Dmitry,

After consulting the internal team, I confirmed that you could not format
the calculate member in the client application.

You need to use this in the SSAS side.

I have sent this feature request to the product team. Thank you for your
understanding! If you have any question, please feel free to let me know!


Sincerely,

Wei Lu
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.

Hello!

Please, write me on my e-mail - I'm reading this
newsgroups very seldom.

Best regards
Dmitry Jolobov


"Shailesh" wrote:

I will be extremely thankful for any answer.
I have created a cube and if I create a pivot from it using all the
dimensions in page field , I am getting correct values.
I have a tsk which requires me to populate all the dimensions in List
views and then generate the report using VBA abd MDX query.
I am not getting that right.Could you pl point me to any sample or
write any sample MDX for the scenario.

Thanks a alot
Regards,
Shailesh

Hello Dmitry,

Thank you for your understanding and if you have any question, please feel
free to let me know.

Sincerely,

Wei Lu
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.