Types of SSRS Reports

With Reporting Services, you can create the following types of reports:

·         Parameterized reports

·         Linked reports

·         Snapshot reports

·         Cached reports

·         Ad hoc reports

·         Clickthrough reports

·         Drilldown reports

·         Drillthrough reports

·         Subreports

Parameterized Report

A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.

Using Parameters

Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.

You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.

Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.

Query Parameters and Report Parameters

Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server.

Linked Reports

A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.

Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.

Snapshot Reports

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.

Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.

Report snapshots serve three purposes:

·         Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.

·         Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.

·         Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Cached Reports

A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes. 

Clickthrough Reports

A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.

Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.

Drilldown Reports

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. For reports with large amounts of data, consider drillthrough reports instead.

Drillthrough Reports

Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query.

Subreports

A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.

Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports. For reports with many instances of subreports, consider using drillthrough reports instead.

Using XMLA Script-Deploy and Schedule SSAS Project

SSAS supports several options to meet different deployment needs. In below table describes outlines these options and a scenario in which using the option is recommended.      

Deployment Options:

Option	Recommended Use
BIDS	Deploying the latest changes to your local server for testing
Deployment Wizard	Deploy to a test or production environment when you need more granular control
XMLA script	Scheduling a deployment task
Synchronize Database Wizard	Synchronizing two cubes, such as a staging cube and a production cube
Backup and restore	Moving a cube from one server to another
AMO	Handling deployment programmatically

When running the Analysis Deployment Wizard,at the final step confirm deployment page you can view select create deployment script checkbox option,If you select  or enable the option the wizard generates an XMLA file named project name scritp.xmla and saves it in the specified location.

Understanding the Deployment Script:

The deployment script consists of two sections.

The first section starts with the Alter XMLA command followed by definitions of all objects in the database.

The second section starts with a Process XMLA command. It instructs the server to process the database by using the processing mode you specified in the Select Processing Options like

Option	Setting	Description
Processing Option	Default	Applies the minimum processing tasks to bring the cube to a ready state
	Full	Full processes the database
	Do not process	Deploys the changes only

For example:

Section-1:

<Alter Allow Create="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>SSAS ProjectDatabaseID>

  Object>

  <ObjectDefinition>

    <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <ID>SSAS ProjectID>

      <Name>SSAS ProjectName>

      <Language>1033Language>

      <Collation>Danish_Norwegian_CI_ASCollation>

      <DataSourceImpersonationInfo>

        <ImpersonationMode>DefaultImpersonationMode>

      DataSourceImpersonationInfo>

    Database>

  ObjectDefinition>

Alter>

After the database is fully created on the SSAS Server but not yet processes. In order to process the database we also need a process XMLA script. Like above we simply create this by grabbing it from SSMS. The script looks like this:

Section-2:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <Type>ProcessFullType>    

      <Object>

        <DatabaseID>SSAS 7 ProjectDatabaseID>

      Object>     

    Process>

Batch>

  

Because the script is described in XMLA, you can modify it manually or you can run the Deployment Wizard again and overwrite the existing script file.

Running the Deployment Script:

When the deployment script is ready, you have several options for executing it. You can execute it manually in SQL Server Management Studio (SSMS), you can schedule the script execution with the SQL Server Agent services.

To execute manually, simply open the script file in SSMS, connect to the target server when prompted, and click the Exclamation toolbar button !Execute to run the script or press CTRL+E.

You can schedule the script execution with the SQL Server Agent Service, which is a component of SQL Server. SQL Server Agent supports an SQL Server Analysis Services Command task, which can use to execute any XMLA script.

Let see, step by step process for scheduling the XMLA script in SSMS. 

1.  Go to SSMS -> click SQL Server Agent -> Jobs Right click and select new job -> Write name

2. Click on Steps option in the left side -> Click New button -> Fill the details like below

Copy and paste the XMLA code which Analysis Deployment wizard generated.

In additional, you can mention some advanced options. 

3.  Click on Schedules options -> Click on new button and fill the details as per the requirement. Below are the sample schedule job timings.

4. Still you can mention Alerts,Notofications,Targets options in order to extend your functionality for the     job.

5.SQL Server Agent will automatically run the Schedule SSAS Cube job and you can check the history of the job by checking the view history options on the properties of the job.

sp_helptext (Transact-SQL)

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

-- View

use AdventureWorksDW2008

sp_helptext vDMPrep

-- Stored Proceudre

use AdventureWorks

sp_helptext uspGetBillOfMaterials

--Object_definition will not do this, but sp_help

--how does sp_helptext  work? 

sp_helptext 'sp_helptext'

SSRS Page Breaks

In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.

The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.

=Ceiling(RowNumber(Nothing)/25)

To allow the user to set a value for the number of rows per page, create a parameter named RowsPerPage and base the group expression on the parameter, as shown in the following expression:

=Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)

SSRS Expressions

Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.
Here are some of the reporting services expressions:
1.To get Today’s date 
=Today
2.The following code will get Today's date -3 days
=DateAdd("d", -3, Today)
3.Get the Name of the day - like sunday
=WeekdayName(DatePart("w", Today))
4.SWITCH statement - An alternative to IIF/CASE 
=SWITCH(WeekdayName(Fields!Date.Value) = "Monday","Blue",
WeekdayName(Fields!Date.Value) = "Tuesday","Green",
WeekdayName(Fields!Date.Value) = "Wednesday","Red")

5.Format Numbers as Currency 
=FormatCurrency(1000)
6. Convert integer values to string
= CStr(123123)
7.The Iif function returns one of two values depending on whether the expression is true or not.

=IIF(Fields!LineTotal.Value > 100, True, False) 

8.Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on the value of PctComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))

9.Test the value of the ImportantDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:

=IIF(DateDiff("d",Fields!ImportantDate.Value,Now())>7,"Red","Blue")

10.Test the value of the Department field and return either a subreport name or a null (Nothing in Visual Basic). This expression can be used for conditional drillthrough subreports.

=IIF(Fields!Department.Value = "Development", "EmployeeReport", Nothing)

11.The Sum function can total the values in a group or
data region. This function can be useful in the header or footer of a group.

=Sum(Fields!LineTotal.Value, "Order") 

12.You can also use the Sum function for conditional aggregate calculations.

=Sum(IIF(Fields!State.Value = "Finished", 1, 0)) 

13.The RowNumber function, when used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. This function can be useful to number rows in a table. It can also be useful for more complex tasks, such as providing page breaks based on number of rows
The scope you specify for RowNumber controls when renumbering begins. The Nothing keyword indicates that the function will start counting at the first row in the outermost data region. To start counting within nested data regions, use the name of the data region. To start counting within a group, use the name of the group.

=RowNumber(Nothing)

14.Color alternate rows in table with a different color - This is very useful when there are a lot of rows with similar data and its hard to differentiate the rows. You have to set the table background color property with the following code.

=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
15. Dynamic DataSource - If you have a requirement for a report to be run against multiple database server you can change the datasource connection string property to be dynamic. You can write your own expression in the connection string option in the data source properties. The following will create a dynamic connection to the server depending on the report parameter (ServerName) selected. Note: You have to make sure that the login has permission on all the servers you would like to connect to.

="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=WSS_Content"
16.Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:

=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

17.Format dates and numbers in a string with the Format function.

=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

18.The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

19.The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)

20.Join - Display the selected values from a multivalue  parameter
=Join(Parameters!MyParameter.Value,",")
21.The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn": 

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")