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%";

Name Split

Quick question.

I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it).

Thanks in advance.

Rayyou have to use a couple and think dynamically. I think you want something like so:

SELECT RIGHT(thename,LEN(thename) - CHARINDEX(' ',thename)) AS lastname,
LEFT(thename,CHARINDEX(' ',thename)) AS firsttname
FROM yourTable|||i think you'll find that there will be many with 3 names, and that it's better to split on the last space, not the first

(although nothing is perfect, e.g. John Smith III will have to be corrected)

here's some code i just happened to have lying around --
select left(fullname,len(rtrim(fullname))
- charindex(' ',reverse(rtrim(fullname))))
as firstname
, ltrim(right(rtrim(fullname)
, charindex(' ',reverse(rtrim(fullname)))))
as lastname
from yourtablealso, note that the code, whichever you use, should also work on single names like Cher

:)|||Oh, that's a low blow, Canadiene!

Let's make a deal. YOU don't mention Cher, and WE won't mention Celine Dion.

Detente', OK?|||It's ya birfday, rkobs.

Use the attached function with this syntax:

select dbo.FormatName([YourField], 'F') as FirstName
select dbo.FormatName([YourField], 'L') as FirstName|||nothin wrong with Cher -- had the hots for her in the 60s and still do ;)

along with Celine Dion, i would also appreciate it if you wouldn't mention Mutt Lange's wife Shania Twain, Avril Lavigne, Anne Murray, Sarah McLachlan, Nellie Furtado, and Alanis Morissette -- they all make my ears hurt|||wow, i have a code snippet lying around, and you have an entire application!!

nice job

just curious, when did you write that?|||Celine Dion, Shania Twain, Avril Lavigne, Anne Murray, Sarah McLachlan, Nellie Furtado, and Alanis Morissette?

Are you some sort of misogynist?|||I've written and revised it over the last seven or eight years.

It was originally an Access VB function.|||Are you some sort of misogynist?not at all, i love women, i've married several of them and would do so again if i weren't so old (women don't usually find old guys all that attractive)

no, it's just that i don't like those particular singers

i mean, you started it with the female singers

you can have Gordon Lightfoot and the Bare Naked Ladies too, if it makes you happy

i'll keep Rush, Pat Travers, Triumph, Kim Mitchell, Saga, Robin Trower, Jeff Healey ... all from Toronto (but not all still together)|||And by the way, Alanis Morissette is an American (as of today...).

http://apnews.excite.com/article/20050217/D88A92MG0.html|||i am flummoxed|||not at all, i love women, i've married several of them and would do so again if i weren't so old (women don't usually find old guys all that attractive)I'm not buying that one... I thought you were married now. My guess is that you wouldn't need to work hard to be married, if you decided that you wanted to be married. Age seems to have very little to do with it, although once you get past a certain point you do have less interest to the twenty-somethings! I guess I don't consider that a bad thing, it would be tough to try to raise a wife when I'm accustomed to thinking of them as partners.

i am flummoxedThat doesn't happen often. It must be some function, I'll have to look at it in more detail!

-PatP|||I thought you were married now. nope, happily divorced

the kids live with me every two weeks

and i don't think i will ever stop being attracted to the hot twenty-somethings

at least i have the good sense not to try to do anything about it :)|||and i don't think i will ever stop being attracted to the hot twenty-somethings

at least i have the good sense not to try to do anything about it :)Like Lamborghini's, they certainly catch your eye, and I can see how they could be marvelous fun for an afternoon under the right conditions, but would you really want one? Even if someone offered to give me a Countach or a Murcilago, I can't imagine what I'd do with one. I don't often handle anything like them, and although it would be fun it would most likely get me killed!

Along the same lines, I can't imagine dating someone in their twenties. I can only think of a very small number of women in their thirties that might interest me. I'm entirely content with the lady that I'm seeing, and she's a bit older than I am.

