Reporting in ASP.NET

Post Reply
Neofriend
Captain
Captain
Posts: 103
Joined: Wed Dec 23, 2009 3:37 pm
Location: Pakistan

Reporting in ASP.NET

Post by Neofriend » Tue Jun 08, 2010 11:23 am

I've a very specific task to do. Based on certain dropdowns filters, I'd like a sql view to appear.

Earlier, I've used Microsoft Report in Windows Forms, but this time in ASP.NET gridview looked better to me, in my case.

I now have 7 dropdowns with datasources attached and a datagridview with a datasource and filters attached. So whenever we change the dropdown item, it reflects the changes on gridview.

So its almost done, BUT I really need a feature and that is. On each of the dropdowns, there should be an empty first item, on selecting it, the results in gridview, shouldn't count that filter.

But again, the problem lies in gridview logic, as its given filter parameters and on being empty, it won't return results. Any suitable solution?

Thanks
User avatar
Neo
Site Admin
Site Admin
Posts: 2642
Joined: Wed Jul 15, 2009 2:07 am
Location: Colombo

Re: Reporting in ASP.NET

Post by Neo » Tue Jun 08, 2010 2:54 pm

I understand what you need. Instead of making it blank, add something like "All" which is more meaningful. You can do that as below.

Code: Select all

<asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation" AutoPostBack="true"
        DataValueField="location" runat="server" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
        <asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
Notice <asp:ListItem Text="All" Value="%"></asp:ListItem> which does the thing. For your information, an example file is attached which does the rest of the processing.

Code: Select all

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridviewwithFiltering.aspx.vb"
    Inherits="GridviewwithFiltering"  %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head profile="http://gmpg.org/xfn/11">
    <link rel="stylesheet" type="text/css" href="gridview.css" media="all" />
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager" runat="server" />
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
        <h3>Gridview with Filtering</h3>
            <div class="GridviewDiv">
            <table style="width: 540px" border="0" cellpadding="0" cellspacing="1" class="GridviewTable">
                <tr >
                    <td style="width: 40px;">
                        ID
                    </td>
                    <td style="width: 120px;" >
                        First Name
                    </td>
                    <td style="width: 120px;">
                        Last Name
                    </td>
                    <td style="width: 130px;">
                        Department
                    </td>
                    <td style="width: 130px;">
                        Location
                    </td>
                </tr>
                <tr >
                    <td style="width: 40px;">
                    </td>
                    <td style="width: 120px;">
                    </td>
                    <td style="width: 120px;">
                    </td>
                    <td style="width: 130px;">
                        <asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment" AutoPostBack="true"
                            DataValueField="department" runat="server" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
                            <asp:ListItem Text="All" Value="%"></asp:ListItem>
                        </asp:DropDownList>
                    </td>
                    <td style="width: 130px;">
                        <asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation" AutoPostBack="true"
                            DataValueField="location" runat="server" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
                            <asp:ListItem Text="All" Value="%"></asp:ListItem>
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td colspan="5">
                        <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
                            AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10" CssClass="Gridview">
                            <Columns>
                                <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id" ItemStyle-Width="40px"
                                    ItemStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
                                    ItemStyle-Width="120px" />
                                <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
                                    ItemStyle-Width="120px" />
                                <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
                                    ItemStyle-Width="130px" />
                                <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
                                    ItemStyle-Width="130px" />
                            </Columns>
                        </asp:GridView>
                    </td>
                </tr>
            </table>
            </div>
            <asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT * FROM [T_Employees]" FilterExpression="Department like '{0}%'
                and Location like '{1}%'">
                <FilterParameters>
                    <asp:ControlParameter Name="Department" ControlID="ddldepartment" PropertyName="SelectedValue" />
                    <asp:ControlParameter Name="Location" ControlID="ddllocation" PropertyName="SelectedValue" />
                </FilterParameters>
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="dsPopulateDepartment" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT DISTINCT Department from [T_Employees]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="dsPopulateLocation" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT DISTINCT Location FROM [T_Employees]"></asp:SqlDataSource>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>
If you also want Gridview.css and GridviewwithFiltering.aspx.vb find it here.

Code: Select all

.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;padding:0.5em 0.5em 0.5em 0.5em;text-align:center}  
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }

Code: Select all

Partial Class GridviewwithFiltering
    Inherits System.Web.UI.Page

End Class
I'm sure you can get on with it now.
Neofriend
Captain
Captain
Posts: 103
Joined: Wed Dec 23, 2009 3:37 pm
Location: Pakistan

Re: Reporting in ASP.NET

Post by Neofriend » Tue Jun 08, 2010 6:49 pm

Cool, I'd love to comment something on your solution. But first, can you please tell how to target the dropdowns that has items inserted from GUI, rather than from a sqldatasource.

I mean is AppendDataBoundItems="true" and other attributes are neccessary? which ones? And what would we be mentioning in filter expression and filter parameters... a little confusion on attribute handling.

Thanks
Neofriend
Captain
Captain
Posts: 103
Joined: Wed Dec 23, 2009 3:37 pm
Location: Pakistan

Re: Reporting in ASP.NET

Post by Neofriend » Tue Jun 08, 2010 6:54 pm

I tried as per my understanding from your response, and so far it seems to work fine now. Thanks. Will keep working on it to get a hang of it.

But what is AppendDataBound... make any difference?
Neofriend
Captain
Captain
Posts: 103
Joined: Wed Dec 23, 2009 3:37 pm
Location: Pakistan

Re: Reporting in ASP.NET

Post by Neofriend » Tue Jun 08, 2010 6:59 pm

Okay, I think that AppendBound.... basically appends the locally added data items to the dropdown while keeping the database items appended right after it too.
Neofriend
Captain
Captain
Posts: 103
Joined: Wed Dec 23, 2009 3:37 pm
Location: Pakistan

Re: Reporting in ASP.NET

Post by Neofriend » Tue Jun 08, 2010 7:15 pm

Do you know who is a genius? :)
You are...thank you friend.

Well, I feel bad that I haven't ask you any good amount of questions in ASP.NET. Well maybe because I thought you were busy and it won't be so good to disturb you. I feel I've missed a lot of learning curve without you.

But never mind, I'll start asking now :) - am worried about that static thing but will only be able to know the approach when things are done. As I'm using my own creative approach :P for login system, both cookie and session based. And the values, userid basically are stored in static, so hope variable will hold the userid different for each user.

Let's see ... as I've implemented like that on all the pages code. But no worries, am sure will do something if it doesn't workout.

Watch out for a new thread :)
Post Reply

Return to “ASP & ASP.Net”