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 select with no aggregation

Dear All,

I tryed to do the following

OpenQuery([ssas DS], select {[Measures].[Weekday], [Measures].[Weekday], [Measures].[sales count]} on columns,
{[Dim Time].[Weekday].members} on rows
from CubeName)

It returns me the followings:

Weekday Weekday Sales Count
All 5 5 20402295
1 1 1 3445560
2 2 2 3950970
3 3 3 4532115
4 4 4 4498965
5 5 5 3974685

But I don't want the row "All 5 5 20402295" to be included in the row how can I remove it from the rowset?

Thanks

Tony Chun Tung Siu

Try replacing {[Dim Time].[Weekday].members} with {[Dim Time].[Weekday].[Weekday].members}

MDX Parameter in Report...

Dear Friends,

I have a doubt, and I need your support...

I have 4 tables that are in one dimension STRUCTURE.

The Structure is:

1. Entidade

2. Carteira

3 Mesa

4. Folder

In my report I have these 4 combobox with data and related...

So the problem is...

I need to show a field in the report based on the selection... For example:

1oWhen

1. Entidade = 'LIS'

2. Carteira = 'ALL'

3 Mesa = 'ALL'

4. Folder = 'ALL'

I want to show in the textbox1.value the CalculatedMember1

2oWhen

1. Entidade = 'LIS'

2. Carteira = 'LIS-CPR'

3 Mesa = 'ALL'

4. Folder = 'ALL'

I want to show in the textbox1.value the CalculatedMember2

3oWhen

1. Entidade = 'LIS'

2. Carteira = 'LIS-CPR'

3 Mesa = 'LIS-CPR-ME1'

4. Folder = 'ALL'

I want to show in the textbox1.value the CalculatedMember3

4oWhen

1. Entidade = 'LIS'

2. Carteira = 'LIS-CPR'

3 Mesa = 'LIS-CPR-ME1'

4. Folder = 'FOLDER1'

I want to show in the textbox1.value the CalculatedMember4

It's Possible?

Someone help me?

Regards!!

I'm assuming that this means that you want the calculation to work differently at the different levels. Why not create a 4th calculation and use scope assignment to return the approriate value at the appropriate level?

I'm assuming that these attributes are in a hierarchy, but you did not mention what the name of that was, so I have just used the text "" as a placeholder.

Code Snippet

CREATE MEMBER currentcube.measures.CalculatedMember5 as (CalculatedMember1)

SCOPE (STRUCTURE.<hierarchy>.Carteira.Members);

(measures.CalculatedMember4) = (measures.CalculatedMember2);

END SCOPE;

SCOPE (STRUCTURE.<hierarchy>.Mesa.Members);

(measures.CalculatedMember4) = (measures.CalculatedMember3);

END SCOPE;

SCOPE (STRUCTURE.<hierarchy>.Folder.Members);

(measures.CalculatedMember4) = (measures.CalculatedMember4);

END SCOPE;

Then in your report you just referece CalculatedMember5

|||

Dear Darren,

I didn't understand your statment...

I need to use CM1 or CM2 or CM3 or CM4 depending on the values selected by the user in the combobox's report.

So I found a solution to get if inside the report, but It would be better If I control it in a CM in spite of textbox report... So I did like this:

Code Snippet

=IIF(Parameters!DimStructureCarteiraID.Value(0)="[DimStructure].[Carteira_ID].[All]"
AND Parameters!DimStructureMesaID.Value(0)="[DimStructure].[Mesa_ID].[All]"
AND Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_1.Value, "dSource1")
,IIF(Parameters!DimStructureMesaID.Value(0)="[DimStructure].[Mesa_ID].[All]"
AND Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_2.Value, "dSource1")
,IIF(Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_3.Value, "dSource1")
,Sum(Fields!CM_4.Value, "dSource1")
)
)
)

But would be better to call for example a CMX in spite of using this formula for each textbox...

I will try to convert this statment to inside my dataset...

Understood?

Regards and thanks!

|||The scope statement will only work inside the MDX script of your cube. You could probably do similar logic to your SSRS expression inside the MDX using a similar IIF() pattern.