Multiple Datasets to one Datagrid

[Julio Sarmiento]

Can anyone help me????

I have a table that I need to get the sum of four individual fields and then
rank them from highest to lowest total. After summing up the four fields
individually and ranking them, I have to place them in a single datagrid.

Example:
Table: contains fields for Revenue, Quantity, Credit Apps, Accounts by
rep (not my table!!!)

SQL Statement: Select sum(Revenue), Sum(Quantity), Sum(CreditApps),
Sum(Accounts)...

Rank each column individual from highest Revenue to Lowest, from highest
quantity to lowest etc...

My solution was to query the tables four time (1 for each field) hence
creating four datasets. I then have a sub routine that adds a field titled
Rank to each dataset and adds the sequence number for the ranking.

NOW, I have to join the four tables but not as parent - child but as
additional fields for the same table

Outcome: Total Revenue, Ranking, Total Quantity, Ranking, Total Credit
Apps, Ranking, Total Accounts Ranking...

HOW DO I DO THAT???? HELP???

Deadline looming near!!!

Thanks,
Julio

 

[William Ryan]

Julio, you can use a DataRelation and an expression column to do
this...Expression Columns support SUM which looks like the only aggregate
you need (they support a bunch of other ones, but that's irrelevant) You'll
can use a DataRelation which will allow you to get the Group By
Functionality http://www.knowdotnet.com/articles/datarelation.html. This is
important b/c if you only have one table and you use SUM(Price) for instance
as the expression every row will have the same value in its SUM(Price)
column

 

[Julio Sarmiento]

Ryan, thanks for the input and link. I am going to read your article to
learn more about it.Hopefully, it will help. If not, you will be hearing
(reading) from me again...

Thanks!

 

[Julio Sarmiento]

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

Dim cnn As New SqlConnection("data source=test;user id=sa;initial
catalog=test")

Dim da As SqlDataAdapter

Dim strSQL As String

Dim ds As New DataSet()

strSQL = "select id, quantitysold from testRelations"

da = New SqlDataAdapter(strSQL, cnn)

da.Fill(ds, "quantity")

cnn.Open()

strSQL = "select id, dollarsSold from testRelations"

da = New SqlDataAdapter(strSQL, cnn)

da.Fill(ds, "dollarsSold")

'For simple single column relation

Dim sample As New DataRelation("hope", ds.Tables("quantity").Columns("ID"),
ds.Tables("dollarsSold").Columns("ID"))



ds.Relations.Add(sample )

Me.DataGrid1.DataSource = ds.Tables(0).DefaultView

Me.DataGrid2.DataSource = ds.Tables(1).DefaultView

Me.DataGrid3.DataSource = ds.Tables(2).DefaultView ' How do I veiw the
relation???

Me.DataGrid1.DataBind()

Me.DataGrid2.DataBind()

Me.DataGrid3.DataBind()

cnn.Close()

End Sub