Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Password Parameter?

Hello.
I've got an odd requirement to password a particular report.Basically
the same authenticated user will have access to all reports, but this
particular report should only be available to select users(all which
share that username).
Any way to mask an ssrs parameter input so that it only shows *******?
Any way to persist that entry for 5 minutes, then have it no long be
valid. I've not tested or developed this yet, but I do suspect if I
call the report again (from asp.net reportviewer) and the default for
the parameter is null, that the previously entered value will not be
available right?
I currently have another post where i can't seem to figure out why my
report is defaulting some fields that no longer have fields, but only
when I test the report through reportviewer, and not my vs.net on my
client.
Thanks for any help or information!it would probably be better to put the report into a separate folder and
secure it via the folder. this would allow to have multiple reports with
this security requirement in the future. Parameters are not really intended
for passwords.
"jobs" <jobs@.webdos.com> wrote in message
news:1193496978.830252.227650@.v23g2000prn.googlegroups.com...
> Hello.
> I've got an odd requirement to password a particular report.Basically
> the same authenticated user will have access to all reports, but this
> particular report should only be available to select users(all which
> share that username).
> Any way to mask an ssrs parameter input so that it only shows *******?
> Any way to persist that entry for 5 minutes, then have it no long be
> valid. I've not tested or developed this yet, but I do suspect if I
> call the report again (from asp.net reportviewer) and the default for
> the parameter is null, that the previously entered value will not be
> available right?
> I currently have another post where i can't seem to figure out why my
> report is defaulting some fields that no longer have fields, but only
> when I test the report through reportviewer, and not my vs.net on my
> client.
> Thanks for any help or information!
>

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben

Passing the User ID and AD group as Parameter

Hi...
I have a requirement where I need to pass the Users Windows userID and the AD group through which he is associated to the database so that I can get appropiate data.

Is there a way I can pass the Windows user ID and the AD group(through which he is authenticated in Reporting Serivces) through the Reporting Serivces as a parameter so that it can be used in the Reporting Query.

Thanks,
siaj

The UserID parameter in the report holds the username information including the logged on domainor machine. getting the AD Group in which the user is authenticated to view the report is a bit different as the user can be in multiple groups, though it can′t be identified which group the user has granted the effective permissions to the report. (if you are hit by a machine gun you probably won′t know which bullet killed you at the end :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

As Jens says, UserID is just available using the internal object. For the AD group you should consider writing a custom assembly that performs the operation according to your business rules to decide which is THE group among the ones the user may pertain. I've not checked how difficult it will be to write such code in .Net but sounds not much complicated.

Best regards,
Jordi Rambla
MVP SQL Server
SolidQualityLearning|||Hi Siaj,

Were you able to figure this out? I am in need of the same functionality. I undertand .Net has a 'IsInGroup()' function that can check if a user is in a group but I am not sure how it goes...

Thanks,
Ben|||test|||Have you figured this out? I am in the same situation...

Thanks,
Ben