Thursday, March 29, 2012

Calculated Member help

Hi, I have a calculated member that computes the Sales per week for any given level. I am using a time dimensiona and a sales dimension. The time dimension has these levels: Year, Quarter, Month, and Weeks. I am using this formula

[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

The problem with this is that it works only if the user select a single time member. I am using Excel as frontend and I fixed the pivot table in such a way that Sales is on the column, and Store is on the Rows. The time dimension is on the top serving as filter (Page axis). Because you can choose multiple dimension member in the dropdown boxes of any dimension in Excel, I am having trouble making it work in these scenario. It only works if you select one time member at a time. Please help.I don't fully understand what you are trying to do, but since the database server is returning a simple dataset to your Excel pivot table, the problem would appear to be with the pivot table and not the query or table you are using. When you filter the pivot table you are fiiltering its stored snapshot of the query results. It does not pass the parameters back to the database server.

blindman|||Hi, thnks so much for the reply. Anyway, to make it clear to you:

I'm using an Analysis Service Cube as my backend and Excel Pivot table as my frontend.

I have two dimension in my cube: 1. Time(Yr, Qtr, Month, Week) and 2. Store

I have two measures: 1. Sales and 2. Sales per week (this measure is a calculated member

The formula I use to make the Sales per week measure is:
[Measures].[Sales]/Descendants([TIME_WEEK_DIM],[TIME_WEEK_DIM].[Time Week Desc]).count

Here's my setup in Excel: The sales is broken down by store and the time is on top acting as a filter

Time: 2003

Store Sales Sales/Week
Store1 100 25
Store2 200 12

My problem occurs evrytime the user selects more than one Time value to filter on (like all Jan AND Feb data). It works only if one time value is selected at a time. When user chooses more than 1 time value, the Sales/Week data appears as: #Num!|||Sorry smb, but I don't have experience with data cubes syntax. Hopefully now that your post is at the top of list someone else will check it out and give you an answer!

blindman|||Thnks anyway blindman :)

Calculated Member Formatting

I'm running Panorama Novaview 4.5 against AS 2005 April CTP (Panorama doesn't work against June CTP yet, but I tested the MDX against June CTP and the same problem occurs). In my cube, I have a Metrics dimension with several associated calculated members. When I run a query where I'm not slicing or slicing by one member per attribute, the calculated members formatting works as expected. However, when I run the query and slice by multiple members of an attribute, all calculated members take on the formatting of the selected measure. This behavior still applies if I run the MDX that Novaview generates in SSMS. The following is the MDX and results:

Query with only single member selection:
With
Member [Metrics].[Metrics].[pnMetaX] As 'Null'
Member [Transaction Type].[Transaction Type].[pnMetaY] As 'iif([Metrics].[Metrics].currentmember is[Metrics].[Metrics].[pnMetaX], count([NewSetY]), Null)'
Set [SetAxisX] As '{Except({[Metrics].[Metrics].[Metrics].[Actual],[Metrics].[Metrics].[Metrics].[Current YTD],[Metrics].[Metrics].[Metrics].[Prior YTD],[Metrics].[Metrics].[Metrics].[YTD % Change]},{[Metrics].[Metrics].[pnMetaX]})}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Transaction Type].[Transaction Type].[Transaction Type].AllMembers,{[Transaction Type].[Transaction Type].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Metrics].[Metrics].[pnMetaX])}), ([Metrics].[Metrics].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ), {([Transaction Type].[Transaction Type].[pnMetaY])}) ,([Transaction Type].[Transaction Type].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Simonton Sales]
WHERE ([Date].[Calendar Monthly].[All Dates].[2005], [Measures].[Dollars], [Plant].[Plant].[Plant].[Ritchie County])

Results: (calc members have correct formatting)
Actual Current YTD Prior YTD YTD % Change pnMetaX
Order $81,249,243.32 81,249,243 191,376,964 -57.54% (null)
Repair $345,643.88 345,644 907,068 -61.89% (null)
Credit ($1,775,492.36) -1,775,492 -3,723,484 -52.32% (null)
Debit $56,010.77 56,011 121,655 -53.96% (null)
pnMetaY (null) (null) (null) (null) (null)

Query with multiple member selection:
With
Member [Metrics].[Metrics].[pnMetaX] As 'Null'
Member [Transaction Type].[Transaction Type].[pnMetaY] As 'iif([Metrics].[Metrics].currentmember is[Metrics].[Metrics].[pnMetaX], count([NewSetY]), Null)'
Member [Plant].[Plant].[pnMulti] As 'Sum({[Plant].[Plant].[Plant].[Ritchie County],[Plant].[Plant].[Plant].[Paris]})'
Set [SetAxisX] As '{Except({[Metrics].[Metrics].[Metrics].[Actual],[Metrics].[Metrics].[Metrics].[Current YTD],[Metrics].[Metrics].[Metrics].[Prior YTD],[Metrics].[Metrics].[Metrics].[YTD % Change]},{[Metrics].[Metrics].[pnMetaX]})}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Transaction Type].[Transaction Type].[Transaction Type].AllMembers,{[Transaction Type].[Transaction Type].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Metrics].[Metrics].[pnMetaX])}), ([Metrics].[Metrics].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ), {([Transaction Type].[Transaction Type].[pnMetaY])}) ,([Transaction Type].[Transaction Type].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Simonton Sales]
WHERE ([Date].[Calendar Monthly].[All Dates].[2005], [Measures].[Dollars], [Plant].[Plant].[Plant].[pnMulti])

Results: (calc members all take the formatting of the selected measure)
Actual Current YTD Prior YTD YTD % Change pnMetaX
Order $124,225,094.82 $124,225,094.82 $292,160,648.80 ($1.15) (null)
Repair $501,626.07 $501,626.07 $1,211,672.17 ($1.11) (null)
Credit ($2,809,978.53) ($2,809,978.53) ($5,447,738.20) ($0.92) (null)
Debit $85,167.51 $85,167.51 $197,187.21 ($1.15) (null)
pnMetaY (null) (null) (null) (null) (null)

Definition of YTD % Change calc member:
YTD % Change:
CREATE MEMBER CURRENTCUBE.[Metrics].[Metrics].[YTD % Change]
AS Iif ([Metrics].[Prior YTD] = 0, NULL,
([Metrics].[Current YTD] - [Metrics].[Prior YTD]) / [Metrics].[Prior YTD]),
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [Units], [Dollars] },
VISIBLE = 1;

