J
Julio Sarmiento
I have a table containing the following fields:
rep numbers, year, month, qty, revenue, credit apps, new accounts.
The data in this table reflects the rep's daily numbers. What I need to do
is sum the columns and rank each column totals against all the records in
the table. For example - assume that the figure below are the monthly
totals for four reps.
Rep Number Year Month Qty Revenue Credit Apps
New Accounts
100 2004 1 5 120,000.00
15 7
101 2004 1 15 90,000.00
4 0
102 2004 1 7 30,000.00
22 3
103 2004 1 4 60,000.00
11 17
Now I must rank the reps accordingly and display the following on a
datagrid!!!:
Rep Number Year Month QtyRank Qty RevenueRank
Revenue CreditAppsRank Credit Apps NewAccounts Rank New
Accounts
100 2004 1 3 5 1
120,000.00 4 5 2
10
101 2004 1 1 15 4
30,000.00 2 14 3
15
102 2004 1 2 7 3
60,000.00 1 22 4
3
103 2004 1 4 4 2
90,000.00 3 11 1
17
"
To get the rankings I ran four query that returned the rep number and the
sum from each column by descending sum order (1 column per query - "select
repnum, sum(qty) from table" - "select repnum, sum(revenue) from table"...)
After retrieving all the records, I call a routine the goes through every
record in each dataset.table(x) and added a column ranking to each table
with the dataset.
My problem and question come from this part. How do I display a datagrid
where I combine the four dataset.tables() into one datagrid??? I thought of
data relations, but as you can see, the four tables do not have a parent -
child relationship. Can anyone see a way for me to do this??? If so,
please give me a brief sample. I have spent a better part of the week
researching this but have yet to find an easy solution!!! There may not be
an easy solution, however, I thought of everything from a stored procedure
to creating a data table in asp.net (VB) code. The stored procedure was a
bit complicated and the data table was not very cost efficient when it came
to speed.
Help!!!!
Thanks,
Julio
rep numbers, year, month, qty, revenue, credit apps, new accounts.
The data in this table reflects the rep's daily numbers. What I need to do
is sum the columns and rank each column totals against all the records in
the table. For example - assume that the figure below are the monthly
totals for four reps.
Rep Number Year Month Qty Revenue Credit Apps
New Accounts
100 2004 1 5 120,000.00
15 7
101 2004 1 15 90,000.00
4 0
102 2004 1 7 30,000.00
22 3
103 2004 1 4 60,000.00
11 17
Now I must rank the reps accordingly and display the following on a
datagrid!!!:
Rep Number Year Month QtyRank Qty RevenueRank
Revenue CreditAppsRank Credit Apps NewAccounts Rank New
Accounts
100 2004 1 3 5 1
120,000.00 4 5 2
10
101 2004 1 1 15 4
30,000.00 2 14 3
15
102 2004 1 2 7 3
60,000.00 1 22 4
3
103 2004 1 4 4 2
90,000.00 3 11 1
17
"
To get the rankings I ran four query that returned the rep number and the
sum from each column by descending sum order (1 column per query - "select
repnum, sum(qty) from table" - "select repnum, sum(revenue) from table"...)
After retrieving all the records, I call a routine the goes through every
record in each dataset.table(x) and added a column ranking to each table
with the dataset.
My problem and question come from this part. How do I display a datagrid
where I combine the four dataset.tables() into one datagrid??? I thought of
data relations, but as you can see, the four tables do not have a parent -
child relationship. Can anyone see a way for me to do this??? If so,
please give me a brief sample. I have spent a better part of the week
researching this but have yet to find an easy solution!!! There may not be
an easy solution, however, I thought of everything from a stored procedure
to creating a data table in asp.net (VB) code. The stored procedure was a
bit complicated and the data table was not very cost efficient when it came
to speed.
Help!!!!
Thanks,
Julio