asked on

asp.net three level drill down

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

Bob Learned

What are you using for data source for the GridView?

Graham_Highlander

ASKER

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

Bob Learned

Are you talking about drilling down, so that there are WHERE clauses at levels 2 and 3 from the selections in the higher levels?

Graham_Highlander

ASKER

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

Bob Learned

Are you using SqlDataSource for the data source for the GridView controls?

Graham_Highlander

ASKER

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

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Graham_Highlander

ASKER

thanks for the advise and link, i have the logic all working.