Showing posts with label defined. Show all posts
Showing posts with label defined. Show all posts

Friday, March 30, 2012

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 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.

Friday, March 23, 2012

Named Instance in a SQL 2000 Cluster

Hi,
When I installed a SQL 2000 Cluster Active/Active I defined one default
instance as SERVER01 and the other instance as SERVER02/I2. Is it
possible to use the second instance without put "/I2" at the end of name
server?
Do I must to install two named instances instead one default instance
and one named instance ?
thanks,
Rubens
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You can only have one default instance, the rest have to be named. You can
also create only named instances, and not have a default.
At this point, you can uninstall the default instance. Then, install a
second named instance.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Rubens Sanches" <rubensluque@.yahoo.com.br> wrote in message
news:%23SB%233M9LFHA.1472@.TK2MSFTNGP14.phx.gbl...
> Hi,
> When I installed a SQL 2000 Cluster Active/Active I defined one default
> instance as SERVER01 and the other instance as SERVER02/I2. Is it
> possible to use the second instance without put "/I2" at the end of name
> server?
> Do I must to install two named instances instead one default instance
> and one named instance ?
> thanks,
> Rubens
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Rubens Sanches wrote:
> Hi,
> When I installed a SQL 2000 Cluster Active/Active I defined one default
> instance as SERVER01 and the other instance as SERVER02/I2. Is it
> possible to use the second instance without put "/I2" at the end of name
> server?
> Do I must to install two named instances instead one default instance
> and one named instance ?
>
yes, but you can run every instance on the cluster at the default port 1433.
When clients connect to the instance they do not need the instance name when
connecting to the server as long as they use tcp/ip and now the
sqlserverport. Try osql -E -Sserver02,1433 or server02,1433 when
registering the server whith the enterprise manager. When you create an odbc
connection deselect automaticly detect the server port, fill in the port
number and forget the instance name.
Hans
|||Hi
Check that SERVER01 is actually a cluster resource. For clusters, the only
way for it to be correct is virtualservername\instancename
SERVER02/I2 is correct, but SERVER01 can not be.
Regards
Mike
"Rubens Sanches" wrote:

> Hi,
> When I installed a SQL 2000 Cluster Active/Active I defined one default
> instance as SERVER01 and the other instance as SERVER02/I2. Is it
> possible to use the second instance without put "/I2" at the end of name
> server?
> Do I must to install two named instances instead one default instance
> and one named instance ?
> thanks,
> Rubens
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Mike,
SERVER01 is a default clustered SQL instance and is the virtualservername. Since it is a default instance, it will work. It does not need an instance name.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Monday, March 19, 2012

Mysterious text in query result

Hi,
I have a database with a column defined as varchar(100) called Surname.
When I use Query Analyzer to retrieve data from the column and display as
text, I get strange text appearing for one of the records. Thus, the field
contains "Wilson" but when I run the following:
SELECT '''' + Surname + '''', Surnname FROM tblManagers
WHERE FirstName = 'Glenn'
I get the following result:
'Wilson d g e d w i t h t h e a ' Wilson
Where has the "d ged with the a" come from. If I show the results in a grid
the final apostrophe is missing from the first part of the query:
'Wilson
If I run a query using ADO and show the result on a web page, the extra text
is not shown.
Any ideas what is going on here?
Glenn
[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
BigMan2001 (BigMan2001@.discussions.microsoft.com) writes:
> I have a database with a column defined as varchar(100) called Surname.
> When I use Query Analyzer to retrieve data from the column and display
> as text, I get strange text appearing for one of the records. Thus, the
> field contains "Wilson" but when I run the following:
> SELECT '''' + Surname + '''', Surnname FROM tblManagers
> WHERE FirstName = 'Glenn'
> I get the following result:
> 'Wilson d g e d w i t h t h e a ' Wilson
> Where has the "d ged with the a" come from. If I show the results in a
> grid the final apostrophe is missing from the first part of the query:
> 'Wilson
> If I run a query using ADO and show the result on a web page, the extra
> text is not shown.
Looks like some junk data slipped in, and there is a NUL character hiding
there. See what
SELECT convert(varbinary(100), Surname) FROM tblManagers
WHERE FisttName = 'Glenn'
returns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Monday, February 20, 2012

my query and user defined fuction problem

I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYE
my query looks like

select ID, DateDue, dateReceived, dueday, month, ContactFYE
from Report
where (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )

user defined function

CREATE FUNCTION dbo.udfDisplayTime ( @.dueday int, @.month int, @.ContactFYE smalldatetime)
RETURNS smalldatetime AS
BEGIN
DECLARE @.ReturnString AS smalldatetime
Declare @.dueday1 as int
if (@.month =1)
begin
set @.dueday1 = @.dueday -7
end
else if (@.month =2)
begin
set @.dueday1 = @.dueday -14
end
else if (@.month =3)
begin
set @.dueday1 = @.dueday -60
end

select @.ReturnString = DATEADD ( dd, @.dueday1, @.ContactFYE)
Return @.ReturnString

END

I got a incorrect result when using this query and user defined function.
the result that I got was out of between displaytime and datdue.

any idea for this , Thanks.I think we are going to need to see some data and desired results in order to be able to help you. The purpose of the function and your different data columns is not obvious (to me at least).|||

The query you have is the same as:

select ID, DateDue, dateReceived, dueday, month, ContactFYE
from Report

the where condition you have specified will always be true, because DateDue is ALWAYS between (anything) and DateDue. It makes no difference what the udf returns.