Help with custom total in matrix (hopefully easy adjustment)

Hey guys. I have a matrix that is working the way I want it as far as
groupings and data. I am doing a custom total that works for the first row
group with the following code:
Private currentTotal As Double = 0
Function SetCurrentValue(ByVal currentValue As Double) As Double
currentTotal += currentValue
return currentTotal
End Function
Function GetCurrentValue() As Double
return currentTotal
End Function
Function ResetTotal() As Boolean
currentTotal=0
return True
End Function
In my detail I have:
=IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
If I use only the first IIF statement I get the correct values 19, 38 , 57,
76, 95, 114, 133, 152, 171, 190, 209, and 228.
If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
361, 399, 437 in the first row and in the second row I get:
494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
I need to run the reset method in between row groupings to get it to go back
to 19 for the second row (it will be a different starting value eventually,
but for now I need it to reset).
I tried putting the code as a second row grouping and the column subtotal,
but it won't reset in the proper spot. Let me know!
Thanks!,
BJYou can use rownumber with scope/group so that it resets each and everytime
group changes. Provided your 19,38,57 etc.. are fixed values...
Amarnath
"bjkaledas" wrote:
> Hey guys. I have a matrix that is working the way I want it as far as
> groupings and data. I am doing a custom total that works for the first row
> group with the following code:
> Private currentTotal As Double = 0
> Function SetCurrentValue(ByVal currentValue As Double) As Double
> currentTotal += currentValue
> return currentTotal
> End Function
> Function GetCurrentValue() As Double
> return currentTotal
> End Function
> Function ResetTotal() As Boolean
> currentTotal=0
> return True
> End Function
> In my detail I have:
> =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> If I use only the first IIF statement I get the correct values 19, 38 , 57,
> 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> 361, 399, 437 in the first row and in the second row I get:
> 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> I need to run the reset method in between row groupings to get it to go back
> to 19 for the second row (it will be a different starting value eventually,
> but for now I need it to reset).
> I tried putting the code as a second row grouping and the column subtotal,
> but it won't reset in the proper spot. Let me know!
> Thanks!,
> BJ|||Where would I use the rownumber function? Would I keep track of this in my
custom code or when I call the function in my detail textbox?
"Amarnath" wrote:
> You can use rownumber with scope/group so that it resets each and everytime
> group changes. Provided your 19,38,57 etc.. are fixed values...
>
> Amarnath
> "bjkaledas" wrote:
> > Hey guys. I have a matrix that is working the way I want it as far as
> > groupings and data. I am doing a custom total that works for the first row
> > group with the following code:
> >
> > Private currentTotal As Double = 0
> > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > currentTotal += currentValue
> > return currentTotal
> > End Function
> >
> > Function GetCurrentValue() As Double
> > return currentTotal
> > End Function
> >
> > Function ResetTotal() As Boolean
> > currentTotal=0
> > return True
> > End Function
> >
> > In my detail I have:
> >
> > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> >
> > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> >
> > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > 361, 399, 437 in the first row and in the second row I get:
> >
> > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> >
> > I need to run the reset method in between row groupings to get it to go back
> > to 19 for the second row (it will be a different starting value eventually,
> > but for now I need it to reset).
> >
> > I tried putting the code as a second row grouping and the column subtotal,
> > but it won't reset in the proper spot. Let me know!
> >
> > Thanks!,
> >
> > BJ|||You can use in the textbox so that when it displays it resets exactly.
Amarnath
"bjkaledas" wrote:
> Where would I use the rownumber function? Would I keep track of this in my
> custom code or when I call the function in my detail textbox?
> "Amarnath" wrote:
> > You can use rownumber with scope/group so that it resets each and everytime
> > group changes. Provided your 19,38,57 etc.. are fixed values...
> >
> >
> > Amarnath
> >
> > "bjkaledas" wrote:
> >
> > > Hey guys. I have a matrix that is working the way I want it as far as
> > > groupings and data. I am doing a custom total that works for the first row
> > > group with the following code:
> > >
> > > Private currentTotal As Double = 0
> > > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > > currentTotal += currentValue
> > > return currentTotal
> > > End Function
> > >
> > > Function GetCurrentValue() As Double
> > > return currentTotal
> > > End Function
> > >
> > > Function ResetTotal() As Boolean
> > > currentTotal=0
> > > return True
> > > End Function
> > >
> > > In my detail I have:
> > >
> > > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> > >
> > > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> > >
> > > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > > 361, 399, 437 in the first row and in the second row I get:
> > >
> > > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> > >
> > > I need to run the reset method in between row groupings to get it to go back
> > > to 19 for the second row (it will be a different starting value eventually,
> > > but for now I need it to reset).
> > >
> > > I tried putting the code as a second row grouping and the column subtotal,
> > > but it won't reset in the proper spot. Let me know!
> > >
> > > Thanks!,
> > >
> > > BJ

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Help with a query please

I have been strugling with for hours -
I have a table with the following columns
UID(decimal) | SD(bit) | MB(bit) | TS(bit) | Total(Decimal)
I want to run a query which will return
UID, Total if SD is True AND
UID, Total if MB is True AND
UID, Total if TS is True
UID is the userID so there would be a WHERE statement
ORDERED BY Total Descending
Many thanks in advance
Many thanks,
Rob
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:9137
On Sun, 12 Mar 2006 03:17:27 -0800, RobA wrote:

