Solved

asp.net three level drill down

Posted on 2014-03-13
9
822 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

15 Experts available now in Live!

Get 1:1 Help Now