How to create Calculations that do not change when dimensions are added.

I would like to calculate the total business done for a week irrespective of the dimension used and I have created a member ([Measures].ForAWeekOfYear) for the same. The Percent1 member calculates the percentage of business done by a company.

with member [Measures].ForAWeekOfYear as
sum( [Dimdate].[Week Of Year] , [Measures].[TotalBusiness] )
member Measures.Percent1 as [Measures].[TotalBusiness]/[Measures].ForAWeekOfYear
select { [Measures].ForAWeekOfYear , ( Measures.Percent1 ) } on 0,
{ [Dim Customer].[Company].members * [Dimdate].[Week Of Year].Members} on 1
from [Account]

The problem I have if I use the above MDX expression is that the calculation gets split based on the Customer dimension and I assume that this is how the query should work. I assume I have to use the SCOPE statement, but not sure how to use it.

Please note that I am trying to execute this in Management console and not within a cube.

If you don't want your calculation to depend on Customer dimension, you should include Root(Customer). If you want to abstract from all of the dimensions in the cube - use Root().

HTH,

Mosha (http://www.mosha.com/msolap)

How to create a assembly

Hi,

I have one ClassLibrary three member functions.I have the dll for this...Can some one please tell me how to create a Assembly in .NET.

I need to create a simple private assembly with the dll that i have.

Thanks

Did you check out the CLR programmability samples? See below link:

http://msdn2.microsoft.com/en-us/library/ms160894.aspx

You can use CREATE ASSEMBLY DDL to create assembly in SQL Server or use the Visual Studio GUI. See Books Online for more details.

|||

I am guessing that this is for SQL Server.

create assembly AssemblyName
from '<path>\AssemblyName.dll'
go

--Then you can declare the objects within using syntax like:
CREATE FUNCTION [dbo].[DeployName](@.ParameterName [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [AssemblyName].[RootNamespace.ClassName].[FunctionName]
GO

Or do you mean what you have to do to build the code to make the assembly ready for SQL Server?

How to cotrol format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?