Transfer SQL server Objects Task

 

Transfer SQL Server Objects Task

The transfer SQL server Objects task is used to transfer one or more SQL server objects to a different database, either on the same or another SQL server instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user-defined functions, etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Create another package in the same project and drag a “Transfer SQL Server Objects Task” to the Control Flow. Right-click on the task and then select Edit. In the “Transfer SQL server objects task editor” click on objects to set the different properties for this task as shown below.

Similar to the way you configured the source connection and destination connection for the Transfer Database Task, you need to configure these properties for this task as well. Source Database is the name of the database from where you are copying the objects and the Destination database is the name Of the database to which you are copying the objects to

If you want to enrich your career and become a professional in SQL Server, then visit Tekslate - a global online training platform: "SQL Server Training" This course will help you to achieve excellence in this domain.

Connection:

Source Connection                                                         - the source instance

Source Database                                                             - the name of the source database

Destination Database                                                     - the name of the new database

Destination

Drop objects first                                                             - Drop selected objects on the target before copy

Include extended properties                                     - while copying operation also include extended properties of SQL objects being copied

Copy Data                                                                 - While copying tables, transfer the data of the selected tables as well

Existing Data                                                                   - Whether to append or replace data

Copy schema                                                                   - Copy the schema of the objects being copied

Use Collation                                       - Make sure collation of the columns are appropriately set on copied tables

Include Dependent objects                 - Include all the objects in copy operation which are dependent on selected objects

 Destination Copy objects

Copy all objects              - Do you want to copy all objects from the source database, if set to false, the next property objects to copy will get enabled.

Objects to copy                                           - With this property, you select types of objects you want to copy.

You can select all objects of one or more types or select a particular version type of object selection will vary.

Security

Copy Database users                                                       - whether to include users

Copy Database Roles                                                       - Whether to include roles

Copy SQL Server Logins                                                   - Whether to include logins

Copy object-level permissions                                         - whether to include object-level permission

Table options

Copy indexes                                                                    - Whether to include indexes

Copy Triggers                                                                   - whether to include triggers

Copy Full-Text indexes                                                     - Whether to include full-text indexes

Copy all DRI objects                                                         - whether to include referential integrity objects

Copy primary keys                                                           - whether to include primary keys

Copy foreign keys                                                            - Whether to include foreign keys

Generate script in Unicode                                              - whether to create a script in Unicode or--------

Transfer jobs task

The transfer jobs task is used to transfer SQL server agent jobs from one SQL server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.

Open BIDS Drag a Transfer Jobs Task from the Toolbox to the Control Flow pane as shown below.  Right-click on this task and click on edit

Steps to configure the Transfer Jobs Task:

Connections

Source connections – specify the connection for the source SQL server instance, if you have already created a connection then you can reuse it here or can create a new one.

Destination Connection - Specify the Connection for the destination SQL Server instance

Jobs

Transfer All Jobs:

If this is set to true it will transfer all jobs. If this is set to False you can select Specific jobs you want to transfer.

Jobs List:

This will be enabled if Transfer All Jobs is set to False. Then you can select Specific Jobs to transfer. See the image below Where I am only Selecting two jobs to transfer to the Destination

Options

• If Object Exists:

If the jobs already exist on the destination. Then you have three Choices, first Fail Task Execution, Second Overwrite the destination job and third Skip the existing job and continue With others.

• Enable Jobs At destination:

After the transfer, you can also specify whether to enable or job.

Transfer Logins Task:

• Transfer Logins Task is used to transfer either all logins (except sa) or Selected databases
• From the Source to the destination SQL Server instance. After the transfer all the transferred
• SQL Logins are assigned random passwords and SQL logins are disabled. The DBA needs to change
• The passwords and enable the SQL login before it can be used on the destination.
• Create another package in the Current Project and drag a Transfer Logins Task from the
• Toolbox to the Control Flow. Right-click on the task and select edit to configure the tasks
• Properties as shown below.

Steps to Configure Transfer Logins Task:

Connections

Source connection - Specify the connection for the source SQL server instance.

 Logins

Login Transfer:  you have three options for this:

• All Logins – this will transfer all Logins from the source.
• Selected Logins – this allows you to select Specific logins
• All Logins from Selected Databases – this allows you to transfer all logins associated with one or more databases as shown in the image below.