I tried to recreate this in the AWDW cube, but the formatting didn't seem to work at all there. I ran the following query (pulled from Novaview) and the Budget Variance % calc member wasn't formatted as %:

With
Member [Scenario].[Scenario].[pnMetaX] As 'Null'
Member [Date].[Fiscal].[pnMetaY] As 'iif([Scenario].[Scenario].currentmember is[Scenario].[Scenario].[pnMetaX], count([NewSetY]), Null)'
Set [SetAxisX] As '{AddCalculatedMembers(Except([Scenario].[Scenario].[Scenario].AllMembers,{[Scenario].[Scenario].[pnMetaX]}))}'
Set [SetAxisY] As '{HIERARCHIZE ( Except([Date].[Fiscal].[(All)].AllMembers,{[Date].[Fiscal].[pnMetaY]}))}'
Set [NewSetY] As '{NonEmpty([SetAxisY],[SetAxisX])}'
Set [NewSetX] As '{NonEmpty([SetAxisX],[NewSetY])}'
Set [MainAxisX] As '{Except([NewSetX],{([Scenario].[Scenario].[pnMetaX])}) ,([Scenario].[Scenario].[pnMetaX])}'
Set [MainAxisY] As '{Except(Subset ([NewSetY],0,1000 ) ,{([Date].[Fiscal].[pnMetaY])}) ,([Date].[Fiscal].[pnMetaY])}'

Select [MainAxisX] On Columns, [MainAxisY] On Rows
From [Finance]
WHERE ([Measures].[Amount])

Results:
Actual Budget Budget Variance Budget Variance % pnMetaX
All Periods $12,609,503.00 $5,583,900.00 $7,025,603.00 1.25818925840362 (null)
pnMetaY (null) (null) (null) (null) 1

Any ideas what could be causing these formatting problems?

Thanks,
Dirk

I upgraded to the June CTP and I'm still having the same problem. Any ideas what could be causing this?

Thanks,
Dirk|||I'm still looking for a solution to this problem. Anyone have any ideas?

Thanks,
Dirk

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated member for the current month

Hi,

Can any one throw whether the above one is correct or not?

Thanks

You need to use CREATE MEMBER syntax instead of WITH MEMBER. Otherwise, it should work.sql

Calculated member for the current month

Hi,

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )

select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mms

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

Tuesday, March 27, 2012

Calculated member for Grand Total...

As part of a cube I would like to have access to the Grand Total for my measure "Events".

Is there a simple way to access the grand total regardless of the Dimensions either as a calculated measure or directly?

for example if the dimensionsion were MonthNumber and EventType and the values for the intersect of these dimensions are

12, a = 5

11,a = 6

12,b = 3

11,b = 2

I'd like to end up with something like :

MonthNumber 12 11

EventType

Events total Events total

a 5 16 6 16

b 3 16 2 16

Where "Events" is the Events value and "total" is combined value of all events in the cube.

Thanks

You would do this by getting the Events measure at the "All Eventtype" level. Your MDX would look something like the following:

WITH
MEMBER Measures.total as '(Measures][Events],[Eventtype].[All Eventtype]'
SELECT
{[Time].[Month].[11],[Time].[Month].[12]}
* {Measures.[Events], Measures.total} ON COLUMNS,
{[Eventyype].members} ON ROWS
FROM <Cube>

Regards
Darren Gosbell [MCSD]
http://geekswithblogs.net/darrengosbell

|||

Thanks for the repy, but I'm kinda looking for access to the grand total functionality, if such a thing exists.

Also this doesn't seem to woirk as a calculated member.

Calculated member for Grand Total...

As part of a cube I would like to have access to the Grand Total for my measure "Events".

Is there a simple way to access the grand total regardless of the Dimensions either as a calculated measure or directly?

for example if the dimensionsion were MonthNumber and EventType and the values for the intersect of these dimensions are

12, a = 5

11,a = 6

12,b = 3

11,b = 2

I'd like to end up with something like :

MonthNumber 12 11

EventType

Events total Events total

a 5 16 6 16

b 3 16 2 16

Where "Events" is the Events value and "total" is combined value of all events in the cube.

Thanks

You would do this by getting the Events measure at the "All Eventtype" level. Your MDX would look something like the following:

Regards
Darren Gosbell [MCSD]
http://geekswithblogs.net/darrengosbell

|||

Thanks for the repy, but I'm kinda looking for access to the grand total functionality, if such a thing exists.

Also this doesn't seem to woirk as a calculated member.

Calculated Member banding

Hi,

I'm not sure if this is possible in analysis services or if i need to find dome other solution but i would like to implement a banding group on one of the measures in the analysis services cube i am developing.

I have a measue group counting visits and i can cross reference this with customers who are doing the visits. I'd like to be able to group customers in bands according to the amount of visits which they have done e.g.

300+ visits

200-299 visits

100-199 visits

<100 visits

Can this be done using a calculated measure or some other functionality within analysis services? If so can somebody suggest a solution.

All help is greatfully appreciated.

Cheers,

Grant

Hi,

I guess there is no nice and fast solution in cube.

Would you have a new dimension with the banding - or an attribute in the customer dimension or a seperate measure for the values?

I would recomend you add an column to your customer table and by ETL add the visits count. Based on this you could do a view or named query whith the band definition.

Other possible way is to add an datamining dimension with a cluster on the visits

Or do calculated mesasures for each band.

HANNES

|||

Thanks for the suggestions. I didn't think it was going to be a simple matter of doing this.

I don't think the value in the customer table will work too well in my case so i may look into a seperate dimension or calculated member for each band.

Your help is appreciated,

Grant

|||

Hi Again,

Sorry; this is probably a stupid question. How would i go about creating a seperate calculated measure for each banding group, what sort of format would it take?

Is it possible to create a calculated member consisting of a case statement which i could then use the results of the case statement to group measures on?

I may not be explaining this as well as i could but hopefully someone understands what i mean.

Many thanks,

Grant

|||

Hi,

from my perspective the calculation is the worst way because its the slowest and most complex and support the least flexibility I guess. I would not recomend if the cube is not small.

with calculation you could only create new measure for each band. I cannot create one measure and group by this.

The question as far as I remember - you need to have this band for each row in your fact data (leaf level at the cube) or for the customer on leaf only and all othere dimensions on the current node? (or root node?)

(if you need it on fact row level i would do it in the fact table query with a case .... - and use it as an fact dimension)

the calculation for customer only would be something like this

