Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding or subtracting

I'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.

The problem I have is that I have to sum these amounts to give a total for each customer and report them as

customer1 total amount1
customer2 total amount2

I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.

By the way I'm not allowed to change the structure of the table.

Could you help me try to create this query?you need to use a CASE stmt. check out BOL for some samples.

hth|||Could you give me an example through this thread please. I have no access to other internet pages. I can only access this one.

I tried something like this but it didn't work out

select r.numpolso, r.numofic,
case r.tdocumen
when 'N' then sum (r.prmtotrb*'-1')
when 'R' then sum (r.prmtotrb)
end as r.prmtotrb
from trecibos r
where r.numpolso = '1010' and r.ramo = '90' and r.subramo = '14' and r.numofic = '001'
group by r.numofic, r.numpolso, r.prmtotrb

I got a syntax error msg

could you give me an example or another way of doing this?

thanks in advance

Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP

Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP

Adding another server instance to another hard drive?

A disaster recovery plan will help you minimize data loss. You need to
plan for various recovery scenarios according to the amount of data loss
that is acceptable to you.
The worst case is that you lose your computer completely due to a disaster
such as for or theft. Your only recourse in this situation is to reinstall
SQL Server on a working machine and restore from backups you've kept in a
safe location.
If your computer crashes and/or you lose your SQL Server drive, you might
get lucky with a successful attach of the database files if your database
files are intact. However, you should not rely on this and plan on
restoring your database from backup because you might not be able to attach
databases which were not detached properly.
The bottom line is that you need to backup your databases periodically and
archive the backup files to a safe location.
Hope this helps.
Dan Guzman
SQL Server MVP
"Niels" <nkistrup@.comcast.net> wrote in message
news:NeudnYnNW-kXTiTdRVn2tQ@.giganews.com...
> Peter,
> So much for my mastery of English. I don't think I'm explaining the
> situation correctly, sorry.
> I'm running Win 2000 Pro, with the developer's edition of MS SQL Server,
but
> I have 4 hard drives ... and it's all on my home computer (I'm the
'network
> guy'). Maybe another server instance is not the correct terminology?
> Currently my data is on D:, but I would like to use one of the other hard
> drives to actively store data.
> If the computer crashes, I'm screwed, but I can always move the hard drive
> to another machine. Heck the drive where the data is 6 1/2 yrs old, and
has
> migrated across machines in the past. (The hard drive.)
> Niels
>Dan,
Lets talk 'preemptive correction'. My data drive is 6 1/2 yrs old & have
the replacement drive ready for swapping. How do you copy the server
instance onto a new drive. From what I can see, it is not as easy as
copying the SQL data folder to another drive, then onto the new one. (Or is
it?)
I am still curious about creating other server instances on other hard
drives. That way, I could spread the data across hard drives. Yes, I need
to transfer my data to a new drive, which will have the same drive. But
just because I cannot figure how out how to create new server instances,
makes me curious about the procedure. I work as a T-SQL developer, but my
DBA skills are minimal, which is why I'm being such a persistent pest about
this.
Hard drive crashes remain my #1 disaster threat. Failure of the computer is
also possible, but like you said, regular backups minimize problems from
that.
Niels
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OPBjaKmREHA.2848@.TK2MSFTNGP10.phx.gbl...
> A disaster recovery plan will help you minimize data loss. You need to
> plan for various recovery scenarios according to the amount of data loss
> that is acceptable to you.
> The worst case is that you lose your computer completely due to a disaster
> such as for or theft. Your only recourse in this situation is to
reinstall
> SQL Server on a working machine and restore from backups you've kept in a
> safe location.
> If your computer crashes and/or you lose your SQL Server drive, you might
> get lucky with a successful attach of the database files if your database
> files are intact. However, you should not rely on this and plan on
> restoring your database from backup because you might not be able to
attach
> databases which were not detached properly.
> The bottom line is that you need to backup your databases periodically and
> archive the backup files to a safe location.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Niels" <nkistrup@.comcast.net> wrote in message
> news:NeudnYnNW-kXTiTdRVn2tQ@.giganews.com...
> but
> 'network
hard[vbcol=seagreen]
drive[vbcol=seagreen]
> has
>|||I am not sure if you really mean another instance or just moving the data
files. If you want to install a second instance of sql server (binaries)
then you need to rerun the setup and point it to the other drive for the
install. If you just want to move the data files you can find what you need
here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.databasejournal.com/feat...cle.php/2228611
Transferring Logins
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
This still does not mean you should not have valid backups. See BACKUP and
RESTORE in BooksOnLine for more info.
Andrew J. Kelly
SQL Server MVP
"Niels" <nkistrup@.comcast.net> wrote in message
news:GY6dneIma9N3zSfdRVn2tA@.giganews.com...
> Dan,
> Lets talk 'preemptive correction'. My data drive is 6 1/2 yrs old & have
> the replacement drive ready for swapping. How do you copy the server
> instance onto a new drive. From what I can see, it is not as easy as
> copying the SQL data folder to another drive, then onto the new one. (Or
is
> it?)
> I am still curious about creating other server instances on other hard
> drives. That way, I could spread the data across hard drives. Yes, I
need
> to transfer my data to a new drive, which will have the same drive. But
> just because I cannot figure how out how to create new server instances,
> makes me curious about the procedure. I work as a T-SQL developer, but my
> DBA skills are minimal, which is why I'm being such a persistent pest
about
> this.
> Hard drive crashes remain my #1 disaster threat. Failure of the computer
is
> also possible, but like you said, regular backups minimize problems from
> that.
> Niels
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OPBjaKmREHA.2848@.TK2MSFTNGP10.phx.gbl...
disaster[vbcol=seagreen]
> reinstall
a[vbcol=seagreen]
might[vbcol=seagreen]
database[vbcol=seagreen]
> attach
and[vbcol=seagreen]
Server,[vbcol=seagreen]
> hard
> drive
and[vbcol=seagreen]
>