multiple groupings of companies within asp.net report

[karlid@yahoo.com]

Hi all,

The DataGrid (or reader) sure makes life easier for the vast majority
of sql query display jobs but I've come across something that is almost
making me wish for the old classic asp days. I'm not ready to start
coding in the aspx page like a class asp'er yet so i'm hoping someone
has some advice for me.

My problem is that I need to show a report with the following pattern.
The sql is easy but how to make it display nicely in a single (or
variable number) datagrid control has me scratching my head.

The desired result looks like this:

Company A
user x 20
user y 30
user z 10
-----------------
60

Company B
user c 14
user d 5
user e 23
user f 100
-----------------
142

Company C
user k 1
user l 39
user w 30
-----------------
70


So... displaying a single company in a datagrid would be easy but how
do i display all three in a single datagrid with them grouped by
company? There are a variable number of companies.

The sql result I have looks something like this:

Company A user x 20
Company A user y 30
Company A user z 10
Company B user c 14
Company B user d 5
Company B user e 23
Company B user f 99
Company C User k 1
Company C user l 39
Company C user w 30

Any help is much appreciated.

[Elton Wang]

Hi,

You can use DataList (that loops thru Companies) + DataGrid (data source =
DataView, set RowFilter for different company).

HTH

<karlid@yahoo.com> wrote in message
news:1122358414.967570.63030@g44g2000cwa.googlegro ups.com...

> Hi all,
>
> The DataGrid (or reader) sure makes life easier for the vast majority
> of sql query display jobs but I've come across something that is almost
> making me wish for the old classic asp days. I'm not ready to start
> coding in the aspx page like a class asp'er yet so i'm hoping someone
> has some advice for me.
>
> My problem is that I need to show a report with the following pattern.
> The sql is easy but how to make it display nicely in a single (or
> variable number) datagrid control has me scratching my head.
>
> The desired result looks like this:
>
> Company A
> user x 20
> user y 30
> user z 10
> -----------------
> 60
>
> Company B
> user c 14
> user d 5
> user e 23
> user f 100
> -----------------
> 142
>
> Company C
> user k 1
> user l 39
> user w 30
> -----------------
> 70
>
>
> So... displaying a single company in a datagrid would be easy but how
> do i display all three in a single datagrid with them grouped by
> company? There are a variable number of companies.
>
> The sql result I have looks something like this:
>
> Company A user x 20
> Company A user y 30
> Company A user z 10
> Company B user c 14
> Company B user d 5
> Company B user e 23
> Company B user f 99
> Company C User k 1
> Company C user l 39
> Company C user w 30
>
> Any help is much appreciated.
>

[karlid@yahoo.com]

Hi,

Your suggestion sounds most promising but i'm unclear on the datagrid
part. Would I be dynamically creating DataGrid objects for each
company or is there a way to reuse one? Perhaps you know of a link to
an article that describes this procedure?

Cheers,

[Elton Wang]

Hope following code is helpful


<asp:DataList id="dataList" runat="server" RepeatDirection=Vertical
BorderWidth="1">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Company") %></br>
<asp:DataGrid ID="datagrid" Runat=server ></asp:DataGrid>
<hr>
<%# DataBinder.Eval(Container.DataItem, "QTY") %></br>
</ItemTemplate>
</asp:DataList>

Code:

Load DataList:

SqlDataAdapter dap = new SqlDataAdapter("Select Company, Sum(QTY) AS QTY
FROM table_name GROUP BY Company",CONNECTION_STRING);
DataTable companyTable = new DataTable();
dap.Fill(companyTable);
dap.Dispose();
this.datLst.DataSource = companyTable;
this.datLst.DataBind();


In DataList_ItemDataBound event

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType ==
ListItemType.AlternatingItem)
{
DataGrid dgrd = (DataGrid)e.Item.FindControl("datagrid");
dgrd.ShowHeader = false;
DataRowView drv = (DataRowView)e.Item.DataItem;
string connString = "server=(local);database=CustomData;Integrated
Security=SSPI";

string sql = "Select Customer, QTY from customers where Company =
@company";
SqlDataAdapter dap = new SqlDataAdapter(sql,connString);
dap.SelectCommand.Parameters.Add("@company",
drv["Company"].ToString().Trim());
DataTable dataTable = new DataTable();
dap.Fill(dataTable);

dgrd.DataSource = dataTable;
dgrd.DataBind();

}



<karlid@yahoo.com> wrote in message
news:1122393109.085494.22720@z14g2000cwz.googlegro ups.com...

> Hi,
>
> Your suggestion sounds most promising but i'm unclear on the datagrid
> part. Would I be dynamically creating DataGrid objects for each
> company or is there a way to reuse one? Perhaps you know of a link to
> an article that describes this procedure?
>
> Cheers,
>

[karlid@yahoo.com]

Wow, thanks for the code samples. That basically solves the problem
for me. Thanks very much.

This whole thing has opened a whole new world to me. Now I see that
you can nest databound controls wihin one another for some very nifty
results.

Thanks again!