• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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
0
Graham_Highlander
Asked:
Graham_Highlander
  • 4
  • 4
1 Solution
 
Bob LearnedCommented:
What are you using for data source for the GridView?
0
 
Graham_HighlanderAuthor Commented:
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
 
Bob LearnedCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Graham_HighlanderAuthor Commented:
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
 
Bob LearnedCommented:
Are you using SqlDataSource for the data source for the GridView controls?
0
 
Graham_HighlanderAuthor Commented:
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
 
Bob LearnedCommented:
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
 
Graham_HighlanderAuthor Commented:
thanks for the advise and link, i have the logic all working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now