• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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 etx. 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 Data base is the name of the database from where you are copying the objects and Destination database is the name Of the database to which you are copying the objects to

Connection:

Source Connection                                                          – the source instance

Source Database                                                              – name of the source database

Destination Database                                                     – 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, 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 particular version type of objects selection will vary.

Security

Copy Data base users                                                     – whether to include users

Copy Data base Roles                                                     – Whether to include roles

Copy SQL Server Logins                                                 – Whether to include logins

Copy object level permissions                                    – whether to include object level permission

Aspired to become a MSBI Developer? Explore the post to discover the know-hows on
 MSBI Training in Bangalore.

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 script in Unicode or——–

Transfer jobs task

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 Tool box to the control Flow pane as shown below.  Right click on this task and click on edit

Steps to configure 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 exit 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 Data bases – this allows you to transfer all logins associated with     one or more data bases as shown in the image below.

Logins List:

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

Data base List : 

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

Options

  • If object Exists :

If the Logins already exit 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.

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

Architecture of SSRS(SQL )

8

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 http:// Local host/Report Server.

Report Manager:

  • Report Manager is a Web based administrative Inter face 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 Grid format that has a fixed number of columns and

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

Learn more about MSBI Interview Questions in this blog post.

(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

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 http:// Local host/Report Server.

Report Manager:

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

Example:   1.Table

  1. Columns Different chart Template
  2. Rectangle
  3. List
  4. 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 Grid format that has a fixed number of columns and 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’s 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 Report menu and Report parameter option
  • Check multi value label Check box
  • Click ok      ———–>
  • Go to preview tab Select multiple Values from the parameter drop down list and click      ———–>
  • Preview Reports

1 . Expressions in SSRS:

            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)”)

  1. 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 ).

  1. 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),”centre ”,”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”

  1. 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”)

  1. 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 chart and Select properties from 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 Grid line and specify the scale (or) interval 0.5     ———–>

Title Aligen – centre     ———–>

Major tick lines – cross     ———–>

Major tick lines – cross      ———–>

Check side Margings check box to display the chart items in centre of the plotter area.     ———–>

In Y – axis,

Title – Size

Check Major Grid line and specify the scale (or) interval 0.5     ———–>

Title Aligen – centre     ———–>

Major tick lines – cross     ———–>

Major tick lines – cross      ———–>

Check side Merging check box to display the chart items in centre of the plotter area.

In Legend Tab,

Check show Legend (by default)

Lay out – 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 Set point labels in chart (Display the exact Value on plotted area)     ———–>

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

Select point labels Tab     ———–>

Check show point labels check point,     ———–>

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 in another page. When

You export it to excel or pdf etc..

Steps

  1. Select Rect Table (Rectangle 1)
  2. Right click and Select properties     ———–>
  3. Set,
  4. Page Breaks – check  Insert Before Rectangle chart 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 export option select excel save it to desk top open the exported excel file you will     ———–>
  8. Observe that sheet 1 contains Table report and sheet 2 Contains 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 Emberd (The Selected image is Stored as a part of the Report)     ———–>
  • Click Next     ———–>
  • Click New image     ———–>
  • Select any jpj 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 check box     ———–>
  • Click ok     ———–>
  • Drag and Drop list 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 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  check box ( To maintain the page breaks after each group )     ———–>
  • Click ok twice     ———–>
  • Go to 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 display the aggregated data in a grid format that has Variable number Of rows and 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 sub  category 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 Sub category base column.     ———–>
  • Drag and Drop newly added column.     ———–>
  • Set,    border Style as Solid and set the font size     ———–>
  • Go to 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 check boxes , are checked .     ———–>
  • Click ok     ———–>
  • Click Details group     ———–>
  • In Details grouping editor, Select  = Fields ! Category Name . Value     ———–>
  • In Details grouping editor, Select Visibility tab and set ,
  • Initial Visibility – check hidden radio button
  • Check Visibility Can be tagged by another report item check box     ———–>
  • Report  Item – Select appropriate text box Name
  • Click ok      ———–>
  • Click ok

How to display Group totals or Sub totals :

From  the  above table report example add the  below expression at 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 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 is always points to data source and layout of its base or its Parent  report.

Sub Report :

Sub Report   item  is used to display am embedded report with in current report.

Steps to Configure Sub report :

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 Sub category

ON Product category . Product category ID =  Product Sub category. 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 Sub report to what

Ever the  value it may be, either Original value or dummy  value. Dummy value must

Be like = “Bikes”.

Globals :  Global parameters or fields applies to a set of variables built in to 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 client)

Render format – Select PDF or Excel Click Select Schedule to specify 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 data base to store reporting Services data

Like report definition (rdl file details) report model , data sources , Schedule information,

Security details .

  1. 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
These core tutorials will help you to learn the Report Server. For an in-depth understanding and
 practical experience, explore MSBI Training in Hyderabad.

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 Sub category 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 Add on Interactive Sort action to this text box check box 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 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 sub report hide the parameter in the embedded Report.

Multi value:

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. Internal parameter is a parameters that can’t be changed at Runtime . A consumer of a published report will never See this as a parameter .

For an Indepth knowledge on MSBI click on:

Summary
Review Date
Reviewed Item
Transfer SQL server Objects Task
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Transfer SQL server Objects Task"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.