Logins List:

This will allow you to select Specific logins if you select Selected Logins  For Logins Transfer.

Database List : 

This will allow you to select the databases if you select All  Logins from Selected Databases for Logins TO Transfer.

Options

• If object Exists :

If the Logins already exist on the destination you have three choices  First Fail Task execution, Second over Write the destination logins, And third skip the existing login and continue with the others.

Checkout Our  Frequently Asked SQL Server Interview Questions

• Copy Sides:    if you set it to  True then the security identifier (SIDS) associated with  Logins is also copied to the destination.

The architecture of SSRS(SQL )

Data Source:

Logical Connection between application (SSRS) and databases

Report Server:

Report Server is the core engine that drives Reporting Services.  The URL for report Server is https:// Localhost/Report Server.

Report Manager:

• Report Manager is a Web-based administrative Interface for Reporting Services.
• Report Designer (BIDS): is a developer tool for Building Simple and Complex
• Reports using existing Report Item Templates.

Example:

1. Table
2. Columns Different chart Template
3. Rectangle
4. List
5. Image

Report Builder:

Is a Simplified end-user or Adhoc reporting tool for building Simple Reports.

TABLE REPORT ITEM

It is used to display data in a Grid format that has a fixed number of columns and

A variable number of rows.

Steps to Configure Table Report Item:

Open Business Intelligence Development     ------------>

Select File Menu     ------------>

Select New    ------------>

Select Project     ------------>

Under Business Intelligence Projects Select Report Server Project option to

Build Simple and complex Reports on the development environment.    ------------>

Change the location and name of the project    ------------>

Click ok    ------------>

Press Alt+ Ctrl + L for Solution explorer    ------------>

Select Shared data Source and right-click. Select add new data source option

From context Menu or right popup Menu    ------------>

Set,

In share Data source editor,

Data source - Remove it as D Source Product Details

Type - Microsoft SQL Server

Connection string – click Edit     ------------>

Provider Server Name (localhost) (or).  (Or) Host Name (or) IP Address    ------------>

Select Adventure Works    ------------>

Click ok    ------------>

Click ok    ------------>

In Solution Explorer, Select Reports, right-click    ------------>

Select Add and also select New Item    ------------>

In Data Tab, Select new data Set Option and Set,

Data set Name – D Set Product Information

Data Source - Select D Source product Details from the drop-down list

Command Type - Text (SQL Script)    ------------>

Click ok     ------------>

Select query Designer Option and Add Tables icon on the rare right and Select Tables

(Or)

Write the query in SSMS and copy and paste in Query Designer

Select Product Category. Product Category ID,

Select Product Category. Product Category Name,

Product. Product ID,

Product. Standard Cost,

Product. Size,

Product. Sell start Date,

Product. Reorder point

From product category Inner Join product subcategory. Product category ID =

Product subcategory. Product category ID.

Inner Join product on the product. Product subcategory. Product =

Subcategory ID Subcategory ID

Inner Join Product cost History ON Product. Product ID =

Product cost History. Product ID

Provide the above query to the created data set and click Refresh     ------------>

Click ok    ------------>

Select View Menu and Select Data Set option or (Alt + Ctrl + D)    ------------>

Select the Layout tab to design the Report     ------------>

Select tool box (or)  (Alt + Ctrl + X)    ------------>

Drag and Drop table Report item on the body of the Report    ------------>

Drag and Drop Category Name field on to Detail section of the table  report [Building (or)    ------------>

Mapping the field (or) Columns from data set to Report item]    ------------>

Map other fields as well     ------------>

In table Report item Select 1 Row and 2 Row    ------------>

Press F4 and Set    ------------>

Border style  - Solid    ------------>

Select 1 Row [Header] and Press F4 for properties and set    ------------>

Background color   - Marron

Text align   - Centre

Font Size  - 9pt

Color – White

Report Server (TABLE REPORT ITEM)

Data Source: Logical Connection between application (SSRS) and databases

Report Server: Report Server is the core engine that drives Reporting Services.  The URL for report Server is https:// Localhost/Report Server.

Report Manager:

• Report Manager is a Web-based administrative Interface for Reporting Services.
• Report Designer (BIDS): is a developer tool for Building Simple and Complex
• Reports using existing Report Item Templates.

