Measures to columns in Excel

Dear anyone

I am transfering the information from the cube to Excel.

My wizard places all measures in the pivot table to one column. I am using Excel 2003.

I would like to place the measures to different columns to get a result which is easy to read and copy.

Could anyone advice me how this can be done ?

Thanks !

Matti

You just have to drag & drop the field "Data" from the rows to the columns.|||

Thanks Rmi

Your advice sounds simple, I expected the same.

The problem is that but my Add-button is inhibited when

trying to do that. Could this be caused by cube definitions ?

Matti

|||

If I try to use drag and drop, I get an error from Excel. Translation of error message is something like this: "The field You are transfering can't be placed in this area of the pivot table".

Matti

|||I don't think it comes from your cube. I am sorry if my answer seems a little too simple, but are you sure you drag & drop the field on the header of the columns (not the content of the table). In fact you can put numeric values in the content of table, but dimension attributes need to be in row headers or column headers or filter.|||

I want to summarize, that I can easily drag and drop all dimensions to either lines or to columns Measures I can only place to the same column.

Matti

|||Yes, of course, sorry I got lost... I just wanted you to drag and drop the field "data" which represents the Measures on the header of the column. And if it doesn't work, then I will let someone else help you because I don't have another idea...|||

Thanks for kind help Rèmi !

This is a question of the layout of the report and the form of the paper.

What I get is:

DimA1 DimA2

DimB1 MV1A1B1 MV1A2B1

MV2A1B1 MV2A2B1

DimB2 MV1A1B2 MV1A2B2

MV2A1B2 MV2A2B2

What I want to get:

DimA1 DimA2

DimB1 MV1A1B1 MV2A1B1 MV1A2B1 MV2A2B1

DimB2 MV1A1B2 MV2A1B2 MV1A2B2 MV2A2B2

.

.

.

Matti

MDX Sumproduct and excel functions

Hi
Has anyone used SUMPRODUCT in a calculated member in analysis services?
Apparantly, according to MS documentation, its possible, but I can see
nothing in the books online - or on the web.
I am using the syntax below, which I would have thought would have worked.
SUMPRODUCT({[Accounts].&[123],[Accounts].&[456]},{
[Accounts].&[111],[Accounts].&[321]})
So does anyone know how to use SUMPRODUCT with AS?
Thank you for your help
JeremySince the arguments to SumProduct() are arrays, use the MDX SetToArray()
function to generate them. The second argument to SetToArray() is the
numerical value to use:
[vbcol=seagreen]
SUMPRODUCT(
SetToArray({[Accounts].&[123],[Accounts].&[456]},
[Measures].[Sales]),
SetToArray({[Accounts].&[111],[Accounts].&[321]},
[Measures].[Sales]))[vbcol=seagreen]
From SQL Server BOL>>
SetToArray
Converts one or more sets to an array for use in a user-defined
function.
Syntax
SetToArray(Set[, Set...][, Numeric Expression])
Remarks
This function converts one or more sets to an array for use in a
user-defined function. The number of dimensions in the resulting array
is the same as the number of sets specified.
The optional numeric expression can be used to provide the values in the
array cells. If omitted, the default value of the set member is used for
the array cell value.
..[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!

MDX query in excel sheet

I am using Excel 2007 and SSAS 2005. I have an excel report that is done by pivot tables, accesing my SSAS cube. Is it possible to view and edit the MDX query for the report? Can I pass parameters to MDX query? If so how?

You can use vba to extract the MDX query from a pivot table, but I do not believe there is anyway to change it. I have not tried this in 2007, but you could not in Excel 2003 and I have not heard anything to make me believe otherwise.|||

Here's a blog post from Marco Russo that provides the code for doing this.

http://sqljunkies.com/WebLog/sqlbi/archive/2007/01/18/26875.aspx

MDX Pivot Table

Hi Everybody:
I have only SQL Server 2000, AS and Office 2000... and i need to build a
customized Excel VBA system with drill-down & drill-up functionality from an
Analysis Services cube. I would like to avoid a lot of VBA/MDX program
code...so the question is: can i control Excel 2000 Pivot Table from VBA MDX
code?... i mean, to fill the PivotTable from a specific MDX query, remaining
the pivot-table functionality...
Thanks a lot...
RodrigoNo.
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:858EE816-20D8-4FA8-9020-EF0A4DDB4123@.microsoft.com...
> Hi Everybody:
> I have only SQL Server 2000, AS and Office 2000... and i need to build a
> customized Excel VBA system with drill-down & drill-up functionality from
> an
> Analysis Services cube. I would like to avoid a lot of VBA/MDX program
> code...so the question is: can i control Excel 2000 Pivot Table from VBA
> MDX
> code?... i mean, to fill the PivotTable from a specific MDX query,
> remaining
> the pivot-table functionality...
> Thanks a lot...
> Rodrigo