Named set using LastPeriods (but not all of them)

To get the current period we have a named set Current Period. To get the last 13 periods we have a named set Last 13 Periods. We can aslo detect period that are not period 14.

Period 14 is an oddball period and I want to skip over it i.e Last 12 Periods will aways show data for the previuous 12 periods that were not periods 14s.

How do we link all of this to give the last 13 periods which were not a period 14?

Many thanks this is driving me bonkers.
Mark.

Code Snippet

CURRENT PERIOD
{
Filter([Alt Week Calendar].[Accounting Period].Members, [Alt Week Calendar].Currentmember.Properties("Period14")="1")
}

LAST 13 PERIODS
{LastPeriods
(13,Filter([Alt Week Calendar].[Accounting Period].Members,[Alt Week Calendar].Currentmember.Properties("Current Period")<>"0").Item(0))
}

NOT PERIOD 14
{
Filter([Alt Week Calendar].[Accounting Period].Members,[Accounting Week Calendar].Currentmember.Properties("Period14")<>"1")
}

Hi Mark,

Here's an example from Adventure Works which returns the last three days that aren't weekends:

Code Snippet

with member measures.demo as

generate(

tail(

exists(

[Date].[Date].currentmember.lag(5):[Date].[Date].currentmember

, [Date].[Day Name].&[2]:[Date].[Day Name].&[6])

,3)

, [Date].[Date].currentmember.name + " (" + [Date].[Date].currentmember.properties("Day Name") + ")", ", ")

select measures.demo on 0,

[Date].[Date].[Date].members on 1

from [Adventure Works]

What I'm doing here is finding the last five days, using an exists to get only the days from that set which are weekdays, then finding the last three days in the resulting set. The outermost Generate() is simply there to display the results of the expression in an easy-to-read way.

HTH,

Chris

named set questions

As I understand it, a named set is not processed until it is needed.

Once these are processed are they cached for use by other client requests?

If there are Aggregations set up in the cube will this trigger the named sets be processed right away with the cube?

I assume that if a named set is too big there could be loss in performance, is there a way to hold down the size of a named set?

here are some examples of named sets i've created using some calculated members. perhaps there is a better way of doing this?

*************** Calculated Members***************************

CREATE MEMBER CURRENTCUBE.[MEASURES].[HP Plus Addl Billing]

AS aggregate([PREP CONTROL HDR].[Bill Formats].&[19],[Measures].[Billed Sales Amount]),

FORMAT_STRING = "Currency",

VISIBLE = 1;

2nd calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[Prep Billing]

AS AGGREGATE(EXCEPT([PREP CONTROL HDR].[Bill Formats].[Bill Formats] ,{[PREP CONTROL HDR].[Bill Formats].&[19],[PREP CONTROL HDR].[Bill Formats].&[7]}),[Measures].[Billed Sales Amount]),

FORMAT_STRING = "Currency",

VISIBLE = 1;

*******************named sets************************************

CREATE SET CURRENTCUBE.[CTP Customers]

AS FILTER([CUSTOMER JOB].[Title Name].[Title Name].members,([Job Complete Date].[Calendar Full],[Measures].[HP Plus Addl Billing])> 0 );

CREATE SET CURRENTCUBE.[Prep Customers]

AS FILTER([CUSTOMER JOB].[Title Name].[Title Name].members,([Job Complete Date].[Year].&[2005],[Measures].[Prep Billing]) > 5000 ) ;

This one, I've used other named sets to create another named set.

Is this a bad thing?

CREATE SET CURRENTCUBE.[Non-Prep-Photo Customers]

AS EXCEPT([CTP Customers],{[Prep Customers],[Photo Customers]});

As I understand it, a named set is not processed until it is needed.

Once these are processed are they cached for use by other client requests?

Actually no - named sets are always processed and evaluated during MDX Script execution. This is done once, and they are cached afterwards.

|||

We've had issues where after the cube was processed it was not available for running reports until the server was rebooted.

When we removed the named sets the issue went away.

Is there any issues related to using named sets on a 32bit server running sql server 2005?

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.

Named set is not working in excel

Hi All,

I have created the named set to get the top 4 referers for my products.

But it is not working as expected.I am using Ecel 2007 as reporting UI using Pivot tables.

The referers are in rows and product is in filter.

