Reference: SSRS-Self Referencing Reports or Drill through to same report. From the Toolbox, choose the subreport object and drag it to the top of the layout section. To select a different folder for the report, select the ellipsis button (. You can add a footer to any group and merge all the cells to create room for the sub report. In the Name text box, type the name of a parameter in the subreport or choose it from the list box. Then in the main report add a subreport item and set its ReportName property to the name of the. From the context, Please select Add Group and the Parent Group option as shown in the screenshot below. Group by id or name. A new row is added in the property dialog. Step 3 is the core of the data driven subscription. Net. Step 3: Create another report which is. In SSRS it is working fine in (the BIDS) editor but when calling from C# it is giving error:1. The DataSet for this tablix should be, like you outlined: SELECT * FROM TableB WHERE CountyID = @CountyID. In the Subreport Properties dialog box, click Parameters. Under the Subreport. Execute the Linked Report to see if the change is reflected. However, when the report is printed or saved as a PDF, part of the sub-report reprints onto a second page. [dbo]. Every report we create using SSRS technology is based on the Tablix data region. A subreport is processed as part of the main report. So, Right-click on the project name (here, SSRS Projects) and select Properties as shown below. I am using SQL Server Reporting Services 2008. Right click on ' (Details)' in the 'Row Groups' Pane (below Design. I want to know which of these reports are using subreports. 1. In this issue, to modify the rendering size of the subreports in the main report, you need to rearrange the original subreports individually. 1. Net using C# and VB. The ReportViewer control is use to process and. A report within another report is a sub report. Next, Merge the empty columns below the product name and color by right-clicking and choosing the Merge Cells option. For example, if your user chooses "Apples" and "Oranges" in the main from a parameter called pCategory then you can simple pass this parameter to your subreport and both values will be passed. II. If your aim is to have the sub report render after each group the place a footer or. From the context, Please select Group Properties. You don't have to do anything with the. Net Framerwork (see references tab in report properties dialog) then make sure that you confugure report viewer control for that. Add Dummy RowGroup. E. Create a new report by right clicking Reports in the Solution Explorer - Click Add New Item and click on Report. On the Insert tab, click Subreport. Parameters in SQL Server Reporting Services (SSRS) add a level of interactivity to reports. Go to Report layout è first tablixè right click on left most of the row è Insert rowè Insert Group below: It will add a new row to tablix within the same grouping: IX. Once you click. rdl, with a single Parm: T9_FK) Set the Subreport properties => Parameters: Name: T9_PK Value: [T9_PK] NOTE: the Parameter for this sub-report is the one that is coming from the dataset for the Main report. For the default value of the parameter in the subreport, no matter whether we set Available Values for the parameter (no NULL check. Then on your tablix add a Group (right-click on the handles on the right of the tablix to add a group) to have a row showing the user details then in the Detail group add the addresses. Insert a table as in Figure 4. ); rv. Headers and footers can contain static text, images, lines, rectangles, borders, background color, background. Reporting. 2. @EndDate - date/time. In the Row Groups pane, right-click the Details group and choose Group Properties. Subreports are commonly used to implement master-detail reports. Both reports retrieve the information correctly, however, I was hoping to find out if there was a way to format the report so as to not waste space. e. Create a parameter 'invoicenumber' in the existing report. For example, you can change from a test data source to a production data source without changing the report. Drag two Subreport controls into the tablix cells in Report1 designer surface (I suspect you are using tablix in Report1). 5-1-1=6. The main report shows responses to a survey on courses being taken; there are five questions, the first four allowing answers in the format 1 to 4 (i. A new row is added to the parameter grid. Point on its cell, Right Click, Insert Subreport 3. Also, if you are referencing/using any external DLLs or classes from . Click on the File menu, find New and then Project. For example: Subreport 1: Record A on 1/5/2011, record B on 1/8/2011, record C on 2/4/2011. If you still have any problem, please feel free to ask. 1 I'm looking to build an SSRS 2008 R2 report using the Report Builder, such that if a cell in a row of the report is clicked, it causes a hitherto hidden subreport to. Customize sets of parameters for multiple users. 3- Still in the main report . Example of a main. Thus, the value that will get passed to the choose function will be either 1 or 2 or 3. In this video of SSRS Tutorial, You will be learning How to add a Sub-report and Parameters in the main report in SSRS to organise and summarise your data in. For instance, you can use the Visible property of SubReport control in the Detail's Format Event to control the visibility of the subreports based on a counter variable incremented each time Detail. But now I having issue with dynamically bind my. Within data regions, a subreport can be reused, with each instance of the subreport using a parameter to filter data. This expression can be used for conditional drillthrough subreports. e. For example, if a subreport that displays order detail information is added to a table cell in the detail row, the subreport is processed once per row of the table and rendered as part of the main report. First we need to create a new Report Server Project. I can put Parameters!ClientID. Go to Parameters and set the Name field as ID and select the ID from the dropdown in the Value field. According to the official document ,If you use the shared datasource , seems it is not possible to create dynamic datasource . E. For more information, see Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS). For example, the parent report has a column for the department names. Value) 'Second Cell =Sum (Fields!AdjustedBillings. Configure the SubReport item; The master report data in this example consists of product categories from the AdventureWorks database. In the Sub-Report properties, click on the ellipsis in parameters and you will get a dialog box. Sub queries with examples in SQL (1) Subreport (1) substring function in SQL (1) sum in each row in sql (1) Sum of only Negative Values of a column in SQL (1) Importing HTML into a paginated report (Report Builder) [!INCLUDEssrs-appliesto] [!INCLUDE ssrs-appliesto-ssrs-rb] [!INCLUDE ssrs-appliesto-pbi-rb] [!INCLUDE ssrb-applies-to-ssdt-yes] . I would like to set up a dashboard report in SSRS that contains a bunch of sub reports, but have the reports that are displayed be configurable at run time. Step 3: SSRS Report Parameters Configuration. This article will show you how to add Report parameters in SSRS or SQL Server Reporting Services with an example. In above example, I am able to see the total marks of the first subject only. 1. In the remaining cell, right-click and choose Insert --> Subreport. This is where the issue comes in. 6 KB. Hide the parameters. I want each subreport's data to be housed on a separate tab when the user exports the report as Excel. Download the ssrs_migration. For those of you who aren’t sure what subreports are, they are a report item that displays another report inside the body of a main report. If you need your document map to point to specific parts of your subreport that requires a bit more effort but still pretty simple. Applies to: Microsoft Report Builder (SSRS) Power BI Report Builder Report Designer in SQL Server Data Tools You specify paths in paginated report item properties to reference items such as drillthrough reports, subreports, and image files that are external to the report definition file and are stored on a report server. Click Add. On the Manage <reportname> page, select Create linked report. What I am having trouble with is getting the subreport to return the data, per employee, in line with the main data. rdlc. I can use print when expression for the hiding. Click on the Layout tab of your report. They make a call to the database for every row and column for each of the subreports in Books, Stories and Articles. By definition, nested data regions are based on the same report dataset. So for example I have tablix on main report, that passes @Number value to sub report to fetch some child records and I want the sum of total values back. sysmanagement_shared_registered_servers s. There are two ways to add a subreport. Value (0)2) You can pass any expression as a parameter to the subreport meaning that they can be group values, main report parameters etc. Click on the File menu, find New and then Project. 3) Deploy the report and view the report using report manager. When possible perform data conversion at the query level; it will improve report performance and reduce maintenance time. Specific example: I pick Pittsburgh West and Pittsburgh East, and export. A subreport can be linked to the main report using a correlated parameter and field reference so that it can be used like a data region, but this is not essential. Data source : random sample data Data set for the Sub report: SELECT Year([OrderDate]) SaleDate ,[Region] ,[Representant] ,[Category] ,[Total] FROM [Test]. To work around this issue, we can use the ReportViewer Control. If my main report is countries and I have employees by country as a subreport. In the context menu of the report, click Manage, and then click Subscriptions in the left pane. When you are working in Report Builder, you must be connected to a report server to view the drillthrough report that opens when you click the drillthrough link in the main report. If you pass a val4 from main report to call the sub report, it will prompt the Error: Subreport could not be shown. Our sample report has two report parameters Year and Report. net application uses . I want to do a report that is a little bit more complex. I inserted subreport on main and subreport parameters as follows: Name Value CurEmp =First (Fields!EMPID. Also keep in mind, when you create cascaded parameters, the independent parameter must be defined before the dependent parameter in the Report Data pane. You cannot do the following:3. 0 . option, a new window will be opened to configure the Group Properties. Text decoration property sets the appearance of decorative lines on text. You need to pass in the value form the dataset of the tablix that you click on. While working on an SSRS report in Visual Studio. Go to Row Groups pane, Right click on (Details) and select Group Properties. In this example, we will show you how to create or Enable Document Map Labels in SSRS or SQL Server Reporting Services with an example. Opening Drill down Report in a new tab without parameters and fields. SSRS Interview Questions and Answers for 2023. Right click on Tablix's row. We will start with a simple sub-report example. In the remaining cell, right-click and choose Insert --> Subreport. Step 2: Below is the dataset used for the MainReport. I. I am totally stuck here. To get the variable definition screen, click on the down arrow to the right of the column group name, and then select Group Properties. If you just need a map entry to point to your subreport then you can set this by simply clicking on the subreport from your main report and setting the DocumentMapLabel property. An SSRS report subscription is a request to deliver the subscribed one to an Email or File System location at a specific time. Plan the strategy for granting the right level of permissions. 6) Shows how subreport could be used to create complex document layouts. However, in the. In the dataset properties set the query type to text. I set the subreport path on the code behind with this code:Now in dataset2 I have add one calculated field called TotalCount and used the Lookup Function the function is like this. To. Value = "Development", "EmployeeReport", Nothing) Test if a field value is null. Right-click the whole subreport row, then select Tablix Properties. create two datasets. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters. check the box display can be toggled by Report ITEm- (TerritoryID1)--This text is same which we added after adding Group by TerritoryiD Group. I would consider reworking the main dataset to contain the subreport data, and group by the main fields. After you save both reports to the same workspace, you can add one to the other as a subreport. Section 3 introduces techniques to build configurable reports. In SSRS Builder, File → New, blank report Add New Datasource [image] In Data Source Props [image] In Connection Props: [image] (Note that #2 is the DB of the source data, not a report DB) Back in Datasource Props, Test Connection , then. Moreover, it can be executed independently. Trigger example in sql (2) Trigger on delete with Example in SQL (1) Trigger on Insert with Example in SQL (1) triggers with example in sql (2) Try and Catch in SQL (1) two column in stack in SSRS (1) two values after decimal in sql (1) U. WhilePrintingRecords; shared Numbervar myTotal; myTotal := myTotal + 200; //or any formula or field. 1- Create the . Fill(ds, "DataTable4") e. To add the parameter, go to Parameters tab and click on Add button. The trick is that each subreport has multiple records with multiple dates. I will use the following tables from the AdventureWorksDW2008R2 database in my sample report: 7 Answers. 3 How do I shows column headers of main report on each page when subreport. First you have to Select the Drill through values textbox. The second option in the list, "Specify Values" will allow. · Hi Parixitsinh, In Reporting Services, each subreport. For example, in this illustration, the sales summary report lists sales orders and totals. If you either move the SQL server to a new box, or change the DB name, then the sub report will have to be updated with new data source connection info. Next, as displayed in the below example, right mouse clicking on the sales order group, selecting group properties, and then navigating to the visibility window allows for the creation of a drilldown toggle from the category field to. Above this detail row (the header you added) you can write the following expressions: 'First Cell =Sum (Fields!LineItem. SSRS report: "Error: Subreport could not be shown" ERP 10. Use subreports to nest groups of data from different data sources within a single data region, reuse a subreport in multiple parent reports, or display a standalone report inside of another report. In this case, the subreport contains data that is related to the data in the main report. If you still have any problem, please feel free to ask. Return to the first dataset and add the following line:Let take up a simple example to demonstrate creation of subreports using JRDataSource. To display data from different datasets, consider using drillthrough reports or subreports. Using a subreport is the easiest method when it works. Case first-Report in new Tab without fields or Parameters. I am trying to create a report where, as an example, it would look like: I have been able to get this to work with subreports however there are two problems with this approach: Subreports are extremely inefficient. YOU CAN set the main property in code as follows: Suppose you have a ReportViewer name rv, then we would code: var rp = new ReportParameter ("MainReportParamName", Properties. Give it the selection selection criteria you want (ex. 8. well reports are running slow. I would like to create an SSRS report that has one row per record in Table A, but then displays a sub-table for each Table B and Table C in one of the columns. Click on the Reporting Services and then Report Server Project . In this tutorial, we will learn "How to create a Dashboard Report" in SSRS. Main:. This solution has the advantage that the subreport can be used as main report and is "simply" pass the actual record as subreport datasource (using groovy as report lang):I would derive new columns in the Dataset to hold the concatenated parameter strings you want to pass. This video is part 1 of How to create RDLC Sub Report and display in main report. Create a Shared DataSet in SSRS. 1 I have a lot of SQL Server Reporting Services (SSRS) reports (*. For example, unable to find the . Try and select a name from the dropdown (it's empty). Add a parameter to the subreport. The example is there is a table holding sales header data and a table holding sales line data. Click the cell that contains [SalesDate]. In the Query string section, type Select 1. After Creating of SSRS Reports for the business need, most of the user want to view it on Website or WebApplication by using different languages. For example: Subreport 1: Record A on 1/5/2011, record B on 1/8/2011, record C on 2/4/2011. Right-click on sub report, select properties. SSRS passing parameter to subreport Ask Question Asked 10 years, 3 months ago Modified 10 years, 3 months ago Viewed 28k times 2 I have 2 tables which. rdl) by providing own Dataset/datatable with values. SQL Server Reporting Services paginated reports are XML-based report definitions that include report data and report layout elements. I have been using subreports for each report to show. 1. From the below screenshot, you can see that we are selecting the REPORT folder in our E. Step 5: Automatically based on query provided in Subreport a new Parameter will be created. Right-click the detail row on the Main report, and then select Insert Row -> Inside Group-Below. Applies to: Microsoft Report Builder (SSRS) Power BI Report Builder Report Designer in SQL Server Data Tools You specify paths in paginated report item properties to reference items such as drillthrough reports, subreports, and image files that are external to the report definition file and are stored on a report server. Populate parameters with a list default values In your subreport, taking @service as an example. Value = 2,False,True) And so on. Using the code. This is the file that will be used as the Subreport. I have a main report which internally calls three sub report. Figure 1: Report server project. If the actual closing date +45 days is greater than Today then hide the report and if not then show. rdlc as a subreport to be linked with Main. rdl. Thanks in advance MikeIn the blank cell, right-click and do Insert => Rectangle . Exporting to JSON Format Using Report Metadata. rdlc file. For this example, TotalDue=1, Tax=2, and Freight=3. In Connection Props: image 401×536 10. and the parameter defined on the subreport should be a dataset filter not a query parameter . Two subreport components will be displayed in the report, and employee information will be displayed in the child report. Add Dataset. JasperReports Ultimate Guide API (Javadoc) JasperReports - Subreport Sample (version 6. The dataset name for the main and all sub report is default as DataSet1; Please guide me to bind the sub reports with appropriate query dataset tables. You don't fetch it all, then filter it, which is horrendously inefficient. Data source : random sample data Data set for the Sub report: SELECT Year([OrderDate]) SaleDate ,[Region] ,[Representant] ,[Category]. You will need to use the Height/Width property of the report inside BIDS. Following is a short SSRS report tutorial-. Right-click on the Reports folder and choose Add-->New Item. Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. 7 Rating. If you want to repeat a set of items under each Project ID , you need to put sub report inside the group. It allows the report developer to set which data is shown and which data is hidden when the report is initially run. In SSDT, click on "New Project", then choose new "Project Report Server": Go to the right pane "Solution Explorer. =Parametrs!ParameterName. Report Designer provides a graphical interface in which you can define data sources, datasets and queries, report layout positions for data regions and fields, and interactive features such as parameters and sets of reports that work. Using reporting services, How to get the value from Sub report to the Main report? I am calling sub report, based on main report's tablix data. Firstly, will create a table that relates to the query from the first dataset. 4) Now go back to BIDS and select the Image control. Typically the subreport is parameterized . Here is an example of my main report page with grouping on the ClerkNo The header i. The Subreport Properties dialog box opens. just like the main report. "CLERK 1 Scott" Is just on the Sub Report, this was added to each sub report so i could identify if it was aligning up correctly The subreports are a MasterData to a query, Each sub report is using a different query (Although the column responses are the same. A subreport with the specified name doesn't exist in the same workspace or app as the main report. . i have a subreport in the list. the code you posted seems correct but you have to previously set subreport parameters in Design View > right click on subreport object > Subreport properties > Parameters; set Parameters value using Expressions or using this syntax: i. Only rows where Date = "Date 3" will have their Value included in the SUM () function. 1 I'm looking to build an SSRS 2008 R2 report using the Report Builder, such that if a cell in a row of the report is clicked, it causes a hitherto hidden subreport to become visible, populated with data retrieved from a query which uses parameter values from the row of the cell clicked. There is no blank. For example: if your date parameter is on group named "table1_Group1", and you want to use it to a subreport, you can use an expression referencing to the group like. It also has another textbox which will be visible only if the dataset Items has rows. there could be 4 parameters which represent the names of 4 reports. How do I hide this?11. I found rc:LinkTarget=_blank parameter that should fix the issue changing a. Sorted by: 1. This tutorilas gives you an overview of how to create an SSRS subreport. This all works fine when run in SSRS, but directly and as a subreport where the subreport itself is by default hidden and can be toggled (so 3 layers of toggling possible) However, when I export to Excel, though the subreport is by default hidden, when I hit the + or the 2 to expand a layer, the subreport opens all at once instead of with the 2. The main report lists the day-to-day sales. May 20th, 2021. You may also have a look at the following articles to learn more –So, to output the page number and total pages in a textbox located in the body simply enter this for the value: ="Page " + Code. 80 Question (s) 40 Mins of Read. Choose a data source and create a query for employeedataset as in Figure 6. 46. We will show the sub-report design using both Report Designer and report developer. The main report has 10 columns of returned information. Start SQL Server Management Studio with administrative privileges. If the Properties option is disabled, verify you ran SQL Server Management Studio with administrative. Hi, I managed to pass parameter by setting from Main Report clicked on the SubReport Properties -> Parameter -> Set "Test1" as Parameter Name and "Test1234" as Parameter Value. The only way I could embed the sub-report was to manually change the link. but then saw in his sample output the count and thought that was. Right-click on the Subreport and select Properties. g. . Click Filters. Open sub report and Create a parameter with the same name and the same type. Then, the main report passes each database name as a parameter to my subreport. Set a pagebreak ' Between each instance of a group '. ". 1. 6) Shows how subreport could be used to create complex document layouts. 3- Still in the main report . If you need to perform date operations to create three different values for your report, you will have to convert. 1. In Solution Explorer, right-click the Reports folder, and then click Add New Report. To relate the reports through data values (for example, to have multiple reports show data for the same customer), you must design a parameterized report (for example, a report that shows the details for a specific customer) as the subreport. Each of the posts is a condensed version of the material covered in a corresponding chapter. Create the subreport. Creating a SSRS subreport Create one report for each detail table, you can create it as you like, as any normal report, but after creating it (via blank report or template), create a. Creating Sub Report in SSRS, filtering SubReport using MainReport Parameters in SSRS, How to create sub Report in SSRS,. Aggregate, rownumber, runningvalue, previous or lookup function. My sub report is not rendering when one of two calls to the sub report returns an empty dataset. Example of one sub report:: in this i have one big rectangle, in that rectangle i have different regular. The SSRS Report Parameters allows the users to enter a specific value in the textBox, and then it will filter the Report data using the user-specified value. Step-by-step process: SSRS 2008 R2. I have a main table (Table A). I believe that I can do this by creating a sub-report for Table B. SSRS Subreports Example In this example, we will show you how to create SSRS Subreports inside the Table Reports with an example. In reporting services, the Tablix is a sum of tables with matrices. I created a subreport that gets me that data. My report has a sub-report in it. If you want to display Report test project to get you started, you can type Report test in Name. The cell displays the example. One subreport summarizes sales by employee. 2. This article covers a little more than half of a Baker's Dozen this time around, with the following SSRS tips: Implementing a tab-style interface for navigation to report page/sections. It is used to embed a report within a report. After this, you need to click on the Business Intelligence . Optionally enter a description. Sub Reports in SSRS. We’ll have two reports to go over. SQL Server Reporting Services - Example for Subreport and Drill-down report In this post,let us see simple example for subreport & drill-down report What is. On the Home tab, in the Shared Datasets group, click Datasets. For what you require to work, SSRS would have to be capable of refreshing the subreport independently. To find a report that you want to work with or to select a shared data source, image, or subreport to use in a report, browse your computer, folders on a report server, or SharePoint site. 5 inches are reserved. So for example I have tablix on main report, that passes @Number value to sub report to fetch some child records and I want the sum of total values back to main. That is there will be two reports one the Master and the other the child where the master will invoke the child report. there could be 4 parameters which represent the names of 4 reports. As you know, the Tablix in our sample report doesn't have any grouping and based on the Product Category we have to divide the data into multiple sheets, so we have to add a dummy row group for Product Category on top of the available parent row group. A subreport is a reference to another report. This video tutorial on SSRS (SQL Server Reporting Services) covers the below topics -1) Creating Linked Reports / Drilling Down by defining Actions on a Re. YOU CAN set the main property in code as follows: Suppose you have a ReportViewer name rv, then we would code: var rp = new ReportParameter ("MainReportParamName", Properties. 4. For example: SELECT CommonCriteria, Count(*) [RowCount] FROM. The code file contains two sample Visual Studio projects and a SQL Script for creating the ReportDemo database. Any report designer will often need to find ways to offer easy navigation paths to report consumers. If you need to pass the ID parameter to the subreport, you need to have the corresponding ID parameter in the target report (subreport). In SSRS, how can I force the dynamic number of sub-reports into a single Excel sheet on export? There could be hundreds of sheets generated and I can't expect the user to cut/paste the data into a single sheet every day. 3] Right click on the field, in the textbox, go to 'Placeholder Properties'. In the Subreport Properties dialog choose AddressSubReport from the subreport dropdown. Scatter charts and drill-down features. Open a shared dataset in shared dataset mode. Create two linked reports based on a. This discussion will demonstration three specific techniques. exe migration script to a local folder. Set the visibility property of the the subreports based on the parameter so only one is visible. You cannot use a data source expression in a shared data source. Data. If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link single Reporting Services paginated report can have characteristics from more than one type; for example, a report can be, at the same time, a stand-alone report, a subreport referenced by a main report, the target of a drillthrough report in a different main report, and a linked report. Step 4: Below is the dataset used for the Subreport. Then from SubReport -> Report Parameters -> Add "Test1" as Parameter Name and Prompt set as "Select Test1". When the SubReport is finished with the Tablix and/or other data objects, the Rectangle forces a page break. Here you can select the Drive, or Folder (Location) to save this CSV file. I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:Choose Subreport properties > Visibility. Expend InteractiveSize node, set the Height value as “0”. 20. Once you click on Group Properties. In the Dataset dropdown of the Data tab, choose <New Dataset. only one dataset is required (as in the example above) the report requires two reports: MainReport and SubReport; MainReport: references a shared dataset consuming the stored procedure with the data. On drillthrough reports this is possible by using an expression to determine which report to open; eg IIF (customer = X, "subreportX", "SubreportY")9: Avoid data conversion at the SSRS report level. For a practical example of how to use Subreports, take a look at the tip Create Centralized Report Headers Using Subreports in Reporting Services. Go back to main report. #PowerBI #PowerBIDesktop #PowerBITutorials Business Queries / Analytics Consulting : analyticswithnags@gmail. 1 Subreport as a header in main report (with page numbering) 0 SSRS Report document map link goes to page before (another subreport) because the subreport it refers to contains a page break before. Set the subreport to the previously created report (e. Need Page Number on Sub report to start from 2 in SSRS. Also, a cool feature, if the SubReport returns no rows of data, the. (Note: A subreport also need to define its datasource in the main report's page. In the subreport control properties, select "Sub1" as the "Use this report as a subreport". If I need to use a subreport, the main report is limited to just a few records. Make sure that all the parameters of the subreport have been assigned a value during design. Copy and paste the dynamic SQL query into the dataset query textbox as shown below: Set the timeout value to a reasonable number, for example 600 seconds. If you don't see an example currency value, in the Numbers group, click Placeholder Styles > Sample Values. e. I have one parameter and based on the valye of teh parameter I need to show only one sub report and need to hide remaning two report. Now when you pass parameter from main report to subreport , it will filter it accordingly on the sub report snapshot without having to execute the costly sql. rdl file that defines the subreport. This subreport is the part we’ll be interested in for the rest of this article. On the Visibility settings, choose the radio button to Hide the details. . For a practical example of how to use Subreports, take a look at the tip Create Centralized Report Headers Using Subreports in Reporting Services. Right-click the subpeort to open the Subreport Properties, and select the subreport name in the drop-down list. A new row is added to the parameter grid. =Lookup (CityColumn, CityColumn, CountColumn, "Dataset1") Lookup includes aggregate, rownumber, runningvalue, previous or lookup function. In the Name text box, type the name of a parameter in the subreport or choose it from the list box. Name.