Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

asp.net three level drill down

Posted on 2014-03-13
9
Medium Priority
?
874 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 1500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

636 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