Solved

asp.net three level drill down

Posted on 2014-03-13
9
827 Views
Last Modified: 2014-03-16
hello

looking for some advise, ideally an with an example. I am using vS2013 and trying to work out how to show a list of invoices (probably a gridview) with an SQL 2012 query providing the list.eg

1234 January 2014 £35
4567 Feb 2014  £38

then allow the user to drill down into one, and display a summary of the charge categories, eg

Calls  £20
Data  £10
SMS   £5

then allow user to select a charge category and display a completely of the individual charges.

01/01/14 Call 07711234444 £0.05
02/01/14 call 07736366333 £0.33
...



this final list will probably be a grid view to allow the user to sort dynamically.

so I probably need three gridviews, but should these be on separate pages, or one page and use multi views to hide the previous level, or can it be 1 clever control which allows multiple levels? I am fine with the using SQL to create the 3 different views.

Thanks

graham
0
Comment
Question by:Graham_Highlander
  • 4
  • 4
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39929562
What are you using for data source for the GridView?
0
 

Author Comment

by:Graham_Highlander
ID: 39929576
thanks TheLearndedOne, please see script, it will create a table, populate some sample data and show the 3 levels of query

-- CREATE table
CREATE TABLE [dbo].[ItemisedDetails](
	[InvoiceNumber] [nchar](10) NULL,
	[TranDate] [datetime] NULL,
	[TranType] [nchar](10) NULL,
	[Cost] [money] NULL
) ON [PRIMARY]

GO
-- insert some dummy data
INSERT INTO ItemisedDetails  (InvoiceNumber, TranDate, TranType, Cost) VALUES  (N'123', CONVERT(DATETIME, '2014-01-01 00:00:00', 102), N'Calls', .55)
INSERT INTO ItemisedDetails  (InvoiceNumber, TranDate, TranType, Cost) VALUES  (N'123', CONVERT(DATETIME, '2014-01-02 00:00:00', 102), N'Calls', .05)
INSERT INTO ItemisedDetails  (InvoiceNumber, TranDate, TranType, Cost) VALUES  (N'123', CONVERT(DATETIME, '2014-01-01 00:00:00', 102), N'Calls', .75)
INSERT INTO ItemisedDetails  (InvoiceNumber, TranDate, TranType, Cost) VALUES  (N'123', CONVERT(DATETIME, '2014-01-01 00:00:00', 102), N'Data', .05)
INSERT INTO ItemisedDetails  (InvoiceNumber, TranDate, TranType, Cost) VALUES  (N'123', CONVERT(DATETIME, '2014-01-01 00:00:00', 102), N'SMS', .52)
-- top level query
SELECT        InvoiceNumber, month(TranDate) as InvoiceMonth, sum (cost) as InvoiceTotal
FROM            ItemisedDetails
GROUP BY InvoiceNumber, month(TranDate)

-- user selects an invoice number, level 2
SELECT         TranType, sum (cost) as SubTotal
FROM            ItemisedDetails
GROUP BY InvoiceNumber, TranType
HAVING        (InvoiceNumber = N'123')

-- user then selects a Transaction type, final level
select TranDate,  Cost from ItemisedDetails where      (InvoiceNumber = N'123') and TranType = 'Calls'

Open in new window

exampleTableSampleDataandthreeQu.sql
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39929694
Are you talking about drilling down, so that there are WHERE clauses at levels 2 and 3 from the selections in the higher levels?
0
 

Author Comment

by:Graham_Highlander
ID: 39930113
absolutely, here are example queries:



-- top level query
SELECT        InvoiceNumber, month(TranDate) as InvoiceMonth, sum (cost) as InvoiceTotal
FROM            ItemisedDetails
GROUP BY InvoiceNumber, month(TranDate)

-- user selects an invoice number, level 2
SELECT         TranType, sum (cost) as SubTotal
FROM            ItemisedDetails
GROUP BY InvoiceNumber, TranType
HAVING        (InvoiceNumber = N'123')

-- user then selects a Transaction type, final level
select TranDate,  Cost from ItemisedDetails where      (InvoiceNumber = N'123') and TranType = 'Calls'

Open in new window


thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 39930177
Are you using SqlDataSource for the data source for the GridView controls?
0
 

Author Comment

by:Graham_Highlander
ID: 39930646
I haven't developed the web page yet, but yes I guess I would use SQLdatasource. just not sure how to interact between the 3 views. don't really want three pages with 3 different gridviews, thinking these days must be a control which allows for summary / details and a smooth way to navigate between

thanks
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39930795
You should be able to use the 3 GridView controls on the same page.  Each GridView will be invisible, until you bind data to it, or you could use the GridView.Visible property to show/hide the controls.

With the SqlDataSource, you can define parameters, as shown in this article.  These parameters can filter each drill-down GridView to the value from the previous selection.

Using Parameters with the SqlDataSource Control
http://msdn.microsoft.com/en-us/library/z72eefad.aspx

 <SelectParameters>
            <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
                                  Name="EmpID" Type="Int32" DefaultValue="0" />
          </SelectParameters>

Open in new window

0
 

Author Comment

by:Graham_Highlander
ID: 39932287
thanks for the advise and link, i have the logic all working.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Re-position the objects 7 96
ASP.net VB.net Load contents of a GridView  to Excel 2 27
Syntax Error 2 44
SQL Exceptions 3 37
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now