Named Sets

Hi,

I defined 2 Named Sets, so as to filter my calculated members:

NamedSet1:

{([Dimension1].[MyLevel1].&[X]),([Dimension1].[MyLevel1].&[Y']),([Dimension1].[MyLevel1].&[Z'])}

NamedSet2:

{([Dimension2].[MyLevel2].&[A']),([Dimension2].[MyLevel2].&[B']),([Dimension2].[MyLevel2].&[C'])}

It works well, but I didn't manage to use them in the same expression like

Sum(Crossjoin([NamedSet1],[NamedSet2], [Measures].[TurnOver]), the result is wrong.

Does anybody know how to do that ?

Regards

Ayzan

I wrote a short sample for you that works with Adventure Works. If this doesn't help, please, explain your scenario better, especially your calculations, expected result and actual result.

with
set set1 as '{ [Product].[Category].&[1], [Product].[Category].&[3] }'
set set2 as '{[Product].[Color].&[Black], [Product].[Color].&[White]}'
member x as 'sum(crossjoin(set1,set2), measures.[Customer Count])'
select x on 0 from [Adventure Works]

NAMED SET/ CALCULATED FIELDS

Hi,

Can I use the following MDX in named set/ calculated fields:

with member [MEASURES].[thisistheratio] as

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%"

Select {[Measures].[Activity Count],

[MEASURES].[thisistheratio]} on columns,

{[Direction].[Direction].mEMBERS} on rows

FROM [CASEACTIVITYDETAIL]

how do I use it? Is there a particular syntax if you apply it inside named sets/calculated fields?

thanks a lot!

cherriesh wrote:

Hi,

Can I use the following MDX in named set/ calculated fields:

with member [MEASURES].[thisistheratio] as

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%"

Select {[Measures].[Activity Count],

[MEASURES].[thisistheratio]} on columns,

{[Direction].[Direction].mEMBERS} on rows

FROM [CASEACTIVITYDETAIL]

how do I use it? Is there a particular syntax if you apply it inside named sets/calculated fields?

thanks a lot!

What you have there is an MDX query, you cannot put the whole thing inside a calculated member/set. I am guessing that what you probably want to do is to create a calculated measure for this ratio. If this is the case you could do either of the following.

If you open up your cube and click on the calculations tab, about the 4th button along the top will be an "New Calculated Member" button. When you click on this it brings up a form to let you add a new calculated member and you would fill out the following properties:

Name: [thisistheratio]

Parent Hierarchy: MEASURES

Expression:

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count])

Format String: "0%"

Alternatively you could switch to the script view and just paste in:

CREATE MEMBER CURRENTCUBE.Measure.[thisistheratio]

AS

([Direction].[Direction].&[1],[Measures].[Activity Count])/

([Direction].[Direction].[ALL],[Measures].[Activity Count]),

FORMAT="0%";

Named Set Question

I want to create a named set that includes a list of some of our major customers. As part of the set, I also want to add a calculated member that represents a subtotal of customers. For example:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores]}

represents a named set of three customers. In the named set, I want to add Smith Stores and Williams Stores together to create a subtotal called Midwest Group. So, my revised set would look like:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores], [Customer].[Customer Hierarchy].[Store].&[Midwest Group]}.

First, can I create a calculation such as [Customer].[Customer Hierarchy].[Store].&[Smith Stores] + [Customer].[Customer Hierarchy].[Store].&[Williams Stores] AS 'Midwest Group' and add it directly to the named set. Or can I create this as a calculated member and add this member to the named set.

Is what I would like to do possible with named sets?

Thank you.

David

You will have to first create calculated member and then add it to the set. Alternative approach is to add new attribute to the Customer dimension with members Major/Minor. This way you will be able to get your Major customer by slicing on that attribute. You also will be able to do any grouping you want. This is more scalable approach once your company grows and you have more than 3 major customers |||

Mosha, when adding the calculated member to the named set, are there any restrictions. For example, does the calculated member need to be in the same hierarchy as the other members of the set. Also, are there any syntax differences when adding the calculated member. For example, is it treated like any other tuple and I would just separate it from the other members with a comma? Thank you.

David

|||There are no special restrictions. The calculated member will have to be from the same hierarchy as other members, but this is not special requirements for the calculated members, all tuples in the set must have same dimensionality (hierarchility). Syntax is the same - if you enumerate the members, than use comma as separator.|||

Mosha, Thank you for the answer. One problem remains. I get the named set working correctly when I run an MDX query in Management Studio. I have the named set appearing in the row axis and everything displays correctly. When I use the same named set in an Excel 2007 pivot table, I get an error message -- "A set has been encountered that cannot contain calculated members". I do have "show calculated members from OLAP Server" box checked in Pivot Table Options in Excel. Is this a bug with Excel 2007?

David

|||I am not sure what exactly you do in Excel - but it is possible that it is limitation of either Excel or AS (the error message is actually from AS, not from Excel).|||

Mosha,

Is it unusual that an MDX query would work correctly in SQL Server Management Studio but yield an error in a front end client?

David

|||Well, obviously the query that you wrote in Management Studio and the query that Excel generated are different queries.

My Problem with calculated field

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date

I use SQL Server 2005 Express

hi,

Samer Selo wrote:

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date

I use SQL Server 2005 Express

you can not directly define a computed column in the object's DDL for this kind of task, but you are not required as well, as this value is dynamic and dependent on ordering and the like..

you can of course dynamically get this value in a "running total" query like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.myTB (

Id int NOT NULL IDENTITY PRIMARY KEY,

Date datetime NOT NULL,

Amount_Debit decimal ( 18, 4 ) NOT NULL DEFAULT 0,

Amount_Credit decimal ( 18, 4 ) NOT NULL DEFAULT 0,

Explanation varchar(10) NULL

);

GO

INSERT INTO dbo.myTB VALUES ('20060101', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060101', 10, 0, '' );

INSERT INTO dbo.myTB VALUES ('20060102', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060103', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060105', 15, 0, '' );

GO

SELECT t.Id, t.Date, t.Amount_Credit, t.Amount_Debit, (SUM(t2.Amount_Credit) - SUM(t2.Amount_Debit)) AS [Balance]

FROM dbo.myTB t

CROSS JOIN dbo.myTB t2

WHERE t2.Id <= t.Id

GROUP BY t.Id, t.Date, t.Amount_Credit, t.Amount_Debit

ORDER BY t.Id;

GO

DROP TABLE dbo.myTB;

--<--

Id Date Amount_Credit Amount_Debit Balance

-- -- -- -- -

1 2006-01-01 00:00:00.000 10.0000 0.0000 10.0000

2 2006-01-01 00:00:00.000 0.0000 10.0000 0.0000

3 2006-01-02 00:00:00.000 10.0000 0.0000 10.0000

4 2006-01-03 00:00:00.000 10.0000 0.0000 20.0000

5 2006-01-05 00:00:00.000 0.0000 15.0000 5.0000

regards

|||Thank You I'll Try that