Connect to Analysis Services 2005 from Excel 2007

I have recently discovered the ability to connect to an Analysis Service 2005 OLAP connection from Excel 2007. It looks and functions great. I do have one thing that am trying to do, however, that I am hoping it has. Does anyone know of a way that from the Pivot field panel, you can see the description metadata that is defined on the measures and dimensions in the Analysis Services cubes? It would be very useful if this come up in a tooltip or something. Otherwise, the business users will need a supplemental document while they are navigating the cubes.

Thanks in advance for your response.

I'd be interested in knowing how to do this as well.|||Sorry Guys, I don't think there is any way of accessing this data using the out-of-the-box functionality in Excel 2007. I even tried using the CUBEMEMBERPROPERTY() function in Excel, but it did not seem to be able to access the intrinsic properties like DESCRIPTION.|||Yikes! I thought Excel 2007 supported all SSAS2005 features? I can't express how useful and how much of a time-saver this feature would be. The "workaround" is that I'd have to create a big ugly document that our business users will have to print out and refer to. Even a simple tooltip that showed the member description in the dimension/measure browser would be immensely useful.

Is this something that we'd need to file w/ MS Connect?|||Thank you for your responses. I suppose I will have to put together a user-friendly on-line reference for people to use. Hopefully a future version will have this feature.|||

Unfortunately I don't think there is a section on Connect for Excel, otherwise this would definitely be the place to lodge such a query. I will see if I can find out if there is an avenue through which such a request can be lodged.

It is not an SSAS issue as the property is present, Excel just does not expose it. It might be possible to extend Excel to display the descriptions, but I have not had a chance to look into this. It would be possible to generate documentation from the meta-data in SSAS BI Documenter (www.bidocumenter.com) does this, but the documentation it produces might be a bit overwhelming for a business user.

sqlsql

Connect from SQL server to Excel file

Is it possible to connect from SQL server directly to .xls file ?
I imagine someting like opening .xls file in Query Analyzer to perform slect
into
You can use a linked server. Take a look at this topic from Books Online
"sp_addlinkedserver"
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
> Is it possible to connect from SQL server directly to .xls file ?
> I imagine someting like opening .xls file in Query Analyzer to perform
> slect
> into
|||In addition to using a Linked Server as Cristian suggested, you can also use
the OPENQUERY command.
--Brian
(Please reply to the newsgroups only.)
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:eeETVNHsFHA.1172@.TK2MSFTNGP11.phx.gbl...
> You can use a linked server. Take a look at this topic from Books Online
> "sp_addlinkedserver"
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> Cristian Lefter, SQL Server MVP
> MCT, MCSA, MCDBA, MCAD, MCSD .NET
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
>

Connect from SQL server to Excel file

Is it possible to connect from SQL server directly to .xls file ?
I imagine someting like opening .xls file in Query Analyzer to perform slect
into :)You can use a linked server. Take a look at this topic from Books Online
"sp_addlinkedserver"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
> Is it possible to connect from SQL server directly to .xls file ?
> I imagine someting like opening .xls file in Query Analyzer to perform
> slect
> into :)|||In addition to using a Linked Server as Cristian suggested, you can also use
the OPENQUERY command.
--
--Brian
(Please reply to the newsgroups only.)
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:eeETVNHsFHA.1172@.TK2MSFTNGP11.phx.gbl...
> You can use a linked server. Take a look at this topic from Books Online
> "sp_addlinkedserver"
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> Cristian Lefter, SQL Server MVP
> MCT, MCSA, MCDBA, MCAD, MCSD .NET
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
>> Is it possible to connect from SQL server directly to .xls file ?
>> I imagine someting like opening .xls file in Query Analyzer to perform
>> slect
>> into :)
>

Connect from SQL server to Excel file

Is it possible to connect from SQL server directly to .xls file ?
I imagine someting like opening .xls file in Query Analyzer to perform slect
into You can use a linked server. Take a look at this topic from Books Online
"sp_addlinkedserver"
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
> Is it possible to connect from SQL server directly to .xls file ?
> I imagine someting like opening .xls file in Query Analyzer to perform
> slect
> into |||In addition to using a Linked Server as Cristian suggested, you can also use
the OPENQUERY command.
--Brian
(Please reply to the newsgroups only.)
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:eeETVNHsFHA.1172@.TK2MSFTNGP11.phx.gbl...
> You can use a linked server. Take a look at this topic from Books Online
> "sp_addlinkedserver"
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> Cristian Lefter, SQL Server MVP
> MCT, MCSA, MCDBA, MCAD, MCSD .NET
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:CFDDF854-1800-4B8A-ABBA-3AE7BAB55E32@.microsoft.com...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...
Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_nam e.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizard
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_name.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizar
d
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Configuring Charts for more than one axis

In Excel it is possible to have a primary and a secondary axis. This would allow for a chart to show to related data sets that have vastly different scales. Is this possible in Reporting Services.Sorry, this is currently not supported out-of-the-box. There are add-on products (e.g. Dundas Chart for Reporting Services) that can plug into RS 2005 and provide that capability.

-- Robert