Although I have to admit that it was hilarious when I wandered over to the pool at a DR site last year and said "Greetings, Gorgeous" to an eighteen year old who promptly offered me a drink. Several of the guys I work with were picking up their jaws until she said "Mom's over on the other side of the pool, can you spread some sun tan lotion on me?" She was my girlfriend's daughter, but none of the folks I work with would recognize her. Simple pleasures!

-PatP

Mysterious truncation

I have a SP that retrives several fields on a table containing cariage
returns in the data (IE Office Address) Im storing the address data in a
temo table and then returning the resuls of the temp table.
The issue is that many of the addresses seem to get truncated even though
the field in the temp table is defined as varchar(1000) - more then enough
space to hold the data. The fields in the table where the data is coming
from are defined as text(16). I cant use text data type in my SP temp table
b/c I need the DISTINCT ability. However, Im not convined the problem lies
with the table being text type and the SP temp table being varchar(1000). An
y
Ideas as to the problem anyone. Most of the addresses are probably not even
500 characters long. Does the SP npt like cariage returns in the data? I
didnt design the orginal table. Im trying to work around it.
--
JP
.NET Software DevelperAre you basing your analysis on ouput from Query Analyzer? If so, there's a
trick in Query Analyzer - It defaults to truncate all column output to a max
of 256 characters. To change that, go to menu option
Tools, Options, Results, and in middle vertically, you will see
Maximum Characters per column set to 256... Change it to 8000
"JP" wrote:

> I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp tabl
e
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
Any
> Ideas as to the problem anyone. Most of the addresses are probably not eve
n
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper|||This did not solve the problem. It still resulting the truncated data even
though its in the table. If the SP results would have been truncated SQL
would have resulted in an error code b/c data would have been truncated.
"CBretana" wrote:
> Are you basing your analysis on ouput from Query Analyzer? If so, there's
a
> trick in Query Analyzer - It defaults to truncate all column output to a m
ax
> of 256 characters. To change that, go to menu option
> Tools, Options, Results, and in middle vertically, you will see
> Maximum Characters per column set to 256... Change it to 8000
> "JP" wrote:
>|||Then is there some pattern to the truncations? Is the length always the
same? i.e., is it always truncating a tthe same character position? Or is it
always truncating just before the Carriage return Line feed? Look at the
ouput, and try to discern some pattern. That will help steer you in the
right direction to debug this...
"JP" wrote:
> This did not solve the problem. It still resulting the truncated data even
> though its in the table. If the SP results would have been truncated SQL
> would have resulted in an error code b/c data would have been truncated.
> "CBretana" wrote:
>|||First step in an operation like this, run profiler and see what commands are
being sent to the server. This will likely show you your problem.
Otherwise post more information like the DDL of your tables and some data.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JP" <JP@.discussions.microsoft.com> wrote in message
news:1503FA3A-0FFC-48EB-ADBD-D8CF183C93A3@.microsoft.com...
>I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp
> table
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
> Any
> Ideas as to the problem anyone. Most of the addresses are probably not
> even
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper

Mysterious Date Problem

Is there a way to either set Sql Server 2000 or ASP.net datetime fields to a standard format. The problem is that I am passing correct datetime fields using stored procedures and keep getting "Cannot convert datetime into string". It seems to me that many other developers are having that same problem. I tried alot of different methods and still have the same problem. I'm using c# and I never had a problem with datetime fields when I was using vb.net. The problem is that SQl Server is returning datetime formats that are not compatible with c#. I have code that works in other projects but when I try to use that same code I get that conversion error. How do I set the datetime in SQl Server and ASP.Net when I run queries so that the datetime output is in mm/dd/yyyy?What is the statement where you get the error "Cannot convert datetime into string"|||well anytime I return a dataset and bind it to a datagrid I get a conversion error. Sometimes I even got 'The conversion of a char data type to a datetime data type resulted in an out-of-range'. When I ran a simple query in Query Analyzer like "select convert (datetime,'12/20/2003',101)" the output returns 2003-12-20 00:00:00.000
What I need is to be mm/dd/yyyy. Is there a way to change the default setting in Sql Server to that particular format instead of yyyy-mm-dd. Because I think C# is using mm-dd-yyyy as a default. In the asp net datagrid all I'm doing it just binding the dataset to the grid and I get that error? Thanks for your reply...|||you should do the formatting at the front end...for instance when you bind the result set to the datagrid...you can use the dataformatstring property of the datagrid columns to format it to mm/dd/yyyy format which is much simpler rather than enforcing the sql server to some other format..

