Running Value with Subtotals and Grand Total

I have a Division parameter (select one or many) and I am grouping my Sales
Goal row by Division with columns for months. I can show the running year to
date total across the columns ok, but I can't figure out how to reset the
running value when I get to a new Division grouping. It is adding the second
division to the results from the first grouping which is not what I want.
How do I this?
I also want a grand total below all the groupings
TIA
DeanI have been looking for help with doing the subtotals and the grand totals of
a report and I came acrross the Running value aggregate and I am no expert
but it seems to me you need to specify the scope for your runningvalue and
that will reset it if the scope changes.
"Dean" wrote:
> I have a Division parameter (select one or many) and I am grouping my Sales
> Goal row by Division with columns for months. I can show the running year to
> date total across the columns ok, but I can't figure out how to reset the
> running value when I get to a new Division grouping. It is adding the second
> division to the results from the first grouping which is not what I want.
> How do I this?
> I also want a grand total below all the groupings
> TIA
> Dean
>
>

running total of differences in Crystal Reports

Hi everyone. I need help writing a function in Crystal Reports. I need a running total of the difference of two columns. I know the starting value and I need something like:

Net := Start + (SubCol - AddCol)

in a loop I guess.

So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)

Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2Why don't you try creating new formula for each runnig total, add a group, copy all fields from detail into group, hide detail and do the difference after using the running total formulas??

Field1 field2 field3
1 1 1
3 4 6
--------------------
detail rt1 rt2 rt3
--------------------
group rt1-rt2

Rob

Hi everyone. I need help writing a function in Crystal Reports. I need a running total of the difference of two columns. I know the starting value and I need something like:

Net := Start + (SubCol - AddCol)

in a loop I guess.

So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)

Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2|||Hi Rob. Thanks for the response. I finally got it to work by declaring a Shared variable and putting it in the report header. Global wouldn't work for some reason. Then, using that variable, I used the same function:

Shared Numbervar x;
x := x + ({Col.Sub} - {Col.Add})

and put it in the details section. Seems to work!

Running sum

I'm not quite sure if i'm aiming for the impossible, but:
In a matrix, i have time (weeks) as columns, items as rows and qty as detail
and i would like to have a running sum for the columns.
This problems arise because i need to columns to be dynamic.
Any suggestions would be more than welcome
Example:
Records:
Item 1, week 1, qty 5
Item 1,week 2, qty 3
Item 1, week 3, qty 4
Matrix
week 1 week 2 week 3
Item 1 5 8 12I seem to have found the solution (RunningValue(Expression, Function,
Scope)).
Sorry for the inconvenience
"Claus Aaberg" <caa@.phaseone.com> wrote in message
news:%23TPAKwKcEHA.368@.TK2MSFTNGP10.phx.gbl...
> I'm not quite sure if i'm aiming for the impossible, but:
> In a matrix, i have time (weeks) as columns, items as rows and qty as
detail
> and i would like to have a running sum for the columns.
> This problems arise because i need to columns to be dynamic.
> Any suggestions would be more than welcome
> Example:
> Records:
> Item 1, week 1, qty 5
> Item 1,week 2, qty 3
> Item 1, week 3, qty 4
> Matrix
> week 1 week 2 week 3
> Item 1 5 8 12
>sql

Running stored procedure multiple times

I'm binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I'm using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?

newSqlCommand = New SqlCommand("getDistinctValues", newConn)
newSqlCommand.CommandType = CommandType.StoredProcedure

Dim ownrParam As New SqlParameter("@.owner_id", SqlDbType.Int)
Dim colParam As New SqlParameter("@.column_name", SqlDbType.VarChar)
newSqlCommand.Parameters.Add(ownrParam)
newSqlCommand.Parameters.Add(colParam)

ownrParam.Value = OwnerID

colParam.Value = "Make"
newConn.Open()
ddlMake.DataSource = newSqlCommand.ExecuteReader()
ddlMake.DataTextField = "distinct_result"
ddlMake.DataBind()
newConn.Close()