create member measures.customer100band;

scope(customer.key.members);

(measures.customer100band)=case when measure between x and y then measure else null end;

end scope;

This then uses the customer key and the current selection of all tothere dimensions for evaluation the formular. IT gets more dificult if you need this band not calculated by the current context.

Maybe someone else has a better solution for the calculation.

Best Regards

HANNES

|||

Thanks again for the response.

I'm having a little bit of a problem getting my head around what needs to be done. The problem initially is probably not completely understanding what i want to do. Taking your advice on board, i'll look into other methods aside from the calculated measure on the CustomerSite dimension. My fact table for visits has a default 1 column for basing the aggregation on so i cannot calculate in this table the bands.

If i have a seperate dimension; presumably this would consist of a key column and a column stating the band i.e.

Key Status

1 300+ visits

2 200 to 300 visits

etc...

The problem i have with getting my head around this method is where is link my dimension to; i cannot include the key in the fact table as i don't know at that stage how many visits are made to a specific customer for a selected date range. Do i not still need to link on the measure value that is returned at the chosen levels. I'm sorry if i'm not explaining this very well; this is a slightly steep learning curve at present.

Thanks again,

Grant

|||

As far as I understand you need your banding dependent on your selection (Time).

With this my calculation described above is the only method i would recommend.

As you already see - you cannot add by calculation new Attributes or Dimensions to the cube - cannot join your virtual dimension.

you only can create new members in existing dimensions / Attirubutes. Therefor you can only create new measures or new members in othere existing attributes.

Best Regards

HANNES

|||

I can see that adding a calculated member for each band will show the customers banding based on the selected date range. What i cannot see is how i would be able to slice the data based on the values in these bands. I don't see how i'd be able to get a count of customers for each of the visit bandings. Would this require another calculated member to work this out?

I'm starting to get to grips with this so thanks for your patience and help.

Grant

|||

Hi,

to slice and dice you need attributehierarchies in dimenions - with measure values associated to differnt members in this attributes.

Therefor you cannot slice and dice based on calculations (this is a singe member)

so you can do furthere calculations with your requirements or with one of my other sugestions.

Hannes

Calculated Member banding

Hi,

300+ visits

200-299 visits

100-199 visits

<100 visits

Can this be done using a calculated measure or some other functionality within analysis services? If so can somebody suggest a solution.

All help is greatfully appreciated.

Cheers,

Grant

Hi,

I guess there is no nice and fast solution in cube.

Would you have a new dimension with the banding - or an attribute in the customer dimension or a seperate measure for the values?

I would recomend you add an column to your customer table and by ETL add the visits count. Based on this you could do a view or named query whith the band definition.

Other possible way is to add an datamining dimension with a cluster on the visits

Or do calculated mesasures for each band.

HANNES

|||

Thanks for the suggestions. I didn't think it was going to be a simple matter of doing this.

I don't think the value in the customer table will work too well in my case so i may look into a seperate dimension or calculated member for each band.

Your help is appreciated,

Grant

|||

Hi Again,

Sorry; this is probably a stupid question. How would i go about creating a seperate calculated measure for each banding group, what sort of format would it take?

Is it possible to create a calculated member consisting of a case statement which i could then use the results of the case statement to group measures on?

I may not be explaining this as well as i could but hopefully someone understands what i mean.

Many thanks,

Grant

|||

Hi,

from my perspective the calculation is the worst way because its the slowest and most complex and support the least flexibility I guess. I would not recomend if the cube is not small.

with calculation you could only create new measure for each band. I cannot create one measure and group by this.

(if you need it on fact row level i would do it in the fact table query with a case .... - and use it as an fact dimension)

the calculation for customer only would be something like this

create member measures.customer100band;

scope(customer.key.members);

(measures.customer100band)=case when measure between x and y then measure else null end;

end scope;

This then uses the customer key and the current selection of all tothere dimensions for evaluation the formular. IT gets more dificult if you need this band not calculated by the current context.

Maybe someone else has a better solution for the calculation.

Best Regards

HANNES

|||

Thanks again for the response.

If i have a seperate dimension; presumably this would consist of a key column and a column stating the band i.e.

Key Status

1 300+ visits

2 200 to 300 visits

etc...

Thanks again,

Grant

|||

As far as I understand you need your banding dependent on your selection (Time).

With this my calculation described above is the only method i would recommend.

As you already see - you cannot add by calculation new Attributes or Dimensions to the cube - cannot join your virtual dimension.

you only can create new members in existing dimensions / Attirubutes. Therefor you can only create new measures or new members in othere existing attributes.

Best Regards

HANNES

|||

I'm starting to get to grips with this so thanks for your patience and help.

Grant

|||

Hi,

to slice and dice you need attributehierarchies in dimenions - with measure values associated to differnt members in this attributes.

Therefor you cannot slice and dice based on calculations (this is a singe member)

so you can do furthere calculations with your requirements or with one of my other sugestions.

Hannes

sql

Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?
Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>
|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>
|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>

Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>
>

Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_ContaLiquidar_CRT7
AS
IIF(((ANCESTOR
(
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].CurrentMember,
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].[Carteira_ID]
),
[Measures].[CM_ContaLiquidar]))<0,0,[Measures].[CM_ContaLiquidar])
,
VISIBLE = 1;

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

Calculated Member (beginner)

Hi,

I'm building a calculated member which should only calculate the measures in the calculated member if specific measure is > 0 (otherwise the value should be Null)

For example:

If physicalStock > 0

[Measures].[Netweight] * [Measures].[Physical]

How do I build this in the calculated member...?

(Using Analysis2000)

Thanks in advance

Haakon

With AS2000 Mosha have a blog post of this subject here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/30/mdx_is_empty.aspx

HTH

Thomas Ivarsson

|||

Here is what I am trying....

Iif([Measures].[Physical] > 0), [Measures].[Netweight] / [Measures].[amount

_count] * [Measures].[Physical]

Which don't work....any ideas - what I should do...?

Thanks

|||