>I have been strugling with for hours -
>I have a table with the following columns
>UID(decimal) | SD(bit) | MB(bit) | TS(bit) | Total(Decimal)
>I want to run a query which will return
>UID, Total if SD is True AND
>UID, Total if MB is True AND
>UID, Total if TS is True
>
>UID is the userID so there would be a WHERE statement
>ORDERED BY Total Descending
>Many thanks in advance
Hi Rob,
It's hard to tell exactly what you need from this description. If the
query below is not what you need, then please read www.aspfaq.com/5006
to find a better way to describe your problem.
SELECT UID,
SUM(CASE WHEN SD = CAST(1 AS bit) THEN Total ELSE 0 END),
SUM(CASE WHEN MB = CAST(1 AS bit) THEN Total ELSE 0 END),
SUM(CASE WHEN TS = CAST(1 AS bit) THEN Total ELSE 0 END)
FROM YourTable
GROUP BY UID
Hugo Kornelis, SQL Server MVP

Help with a Query

What I need to do is select a persons name and total up there hours worked times their bill rate. The trick is that they can have multiple bill rates based on the effective date of that bill rate. Their bill rate( there can be multiple bill rates per person), Effective date (There is one effective date per bill rate per person), Total hours hours worked, where work is broken up to the day the work was done. So I need to multiply the bill rates on the day the work was performed and the total them up.

Example--John works 200 hours in june at $100 and hour, then we change his bill rate to $150 on July first. He then works 100 hours in July. His total bill would be $35,000.

Here is basically how the tables are configured. Any Suggestions?

Tables

--Resources--
Columns
Name, ResourceID(This field is the FK relation ship to the other two tables.)

--ResourceRate--
Columns
ResourceID,HourlyBillRate,EffectiveDate(The date the rate takes affect.)

--Time--
Columns
ResourceID,TimeDate(The day the work was performed),RegularHours(The number of hours worked on that day)

Below is mty attempt.

select B.EffectiveDate,CAST(A.name as char(20))Name, sum(C.RegularHours) As Total_Regular_Hours, B.HourlyBillRate
from Resources A INNER JOIN ResourceRate B ON A.ResourceID = B.ResourceID
INNER JOIN Time C ON C.ResourceID = A.ResourceID
Group by A.name, B.HourlyBillRate,B.EffectiveDate,C.TimeDate
order by A.nameThat's why I prefer a 2-field representation of a period. And in your case for as long as the rate is current, DateEnd would stay NULL, once the rate changes, - DateEnd acquires the last day for the old rate, and a new record with DateEnd=NULL gets created. By doing so you can sum HourlyBillRate X RegularHours by NAME and EffectiveDate WHERE TimeDate between EffectiveDate and DateEnd.

help with a max length table constraint

Table File_Paths (physical system file paths)

The columns represent parts of the path.

How can I set up a constraint that the total concatenated length of all the columns within a row is less than 260 chars?

Thanks

ALTER TABLE SomeTable WITH NOCHECK
ADD CONSTRAINT SomeCheck CHECK (LEN(Col1+ co2 + col 3) <= 260)

HTH, Jens Suessmeyer.

|||

Right on target. Thanks

For some reason, when it gets saved a bunch of unnecessary parentheses both square and round get added.

The square brackets help if there are white spaces but the program just forces them. Likewise with the round the len() function did not need the items enclosed individually either.

Is there wa way to turn that off in SSMS?

|||The database engine modifies expressions specified in constraints, defaults, computed column etc. There is no way to control this behavior or suppress it. This is even more so in SQL Server 2005. So you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.|||

Thanks.

A newbie needs hand holding and the GUI does that. I am sure eventually I will get more independent but until then...

Umachandar Jayachandran - MS wrote:

you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.

Let me get this straight:

After I execute the script, the only other time I need it is if I want to modify it or reuse it on another server. Otherwise, it is for informational purposes. For the info to be effective, the DDL code would have to be broken down into smaller files for granularity and a huge effort to duplicate the tree-like organization. No automation at all. And all this while the same already exists, but the code is munged.

What is it with MS and code munging by force? Didn't they get enough complaints about mutilating html/aspnet markup in VS1.x? That was a sheer nightmare!

What good does it do to add over 40 unnecessary bracket chars in just one line of code?

</rant>

Help talking to stored proc

Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.

try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}

Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."

Can someone point me to what I’m doing wrong?

Hmm.. You mixed up every thing here..

First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one

And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.

Anyway, try this..

ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here

 @.sessionUservarchar(25),

 @.idOUT varchar(25) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)

return @.idOUT
END

and your code to

cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;

command.Parameters["@.sessionUser"].Value =  Session["UserName"]

 cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;

SqlDataReader reader = cmdReturnID.ExecuteReader();

 reader.close();

 Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;

Even though, the code looks real, its not... I typed by hand, so beware of small errors... 

|||

Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

And here is the modified code:

try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; }

Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!