Say I want the top referers (google.com,altavista.com...etc) for VB (or) VC++ or SQL 2005 ....etc

Please suggest me how I can do this i.e arrange in order and vary with change of product.

I have three tables Dim_Product, Dim_Referer, Fact table

The content of this new named set would be:

{([Dim Referer].[name category].Members,4,[Measures].[value])}

Rgds

Sai

Named sets created in the MDX Script are static - they are evaluated only once. Therefore changing the current product won't affect the content of the named set.sql

Named Set in Excel Pivot Table Displays as Enumeration

Pardon me if this is really an Excel 2007 Pivot table question. I have a named set defined in the cube as:

CREATE SET CURRENTCUBE. [Team Building Overall]

AS {[Question].[Short Name].[Q-02],

[Question].[Short Name].[Q-03],

[Question].[Short Name].[Q-05],

[Question].[Short Name].[Q-06],

[Question].[Short Name].[Q-08],

[Question].[Short Name].[Q-13],

[Question].[Short Name].[Q-17],

[Question].[Short Name].[Q-18],

[Question].[Short Name].[Q-21],

[Question].[Short Name].[Q-30],

[Question].[Short Name].[Q-31]};

And can display measure values for this set as whole in Proclarity with:

SELECT { [Measures].[Average]} ON COLUMNS ,

{ [Corporate Hierarchy].[Hierarchy].DEFAULTMEMBER } ON ROWS

FROM [360 Training Survey]

WHERE ([Team Building Overall], [Test Month].[Test Month].&[200704] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

In Excel 2007, unfortunately I can not use this set as a filter as I can in Proclarity. Rather, the best I can accomplish is to display this as 11 different columns, one for each member of this named set.

Any suggestions ?

Anyone ?

I figure I can create a calculated measure in the cube named [Team Building Overall Average] which is defined as ([Team Building Overall],[measures].[Average]), but this seems a bit klutzy, especially given that I have eight sets and six measures, thus resulting in a total of 48 calculate measures I need to create.

|||

You could create a measure independant calculated member in the Question dimension as follows.

CREATE MEMBER CurrentCube.[Question].[Short Name].[Team Building Overall] AS AGGREGATE([Team Building Overall])

If the only reason that you have the sets is for filtering you could put the set definition inline in this memeber, otherwise you could leave them separate.

NAMED SET / PIVOT TABLE ! HELP ME

NEED KNOW IF SOMETHING KNOW CERTANLY ....ABOUT THIS :

************************************************** ********************************
PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...
EXAMPLES... ALL !

ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM A
CUBE ( OLAP)

************************************************** ************************************************

REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ...A OLNLY FIN MANY
QUESTIONS IN FORUMS...BUT NO ONE RESPONSES....APARENTLY TOO MUCH PEOPLE
HAVE THIS TROUBLE ..... I KNOW ...NO EVERY DAY .MEMBERS THAT POST IN THIS
WEBSITE..TAKE REPLY...BUT......, REALLY NEED INFO ABOUT .AS SOON AS
POSSIBLE...
MI E-MAIL :
THANABULLET@.HOTMAIL.COM

THANKS !.
MY BEST WISHES...

--
hi all !wow

it shows up just like a dimension i thought

are you using the olap 8.0 oledb provider?

-aaron
msolap1.1 or somethign right

named server failed logon after installing MS04-011 patch

Need help urgently.
I have on win 2000 machine a default SQL Server (local) & a named SQL Server
instance (for separate web machine).
After installing the MS04-011 Patch last nite, the named SQL Server fails to
startup on reboot or manual. The default server on the other hand is runnin
g. But I cannot use the default server as it requires access within the loca
l machine only.
I tried uninstalling the patch, but it still cannot get the SQL server runni
ng again.
HOW CAN I RECOVER THE SQL Server Instance?
Additional info: On logon, following error massage:
"NtUninstall Q8877565\WinSys.cer" not found
before and after the uninstall of the patch.What error do you get when you start it? Is there anything in he SQL Server
errorlog when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thank Rand for responding.
The Error is simply Login Failed. Service Manager shows the instance is stop
ped, while the Default Instance (Local) is started. Click start, and return
Login Failure again.
Any idea what's wrong and how I may recover the Instance?
Almon|||Error listing end at the last restart after patch install. That is, never re
start again.
Almon