Put SUM in the last row

Hello everyone:

I have a detail table like,
Cust_Num Order_Num Prod_Num QTY Unit_Price Total
26389850 84662965 7273 1 26.99 NULL
26389850 84662965 6582 1 31.39 NULL
26389850 84662965 131 1 19.55 NULL
26389850 84662965 5141 1 8.89 NULL
26389850 84662965 7903 1 4.99 NULL
26389850 84662965 2180 1 6.35 NULL
26389860 84662975 7743 1 9.49 NULL
26389860 84662975 5745 1 12.99 NULL
26389860 84662975 78634 1 29.99 NULL
26389860 84662975 3640 1 4.65 NULL
26389860 84662975 3650 1 3.5 NULL
26389860 84662975 1840 1 8.95 NULL

I want to put SUM of unit_price in the last row for each order_num, like,
Cust_Num Order_Num Prod_Num QTY Unit_Price Total
26389850 84662965 7273 1 26.99 NULL
26389850 84662965 6582 1 31.39 NULL
26389850 84662965 131 1 19.55 NULL
26389850 84662965 5141 1 8.89 NULL
26389850 84662965 7903 1 4.99 NULL
26389850 84662965 2180 1 6.35 98.16
26389860 84662975 7743 1 9.49 NULL
26389860 84662975 5745 1 12.99 NULL
26389860 84662975 78634 1 29.99 NULL
26389860 84662975 3640 1 4.65 NULL
26389860 84662975 3650 1 3.5 NULL
26389860 84662975 1840 1 8.95 69.57

Any suggest is appreciated.

ZYTThe easiest method would be to use an application language, such as JAVA, .NET, C#, and what other languages exist. If you want to do it in the database, you can just a sub-query and a union statement.

Regards,|||look up GROUP BY WITH ROLLUP|||another way would be to use WITH ROLLUP in your query

the tricky part is deciding what goes in the GROUP BY :)

hint: i bet you don't really want the SUM of unit price, but rather the SUM of quantity times unit price

your example shows only Qty=1, by the way|||another way would be to use WITH ROLLUP

[Guiness commerical]

Brilliant!

[/Guiness commerical]

Pulling row values from a dataset using embedded code?

I am trying to figure out a way to pull the values of one of my report
columns once the report has been rendered to create a comma separated list of
those values to pass as a paramater to another report. I figure i will have
to do this in the code part of RS. Can anyone help me with the proper
functions and syntax to walk through the rows returned by my report and pull
the values from the column I need.
I imagine it would look like this
function GetItemList()
Dim rtnstr As String
Dim x As Interger
Set rtnstr = ""
for x = 0 to (RowNumber("DatasetName")-1)
rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
next
return rtnstr
end function
What I am stuck on is how to pull the values from the column in the
particular dataset I need and also how to determine the Scope of the dataset
to know how long th loop needs to run for.
ThanksOut of the box, you can only base a report on the following data sources: SQL
Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
into one of these categories; in other words, there is no data processing
extension that allows you to read data from one report into another report.
You should re-design your solution. What about writing a stored procedure
that writes the data to a second table, appending commas as required to
create the csv format you want? You could then report off this second table?
Charles Kangai, MCDBA, MCT
"jordang" wrote:
> I am trying to figure out a way to pull the values of one of my report
> columns once the report has been rendered to create a comma separated list of
> those values to pass as a paramater to another report. I figure i will have
> to do this in the code part of RS. Can anyone help me with the proper
> functions and syntax to walk through the rows returned by my report and pull
> the values from the column I need.
> I imagine it would look like this
> function GetItemList()
> Dim rtnstr As String
> Dim x As Interger
> Set rtnstr = ""
> for x = 0 to (RowNumber("DatasetName")-1)
> rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> next
> return rtnstr
> end function
> What I am stuck on is how to pull the values from the column in the
> particular dataset I need and also how to determine the Scope of the dataset
> to know how long th loop needs to run for.
> Thanks|||Hi Charles
My Report is already driven by a stored procedure, however, there are local
filters the user can apply to the report through the report viewer as well.
So As far as the stored proc generates the dataset, I'm good to handle those
results and create the list i need. The problem is when the user applies a
filter that is specific to that report and thus not sent back to the stored
proc. I was hoping there was a way I could avoid rewriting my stored proc to
include all my report parameters, or furthermore, writing another stored proc
to generate my list.
Thanks for your help.
"Charles Kangai" wrote:
> Out of the box, you can only base a report on the following data sources: SQL
> Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
> into one of these categories; in other words, there is no data processing
> extension that allows you to read data from one report into another report.
> You should re-design your solution. What about writing a stored procedure
> that writes the data to a second table, appending commas as required to
> create the csv format you want? You could then report off this second table?
> Charles Kangai, MCDBA, MCT
>
> "jordang" wrote:
> > I am trying to figure out a way to pull the values of one of my report
> > columns once the report has been rendered to create a comma separated list of
> > those values to pass as a paramater to another report. I figure i will have
> > to do this in the code part of RS. Can anyone help me with the proper
> > functions and syntax to walk through the rows returned by my report and pull
> > the values from the column I need.
> >
> > I imagine it would look like this
> >
> > function GetItemList()
> > Dim rtnstr As String
> > Dim x As Interger
> > Set rtnstr = ""
> > for x = 0 to (RowNumber("DatasetName")-1)
> > rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> > next
> > return rtnstr
> > end function
> >
> > What I am stuck on is how to pull the values from the column in the
> > particular dataset I need and also how to determine the Scope of the dataset
> > to know how long th loop needs to run for.
> >
> > Thanks