hth|||Yah, I used that format and that is not the problem. The problem is that I can't even bind the dataset to the datagrid. Its in the binding that is causing the error. But thanks anyway.|||What is the code that you are using??|||basically I just call pass parameters to a method and it returns a dataset

datagrid.DataSource = ds;
datagrid.DataBind();

public DataSet GetOriginationReport( DateTime dtStartDate, DateTime dtEndDate)
{
// instantiate connection and command objects
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connString2"]);
SqlDataAdapter myCommand = new SqlDataAdapter("rptSelectReport", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add("@.pStartDate", SqlDbType.DateTime );
myCommand.SelectCommand.Parameters.Add("@.pEndDate", SqlDbType.DateTime );

myCommand.SelectCommand.Parameters["@.pStartDate"].Value = dtStartDate;
myCommand.SelectCommand.Parameters["@.pEndDate"].Value = dtEndDate;

DataSet myDS = new DataSet();
myCommand.Fill(myDS,"viewReportPipe");
return myDS;
}

===============================
Stored Procedure
===============================
CREATE PROCEDURE rptSelectReport
(
@.pStartDate DateTime,
@.pEndDate DateTime,
)
As

Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||What result do u get when u run this query in Sql analyzer??
Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||well I get a list of results. The problem is occuring in the binding. When I run the query and bind it to a datagrid it works. But I have sorting enable in the datagrid and when I run that same query and bind it to the grid it fails and gives me that "Cannot convert datetime to string" error. The datefield in the table is "AppDate" and is a datetime datatype and is not null. I know Sequel stores dates internally and is different then what is displayed. I have dates 01/01/1900 as default dates. I just discover that sequel uses that same date as a default date. To me its seems like Sequel is switching datetime formats and C# is not recognizing it as a valid datetime. Thanks for you quick response...|||Also should I changed my select statement and convert the date in the stored procedure
like "select AppDate = convert(datetime,AppDate) from ...|||What value does dtStartDate have??|||dtStartDate has 12/01/2003 12:00:00 AM

and the dtEndDate has 12/31/2003 12:00:00 AM|||I am not sure why you get error??
Cannot convert datetime to string

It must be probably your sorting procedure handling some parameter in wrong manner|||its funny because that what I originally though. I was able to sort by a few columns and it works. But some columns would throw that conversion error. I know I am sorting by the right field name but for some reason and I don't why but Sequel is switching datetime formats. That leads me to my original question that Sequel is returning the recordset as yyyy-mm-dd and and is there a way to change it to mm-dd-yyyy. I notice that other developers are experience the same problem and the answer seems to be that SQl Server and asp.net application have to have the same regional setting(datetime). But I haven't found an answer to change the setting in SQL Server or asp.net application. I know how to change the settings on the server but that doesn't help.|||Post your Sorting code...

mysql EXPLAIN = what in MS SQL 2000

If you have a table namedUsers then using mysql
you can runEXPLAIN Users; to get all the fields & types, etc.
How would you do this same thing in SQL 2000?There are several ways to get this information. sp_help would be a good place to start: sp_help Users

Terri

My Table that uses aspnet foreign Keys wont display anything - CAUTION - Noob at work

I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough.

Thanks

ps I've used several different ways to link the tables. First I tried building the relationships through the SqlMembershipProvider, created tables and added the foreign keys. Not sure of whether I'd done this correctly, I then used the diagram method which is pretty straightforward, however the result is the same. Surely now these tables are keyed to the UserId of aspnet_Users they should build and display a table of my existing test membership - even if the other fields, for the moment, are not carrying any data?

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.