Example:   

1. Table
2. Columns Different chart Template
3. Rectangle
4. List
5. Image

Report Builder: Is a Simplified end-user or Adhoc reporting tool for building Simple Reports.

TABLE REPORT ITEM

It is used to display data in a Grid format that has a fixed number of columns and a Variable number of rows.

Steps to Configure Table Report Item:

Open Business Intelligence Development        ----------->

Select File Menu        ----------->

Select New       ----------->

Select Project        ----------->

Under Business Intelligence Projects Select Report Server Project option to       ----------->

Build Simple and complex Reports on development environment.       ----------->

Change the location and name of the project       ----------->

Click ok       ----------->

Press Alt+ Ctrl + L for Solution explorer       ----------->

Select Shared data Source and right-click .Select add new data source option       ----------->

From context Menu or right popup Menu       ----------->

Set,

In share Data source editor,

Data source - Remove it as D Source Product Details

Type - Microsoft SQL Server Connection string – click Edit        ----------->

Provider Server Name (local host) (or).  (Or) Host Name (or) IP Address       ----------->

Select Adventure Works       ----------->

Click ok       ----------->

Click ok       ----------->

In solution Explorer, Select Reports, right click       ----------->

Select Add and also select New Item       ----------->

In Data Tab, Select new data Set Option and Set,       ----------->

Data set Name – D Set Product Information

Data Source - Select D Source product Details from drop down list

Command Type - Text (SQL Script)       ----------->

Click ok        ----------->

Select query Designer Option and Add Tables icon on the rare right and Select Tables

(Or)

Write the query in SSMS and copy and paste in Query Designer

Select product Category.

Product Category ID, Select product Category.

Product Category Name, Product.

Product ID,Product.

Standard Cost, Product.

Size, Product.

Sell start Date, Product.

Reorder point From product category Inner Join

product sub category.

Product category ID = Product sub category. Product category ID.

Inner Join product on product. Product product sub category.

Product = Sub category ID Sub category ID

Inner Join Product cost History ON Product.

Product ID = Product cost History.

Product ID Provide the above query to the created data set and click Refresh        ----------->

Click ok       ----------->

Select View Menu and Select Data Set option or (Alt + Ctrl + D)      ----------->

Select Layout tab to design the Report       ----------->

Select tool box (or)  (Alt + Ctrl + X)      ----------->

Drag and Drop table Report item on body of the Report      ----------->

Drag and Drop Category Name field on to Detail section of the table  report [Building (or)      ----------->

Mapping the field (or) Columns from data set to Report item]      ----------->

Map other field as well       ----------->

In table Report item Select 1 Row and 2 Row      ----------->

Press F4 and Set      ----------->

Border style  - Solid      ----------->

Select 1 Row [Header] and Press F4 for properties and set      ----------->

Back ground color   - Marron Text align   - Centre Font Size  - 9pt Color – White

Parameterized Report : In Parameterized Report  the Report will be populated data based on the user input.

Steps to Configure Parameterized Report :

1.In  previous Report example  - In data tab add the following Where Clause to set the

Parameters

WHERE,

(Production. Product category. Product category ID = @ Product category ID)

2.In Data tab select New Data Set and Rename it as D set Product category ID Name

Data Source  - D src product Details

Common Type  - Text

Query String – Provide the following SQL command to populate all

The Product category ID, Name as well.

Select

Product category ID,

Name as  category Name

From

Product. Product category.

3. Select Report Menu and Select Report parameters 4. Select Product category ID parameter and set,

Prompt – Select  category

Check from Query Radio Button (to  populate all ID from the Selected Data Set)

Data Set – D set product Category Id Name

Value field  - Product category ID

Label field -  category Name.

Click ok

Multi-Valued Parameterized Report: In Multi-Valued Parameterized Report  the user Can Select multiple values from the Specified parameter.

Steps to Configure Multi-valued Report:

• From the Previous Report Example, Select Data Tab      ----------->
• Select D Set product information Data Set      ----------->
• Append the following Where clause
• WHERE
• (  Production. Product category  Product category ID IN (  Product category ID) )
• Refresh      ----------->
• Select the Report menu and Report parameter option
• Check multi-value label Checkbox
• Click ok      ----------->
• Go to the preview tab Select multiple Values from the parameter drop-down list and click      ----------->
• Preview Reports

