How to show attribute ValueColumn in Excel 2007?

I'm using Excel 2007 to access my SSAS 2005 cube. For an attribute, I have defined the ValueColumn as the alternative description for the attribute. How can I show the ValueColumn in Excel 2007? Better yet, I want to see if there is any way I can toggle between the NameColumn and ValueColumn.

Thanks,

Mitch

The only way I can think of to expose the MemberValue to excel would be to create a calculated measure, but this would mean you would have to have the value in the data section of the pivot table and it sounds like you want to get it into the row/column labels.

Another approach would be to set this "alternate description" up as an attribute in it's own right that is related to the main attribute, you could then display it using the member properties feature in Excel.

Finally, I have not tried this myself, but you could look into maybe using the translations feature to store the alternate description. You might then be able to write a small macro in Excel that would alter the connection to use a different language that would bring up your alternate description. I would suggest doing a small test before going too far down this path.

|||

Darren,

These are not bad suggestions. The closest solution is to use the member property. It is just that my users are used to toggle between one description and another with their current data warehouse tool, and I'm trying to replace the current tool with SSAS and Excel 2007.

Mitch

sql

how to set up a cube for pivot table service in Excel?

background: sql2k and analysis service
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select'bogus - '+LEFT(DATENAME(month,getdate()),3)+' '+(CAST(DatePart(dd,GetDate())asnvarchar)+' '+CAST(DatePart(yyyy,GetDate())asnvarchar))asFILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only

How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!

How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!

How to set excel sheet name?

Is there any possibility that I can change properties of excel format
when exporting into it.? I need to have two or many sheets in that excel and
I manage to do that but their names are Sheet1,Sheet2,Sheet3 etc.
How can I name them in design mode of report?
DomagojThere is no way to specify sheet names in the current version. This is on
our wishlist for a future release.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Domagoj Fabricni" <something@.pero.com> wrote in message
news:%23RZPVNAkEHA.672@.TK2MSFTNGP10.phx.gbl...
> Is there any possibility that I can change properties of excel format
> when exporting into it.? I need to have two or many sheets in that excel
and
> I manage to do that but their names are Sheet1,Sheet2,Sheet3 etc.
> How can I name them in design mode of report?
> Domagoj
>|||Has control over the sheetnames been included in the SP2? I can't see the
functional enhancements list as I'm not a beta program member.
"Chris Hays [MSFT]" wrote:
> There is no way to specify sheet names in the current version. This is on
> our wishlist for a future release.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Domagoj Fabricni" <something@.pero.com> wrote in message
> news:%23RZPVNAkEHA.672@.TK2MSFTNGP10.phx.gbl...
> > Is there any possibility that I can change properties of excel format
> > when exporting into it.? I need to have two or many sheets in that excel
> and
> > I manage to do that but their names are Sheet1,Sheet2,Sheet3 etc.
> > How can I name them in design mode of report?
> >
> > Domagoj
> >
> >
>
>|||No, it is not included.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
news:E2E21646-9E16-44F7-AD7D-36FD4DB3301F@.microsoft.com...
> Has control over the sheetnames been included in the SP2? I can't see the
> functional enhancements list as I'm not a beta program member.
> "Chris Hays [MSFT]" wrote:
>> There is no way to specify sheet names in the current version. This is
>> on
>> our wishlist for a future release.
>> --
>> This post is provided 'AS IS' with no warranties, and confers no rights.
>> All
>> rights reserved. Some assembly required. Batteries not included. Your
>> mileage may vary. Objects in mirror may be closer than they appear. No
>> user
>> serviceable parts inside. Opening cover voids warranty. Keep out of reach
>> of
>> children under 3.
>> "Domagoj Fabricni" <something@.pero.com> wrote in message
>> news:%23RZPVNAkEHA.672@.TK2MSFTNGP10.phx.gbl...
>> > Is there any possibility that I can change properties of excel
>> > format
>> > when exporting into it.? I need to have two or many sheets in that
>> > excel
>> and
>> > I manage to do that but their names are Sheet1,Sheet2,Sheet3 etc.
>> > How can I name them in design mode of report?
>> >
>> > Domagoj
>> >
>> >
>>|||You may want to check out SoftArtisians's OfficeWriter:
http://officewriter.softartisans.com/officewriter-250.aspx
--
Adrian M.
MCP
"Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
news:E2E21646-9E16-44F7-AD7D-36FD4DB3301F@.microsoft.com...
> Has control over the sheetnames been included in the SP2? I can't see the
> functional enhancements list as I'm not a beta program member.
> "Chris Hays [MSFT]" wrote:
>> There is no way to specify sheet names in the current version. This is
>> on
>> our wishlist for a future release.
>> --
>> This post is provided 'AS IS' with no warranties, and confers no rights.
>> All
>> rights reserved. Some assembly required. Batteries not included. Your
>> mileage may vary. Objects in mirror may be closer than they appear. No
>> user
>> serviceable parts inside. Opening cover voids warranty. Keep out of reach
>> of
>> children under 3.
>> "Domagoj Fabricni" <something@.pero.com> wrote in message
>> news:%23RZPVNAkEHA.672@.TK2MSFTNGP10.phx.gbl...
>> > Is there any possibility that I can change properties of excel
>> > format
>> > when exporting into it.? I need to have two or many sheets in that
>> > excel
>> and
>> > I manage to do that but their names are Sheet1,Sheet2,Sheet3 etc.
>> > How can I name them in design mode of report?
>> >
>> > Domagoj
>> >
>> >
>>