Named Calculation, Referencing a field from a linked table

Is there a way to reference a field from another table in a named calculation.

Ex: Table movies has a link to table theatre, in the theatre there is a display code, the display-name of the movie should be comp.comp_displaycode + ' - ' + mov.mov_name.

I know i could do this in a view and than use the view instead of the movies table, but if it is possible in the Datasourceviews it would be easier.

Try replacing table in the DSV with named query joining 2 tables.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||This is very similar to defining views, i just hoped that there was a syntax available to do it.

Name storage in database

I'm dealing with a system that only stores a single "Name" field in an OLTP
(web site) database. Now my immediate reaction was to split it into: Prefix,
First, Middle, Last & Suffix. However, the UI could easilly get a little
unfriendly. So I popped into Amazon and found they were using a single field
in the UI too. Other sites I checked only used first and last names.
Someone must have done some research on this and made a paper available.
Thoughts and/or links?On Wed, 5 Dec 2007 12:37:24 -0800, Jay wrote:
>I'm dealing with a system that only stores a single "Name" field in an OLTP
>(web site) database. Now my immediate reaction was to split it into: Prefix,
>First, Middle, Last & Suffix. However, the UI could easilly get a little
>unfriendly. So I popped into Amazon and found they were using a single field
>in the UI too. Other sites I checked only used first and last names.
>Someone must have done some research on this and made a paper available.
>Thoughts and/or links?
>
Hi Jay,
It all depends on what you want to use the data for.
If you just intend to print it on address labels for your business
correspondence, than you can just as well leave it in a single column.
If you intend to do something wiith a component of a name (e.g. sorting
by last name, or personalizing a letter by having it start with "Dear
Mr. Jones" or "Hi Barry", or analysing whether your customers with a
specific title spend more than those without), then you're better off
splitting the parts you eed seperate right from the off.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Name storage in database

I'm dealing with a system that only stores a single "Name" field in an OLTP
(web site) database. Now my immediate reaction was to split it into: Prefix,
First, Middle, Last & Suffix. However, the UI could easilly get a little
unfriendly. So I popped into Amazon and found they were using a single field
in the UI too. Other sites I checked only used first and last names.
Someone must have done some research on this and made a paper available.
Thoughts and/or links?On Wed, 5 Dec 2007 12:37:24 -0800, Jay wrote:

>I'm dealing with a system that only stores a single "Name" field in an OLTP
>(web site) database. Now my immediate reaction was to split it into: Prefix
,
>First, Middle, Last & Suffix. However, the UI could easilly get a little
>unfriendly. So I popped into Amazon and found they were using a single fiel
d
>in the UI too. Other sites I checked only used first and last names.
>Someone must have done some research on this and made a paper available.
>Thoughts and/or links?
>
Hi Jay,
It all depends on what you want to use the data for.
If you just intend to print it on address labels for your business
correspondence, than you can just as well leave it in a single column.
If you intend to do something wiith a component of a name (e.g. sorting
by last name, or personalizing a letter by having it start with "Dear
Mr. Jones" or "Hi Barry", or analysing whether your customers with a
specific title spend more than those without), then you're better off
splitting the parts you eed seperate right from the off.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

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

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>

my value doesnt reset when i used sum(field, scope)

pls help what seems to be the problem? my sum doesnt restart as the group changes. instead it accumulates the sum? thanks.

use

RunningValue(Fields, Sum, scope)

my value doesnt reset when i used sum(field, scope)

pls help what seems to be the problem? my sum doesnt restart as the group changes. instead it accumulates the sum? thanks.

use

RunningValue(Fields, Sum, scope)

My report doesn't appear WYSWYG

I have a data field(text) which doesn't appear as what I see in preview. How
can I display the text with paragraphs?
I see squares between words from data I retrieved. I guess that is the
something indicates new line or new paragraph. In preview, the text is
properly displayed in different lines/paragraphs. But when I built ad
deployed it, the text becomes cramp into one paragraph. This reduce the
readability.
Appreciate if someone could give me an idea on where to set the format or
cast the data field. Thank you.Still couldn't solve my problem. Anyone can help?
More elaboration on this problem: -
When I preview I see the data field display nicely like below (eg.):
Stamp[xxx]
Subject: Server not up after rebooting
Assigned to mho3.
This has moderate impact to customer site.
--Then, after I deployed it, I browse it via IE and unfortunately I couldn't
get what I see in Preview. It gives me result like this:
Stamp[xxx] Subject: Server not up after rebooting Assigned to mho3. This has
moderate impact to customer site.
--Just imagine some have long data. It will be difficult to read.
Any help is highly appreciated. Thanks!
"sammy" wrote:
> I have a data field(text) which doesn't appear as what I see in preview. How
> can I display the text with paragraphs?
> I see squares between words from data I retrieved. I guess that is the
> something indicates new line or new paragraph. In preview, the text is
> properly displayed in different lines/paragraphs. But when I built ad
> deployed it, the text becomes cramp into one paragraph. This reduce the
> readability.
> Appreciate if someone could give me an idea on where to set the format or
> cast the data field. Thank you.
>|||Where do you get the data from? Is the new line formating something you get
by modifying fields from different columns in the database, or is it one
field that has some format in one column in your database?
Hilary Cotter suggested in a different thread that you use char(10) +
char(13) in your SQL select to get the line breaks and new lines directly
from the database. Like this:
SELECT @.a = '123' +char(10)+char(13)+ 'xyz'
I don't know why it works in preview but not on the server, though. Guess
it's the rendering format that is different. What happens when you export to
pdf on your server? Does it go all on one line or does the lines break?
Kaisa M. Lindahl Lervik
"sammy" <sammy@.discussions.microsoft.com> wrote in message
news:7D920AFA-1350-44A0-ABA9-1AA9CD71D65E@.microsoft.com...
> Still couldn't solve my problem. Anyone can help?
> More elaboration on this problem: -
> When I preview I see the data field display nicely like below (eg.):
> Stamp[xxx]
> Subject: Server not up after rebooting
> Assigned to mho3.
> This has moderate impact to customer site.
> --Then, after I deployed it, I browse it via IE and unfortunately I
> couldn't
> get what I see in Preview. It gives me result like this:
> Stamp[xxx] Subject: Server not up after rebooting Assigned to mho3. This
> has
> moderate impact to customer site.
> --Just imagine some have long data. It will be difficult to read.
> Any help is highly appreciated. Thanks!
>
>
> "sammy" wrote:
>> I have a data field(text) which doesn't appear as what I see in preview.
>> How
>> can I display the text with paragraphs?
>> I see squares between words from data I retrieved. I guess that is the
>> something indicates new line or new paragraph. In preview, the text is
>> properly displayed in different lines/paragraphs. But when I built ad
>> deployed it, the text becomes cramp into one paragraph. This reduce the
>> readability.
>> Appreciate if someone could give me an idea on where to set the format or
>> cast the data field. Thank you.|||Teng! Teng! Teng! The answer is:
=Replace(Fields!Activity_Log.Value,CHR(10),vbCrLf)
I found that those special character "squares" is represented by CHR(10) -
Line feed, from my try and error.
And in VB.NET, vbCrLf indicates new line as in "\n" for C.
Kaisa,
I got my data in a single field (not combining multiple fields), and that
field type is Text. I cast it to string so I found "squares" between words.
Before I applied the Replace function, I tried to export my report to PDF and
other format, it's just the same, no break lines. After applying the Replace
function, it works fine.
Anyway, Thks a lot for your hints, Kaisa. :-)
best regards,
sammy

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