Expressions in SSRS:

1.Expression to format the Data using format function (or) format codes.

Select Standard cost text box     ----------->

Right-click and select expression  = Format (Fields ! standard cost. Value,” N 0  (Zero)”)

2.Expression to check must Records in the Specify field.

If any field value is Null then place - - Other Wise Display the actual Value of the field = IIF (IS Nothing (Fields Size. Value),)”- - ”, Field! Size. value ).

3.Expression to align the text:- If the text is Null then place (or) display the -- in Centre

Else left just field. Select Size text box Press F4 from properties and set Text Align - - IIS (IS Nothing (Fields Size. Value),” center”,” Left”

4.Expression to display the values in various colors based on the conditions Select size text box     ----------->

Press F4 for properties     ----------->

Set,     ----------->

Color – =IIF (Fields!  Size. Value  < 39 ”RED ”, IIF (Fields!  Size. Value  > 30 ”BLUE ”, “Green”

5. Expression to Handle negative Values for example

(Blue - 79) -->(79) in Red color. In table Report item add one column and Rename the Header as Totals and provide The following expression = IIF((Field ! Standard Cost. Value- Field ! Re Order point . Value)) < 0, Replace  ((Field ! Standard Cost. Value- Field ! Re Order point . Value),” -” ,”(“) & “)”, ((Field ! Standard Cost. Value- Field ! Re Order point . Value)

Select Totals text box (In Details section)     ----------->

Press F4 properties     ----------->

Set, Color - = ((Field ! Standard Cost. Value- Field !( Re Order point . Value)< 0 , “RED”, “Black”)

6. Expression to display Report with alternative Row color.

= IIF (Row number (nothing) mod 2, “silver”, ”Transparent”)

Chart:  Chart Report item is used to display the data graphically as bass pie, column, area, and Other chart types. 

In our previous Report example,

Drag and drop chart Report item after The table Report item.     ----------->

Right Click on the chart and select Properties from the context menu.

In General tab, Title - = “Product Details for The month – “ & month Name (month (Field ! Sell start Date. Value)) Select Data tab     ----------->

Data Set Name  - Select D Set Product information from drop-down list value  - Click Add to add column (or) Fields that would be plotted on Y-axis      ----------->

In Edit chart Value, Series Label – standard cost value  - = Field! standard cost value     ----------->

Click ok -     ----------->

Values – click Add to add column (or) fields that would be plotted on Y-axis     ----------->

In Edit chart value     ----------->

Series label – size     ----------->

Value – Fields ! Size .Value     ----------->

Click ok     ----------->

Category Group – click Add to Add column (or) Fields that would be plotted on X-axis Select expression and provide the expression display the records like JAN – 01 Format, = Left (Month Name()(Month [ Fields ! sell start Date. value)).3 ) & “_” & Right (year (Fields ! sell start Date. value)).2)     ----------->

Click ok twice     ----------->

Select x-axis tab and set, Title – sell standard data     ----------->

Check Major Gridline and specify the scale (or) interval 0.5     ----------->

Title Align – center ----------->

Major tick lines – cross     ----------->

Major tick lines – cross      ----------->

Check side Margins checkbox to display the chart items in center of the plotter area.     ----------->

In Y-axis, Title – Size Check Major Gridline and specify the scale (or) interval 0.5     ----------->

Title Align – center ----------->

Major tick lines – cross     ----------->

Major tick lines – cross      ----------->

Check side Merging checkbox to display the chart items in center of the plotter area. In Legend Tab, Check show Legend (by default) Layout – Table Check legend style and set, font size -  8pz Click ok     ----------->

How to set or fill custom color to the chart (series)     ----------->

In data tab, Values – Select Standard cost and click edit     ----------->

Select Appearance Tab     ----------->

Click series style     ----------->

Select Fill tab     ----------->

Click Browse to select a color from the list     ----------->

Select any color and click ok How to Setpoint labels in the chart (Display the exact Value on plotted area)     ----------->

Values – Select Standard cost and click edit,     ----------->

Select point labels Tab     ----------->

Check show point labels checkpoint,     ----------->

Data label -  =  “&” and Fields! standard cost. Value     ----------->

Click ok

Rectangle : Rectangle is a Container Report item for other report items.

Steps to Configure rectangle report item

• In  our previous report example drag and drop rectangle     ----------->
• Select rectangle and right-click , properties, In General Tab Rename it as Rect Table.     ----------->
• Click ok     ----------->
• Drag and Drop the designed table report item on the rect table-rectangle     ----------->
• Drag and Drop another rectangle and rename it as Rect chart in rectangle properties editor     ----------->
• Drag and Drop the configured chart on to Rect chart rectangle.     ----------->

Scenario: Display Table report item in 1 page and matrix report item on another page. When You export it to excel or pdf etc..

Steps

1. Select the Rect Table (Rectangle 1)
2. Right-click and Select properties     ----------->
3. Set,
4. Page Breaks – check  Insert Before Rectangle chat box. [To maintain the page   Breaks after Rect Table Rectangle]
5. Select preview Tab,     ----------->
6. Select bikes as a parameter and click view Report     ----------->
7. In the export option select excel save it to desktop open the exported excel file you will     ----------->
8. Observe that sheet 1 contains a Table report and sheet 2 Contains a chart Report.     ----------->

Image: page report item is used to display a bit-mapped Image Suck as photos or Logos in a report. The Selected or Configured image will be embedded to the report or to the  Selected project.

Scenario: Display Company Logo on each and every Report page.

• Select Report Menu and select page header and also select page footer     ----------->
• Press Alt + Ctrl + X     ----------->
• Drag and Drop image report item onto left corner of the page header     ----------->
• In Welcome to the image Wizard Click next     ----------->
• Select Embed (The Selected image is Stored as a part of the Report)     ----------->
• Click Next     ----------->
• Click New image     ----------->
• Select any jpg file and click open     ----------->
• Click next     ----------->
• Click Finish     ----------->
• Resize it based on the requirement

Text Box: Text Box is used to display labels or fields or values calculated from expressions.

From our previous report example,

Drag and Drop text box report item on to page footer     ----------->

Select  Text Box and right click and select expression option ; compose the following expression = “page ” & Globals ! Page Number  + “of ” & Globals ! Total pages = iif (Parameters  !  Product category ID . Value = 1,”Bikes”,”components”) & Datails for the Month –“ & Month Name (Month (Field ! Sell Start Date.Value))

List Report Item: It is used to display a set of Report item that are Repeated for each group (or) Row of Data.

Steps to Configure List Report item :

• In our previous Report Example, Remove the Existing Where Clause and add the below
• To implement multi-value Report.
• Where,
• (Production. Production category. Production category  ID IN @ Production category  ID)
• Select Report menu and Select Report parameter option, Set check multi-value checkbox     ----------->
• Click ok     ----------->
• Drag and Droplist Report item and place table Report item and chart Report item on to     ----------->
• List Report item     ----------->
• Select List Right-click and Select properties     ----------->
• In List properties editor,
• Select the General tab,     ----------->
• Data Set Name  - D set  product information      ----------->
• Click  Edit Details Group     ----------->
• Select   = Fields ! Production category  ID. Value in Group on section     ----------->
• Check page break at End  checkbox ( To maintain the page breaks after each group )     ----------->
• Click ok twice     ----------->
• Go to the preview tab Select accessories and bikes and click view Report      ----------->
• Export the Report to Excel format save it on desktop     ----------->
• Open the exported Excel, you would observe that Sheet 1 Contain accessories     ----------->
• details and Sheet 2 contains Bikes Data.     ----------->

Matrix:  Matrix report item displays the aggregated data in a grid format that has a Variable number Of rows and a variable number of Columns as well .

Steps to Configure Matrix Report Item

• In our  previous report example, drag and drop matrix report item     ----------->
• Go to the properties of matrix     ----------->
• In Matrix properties editor,     ----------->
• Data Set Name – Select D set_ product Information     ----------->
• Click ok     ----------->
• Press  “Alt + Ctrl + D” for data set      ----------->
• Drag and drop category Name on a row text box     ----------->
• Drag and drop subcategory Name on a Column  text box     ----------->
• Drag and drop Standard cost on a data  text box     ----------->
• Select data text box     ----------->
• Right-click and select add column. To add a new column Subcategory base column.     ----------->
• Drag and Drop the newly added column.     ----------->
• Set,    border Style as Solid and set the font size     ----------->
• Go to the preview tab

Drill Down Report :

• Select configured table report item     ----------->
• Go to table properties     ----------->
• Select Group tab, click add to  add a new group list     ----------->
• In Grouping and sorting properties editor, Select  = Fields. Category  Name. value.     ----------->
• From  the drop-down list , make sure that the include group header and group     ----------->
• Footer checkboxes , are checked.     ----------->
• Click ok     ----------->
• Click Details group     ----------->
• In Details grouping editor, Select  = Fields! Category Name. Value     ----------->
• In Details grouping editor, Select the Visibility tab and set,
• Initial Visibility – check the hidden radio button
• Check Visibility Can be tagged by another report item checkbox     ----------->
• Report  Item – Select appropriate text box Name
• Click ok      ----------->
• Click ok

How to display Group totals or Subtotals : From the above table report example add the below expression at the group footer Under Standard cast header.   = Sum ( Fields ! Standard cost. Value)

How to display Group totals of the report: From the above table report item, Provide the following expression to display the grand total Of the entire report. In report footer area Under Standard cost. = Sum (Fields ! Standard cost. Value)

How to Create template Reports in SSRS: Design basic Report template [Add a logo on header and display page numbers in page footer]     ----------->

Rename  it as Vijay IT Template, RDL     ----------->

Go to Project path     ----------->

Copy Vijay IT Template ,RDL and paste it  in the specified (or) bellow mentioned path     ----------->

C:/ Program Files / Microsoft visual studio 8 / common 7 / IDE / Private Assemblies / Project Assemblies / Project Items / Report project .

Linked Reports: Linked Report is the report with the settings and properties of its own. A linked report can have its own Name Description, Location, Parameter properties, Report Execution properties, etc..

Note:  A linked report must use data source properties and layout of its base report .

Steps to Configure Linked Report:

• Open  report manager (http :// Local host /reports)     ----------->
• Select a report to which you would like to create a linked report.     ----------->
• Select properties of the report     ----------->
• In General, Tab, click create  Linked Report     ----------->
• Provide Linked Report  name, description and change the location, etc.     ----------->
• Click ok

Note:    In properties of the Linked Report, Data Source, edit, and update option are not Available.  Since linked report always points to the data source and layout of its base or its Parent report.

Sub Report: Sub Report item is used to display am embedded report within current report.

Steps to Configure Subreport : Open Business Intelligence Development Studio      ----------->

Create a new report and rename it as Master Report .rdl      ----------->

Create a new dataset and provide the following query Select *         ----------->

From product category, INNER JOIN Product Subcategory ON Product category. Product category ID =  Product Subcategory. Product category ID WHERE (Product category. Product category ID = @ Product category ID)     ----------->

Create dataset 2 and provide the following Query Select Product category Id, Name As category Name from production. Product category     ----------->

Select Report Menu  and Select Report parameters     ----------->

Set,     ----------->

Select Product category ID parameter, Name - Product category Id Data type – String Prompt – Select Category     ----------->

Select “From Query” radio button to populate all the values in a parameter drop-down list and set,

Data set – Data set 2 Value Field - Product category Id Label Field -  Category Name        ----------->

Click ok      ----------->

In Layout Tab, Drag and Drop sub-report item     ----------->

Select Sub report , right-click and Select properties      ----------->

In General Tab,     ----------->

Sub Report – select any report from the drop-down list (The DDL contains only the reports  From the current projects) à In parameter Tab,     ----------->

Parameter Name  - Product category Id.     ----------->

Parameter Value -  = Parameters ! Product category ID value       ----------->

Click ok      ----------->

Select preview Tab

• Pass all the parameters Which are in the embedded report to Subreport to what

Ever the value it may be, either Original value or dummy value. The dummy value must Be like = “Bikes”.

Globals:  Global parameters or fields applies to a set of variables built into reporting Services, Details While configuring report Service in  SQL Server Configuration Tools Path – Provide path in Uniform Naming Convention format like //c or pc / Rama Credentials – provide User Name and password (given by the client) Render format – Select PDF or Excel Click Select Schedule to specify a time to execute the report.     ----------->

Select once Radio button and specify the time when to execute the report     ----------->

Click ok     ----------->

Select parameter Values     ----------->

Click ok The Report Server uses two databases to store the data after  Successful completion of report configuration.

1. Report Server
2. Report Server Temp DB

1. Report Server: Report Server  is the main database to store reporting Services data

Like, report definition (pdf file details) report model, data sources, Schedule information, Security details.

2. Report Server Temp DB: The  Report Server Temp DB  stores temporary reporting Services

Data like user Sessions, Cache information.

Expression to Show and Hide a column based on Parameter: Select any table column Press F4 for properties of the Selected table Column and set, Expand visibility Hidden – Select Expression = IIF (Parameters ! product Category ID . Value = 1, false, True)

How to provide  Role-Based security on Report Manager :

• Open Report Manager     ----------->
• Select any report to which you want to provide Security with respect to a role     ----------->
• Select properties option     ----------->
• Select Security page     ----------->
• Click Edit Item security     ----------->
• Click ok     ----------->
• Click New Role Assignment     ----------->
• Provide User Name and also Select one or more roles to assign to the Specific group or user     ----------->
• Click ok

Report Data Model :

• Open  Business Intelligence Development Studio     ----------->
• Select File Menu     ----------->
• New and Select project     ----------->
• Select  Business Intelligence project option     ----------->
• Select Report Model project Under template folder     ----------->
• Change the name and rename it as product details     ----------->
• Click ok     ----------->
• In Solution Explorer , Select Data source     ----------->
• Right Click and select Add new data source     ----------->
• In Welcome to data source wizard, click next     ----------->
• Select the data source if exits     ----------->
• Click Next, Rename the data source as product Details     ----------->
• Click Finish     ----------->
• Select Data source view     ----------->
• Right-click and select Add new data source view     ----------->
• Click Next     ----------->
• Select product Details data source and click Next      ----------->
• Select production. product Subcategory table and click Add related tables.     ----------->
• Click Next     ----------->
• Rename the data source view as product Details     ----------->
• Click finish     ----------->
• Select report models in solution explorer     ----------->
• Right click and select  Add New report model     ----------->
• Click Next     ----------->
• Select product Details . Dsv     ----------->
• Click Next thrice     ----------->
• Rename it as product Details     ----------->
• Click Run     ----------->
• Click Finish

At this point, the report model is available but it is not yet available to the end-user . Once we publish or deploy a report model to the Report Server, So that it will be available at the report Builder.

Report Builder: Report Builder is a windows application. Report Builder will load With the new report dialogue box and shows all the available report models.

Publish a report from the Report Builder :

• Open report builder from report manager     ----------->
• Double click on the available data model (Product Details)     ----------->
• Design the report by drag and drop and or double  click on the fields     ----------->
• Select File Menu and Select Save     ----------->
• The default save location is the Home folder on the reporting Services Server that hosts the report model     ----------->
• Click ok     ----------->
• Interactive Sort – Sort the report data dynamically In our  previous report example, Select category Name header text box
• Right-click and Select properties     ----------->
• In properties Editor, Select Interactive Sort tab     ----------->
• Check to Add on Interactive Sort action to this text box checkbox and Set     ----------->
• Sort Expression - = Fields ! Category Name . value     ----------->
• Click ok

Drill through Reports (or) Hyperlink Reports (or) Navigation Reports: Jump To Report:

• In our previous report example Select sub-category Details Selection text box     ----------->
• Right-click and select properties     ----------->
• In text box properties editor, select the Navigation tab and set,     ----------->
• Check Jump to Report radio button     ----------->
• Select any report from the DDL     ----------->
• Click ok

Jump to  Url:

• In  our previous report example, select Name detail text box     ----------->
• Right-click and Select Navigation tab     ----------->
• Check Jump to Url radio button     ----------->
• Click fx and provide the following expression = “Java Script: Void window. Open
• (URL of child report from report Manager)”

Report Parameters properties :

Hidden: By using hidden properties we can hide the parameter on the parameter input area of the published Report .

For example: While implementing subreport hide the parameter in the embedded Report.

Multivalue: If you want to display multiple elections then multi-value parameter is used. In order to implement multi-value parameter Report  IN operator must be used in the dataset. Internal: If you Set a parameter to internal it is not exposed in any way except in the Report  Definition. The internal parameter is a parameter that can’t be changed at Runtime. A consumer of a published report will never see this as a parameter .