Pull data from Cache 5.0 to SQL 2000 (fast?)

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five
days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational
format (SQL 2000)?

Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.

Thanks,

-p"Pippen" <123@.hotmail.com> wrote in message
news:uadBb.1525$8y1.14237@.attbi_s52...
> I'm currently working with a 10 million plus row database with the data
> residing on a Unix box with Cache 5.0. The problems is that it can take
five
> days to pull one table from Cache to SQL 2000 using the ODBC connection
> provided by Cache in a SQL 2000 DTS package. I think the real problem is
> converting the data from the post relational format (Cache) to a
relational
> format (SQL 2000)?
> Does anyone have any ideas / suggestions on how to speed this transfer of
> data? I'm very new to Cache and any help would be greatly appreciated.

I don't know a thing about Cache, but it could be several things.

Is the table you're pulling the data into have a large number of indices?
If so, it is probably faster to drop them, load the data and then rebuild
them.

Also, you can try dumping the cache data to a text file of some sort and
using BCP or a DTS package to load it.

As reference, I load 3 tables compromising I think about 10 gig of data (I'd
have to look it up) and index them in I think under 6 hours. And that's on
moderately old hardware (3 years old).

I do this 4 times a year for 3 different databases. If I had to do it more
often I could probably speed it up even faster than that.

So, it's quite possible you may be able to speed this up.

> Thanks,
> -p|||"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:5AdBb.198332$1N3.102110@.twister.nyroc.rr.com. ..
> "Pippen" <123@.hotmail.com> wrote in message
> news:uadBb.1525$8y1.14237@.attbi_s52...
> > I'm currently working with a 10 million plus row database with the data
> > residing on a Unix box with Cache 5.0. The problems is that it can take
> five
> > days to pull one table from Cache to SQL 2000 using the ODBC connection
> > provided by Cache in a SQL 2000 DTS package. I think the real problem is
> > converting the data from the post relational format (Cache) to a
> relational
> > format (SQL 2000)?
> > Does anyone have any ideas / suggestions on how to speed this transfer
of
> > data? I'm very new to Cache and any help would be greatly appreciated.
> I don't know a thing about Cache, but it could be several things.
> Is the table you're pulling the data into have a large number of indices?
> If so, it is probably faster to drop them, load the data and then rebuild
> them.
> Also, you can try dumping the cache data to a text file of some sort and
> using BCP or a DTS package to load it.
> As reference, I load 3 tables compromising I think about 10 gig of data
(I'd
> have to look it up) and index them in I think under 6 hours. And that's
on
> moderately old hardware (3 years old).
> I do this 4 times a year for 3 different databases. If I had to do it
more
> often I could probably speed it up even faster than that.
> So, it's quite possible you may be able to speed this up.
>
> > Thanks,
> > -p
>
Thanks for the advice. I probably should have mentioned that the data is
going in to empty non indexed tables...

-js|||"Pippen" <123@.hotmail.com> wrote in message
news:9koBb.484514$Tr4.1330184@.attbi_s03...
> Thanks for the advice. I probably should have mentioned that the data is
> going in to empty non indexed tables...

Hmm, interesting. I'd then try to dump the data out of Cache and use BCP
and see if that's any faster.

At the very least it may break it into two parts and give you an idea of
which one is the slow part.

> -js