AS 2000, Excel 2003 and calulated measures

Hi

I have created a calculated measure - which when i view in the Analysis Manager - Browse it displays what I need.

In my Excel Pivot Table I cannot see the field when I Show Field List? is it possible to have calc members in Excel 2003?

however, my initial problem is this (and I am hoping someone can come up with a better solution):

I have a fact table that is simply number of hours worked per month and linked to dimensions such as the customer. Each Customer has a budgeted time (which is on the customer dimension) - I need to include this budget.

I have a separate cube that is the detail of the budget and I have tried creating a virtual cube - the problem with this is the budget is only visible if I use dimensions from that cube and the hours are only visible when I use dimensions from that cube - I would liek to see them together. (the dimensions in both cubes are identical)

Anyone?

Steve

Sounds like you have a bunch of private dimensions in your cubes. Its been a while since I worked on 2K, but i think global dimensions (dimensions that can be shared in virtual cubes) are called "shared dimensions". If you used the wizard to create the dimensions and cubes I think it defaults to adding dimensions as private.

|||

That may solve the virtual cube issue - thanks.

Any idea why I cannot see a calculated member in Excel while I can see it in the MMC browser?

Thanks

|||

Shared dimensions and virtual cubes work a treat - thanks.

Apparently, a calculated measure that is based on a dimension table does not appear in Excel 2003!!

Aritmectic Operator in Formula?

Hi all,

this formula doesn't work as calculated column but WHY?

Column [MAIN]: VARCHAR(150)

VALUE : 'blabla - zobzob'

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])) WORKS and return 'blabla -'

BUT

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])-2) DOESN'T WORK in a Formula BUT well in a select clause...

Any Idea?

Thanks

In what way does it 'not work'...?

It seems to work ok... (SQL Server 2000)

create table #x (main varchar(20) not null)

insert #x select 'blabla - zobzob'

alter table #x add b as SUBSTRING(MAIN,1,CHARINDEX('-',MAIN)-2)

select * from #x

drop table #x

main b
-- --
blabla - zobzob blabla

/Kenneth

|||

Yes indeed, it works with a sql script but NOT manually in SQL Server 2000... Very Strange...

Tks a lot

|||

Hmm.. what do you mean by 'manually'..?

In my mind T-SQL code in QA is as 'manual' as you can get..?

/Kenneth

|||With the editor provided in SQL Server 2000...|||

You mean Enterprise Manager?

I'd suggest as a solution that you don't use it for other purposes than viewing.
For one thing it doesn't allow you any transactional control when you perform write operations, and some things it does in an awkward way, which adds up to that it takes unnecessary long time and/or large amount of resources to complete.

Make the wrong choice in EM and chances increase that you're looking for the latest good backup, instead of just typing ROLLBACK in QA, when something unexpected happened with the latest change. This ability is due to the fact that you can start your scripts with BEGIN TRAN (don't forget it) before actually performing the change. This is something that EM doesn't give you. =:o/

EM is imo not a fitting tool for database change management. (perhaps with the exception of user and roles management)

Do all your (data/schema) changes in Query Analyzer by controlled scripts instead.

/Kenneth

Arithmetic Operation with Dates

Hi I'm trying to get the difference between two dates. I need to create a Calculated member to show it. I'm using this formula

PeriodDays = LastDate - FirstDate

For example, I have a Time Dimension, I choose two dates:

FirstDate = 07/04/2006 it's identity is 300

LastDate = 15/04/2006 it's identity is 308

PeriodDays = 308 - 300 = 8 this value I need to show in a Calculated Member

The Time dimension has three levels

Year, month and date

What happens when I choose only a month?

Thanks

Dear,

I dont understand onething u are talking about date diffrence and u are giving an identity example.

Pls explain in detail.

Regards

Sufian

|||

Hi, ohh, I will try to explain again

I need to get how many days are between two dates, For example, I choose two dates from my Time dimension

Date1 = 07/04/2006

Date2 = 15/04/2006

Days = Date2 - Date1 = 15/04/2006 - 07/04/2006 = 8 days and this value I need to show in a Calculated member

Is it possible?

My time dimension has three levels

Year, Month and day

Can I do this operation, with the month level?

Regards

Thanks