sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report.
To learn basic of reports see my previous article How to create basic report in Sql Server 2008
Here is the report output which we will create in this article.
Let’s create a new blank project to work (if you are familiar then omit this section)
It will create blank project with two folders
Right click on Shared Data Source and select Add New Data Source
Click Edit to Add connection String and set your Sql Server connection
Now we created a new Shared Data Source which we will use in our project for every report.
We will use Northwith database to create an Orders report with Order Details as sub report, so let’s create our main report Orders
It will open a blank surface to add our report columns, header Title etc.
Open Toolbox and drag a Table to the report surface
It will open Data Source Property Dialog
Click on Use Shared Data Source reference and select our created data source
Click Next and type your query or procedure name which you want to use for this report, in our case
Select * from Orders
Execute it to check the records and click Finish
A table will be added to the report area, now time to drag columns one by one, if need more column click on header and add more columns to it.
Adjust header background color and text color and font.
Click on Preview button to see the report, good it is working fine till now, let’s format the date column.
Right click on Order Date column -> Expression and change it to
=Format(Fields!OrderDate.Value, "dd MMM, yyyy")
Now report will show Order date as 04 Jul 2014.
Now time to create Order Details report which we will use as sub report in our main report Orders As earlier we created our Orders report, create a new report with name “Order Detail” and here is the sql statement
Select P.ProductId, P.ProductName, od.UnitPrice, OD.Quantity, OD.Discount
From [Order Details] OD
INNER JOIN Products P ON P.ProductId = OD.ProductID
Where OD.[OrderID] = @OrderID
As you can see we used @OrderID as a parameter so this report will take order Id to run it which we will pass on toggle of parent report.
Now we will add our order detail report to main report Orders.
Right click on the item row -> Insert Row -> Inside Group – Below
Select all the column to merge, right click and merge it.
Open Toolbox and drag a Subreport in our merged cells
Right click on it -> Subreport Properties
From Use this report as a sub report select Order Details
Select Parameters -> Click Add -> Select OrderID in both dropdown
Now if you will see the order report it will show all the order with detail without drill down so let’s add drill down
Again open sub report properties -> Visibility
Now we completed run and see the report.
|
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
| By Ali Adravi On 09 Sep, 13 Viewed: 11,686 |
SQL Reporting services also known as SSRS is a great tool to create report from database. In this article we will learn how to create a basic Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in... By Ali Adravi On 10 Sep 2013 Viewed: 1,119
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,311
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 666
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,556
Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma?... By Ali Adravi On 08 Feb 2013 Viewed: 17,173