Introduction
This article provides a step-by-step demo on how to create drill-through reports in local mode using SQL Server 2005, Microsoft Application Blocks, and the ReportViewer control in ASP.NET 2.0.
Scenario
We will create a parent report, listing all orders placed by each customer. When the user clicks on the [Order ID] field on the ReportViewer, a drill-through report is displayed to show the line items which make up the parent level order summary. When the user clicks on the [Product ID] field on each line item, our demo will display another level of drill-through which shows the product details.
Step 1: Create a stored procedure to list all orders and its dollar total per customer. I have limited the number of display records by selecting only two customers in this demo.
ALTER PROCEDURE List_Customers_OrderTotal
AS
SELECT Customers.CompanyName,
Orders.OrderID,
Orders.OrderDate,
SUM([Order Details].Quantity * [Order Details].UnitPrice) AS TotalDollars
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.CustomerID IN ('THEBI', 'THECR')
GROUP BY CompanyName, Orders.OrderID, OrderDate
ORDER BY CompanyName, Orders.OrderID, OrderDate
RETURN
Step 2: Create a stored procedure to show order details. Results from this stored procedure supplies data to our level 1 drill-through report.
ALTER PROCEDURE Show_OrderDetails ( @OrderID int )
AS
SELECT [Order Details].OrderID,
Products.ProductID,
Products.ProductName,
[Order Details].UnitPrice,
[Order Details].Quantity
FROM [Order Details] INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].OrderID = @OrderID
RETURN
Step 3: Create a stored procedure to show product details. Result from this stored procedure supplies data to our level 2 drill-through report.
ALTER PROCEDURE Show_Products ( @ProductID int )
AS
SELECT Products.ProductID,
Products.ProductName As Product,
Categories.CategoryName As Category,
Categories.Description,
Suppliers.CompanyName AS Supplier,
Suppliers.Phone AS SupplierPhone
FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE ProductID=@ProductID
RETURN
Step 4: Create data tables in a dataset (.xsd) under the App_Code folder. This .xsd is used to store data retrieved from each stored procedure. Each data table will serve as a data source for an individual .rdlc. We will create three .rdlcs in the next few steps.
Step 5: Create a parent report (Parent.rdlc)
Step 6: Create drill-through report #1 (Level1.rdlc)
Step 7: Define the Report Parameter for report #1 (Level1.rdlc). When the user clicks on the OrderID field in the parent.rdlc, the OrderID value is passed to Level1.rdlc. The OrderID value is used to retrieve the correct order detail line item.
Click in the body of Level1.rdlc. Click the menu option �Report�. Select �Report Parameters�. Click �Add� to add OrderID to the Report Parameters list.
Step 8: Create drill-through report #2 (Level2.rdlc)
Step 9: Define Report Parameter for report #2 (Level2.rdlc). When the user clicks on the ProductID field in Level1.rdlc, the ProductID value is passed to Level2.rdlc to pull up the correct product information.
Click in the body of Level2.rdlc. Click the menu option �Report�. Select �Report Parameters�. Click �Add� to add ProductID to the Report Parameters list.
Step 10: Identify drill-through report to navigate to when the OrderID in parent.rdlc is clicked. For example, when the user clicks on an OrderID value in Parent.rdlc, our application will display the Level1.rdlc with the correct Order Details info.
Open Parent.rdlc. Right click on the value of OrderID. Select Properties from the context menu. Select the Navigation tab. Pick Level1.rdlc from �Jump to report�.
Click on �Parameters� and open the dialog box below. Enter �OrderID� as the Parameter Name, and identify where to get the parameter value. Click OK.
When the user clicks on a ProductID value in Level1.rdlc, our application will display the Level2.rdlc with the correct Product Info. Open Level1.rdlc. Right click on the value of ProductID. Select Properties from the context menu. Select the Navigation tab. Pick Level2.rdlc from �Jump to report�.
Click on �Parameters� and open the dialog box below. Enter �ProductID� as the Parameter Name and identify where to get the parameter value.
Step 11: Create an .aspx to house all three .rdlcs. DrillThroughReport_Parent.aspx is created below to display the initial report �Parent.rdlc� and subsequently, �Level1.rdlc� and �Level2.rdlc�. There is no need to create a separate .aspx to house �Level1.rdlc� or �Level2.rdlc�.
Be sure to set the ReportViewer control�s Visible property to false in design mode. The Visible property will be re-set to true programmatically once its data source is filled.
Step 12: Add source code to parent report�s drill-through event (DrillThroughReport_Parent.aspx)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
public partial class DrillThroughReport_Parent : System.Web.UI.Page
{
public string thisConnectionString =
ConfigurationManager.ConnectionStrings
["NorthwindConnectionString"].ConnectionString;
public string thatConnectionString =
ConfigurationManager.ConnectionStrings
["NorthwindConnectionString"].ConnectionString;
public SqlParameter[] Level1SearchValue = new SqlParameter[1];
public SqlParameter[] Level2SearchValue = new SqlParameter[1];
protected void RunReportButton_Click(object sender, EventArgs e)
{
ReportViewer1.Visible = true;
SqlConnection thisConnection = new SqlConnection(thisConnectionString);
System.Data.DataSet thisDataSet = new System.Data.DataSet();
thisDataSet = SqlHelper.ExecuteDataset(thisConnection,
"List_Customers_OrderTotal");
ReportDataSource datasource =
new ReportDataSource("DrillThroughDataSet_ListCustomersOrderTotal",
thisDataSet.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
ReportViewer1.LocalReport.Refresh();
}
protected void ReportViewer1_Drillthrough(object sender,
DrillthroughEventArgs e)
{
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
lblParameter.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
SqlConnection thisConnection = new SqlConnection(thisConnectionString);
System.Data.DataSet Level1DataSet = new System.Data.DataSet();
Level1SearchValue[0] = new SqlParameter("@OrderID",
lblParameter.Text.Trim());
Level1DataSet = SqlHelper.ExecuteDataset(thisConnection,
"Show_OrderDetails", Level1SearchValue);
ReportDataSource level1datasource = new
ReportDataSource("DrillThroughDataSet_ShowOrderDetails",
Level1DataSet.Tables[0]);
localreport.DataSources.Clear();
localreport.DataSources.Add(level1datasource);
localreport.Refresh();
SqlConnection thatConnection =
new SqlConnection(thatConnectionString);
System.Data.DataSet Level2DataSet = new System.Data.DataSet();
Level2SearchValue[0] =
new SqlParameter("@ProductID", lblParameter.Text);
Level2DataSet = SqlHelper.ExecuteDataset(thisConnection,
"Show_Products", Level2SearchValue);
ReportDataSource level2datasource =
new ReportDataSource("DrillThroughDataSet_ShowProducts",
Level2DataSet.Tables[0]);
localreport.DataSources.Add(level2datasource);
localreport.Refresh();
}
}
Let�s run the application: When the user clicks on �Run Report� button, parent.rdlc shows the result from the stored procedure �List_Customers_OrderTotal�.
When the user clicks on the Order ID �10310�, Level1.rdlc shows the result returned by the stored procedure �Show_OrderDetails�.
If the user clicks on Product ID �62�, Level2.rdlc shows the result returned by the stored procedure �Show_Products�.
Conclusion
We have created a two-level drill-through report in local mode using Microsoft Application Blocks, SQL Server 2005, and Visual Studio 2005. Hope this example offers some insights into the �How To�s� in creating drill-through reports. If this is the first time you use the ReportViewer control, you may want to look up my other article �Using the ASP.NET 2.0 ReportViewer in Local Mode� for installation hints on your local Web.config and the .exe required on the server.
Happy Computing!
|
|
 |
 | Error:An attempt was made to set a report parameter '@SubstanceName' that is not defined in this report. Please help ASAP!
archie@hyd | 23:03 21 Jan '10 |
|
|
Hi All,
I am working with rdlc reports. I have an asp.net web form where i'll select a parameter (substance
name)from a dropdown. When I click on view reports button, the report should open in a pop up. I am passing the
ID(substance ID) from dropdown in query string to the popup page.
When I click on view reports button, the report should open in a pop up. For this I have taken report viewer control
in another aspx page. The code in the aspx.cs page which contains the report viewer control is as follows :
I have taken two rdlc reports. The main report has the parameter SubstanceID. The child report has
parameter substance name.When I click on one particular textbox in main report it should navigate to another
report. So, in textbox properties of main report in Navigation tab,I am giving "jump to report"--child report name
and parameters "@SubstanceName" and its Parametervalue as : =Fields!SubstanceName.Value.
When I work out this report in server side reporting(ReportServerProject) it works as expected.But in
coding it throws an error :
An error occurred during local report processing.
An attempt was made to set a report parameter '@SubstanceName' that is not defined in this report.
I have taken a report viewer and two ObjectDataSources....one for parent report and other for child report.
The code for the page that contains report viewer is as follows:
In page Load : Binding Parent report
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(@" ................. ");
con.Open();
if (!IsPostBack)
{
Hashtable parms = new Hashtable();
//Parameter for parent report (comes from main page to pop up page in query string)
parms.Add("@SubstanceID", Request.QueryString["Id"].ToString());
//SP name
SqlDataAdapter da = new SqlDataAdapter("SampleSubReportSP", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (parms.Count > 0)
{
foreach (DictionaryEntry de in parms)
{
da.SelectCommand.Parameters.AddWithValue(de.Key.ToString(), de.Value);
}
}
DataSet ds = new DataSet();
da.Fill(ds);
ReportDataSource datasource = new ReportDataSource("SIEFds_SampleSubReportSP", ObjectDataSource1);
RVSIEF.LocalReport.DataSources.Clear();
RVSIEF.LocalReport.DataSources.Add(datasource);
RVSIEF.LocalReport.Refresh();
}
}
//For child report
protected void RVSIEF_Drillthrough(object sender, DrillthroughEventArgs e)
{
Hashtable parms1 = new Hashtable();
parms1.Add("@SubstanceName", Request.QueryString["SubName"].ToString());
SqlDataAdapter da1 = new SqlDataAdapter("SampleMyConsReportSP", con);
da1.SelectCommand.CommandType = CommandType.StoredProcedure;
if (parms1.Count > 0)
{
foreach (DictionaryEntry de in parms1)
{
da1.SelectCommand.Parameters.AddWithValue(de.Key.ToString(), de.Value);
}
}
DataSet ds1 = new DataSet();
da1.Fill(ds1);
con.Close();
ReportDataSource datasource1 = new ReportDataSource("MyConsDS_SampleMyConsReportSP",
ObjectDataSource2);
RVSIEF.LocalReport.DataSources.Add(datasource1);
RVSIEF.LocalReport.Refresh();
}
Thanks.
archie
|
|
|
|
|
|
|
Hi Archie :
Your code looks correct. I suspect it is in the parameter definition for your child report. The error shows "@SubstanceName" is not defined in the child report, is this parameter defined in the design mode? Perhaps set up some breakpoints and check the passing value for @SubstanceName in immediate window to confirm? Good Luck
Shirley
|
|
|
|
|
| Drill-Through Reports using ReportViewer Getting Error " An error occurred during local report processing"
kanagaraju | 19:45 2 Sep '09 |
|
|
i created the report using report viewer with drill Through Event.
but i will get error in this line
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
ReportViewer1.Visible = true;
ReportParameterInfoCollection DrillThroughValues = e.Report.GetParameters(); - I will Get error this line error message for 'An error occurred during local report processing.'
}
An error occurred during local report processing.
An attempt was made to set a report parameter 'password' that is not defined in this report.
|
|
|
|
|
|
|
It sounds like your stored procedure is expecting a parameter named "Password" but you are not prompting for its value in your source code or vice versa.... you are setting the value of the parameter "Password" in your source code but such parameter is not defined in your stored procedure. There is a mismatch somewhere between your stored procedure and the parameter value you prompt user for. Hope this makes sense.
|
|
|
|
|
| WinForm Drill-Through
Excalibur77 | 0:14 13 May '09 |
|
|
Hi Shirley
I am busy with a Win App using C# in VS 2005. How would I need to change you Web solution to work in a Win App environment?
Thanx
Hennie
------------
"It's not the blowing of the wind that determines your destination, it's the set of the sail." - Jim Rohn.
|
|
|
|
|
|
|
Hennie :
I have not written a win app since 2005 so my win app knowledge is obsolete. I know the stored procedures would be the same, the report designer steps should be more or less the same. I would just copy the code from my web example onto the win form and debug from there, may be the .dll (from the using statements) are different? Not sure. Sorry, not much help.
|
|
|
|
|
|
|
Thanx Shirley.
I'll give it a try.
Hennie
------------
"It's not the blowing of the wind that determines your destination, it's the set of the sail." - Jim Rohn.
|
|
|
|
|
| Great Example, but can't get past this error when I click the button
geezer99 | 8:04 25 Apr '09 |
|
|
A data source instance has not been supplied for the data source 'DataSet1_List_Customers_OrderTotal'.
|
|
|
|
|
|
|
Hi :
A data source instance has not been supplied for the data source 'DataSet1_List_Customers_OrderTotal'. means the dataset.table[0] is empty or the stored proecure does not return matching columns to the data table in your .xsd
2 things I would check : (1) be sure to use the proper syntax, ex. ReportDataSource datasource = new ReportDataSource ("DrillThroughDataSet_ListCustomersOrdersTotal", thisDataSet.tables[0]);
DrillThroughDataSet is the name of the .xsd in App_Code, followed by the underscore, and then the corresponding data table name in the .xsd.
(2) thisDataSet.tables[0] is to be pre-populated with the proper stored procedure and that this stored procedure must return the same column names and data types as defined in the data table titled "ListCustomersOrdersTotal", if any column names mismatch, the data will not go from the stored procedure into the data table.
Hope this help....Good Luck.
Shirley
|
|
|
|
|
|
|
I had the same problem and then I realized I was trying to set the properties of the Report via the ReportViewer control on the page. I should have been using the Report instance passed into the DrillThroughEventHandler. Everything worked fine after I corrected my mistake.
Thanks for the great example 
|
|
|
|
|
 | Parent report gives error after drillthrough