IIF(([Measures].[Physical] > 0, [Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

HTH

Thomas Ivarsson

|||

Thanks for your input - but it still seems to be causing problems:

I am getting the following error in the calculated member builder:

"Unable to update the calculated member. Formula error - Syntax error - token is not valid"

I am using the following suggested value expression:

Iif(([Measures].[Physical] > 0,[Measures].[Netweight] / [Measures].[amount_count] * [Measures].[Physical]),Null)

Your suggestions are appriciated,

Thanks,

|||

It is my error. Try this:

IIF([Measures].[Physical] > 0,[Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

Copy and paste this example above.

Regards

Thomas Ivarsson

sql

Calculated member - please help (SQL Server 2000)

hi,

please if u could help with this:

i have a cube with 3 dimensions, Sales (fact table), Time and Accounts (general ledger accounts). Accounts dimension hierarchy is as follows:

1. Revenues
1.1 Income
1.1.1 number of account in General Ledger
1.1.2 number of account in GL etc.....
2. Expenses
2.1 Fixed costs
2.1.1 number of account in GL etc....
2.2 Operating costs
2.3 Overhead
3. Unsorted

now what i need to do is next: i need to calculate measure.salesamount in percentage of total sales amount for each category of accounts. for example i need measure.salesamount of each account number in Revenue/Income in percentage of the total salesamount of the category Revenue.

i hope i was clear enough in what i need.

please can anyone help me with this?

thank you in advance!

marko

try an expression something like the following in your calculation:

Measures.salesAmount / (Measures.salesAmount,Accounts.CurrentMember.Parent)

You can then either wrap the whole thing in brackets and mulitply by 100 or you should be able to use a percentage style format string - it depends a bit on how your front end works.

|||Hello Marko,

i am not 100% clear on ur pblm. but on what ever i understood i have given u a formula for calculating the calculated-member.

iif(
[revenue_dimension].currentmember.parent is null,1, ( [revenue_dimension].currentmember , [measures].[salesamount] ) / ([revenue_dimension].currentmember.parent , [measures].[salesamount] )

)
then make the data type of this calculated-member to percentage.

regards,
Rajiv.|||

Hi,

thank you both for your answers. both formulas work and give the same result.

Thank you once again for your help! Much appreciated!

best regards

marko

Calculated member - Count function

Hello
I have te following problem:
I have made a cube with quotations. But a quotation consist of one or more
lines. On each line a product is selected (with price, amount, etc.).
The records in my fact table are quotationlines (not quotation-headers),
because the producthierarchy is linked to the articles (in quotationlines).
I would like to count the quotations (not the lines).
The lines linked to a certain quotation have the same document number.
How can I count the number of quotations, e.g. by using the function
calculated member or...? I have tried something but without a good result.
Can you help me?
Thanks in advance
HansIs the relatoinship between the document number and quotation is one to one?
If then, it's possible to aggregate the document number with aggregation
function of "distinct count". You can set the property of measure in Cube
Editor.
Ohjoo Kwon
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> Hello
> I have te following problem:
> I have made a cube with quotations. But a quotation consist of one or more
> lines. On each line a product is selected (with price, amount, etc.).
> The records in my fact table are quotationlines (not quotation-headers),
> because the producthierarchy is linked to the articles (in
quotationlines).
> I would like to count the quotations (not the lines).
> The lines linked to a certain quotation have the same document number.
> How can I count the number of quotations, e.g. by using the function
> calculated member or...? I have tried something but without a good result.
> Can you help me?
> Thanks in advance
> Hans|||Thank you,
But I still have a problem. At the Aggregate Function the option 'count
distinct only appears when the source field is an Numeric field type. The
documentnumber I would like to count (distinct count) is a Text-field (e.g.
VF05001234).
Do you have any ideas to solve this problem?
Or is the problem not the field type, but something else I don't know?
Hans
"Ohjoo Kwon" wrote:

> Is the relatoinship between the document number and quotation is one to on
e?
> If then, it's possible to aggregate the document number with aggregation
> function of "distinct count". You can set the property of measure in Cube
> Editor.
> Ohjoo Kwon
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> quotationlines).
>
>|||Yes, you are right. It must be numeric.
I recommend you use surrogate key mapped to document number.
Ohjoo Kwon
"hans.de.korte@.verder.nl"
<hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> Thank you,
> But I still have a problem. At the Aggregate Function the option 'count
> distinct only appears when the source field is an Numeric field type. The
> documentnumber I would like to count (distinct count) is a Text-field
(e.g.[vbcol=seagreen]
> VF05001234).
> Do you have any ideas to solve this problem?
> Or is the problem not the field type, but something else I don't know?
> Hans
> "Ohjoo Kwon" wrote:
>
one?[vbcol=seagreen]
Cube[vbcol=seagreen]
more[vbcol=seagreen]
quotation-headers),[vbcol=seagreen]
result.[vbcol=seagreen]|||Thank you!!
"Ohjoo Kwon" wrote:

> Yes, you are right. It must be numeric.
> I recommend you use surrogate key mapped to document number.
> Ohjoo Kwon
> "hans.de.korte@.verder.nl"
> <hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
> news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> (e.g.
> one?
> Cube
> more
> quotation-headers),
> result.
>
>

Calculated Member - #VALUE! in the Grand Total

Hi all

This is a simple question on MDX.

We have created a calcualted member that basically compares an existing measure to an attribute, and displays a certain value based on what's in the attribute (simple case statement)

The calcualtion works fine against the proper dimension, but for Grand Total displays #VALUE!. What do I have to do to make the grand total actually display a grand total of this calculated member in the cube?

Thanks in advance

Moe

Hi Moe,

Can you give an idea of what the MDX for the calculated member looks like?

|||

Hi Deepak

Here's the code for the calculated member. Please let me know if you need anything else.

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS case when [Repairs].[Repair Status].&[Done] then [Measures].[ARC Buy Price]

when [Repairs].[Repair Status].&[Not Done] then 0

else -1 end,

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

If the intent is to return the value of [Measures].[ARC Buy Price] associated with the [Repairs].[Repair Status].&[Done] member, maybe something simpler like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS iif([Repairs].[Repair Status].CurrentMember is [Repairs].[Repair Status].&[Not Done],

0, ([Repairs].[Repair Status].&[Done], [Measures].[ARC Buy Price])),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

Hi Deepak

Thanks for the reply.

The code sample you sent me gives me a value in the Grand Total now, but I am confused as to what this Grand Total actually is .

To simplify my problem, I created the following example based on Northwind:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

AS iif(

[Orders].[Customer ID].CurrentMember = [CHOPS], 5, 1),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [Orders Count] },

VISIBLE = 1 ;

The above code gives me a Grand Total of 1! is this correct?

Thanks again

Moe

|||

Hi Moe,

I'm not clear how to run your sample code - could you provide an Adventure Works equivalent? Also, what should your results layout look like - and how many members of [Repairs].[Repair Status] are there (I assumed that there are just 2)?

Calculated Member

When looking at the template of the calculated member [Percentage of Total] it appears that I have to refer to the <<Target Hierarchy>>.
That means that I have to create a calculated member for every hierarchy.
Is there a way to create only one calculated member that refer to current hierarchy.

Thanks

Yes, this template assumes you want to see percentage of total relative to a single dimension. If you want to see different percentages of totals relative to different dimensions at the same time you'll need seperate calculated members. However, if you want to see percentage of total relative to multiple dimensions, you can additional dimensions to the formula as follows:

/*Calculates the ratio of a specific member's value to the value of all members.*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage of Total]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[<<Target Measure>>]

)

Then Null

Else ( [<<Target Dimension 1>>].[<<Target Hierarchy>>].CurrentMember,

[<<Target Dimension 2>>].[<<Target Hierarchy>>].CurrentMember,

...

[<<Target Dimension n>>].[<<Target Hierarchy>>].CurrentMember,

[Measures].[<<Target Measure>>] )

(

// The Root function returns the (All) value for the target dimension.

Root

(

[<<Target Dimension 1>>]

Root

(

[<<Target Dimension 2>>]

...

Root

(

[<<Target Dimension 1N>>]

[Measures].[<<Target Measure>>]

)

End

FORMAT_STRING = "Percent";

Calculated Member

I'm trying to add a calculated member to my SSAS 2005 cube. I can calculate what I want using MDX, but I'm having trouble converting my MDX query to the something SSAS understands as a calulated measure.

This is my MDX query.

Working MDX

SELECT

{([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)} ON COLUMNS

,{[Employee].[Emp Full Name].allmembers}ON ROWS

FROM [Employee Scorecard]

WHERE ([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34])

Here is my calculated member

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

[EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

I would also like to add a similar member where the state name is not Texas. How would I do that as well?

I changed my scope. I forgot the "This =" . But it still dosn't work.

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

This = ([EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]);

end scope;

|||

It appears that you want to sum the activity count for a set of activity turn times, filtered by a specific segmentation and special activity. I would start off by creating a base measure (which you could set to invisible if you want)

Code Snippet

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

SUM([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34],

([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

VISIBLE = 1;

Then creating a TX specific version

Code Snippet

Create MEMBER CurrrentCube.Measures.[TX Docs Ready <=10 Days]

(Measures.[Docs Ready <=10 Days],[Geography].[State Name].&[TEXAS])

Then the non TX can simply be the total, minus the TX measure. You could calculate this by summing a set of all states except TX, but this should be faster

Code Snippet

Create MEMBER CurrrentCube.Measures.[Not TX Docs Ready <=10 Days]

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

- Measures.[TX Docs Ready <=10 Days]

|||

I see what your saying. However, I think that by using "Scope" the code would be more readable. I've been playing with it and this is what I have so far.

I can get this to work and it returns values. But it's not exactly what I want.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS ([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

VISIBLE = 1;

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = [EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

This = [EN To Activity Turn Time].[Turn Time Name].&[2];

end scope;

What I really want, is something like this

Code Snippet

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

But when I run this code all I get is empty values.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

What am I doing wrong with this scope?

|||

The scope statement is not about readability, it is about restricting the sbucube over which a calculation is performed. I am pretty sure that the second assignment you are doing will just override the previous one that you did for Texas and by summing the measure that you are scoping over, it could be trying to recurse over itself. What I think you want is something more like the following. Assuming that you want to sum over Activity 2-32 if it's not Texas and 2-34 if it is Texas.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

scope ([Geography].[State Name].&[TEXAS]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]});

end scope;

|||

It dosn't like the aggregate. I've tried both of these and the results are the same as if I never did the scope. I've also tried Sum(Aggregate()) and Sum().

Code Snippet

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].currentmember);

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

I feel like I'm really really close but I'm clearly missing an important concept.|||

I'm not exactly sure what the problem is. We are making it hard for the calc engine, telling it that this measure is the sum of itself. Normally I would do an approach like the following.

Code Snippet

-- this is the default calc

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS SUM({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)),

VISIBLE = 1;

-- override the calculation for TX

scope ([Geography].[State Name].&[TEXAS]);

([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]) = SUM(

{[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]}

,([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

));

end scope;

If this does not work, could you post a simple example of what results you are seeing and what you were expecting.

sql

calculated member

Hi,

I created the calculated member ( mkdn dollars/ sale dollars) in the cube but when I see the variance(Prior year Chg), it seems it calculates the mkdn dollars - prior year chg / sales dollars -prior year chg

Here are samples for better understanding:

==================================================================

1st week - 2008

current period prior period prior period chg

Mkdn % 48.3 % 46.99% 79.82 %

mkdn dollars 524,725 490, 198 34,526

sales dollars 1086,433 1043,178 43,254

-- Prior Year Chg

([Time Calculations].[Prior Year Chg]=

[Time Calculations].&[Current Period] - [Time Calculations].[Prior Year]);

==================================================================

If I want to get prior priod chg ( 48.3 -46.99) based on mkdn % then I should I change the prior year chg calc ?

Also, I tried to create named calcuation in DSV for mkdn % but it returns a wrong value. (why does not return a right

value? something missing?) This is the expression I created as under:

==================================================================

CASE
WHEN [Net_Sale_Dollars] = 0 THEN NULL
ELSE [POS_Mrkdn_Dollars]/[Net_Sale_Dollars]
END

==================================================================

Please give me any comments

Thank you in advance.

You already tried to use the function ParalellPeriod and PeriodsToDate?

regards!

|||

Check the order of calculations in the cube MDX script, and make sure that [Mkdn %] precedes [Prior Year Chg], so that [Mkdn %] gets computed prior to [Prior Year Chg].

"Also, I tried to create named calcuation in DSV for mkdn %" - but what measure aggregation function are you applying to this, since it is a ratio?

|||

If I put mkdn % in the measure scope then it gives an error as follow:

Error 1 A set has been encountered that cannot contain calculated members. 0 0

[mkdn %-] calculation is as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[ Mkdn %]

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

calculated member can't be contained in time calculation?

And you are right, since this measure is ratio I can't you any aggregate function. ( I'm so stupid )

Please give me any comments.

Thanks.

|||"Error 1 A set has been encountered that cannot contain calculated members" - if you received this error when dropping the time calculation in the top (subcube) filter in the BIDS cube browser, try placing it in the bottom (where) filter section instead, and see if you still get the error.|||

This error pops up when I put ' mkdn % ' calc measure in the mdx scripts (scope statement which shows all measures )

I also tried to drop the time calc in bottom filter section but it didn't work.

my question is calculated member can't be contained in measure scope in mdx script?

(it doesn't make sense to me)

What else do I need to try to correct this? Please give me a direction.

Thanks always Deepak

|||That's a different issue - calculated member definitions have global scope, so they shouldn't be placed inside scoped calculations; but subsequent assignments to such members can be scoped.|||

Okay. you mean calculated member definitions have global scope, these measrues can't be contained in scoped calculation.

If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?

And this is the only way to calculate this scenario? or is there any way to use time calculation(shell dimension) for the calculated measures?

|||"If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?" - could you explain what you mean in more detail? For example, the calculations generated by the Time Intelligence Wizard are added to the cube MDX script, so I'm not sure what distinction you're drawing between time calculations and MDX scripts. Typically a calculated member will be created (Create Member ..), and then scoped assignment(s) will be used to apply calculations to the appropriate subcube(s) containing the member.|||

my mdx script is exactly same as you described.

And I manually created time calc as under:

/* calculated member */

create member prior year

create member prior year chg

create member mdkn %

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

.....

/* scope for specific measures */

>> when I added calculated member ( mkdn %) then it gave an error and as you said since calc members have global scope, I can't add calc member in scoped calc.

scope {

[measures].[sale dollars]

....

}

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);

-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

This time calc works fine except all calculated members (current period and prior period work properly but prior year chg doesn't ; i.e. [mkdn % - prior year chg] computes each measure in current period and prior period and then recalculate the calcuated members as I explained at first. )

Hope you can get better understanding and please give me some comments.

Thanks.

|||

I was referring to calculated member creation, not to assignment - so see if you can repeat the scoped assignment separately for [mdkn %] alone, to resolve the error, like:

Code Snippet

/* scope for [mdkn %] measure */

scope([measures].[mdkn %]);

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);

-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

|||

I put (mkdn % + all calc members) scope seperately outside the time calc sope, and repeated time calc scripts( prior year and prior year chg) with calculated members, then it works

Thanks a lot !!!!

You Save me

Calculated member

Hello (sory i don( speack very well english)
I want to build a new calculated member in my cube, but i don't know MDX .
I want the same result that this SQL request :
--
SELECT count(Distinct Code)
FROM dbo.Fact_Project
Where
ID_Time=4 and ID_Currency=2 and ID_CBU=31 and Montant<>0

ID_Time, ID_Currency and ID_CBU are dimension member.
Is it possible to find the same result in MDX.
Thank you

Guillaume

Hi Guillaume,

I think what you actually want to do is not create a calculated member, but create a real measure on your Code column with aggregation type 'Distinct Count'. This will display the distinct count of the number of values in the Code column as a measure.

HTH,

Chris

|||Hi Chris
Thank you for your solution, i created a new mesure in a tree view who use Distinct Count. But i have a new question, is it possible to put a filter in this mesures because i don't want the Montant (amount) equals to 0.
thx|||

Probably the easiest way of doing this would be to add a boolean field to your fact table (or create a named calculation in your dsv) which returned true if Montant=0 and false otherwise. You could then create a new dimension in your cube with one attribute and two members: Montant=0 and Montant<>0. Selecting the first of these members in your query would then allow you to filter out all transactions where Montant=0.

HTH,

Chris

Calculated Member

Hello All.
I am using AS 2000 and would like to create a calculated member that shows a
calculated value back to a particular month regardless of year.
Here is an example.
I am looking at data for the month of July, 2005 and I want my calculated
member to show a calculated value for the month ofo December, 2004.
I would like this to be not dependant on the year. So if I am looking at
data for July, 2004, it will calculate the member for December of 2003. The
month that I am looking for backwards from where I am is always going to be
December of the previous year.
I hope I am being clear about this.
Bill
if you have only year and month levels and if the december month is the last
month of the year this formula will return the last december:
time.currentmember.parent.prevmember.lastchild
you can create a more generic formula (using ancestor function) and there is
other way to do this job (likeusing parrallelperiod, closingperiod...).
"Bill Walo" <williamwalo@.nvgeneral.com> wrote in message
news:A105A356-BA58-4726-806F-FFFBE2E72752@.microsoft.com...
> Hello All.
> I am using AS 2000 and would like to create a calculated member that shows
> a
> calculated value back to a particular month regardless of year.
> Here is an example.
> I am looking at data for the month of July, 2005 and I want my calculated
> member to show a calculated value for the month ofo December, 2004.
> I would like this to be not dependant on the year. So if I am looking at
> data for July, 2004, it will calculate the member for December of 2003.
> The
> month that I am looking for backwards from where I am is always going to
> be
> December of the previous year.
> I hope I am being clear about this.
> Bill
>

Calculated Member

Hello All.
I am using AS 2000 and would like to create a calculated member that shows a
calculated value back to a particular month regardless of year.
Here is an example.
I am looking at data for the month of July, 2005 and I want my calculated
member to show a calculated value for the month ofo December, 2004.
I would like this to be not dependant on the year. So if I am looking at
data for July, 2004, it will calculate the member for December of 2003. The
month that I am looking for backwards from where I am is always going to be
December of the previous year.
I hope I am being clear about this.
Billif you have only year and month levels and if the december month is the last
month of the year this formula will return the last december:
time.currentmember.parent.prevmember.lastchild
you can create a more generic formula (using ancestor function) and there is
other way to do this job (likeusing parrallelperiod, closingperiod...).
"Bill Walo" <williamwalo@.nvgeneral.com> wrote in message
news:A105A356-BA58-4726-806F-FFFBE2E72752@.microsoft.com...
> Hello All.
> I am using AS 2000 and would like to create a calculated member that shows
> a
> calculated value back to a particular month regardless of year.
> Here is an example.
> I am looking at data for the month of July, 2005 and I want my calculated
> member to show a calculated value for the month ofo December, 2004.
> I would like this to be not dependant on the year. So if I am looking at
> data for July, 2004, it will calculate the member for December of 2003.
> The
> month that I am looking for backwards from where I am is always going to
> be
> December of the previous year.
> I hope I am being clear about this.
> Bill
>

Calculated Measures using AMO

Hi,

Stuck again......

I am creating Dimensions, Cubes, MeasureGroups, Measures, Partitions using AMO dynamically. What I need now is to create Calculated Measures using AMO, which would use some aggregation between one or two measures of the cube.

I did not find any object that would enable me to work with Calculated Measures in AMO. It would be really helpful if I can get some code snippets on this.

Thanks and Regards

Vijay R

MdxScripts is what you're looking for. Here's a short discussion on the topic which I think will help.

Multiple MdxScripts and Commands in Cubes

A cube can contain multiple MdxScipts, however only one is active at a given time.Which script is active can be controlled by setting the DefaultScript>false</DefaultScript> XMLA tag.(The default value of <DefaultScript> is true so omitting this tag is equivalent to setting it to true.However, since only one script can be the default, the first script with <DefaultScript> omitted or explicitly set to true wins and all other scripts considered non-default regardless of their setting.This means that the first script encountered without <DefaultScript>false</DefaultScript> is the real default script.)The UI will only work with the active script.

Each MdxScript can contain multiple Command objects, all of which are active (provided the script is active).Each Command object can contain an arbitrary number of calculation statements within its text body.Normally a cube will only contain one MdxScript with one Command which contains all the calculations.If you have multiple commands in the active script, the UI will merge them into one when they are displayed and if you make any changes in the UI, they will be saved as one command.(The value of having multiple commands is that it is possible for some to be completely unparsable and yet the others still work.This is useful in migration since it preserves this capability.However, for new cubes it is recommended that your calculations should at least parse, and the UI will help ensure this, so multiple commands is of little value.)

The CalculationProperties collection contains special XMLA properties of calculations contained in Command objects. Such properties are associated with script elements by a CalculationReference which matches the name used in the creation of a set, calculated member, or calc cell in the script.CalculationProperties are generally just used for Display Folder, Associated Measure Group, and Translation as these three properties cannot be set in the script itself. (In the UI you can see Display Folder and Associated Measure Group calculation properties in the Calculation Properties dialog which can be launched from the Calculations tab of the cube editor.)

|||

HI,

Thanks a lot, that gave some insight, and helped me investigate further.

This leads to the obvious question, What MDX should I write in the command, so that it would create a calculated measure, with some formula. meaning what would be the general syntax of the MDX that we would have to write.

I created a calculated measure from the BI studio and then used AMO to get the cube object, I then browsed the cube object: Cube > MDXScripts > Commands

Command Text was: string CmdTxt =
CREATE MEMBER CURRENTCUBE.[MEASURES].CalTestMeasure
AS [Measures].[M1] * 5,
FORE_COLOR = 6776628 /*R=52, G=103, B=103*/,
VISIBLE = 1;

Now I suppose this code must do the same programmatically?

AMO.MdxScript MdxScript = NewCube.MdxScripts.Add("test", "test");
AMO.Command comd = new Microsoft.AnalysisServices.Command(CmdTxt);

MdxScript.Commands.Add(comd);

I will try this with some more modifications, but this is surely going to create a problem in the future. Now imagine I want to modify the calculated measures, their calculations programmatically ..etc, What happenes is that all the calculated measures are in one huge string of MDX!!!!!!!, How do i work on it? string manipulation? that makes it very very error prone.

so we have no other way to do this? other than using MDX scripts?

|||

This is the old script versus object model dilema. A script is generally easier for people to read and maintain while an object model is easier to write code against. When you use the UI, all your calculations are placed in a single Command object so that it can be presented as one nice, editable script (there's a toolbar button that allows you to switch between form view and script view for your calculations). If you use a single command containing the entire calculation script as the UI does, then you will need to parse the string to find individual calculations. Regular expressions can help here, but it gets real hairy if you start dealing with invalid calculations in the script. However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript and no parsing will be necessary on your part. Just be sure not to make any changes in the UI or the UI will save them back into a single Command object containing all of the calculations.

Also, don't forget the "Calculate" command. Without this no aggregation of values will happen in your cube and leaf cells and cells you have explicitly assigned values to will have any data.

|||

Hi Matt,

This is exactly what I was looking for,

Quote > "......However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript......"

1. How do we create calculations from code?

2. How and where to use the "Calculate" command that you specified?

Please provide a sample line of code or a link to the same, It would help me to understand better.

Thanks a lot for all your help.

Regards

Vijay R

|||

1. Here's a very simple code sample:

Server srv = new Server();

srv.Connect( "localhost" );

Database db = srv.Databases[0];

Cube cb = db.Cubes[0];

MdxScript script = cb.MdxScripts[0];

// Append calc to existing command

script.Commands[0].Text = script.Commands[0].Text + "\nCREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;";

// Create calc in new command

script.Commands.Add( new Command( "CREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;" ) );

script.Update();

srv.Dispose();

2. The Calculate command is added to the default script automatically be the UI and assumed by the engine if no script is present. This command is normally the very first command in the script and it is what causes the aggregation of leaf cells into non-leaf cells. (See http://msdn2.microsoft.com/en-us/library/ms145565.aspx)

|||

Whether or not this is intended to be public, I don't know... but it works...

new Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c)

That returns a Scripts object which gets you everything you need, I believe. I think you need a reference to C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Design.DLL

So you might play around with that. Sure would be nice if that were part of AMO, huh!

sql

Calculated measures on Analysis server

Hi,

Is there anyway I can process calculated measures of the cube on server? As of now its getting processed on client and its taking lot of client memory. We have got a good configuration server. Is there anyway I can configure to get this processing done on OLAP server?

Any help/tip will be appreciated

Thanks,
SP

Are you alluding to MDX query processing, and are you using AS 2000 or AS 2005?|||

Hi Deepak,

We are usin AS2000. What exactly u mean by alluding by "alluding to MDX query processing"?

Thanks

|||

Just wanted to confirm that you weren't referring to cube processing time, rather than to query response time.

With AS 2000, you can (to some extent) increase server-side query execution by changing connection string parameters:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

Microsoft SQL Server 2000 Analysis Services Performance Guide

Published: June 1, 2003

By Carl Rabeler, Len Wyatt, Dave Wickert

Summary: This paper describes techniques you can use to optimize query responsiveness and processing performance in Microsoft? SQL Server? 2000 Analysis Services.

...

The Execution Location and Default Isolation Mode properties are always used together.

The Execution Location property controls whether a query is executed on the client or on the Analysis server. By default, the execution of the query is split between the client and the server in order to distribute the processing load across multiple computers. Using an Execution Location setting of 3 in the connection string causes most queries to be executed on the Analysis server. This setting also reduces the amount of data that is returned to the client over a slow network connection. In most cases, only the final result is returned to the client, and the client processor resources are not used to help resolve the query. The value of the Execution Location property is set in the connection string when a session is established. It can be changed during the session. However, every client using this setting adds to the load on the Analysis server.

The Default Isolation Mode property controls the refreshing of the cache on the client. Using a Default Isolation Mode setting of 1 causes the cache to be invalidated whenever a query statement is executed. When this setting is used in conjunction with the Execution Location setting of 3, you can ensure that most queries are resolved on the Analysis server rather than at the client. By default, PTS will use data in the local cache to resolve queries when possible, which conflicts with the Execution Location setting if you are attempting to execute as many queries as possible on the Analysis server. The value of the Default Isolation Mode property is set in the connection string when a session is established. This value can also be changed during a session.

|||

Hi Deepak,

Thanks a lot,

This is definitely goigng to help us in our approach

Regards,

Calculated measures on Analysis server

Hi,

Any help/tip will be appreciated

Thanks,
SP

Are you alluding to MDX query processing, and are you using AS 2000 or AS 2005?|||

Hi Deepak,

We are usin AS2000. What exactly u mean by alluding by "alluding to MDX query processing"?

Thanks

|||

Just wanted to confirm that you weren't referring to cube processing time, rather than to query response time.

With AS 2000, you can (to some extent) increase server-side query execution by changing connection string parameters:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

Microsoft SQL Server 2000 Analysis Services Performance Guide

Published: June 1, 2003

By Carl Rabeler, Len Wyatt, Dave Wickert

Summary: This paper describes techniques you can use to optimize query responsiveness and processing performance in Microsoft? SQL Server? 2000 Analysis Services.

...

The Execution Location and Default Isolation Mode properties are always used together.

|||

Hi Deepak,

Thanks a lot,

This is definitely goigng to help us in our approach

Regards,

Calculated Measures not showing in SSRS.

Hi,
I have two cubes , One is fairly large Cube and the other one is Smaller one.
When I try to Use SSRS to report on these two cubes, i could see the calculated measures only in the Smaller Cube and there are no such things in the Large Cube.
I could Report on the Large Cube with base Measures.
I see the below message at the end of the Measures List.
"More Measures Exists than can Be shown."

Am i missing something?
Can anyone point me to some clue .

Thankyou
Vidya

Hi,

did you reprocess the large cube after,adding the calculated measures?

|||

I have not used SSRS yet, but I came accross a similar issue when I was creating a cube.

When you create a cube on SSAS, it automatically creates a script called "CALCULATE." You can view it on Caluculation tab on the cube design. It contains the following code:

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

CALCULATE

If this script is modified or deleted, you may not see your measures correctly in your cube.

|||Thankyou for the Reply,
But I Have that CALCULATE untouched.
Another intresting note is that i can see all the calculated measures with the values from EXCEL 2007.
When i try querying the cube for the Calculated Measure then i am getting the values ; Only problem is that i am not able to see them through SSRS.
I have tried re-processing the Cube but no luck.
Thankyou
vidya

Calculated measure's MDX formula in (regular) Non-calculated measure

Hi,

This is sort of an extension to my previous question: converting seconds to HH:MM:SS format. I got the MDX formula for that from deepak. but for that i have to create a Calculated measure!!!!

I already have the measures which display seconds, now i have to create a calculated measure which will display the seconds in HH:MM:SS. but I dont want the extra seconds measure to be displayed. I know i can make it visible=false. but this is cumbersome for me because all this is done programmatically using AMO in a program.

so is there a way to provide the MDX formula in the regaular measure itself without having to create a calculated measure.

Thus display/convert the seconds to the HH:MM:SS format in the original measure?

Regards

I forgot one more thing, can we use the "MeasureExpression" and "FormatString" properties of the measure itself? I tried puttin the MDX formula here but it gives a error.|||

Using the MeasureExpression property is the preferred technique to accomplish what you want. It works for the cases I have used it in. What error do you receive, and what is the MDX you're providing as an expression?

PGoldy

|||Only multiplication and division are allowed for measure expression.|||

Hi,

Thanks for the reply.

Here is the scenario with the MDX formula:

Consider that I have a regular measure M1. The measure M1 contains seconds as data. (eg: 400 seconds, 1000 seconds..etc.) currently what I do is whenever I have a measure which contains seconds and has to be displayed in the HH:MM:SS format:

1. I rename the M1 measure as "_M1" and make it visible=false,
2. Then I create a calulated measure with the name M1 and with the formula TimeSerial(0,0,Measures.[_M1]) and FormatString as 'hh:mm:ss'
3. Now the calculated measure M1 contains the the values of seconds formatted to the HH:MM:SS format. example: (TimeSerial(0,0, 37895) = 10:37:35 ; TimeSerial(0,0,1023) = 00:17:03)

What I require is not to create the extra calculated measure M1, but use the TimeSerial(0,0,MeasureName) formula in the regular measure M1. When I put the TimeSerial(0,0,MeasureName) formula in the MDXExpression property and the FormatString as 'hh:mm:ss', I get the following error : "The Cube xxxx cannot be saved because of the following errors: Errors in the metadata manager. The measure expression of the M1 measure is not of the form [measure1]*[measure2] or [measure1]/[measure2]"

Is there a way out for this? what other alternative is possible?

Regards

Thursday, March 29, 2012

Calculated Member help

Calculated Member Formatting

Calculated member for the current month

Calculated member for the current month

Calculated member for the current month

Tuesday, March 27, 2012

Calculated member for Grand Total...

Calculated member for Grand Total...

Calculated Member banding

Calculated Member banding

Calculated member and Calculated Cells

Calculated member and Calculated Cells

Calculated member (summing quarters)

Calculated member (summing quarters)

Calculated Member (beginner)

Calculated member - please help (SQL Server 2000)

Calculated member - Count function

Calculated Member - #VALUE! in the Grand Total

Calculated Member

Calculated Member

calculated member

Calculated member

Calculated Member

Calculated Member

Calculated Measures using AMO

Multiple MdxScripts and Commands in Cubes

Calculated measures on Analysis server

Microsoft SQL Server 2000 Analysis Services Performance Guide

Calculated measures on Analysis server

Microsoft SQL Server 2000 Analysis Services Performance Guide

Calculated Measures not showing in SSRS.

Calculated measure's MDX formula in (regular) Non-calculated measure

Bulkload XML Code

Blog Archive

About Me