datarow and aggregate functions

I am building a system that pulls a large dataset from the sql backend. I am
then applying a row filter to the dataview, i have 5 filters, day, week,
month, quarter and year.

However i need to total up 2 / 3 of the columns depending on the dataset and
i am unsure of how to do this. I have tried adding a datacolumn to the
dataview and adding an expression but i get an error
Message "Object reference not set to an instance of an object." String.

I am also thinking about doing a loop through the dataview and adding up the
values in a loop. But for some reason i don't think this is a good idea,
maybe in Classic ASP, but dot net as i am led to believe has the power to do
all this builtin.

if anyone could offer any suggestions or places to look that has examples of
what i want to achieve i would be very grateful.

I am doing this in a loop a sample of the code is below

Dim TotalAppsWrit As DataColumn = New DataColumn

With TotalAppsWrit
.DataType = System.Type.GetType("System.Decimal")
.ColumnName = "TotalApps"
End With

cmd.CommandTimeout = 1000
cmd.Connection = conn

Try

'there are currently 8 views that we pull information from so we
need to do a main loop for these
For n = 0 To 7

'here we fill the dataset with the information for the view
we are working on
cmd.CommandText = AllTables(n, 1)
da.Fill(ds, AllTables(n, 0))

'get the values into the variables

'we will do a loop through the seven tables using the
upperbound function of the array of tables
'if any is added in the future then this wont need to be
modified.

dt = ds.Tables(AllTables(n, 0)) 'create the datatable
dt.Columns.Add(TotalAppsWrit)
dv = New DataView(dt) 'create the dataview linked to the
datatable
dv.Table.Columns("TotalApps").Expression = "count('" &
CType(drv.Item("Total_Apps_Writ"), String) & "')"

'loop through the datatables and add each val to the
collection ie. RetVol & "Day"
For i As Integer = 0 To 4
Select Case i
Case 0
'this will do the days values
'but we need to take todays date and subtract
the period
'date is >
DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) and <
DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period)
dv.RowFilter = "Date_App_Written > '" &
DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "' AND
Date_App_Written < '" &
DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "'"


Select Case AllTables(n, 0)
'0 is always the volume of apps, this is
integer type
'1 is always the total value of apps, this
is double type
'2 is always the total fees of the apps,
this is double type
Case "CC", "Sourcing"
drv = dv.Item(0)

AV.add("dy" & AllTables(n, 0) &
"AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
AV.add("dy" & AllTables(n, 0) &
"AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))

Case Else
drv = dv.Item(0)
AV.add("dy" & AllTables(n, 0) &
"AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
AV.add("dy" & AllTables(n, 0) &
"AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))
AV.add("dy" & AllTables(n, 0) & "Fees",
New DoubleVariables(CType(drv.Item(2), Double)))
End Select

Steven,

For to agregate a column is the Compute

http://msdn.microsoft.com/library/d...omputetopic.asp

I hope this helps,

Cor


"steven scaife" <stevenscaife@discussions.microsoft.com> schreef in bericht
news:AC1304B8-B937-4EF8-873A-9FD9E9002233@microsoft.com...
>I am building a system that pulls a large dataset from the sql backend. I
>am
> then applying a row filter to the dataview, i have 5 filters, day, week,
> month, quarter and year.
>
> However i need to total up 2 / 3 of the columns depending on the dataset
> and
> i am unsure of how to do this. I have tried adding a datacolumn to the
> dataview and adding an expression but i get an error
> Message "Object reference not set to an instance of an object." String.
>
> I am also thinking about doing a loop through the dataview and adding up
> the
> values in a loop. But for some reason i don't think this is a good idea,
> maybe in Classic ASP, but dot net as i am led to believe has the power to
> do
> all this builtin.
>
> if anyone could offer any suggestions or places to look that has examples
> of
> what i want to achieve i would be very grateful.
>
> I am doing this in a loop a sample of the code is below
>
> Dim TotalAppsWrit As DataColumn = New DataColumn
>
> With TotalAppsWrit
> .DataType = System.Type.GetType("System.Decimal")
> .ColumnName = "TotalApps"
> End With
>
> cmd.CommandTimeout = 1000
> cmd.Connection = conn
>
> Try
>
> 'there are currently 8 views that we pull information from so
> we
> need to do a main loop for these
> For n = 0 To 7
>
> 'here we fill the dataset with the information for the view
> we are working on
> cmd.CommandText = AllTables(n, 1)
> da.Fill(ds, AllTables(n, 0))
>
> 'get the values into the variables
>
> 'we will do a loop through the seven tables using the
> upperbound function of the array of tables
> 'if any is added in the future then this wont need to be
> modified.
>
> dt = ds.Tables(AllTables(n, 0)) 'create the datatable
> dt.Columns.Add(TotalAppsWrit)
> dv = New DataView(dt) 'create the dataview linked to the
> datatable
> dv.Table.Columns("TotalApps").Expression = "count('" &
> CType(drv.Item("Total_Apps_Writ"), String) & "')"
>
> 'loop through the datatables and add each val to the
> collection ie. RetVol & "Day"
> For i As Integer = 0 To 4
> Select Case i
> Case 0
> 'this will do the days values
> 'but we need to take todays date and subtract
> the period
> 'date is >
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) and <
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period)
> dv.RowFilter = "Date_App_Written > '" &
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "' AND
> Date_App_Written < '" &
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "'"
>
>
> Select Case AllTables(n, 0)
> '0 is always the volume of apps, this is
> integer type
> '1 is always the total value of apps, this
> is double type
> '2 is always the total fees of the apps,
> this is double type
> Case "CC", "Sourcing"
> drv = dv.Item(0)
>
> AV.add("dy" & AllTables(n, 0) &
> "AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
> AV.add("dy" & AllTables(n, 0) &
> "AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))
>
> Case Else
> drv = dv.Item(0)
> AV.add("dy" & AllTables(n, 0) &
> "AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
> AV.add("dy" & AllTables(n, 0) &
> "AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))
> AV.add("dy" & AllTables(n, 0) & "Fees",
> New DoubleVariables(CType(drv.Item(2), Double)))
> End Select
>