chetangarude | 4:57 6 Sep '07 |
|
|
Similar to this demo, I have a main report whose report parameters(three of 'em) are populated dynamically, and a drillthrought report with a dynamically populated parameter.
So far so good, however when I go back to the parent report after new selections, the reportviewer's local report still contains the drillthrough parameter and won't allow me to set the parent report parameters on the SetParameters() statement.
Here's the error message: "An attempt was made to set a report parameter that is not defined in this report". Any thoughts?
|
|
|
|
|
|
|
The error message indicates a missing report parameter....in the article's example, Level2 report (.rdlc) defines "ProductID" as its report parameter, Level1.rdlc has OrderID as a report parameter and the parent.rdlc has no parameters because it shows everything. You may want to check all the .rdlc to make sure they have parameters that match what you are prompting in your source code. Good Luck.
|
|
|
|
|
| Error messages
odeddror | 5:55 4 Jul '07 |
|
|
Hi there,
After I followed the instructions and run the aspx file I'm getting
using Microsoft.ApplicationBlocks.Data;
Compiler Error Message: CS0234:
The type or namespace name 'ApplicationBlocks' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
After I comment that //
I'm getting - SqlHelper error
Compiler Error Message: CS0103:
The name 'SqlHelper' does not exist in the current context
I'm using SQL Server 2005 Developer and Visual Studio 2005 Standard on Windows XP SP2
Thanks,
Oded Dror
odeddror@cox.net
|
|
|
|
|
|
|
Hello Oded :
You have to download Microsoft ApplicationBlocks in order to use its source code. For example, the command "SqlHelper.ExecuteDataset(...)" will come into effect, if you add the line "using Microsoft.ApplicationBlocks.Data;" at the top of the .aspx.cs. To use the line "using Microsoft.ApplicationBlocks.Data;", you have to first download the source code from Microsoft and install the Microsoft.ApplicationBlocks.Data.dll in your VS2005 project's bin directory.
This is one of many options (which I used in my article). You can replace all the SqlHelper commands with direct SQL calls using SQLDataSet, SQLDataAdapter, etc. It will work the same.
http://aspnet.4guysfromrolla.com/articles/062503-1.aspx
Use the link above to get more info on ApplicationBlocks. The article also includes a link to download and install this namespace.
Good Luck..........
Shirley
|
|
|
|
|
| pagination on the drill-down report
Spatacoli | 15:03 19 Jun '07 |
|
|
I've run into a problem where when I'm on the second report in a drill down report, and the report has more than one page if you click the arrow to navigation to the next page you are presented with the parent report. Do you have any idea how to fix this?
|
|
|
|
|
|
|
Hmmm....I tried to re-create your scenario by adding a bunch of records so the drilldown reports have more than 1 page. I was not able to re-create your scenario? When I clicked on the forward/backward buttons (on the reportviewer control) to navigate from page 1 to page 2 and then back to page 1, it worked for me. It only showed the pages within the drilldown report. I would look through the properties of the reportviewer on the .aspx to see if combination of 'showcontrols' buttons were set up the way you want to. Sorry...not much help. Good Luck.
Shirley
|
|
|
|
|
| Updated code for this demo
JessicaH | 7:44 8 Jun '07 |
|
|
The demo part of this page is great and it brought me quite far. But the code section has data object construction that is not necessary because our datasets, adapters, parameters are already created -- we just need to add the datasources with the parameters.
Below is my code. The only change I made is to make the paramters Integers instead of Strings when they are added in earlier steps in this demo. And, don't forget to add
the event OnDrillthrough="ReportViewer1_Drillthrough" to the ReportViewer control.
Good luck! Jessica
******************************************************************************
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Reporting.WebForms;
using DrillThroughDataSetTableAdapters;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
this.ReportViewer1.Visible = true;
this.ReportViewer1.LocalReport.Refresh();
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
ReportParameterInfoCollection reportParameters = e.Report.GetParameters();
// only one parameter here in our case, but if you had to run through them...
foreach (ReportParameterInfo p in reportParameters)
{
lblParameter.Text = p.Values[0].ToString().Trim();
}
LocalReport localReport = (LocalReport)e.Report;
ShowOrderDetailsTableAdapter OrderAdapter = new ShowOrderDetailsTableAdapter();
DrillThroughDataSet.ShowOrderDetailsDataTable orderTable = OrderAdapter.GetData(Convert.ToInt16(reportParameters[0].Values[0]));
// add an instance of the datasource, in the DataSet designer (.xsd file)
localReport.DataSources.Add(new ReportDataSource("DrillThroughDataSet_ShowOrderDetails", orderTable));
localReport.Refresh();
// Load the product drill through report
ShowProductsTableAdapter ProductAdapter = new ShowProductsTableAdapter();
DrillThroughDataSet.ShowProductsDataTable productTable = ProductAdapter.GetData(Convert.ToInt16(reportParameters[0].Values[0]));
// add an instance of the datasource, in the DataSet designer (.xsd file)
localReport.DataSources.Add(new ReportDataSource("DrillThroughDataSet_ShowProducts", productTable));
localReport.Refresh();
}
}
******************************************************************************
|
|
|
|
|
|
|
Hi there,
When I click on Botton1 the lblParameter whon't popuilate
When I click om the OrderID i'm getting
A data source instance has not been supplied for the data source 'DrillThroughDataSet_Show_OrderDetails'.
What should I do
Thanks,
Oded Dror
odeddror@cox.net
|
|
|
|
|
|
|
I have the same problem,
I was working on a different drill down problem couldn't get it to work due to this error...
figured I try this example.. got the same error..
must be doing something wrong..
The parent report shows up, but not level1..
any help would be appreciated
Rolf
|
|
|
|
|
|
|
|
| Drill through report in new window
Raj Sohoni | 23:16 26 Feb '07 |
|
|
Is there a way to show the drill thru reports in a new pop-up window?
R
|
|
|
|
|
|
|
I think so but haven't tried it myself. I would create and overlay 2 reportviewer controls on top of each other within the same form. Through source code, I would load and then show the 2nd reportviewer based on parameters selected from the 1st reportviewer. So it is not really a pop-up window but a 2nd reportviewer made visible within the same screen. My experiment is limited to web, not sure about windows app. Good Luck.
Shirley
|
|
|
|
|
| navigating to parent report
kavirajan | 21:46 25 Feb '07 |
|
|
Is there anyway we can set a link to parent report from the drilldown report?
Regards
M.Michal
|
|
|
|
|
|
|
Yes, I think so but it is somewhat redundant in the conventional way. In my example, the Parent is linked to "Level1 report" by OrderID. "Level1" is linked to "Level2" by productID. If you make "Level2 productID" a link via "Jump To Report" and runs the Level1 query, in theory it should work. (I have not tried this out). It is redundant because within the same session/context, it runs the Level1 query twice. But I think it will work. Good Luck.
Shirley
|
|
|
|
|
|
|
There is a readymade property available in reportviewer control to go back to parent available. set 'ShowBackButton' to true will enable a go back option in the reportviewer toolbar.
|
|
|
|
|
|
|