colParam.Value = "Model"
newConn.Open()
ddlModel.DataSource = newSqlCommand.ExecuteReader()
ddlModel.DataTextField = "distinct_result"
ddlModel.DataBind()
newConn.Close()

and so on for 9 columns…

First of all, reusing the connection will increase your performance significantly.

|||

Hi

if you create a stored procedure returning multiple result sets and load these result sets into the DataSet, you will avoid multiple roundtrips to SQL server.

-yuriy

|||

Under ADO.NET, only the first Open opens the database, the rest pick up a connection from a pool as closing the database connection does not actually close the connection but returns it to a pool. You still open and close in your code, but what happens behind the scenes is slightly different from previous access methods such as ADO.

|||

Thanks guys.

Tatworth, I believe you are saying that I'm reusing the same connection by doing it this way. In that case, is it an acceptable and reasonably efficient way to do things?

I need the DISTINCT values for these columns, so I can't simply select all the values for all 9 columns at once, unless I'm prepared to eliminate repeated values in code. Is it possible for a single stored procedure to return 9 sets of distinct values?

|||

>Tatworth, I believe you are saying that I'm reusing the sameconnection by doing it this way. In that case, is it an acceptable andreasonably efficient way to do things? yes

> need the DISTINCT values for these columns, so I can't simplyselect all the values for all 9 columns at once, unless I'm prepared toeliminate repeated values in code. Is it possible for a single storedprocedure to return 9 sets of distinct values?
It is possible as a stored procedure can return multiple recordsets but not practical as ASP.NET can only read the first recordset without extra code - so keep doing it the way you are doing it now.

I will be unable to access this site for some days, so I suggest you mark this thread as closed and open a new thread if you have further queries.

|||

There are multiple approaches to handling the above depending on your situation, and the data involved.

Normally, I would use a sqldatasource (9 of them), and bind the dropdowns to the datasources, and evaluate from there if necessary. The SqlDatasource control is nice because you can quickly and easily add caching if necessary (If you think that the ownerid will be called multiple times in a short period of time and the results should always remain the same for each call, then it'd be very helpful).

However, since you've gone down the approach of handling it manually, the only thing that is going to really buy you anything (other than coding any caching yourself), is to return the results in a single trip from the database. One such method would be to do something like return a single resultset that has 2 values, one being the column name, the other the value (one of the distinct values) likes:
col1 val1

col1 val2

col1 val3

col2 val1

col2 val2

Then you can retrieve this resultset from the database into a dataset. With the dataset, you can then derive a view that has a filter clause (Like a WHERE clause), and bind to the dataview.

Another approach, although I have not personally tested this is to use the datareaders nextresult property. Like:

conn.open

dim dr as sqldatareader=cmd.executereader

{Your code to prep control 1 here}

control1.datasource=dr

control1.databind

{Ending stuff for control 1 here}

dr.nextresult

{Your code to prep control 2 here}

control2.datasource=dr

control2.databind

{Ending stuff for contorl 2 here}

dr.nextresult

...

This approach would be extremely easy to modify your current code into, as you would just make need to make a master stored procedure like:

CREATE PROCEDURE dbo.sp_TheMaster ( @.ownerid int) AS

EXEC sp_OriginalProcedure @.ownerid,'column1'

EXEC sp_OriginalProcedure @.ownerid,'column2'

...

That would return the 9 resultsets that you need, although I'm not sure of it's performance, but I suspect it would be better.

|||

Thanks for your detailed response Motley. I considered the sqldatasource – I think it would be possible to use a single datasource for all 9 queries – but I chose the RP option only because it seems tidier to me (less 'markup'). I'm assuming there would be no performance difference?

As time is an issue, and as it seems that what I'm doing is not disastrous in performance terms, I'll leave it and monitor it when the site is uploaded.

Thanks all for your input.