We can create different kind of reports using Reporting Services. This article describes the terminology used for the various types of reports and the ways reports can be created and then used. A single report can have multiple characteristics e.g. snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report, and subreports can be linked reports.
With Reporting Services, we can create following types of reports:
* Parameterized reports
* Linked reports
* Snapshot reports
* Cached reports
* Ad hoc reports
* Drilldown reports
* Drillthrough reports
* Subreports
Parameterized reports: A parameterized report uses input parameter to complete report processing. With a parameterized report, you can filter report data based on the values that are set at run time. Parameterized reports are frequently used for filtering reports data.
Linked reports: A linked report is a report that provides an access to an existing report. It is similar to a hiperlink that we use to open a page. A linked report is derived from an existing report and retains the original report definition. We can create a linked report on the report server when we want to create additional versions of an existing report. We can create linked reports whenever we want to deploy an existing report with different settings.
Snapshot reports: A snapshot report contains query results that were retrieved at a specific time. Unlike on-demand reports, which get up-to-date query results when we run the report, snapshots reports are processed on a schedule and then saved to Report Server. When we select a report snapshot to view, Report Server retrieves the stored report from the report server database and shows the data and layout that were captured for the report at the time the snapshot was created.
Report snapshots serve three purposes:
1. Report history - by creating a series of report snapshots, we can build a history of a report that shows data changes over time.
2. 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.
3. Performance - by scheduling large reports to run during off-peak hours, we 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. These 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.
Ad hoc reports: An ad hoc report can be created from an existing Report Model using Report Builder. Ad hoc reports refer specifically to Report Builder reports. Ad hoc reports leverage report models and pre-defined templates to enable information workers to quickly and easily explore business data using familiar terminology and data structures that have been implemented in the report model. Ad hoc reports can be saved and run locally or published to a report server, just like other Reporting Services reports.
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. Best example of Drilldown report is Sale information for the Year, then drill down for Quarters followed by Months and week. Other example is Sale by Region then drilldown by Countries followed by State and Cities.
Drillthrough reports: Drillthrough reports are standard reports that are accessed through a hyperlink on a report item 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.
Subreports: A subreport displays another report inside the body of a main report. A subreport is used to embed a report within another report. Any report can be used as a subreport. The subreport can use different data sources than the main report. We 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.
Tuesday, 19 October 2010
Table Partition
Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.
Benefits of table partitioning
There are numerous benefits of table partitioning:
* To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
* Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
* Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
* A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.
The steps for creating a partitioned table include the following:
1. Create a partition function to specify how a table or index that uses the function can be partitioned.
2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
3. Create a table or index using the partition scheme.
Below are the steps to creation Horizontal Table Partition
-- Create the partition function
CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO
-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO
-- Create a simple table
CREATE TABLE PartitionTable (
DateKey int NOT NULL,
CustomerKey int NOT NULL,
SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
(DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO
------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO
-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO
--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO
Benefits of table partitioning
There are numerous benefits of table partitioning:
* To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
* Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
* Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
* A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.
The steps for creating a partitioned table include the following:
1. Create a partition function to specify how a table or index that uses the function can be partitioned.
2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
3. Create a table or index using the partition scheme.
Below are the steps to creation Horizontal Table Partition
-- Create the partition function
CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO
-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO
-- Create a simple table
CREATE TABLE PartitionTable (
DateKey int NOT NULL,
CustomerKey int NOT NULL,
SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
(DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO
------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO
-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO
--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO
Date Dimension
USE [DatabaseName]
GO
IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO
CREATE TABLE [dbo].[Date](
[DateSK] [int] NOT NULL,
[FullDate] [datetime] NOT NULL,
[DateName] [char](11) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayNameOfWeek] [char](10) NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[WeekdayWeekend] [char](7) NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[MonthName] [char](10) NOT NULL,
[MonthOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarYearMonth] [char](7) NOT NULL,
[CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)
DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'
WHILE (@StartDate <= @EndDate ) BEGIN INSERT INTO Date SELECT CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK ,@StartDate AS [Date] ,CONVERT(varchar(20),@StartDate,106) AS DateName ,DATEPART(DW,@StartDate) [DayOfWeek] ,DATENAME(DW,@StartDate) [DayNameOfWeek] ,DATENAME(DD,@StartDate) [DayOfMonth] ,DATENAME(DY,@StartDate) [DayOfYear] ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd' ELSE 'WeekDay' END [WeekdayWeekend] ,DATEPART(WW,@StartDate) [WeekOfYear] ,DATENAME(MM ,@StartDate) [MonthName] ,DATEPART(MM ,@StartDate) [MonthOfYear] ,DATEPART(QQ,@StartDate) [CalendarQuarter] ,DATEPART(YY ,@StartDate) [CalendarYear] ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2) [CalendarYearMonth] ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr] SET @StartDate = @StartDate +1 END GO Date Dimension is ready to use as soon as you execute this script in required database. You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD). Year-->Quarter-->Month-->Week-->Date
GO
IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO
CREATE TABLE [dbo].[Date](
[DateSK] [int] NOT NULL,
[FullDate] [datetime] NOT NULL,
[DateName] [char](11) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayNameOfWeek] [char](10) NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[WeekdayWeekend] [char](7) NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[MonthName] [char](10) NOT NULL,
[MonthOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarYearMonth] [char](7) NOT NULL,
[CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)
DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'
WHILE (@StartDate <= @EndDate ) BEGIN INSERT INTO Date SELECT CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK ,@StartDate AS [Date] ,CONVERT(varchar(20),@StartDate,106) AS DateName ,DATEPART(DW,@StartDate) [DayOfWeek] ,DATENAME(DW,@StartDate) [DayNameOfWeek] ,DATENAME(DD,@StartDate) [DayOfMonth] ,DATENAME(DY,@StartDate) [DayOfYear] ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd' ELSE 'WeekDay' END [WeekdayWeekend] ,DATEPART(WW,@StartDate) [WeekOfYear] ,DATENAME(MM ,@StartDate) [MonthName] ,DATEPART(MM ,@StartDate) [MonthOfYear] ,DATEPART(QQ,@StartDate) [CalendarQuarter] ,DATEPART(YY ,@StartDate) [CalendarYear] ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2) [CalendarYearMonth] ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr] SET @StartDate = @StartDate +1 END GO Date Dimension is ready to use as soon as you execute this script in required database. You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD). Year-->Quarter-->Month-->Week-->Date
Cascading Parameters in SSRS 2008
Cascading parameters provide a way of managing large amounts of report data. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. Its values are updated with a list of subcategories within the chosen category. When the user views the report, the values for both the category and subcategory parameters are used to filter report data.
In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters.
I will use AdventureWorks2008R2 database for this article. You can install this database from codeplex.
STEP 1:
Open your existing Report Server project or create a new Report Server project. Add a new report and rename it as CascadingParameters.rdl. Now create a Data Source for AdventureWorksDW2008R2 database. For more information about creating shared Data Source, Click Creating Shared Data Source in SSRS 2008.
STEP 2:
Create three data sets as mentioned below:
1. dsProductCategory: This Data Set will be used to create a parameter for Product Categories. Use below query for this data set:
SELECT ProductCategoryID,Name AS ProductCategory
FROM Production.ProductCategory (NOLOCK)
ORDER BY Name
2. dsProductSubcategory: This Data Set will be used to create a Cascading Parameter for Product Subcategories. Use below stored procedure for this data set.
CREATE PROC procProductSubcategory
(
@ProductCategoryID varchar(1000)
)
AS
BEGIN
SELECT ProductSubcategoryID,Name AS ProductSubcategory
FROM Production.ProductSubcategory (NOLOCK)
WHERE ProductCategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductCategoryID,','))
ORDER BY Name
END
-------- UNIT TESTING ------------------
-- EXEC procProductSubcategory '1'
-- EXEC procProductSubcategory '1,2'
-- EXEC procProductSubcategory '1,3'
----------------------------------------
GO
Note: Function SplitMultivaluedString is used to split comma seperated values of parameter @ProductCategoryID. Click Function to Split Multi-valued Parameters to get T-SQL script of this function.
Result of EXEC procProductSubcategory '1,3' is shown below:
3. dsProduct: This data set will give the list of all the products based on the Product SubCategories selected at run time. Use below stored procedure for this data set:
CREATE PROC procProductsList
(
@ProductSubcategoryID varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT
PC.Name ProductCategory
,PS.Name ProductSubcategory
,P.Name ProductName,ProductNumber
,Color,Size,[Weight]
FROM Production.Product P (NOLOCK)
INNER JOIN Production.ProductSubcategory PS
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE P.ProductSubcategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductSubcategoryID,','))
END
-- EXEC procProductsList '6,7,8,18,30,36'
GO
Result of EXEC procProductsList '6,7,8,18,30,36' is shown below:
STEP 3:
Once you create above data sets, you can see two parameters created automatically - ProductCategoryID and ProductSubcategoryID as shown below:
Now double click on parameter ProductCategoryID to open Report Parameter Properties window. In General, change the Prompt value as Product Category and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field and ProductCategory in Label field. Now click on Default Values, Click on Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field. Finally click OK to save changes.
Similarily change the properties of parameter ProductSubcategoryID. In General, change the Prompt value as Product Subcategory and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field and ProductSubcategory in Label field. In Default Values, Click on Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field.
Finally click OK to save changes.
STEP 4:
In report Design, drag and drop a table control and map with dsProduct data set. select all the columns from dsProduct in the tables and format this as shown below:
Thats all. We are done with a report having one cascading parameter. Click on Preview to view the report. Now you can see that values of Parameter Product Subcategory depends on the parameter Product Category. By Default, Product Category parameter has (Selected All) so Product Subcategory will also display all the values.
Values of Product Subcategory parameter will be filtered as per selection of the values of Product Category parameter, which will filter the actual report data because Product Subcategory parameter is used to filter report data.
In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters.
I will use AdventureWorks2008R2 database for this article. You can install this database from codeplex.
STEP 1:
Open your existing Report Server project or create a new Report Server project. Add a new report and rename it as CascadingParameters.rdl. Now create a Data Source for AdventureWorksDW2008R2 database. For more information about creating shared Data Source, Click Creating Shared Data Source in SSRS 2008.
STEP 2:
Create three data sets as mentioned below:
1. dsProductCategory: This Data Set will be used to create a parameter for Product Categories. Use below query for this data set:
SELECT ProductCategoryID,Name AS ProductCategory
FROM Production.ProductCategory (NOLOCK)
ORDER BY Name
2. dsProductSubcategory: This Data Set will be used to create a Cascading Parameter for Product Subcategories. Use below stored procedure for this data set.
CREATE PROC procProductSubcategory
(
@ProductCategoryID varchar(1000)
)
AS
BEGIN
SELECT ProductSubcategoryID,Name AS ProductSubcategory
FROM Production.ProductSubcategory (NOLOCK)
WHERE ProductCategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductCategoryID,','))
ORDER BY Name
END
-------- UNIT TESTING ------------------
-- EXEC procProductSubcategory '1'
-- EXEC procProductSubcategory '1,2'
-- EXEC procProductSubcategory '1,3'
----------------------------------------
GO
Note: Function SplitMultivaluedString is used to split comma seperated values of parameter @ProductCategoryID. Click Function to Split Multi-valued Parameters to get T-SQL script of this function.
Result of EXEC procProductSubcategory '1,3' is shown below:
3. dsProduct: This data set will give the list of all the products based on the Product SubCategories selected at run time. Use below stored procedure for this data set:
CREATE PROC procProductsList
(
@ProductSubcategoryID varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT
PC.Name ProductCategory
,PS.Name ProductSubcategory
,P.Name ProductName,ProductNumber
,Color,Size,[Weight]
FROM Production.Product P (NOLOCK)
INNER JOIN Production.ProductSubcategory PS
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE P.ProductSubcategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductSubcategoryID,','))
END
-- EXEC procProductsList '6,7,8,18,30,36'
GO
Result of EXEC procProductsList '6,7,8,18,30,36' is shown below:
STEP 3:
Once you create above data sets, you can see two parameters created automatically - ProductCategoryID and ProductSubcategoryID as shown below:
Now double click on parameter ProductCategoryID to open Report Parameter Properties window. In General, change the Prompt value as Product Category and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field and ProductCategory in Label field. Now click on Default Values, Click on Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field. Finally click OK to save changes.
Similarily change the properties of parameter ProductSubcategoryID. In General, change the Prompt value as Product Subcategory and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field and ProductSubcategory in Label field. In Default Values, Click on Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field.
Finally click OK to save changes.
STEP 4:
In report Design, drag and drop a table control and map with dsProduct data set. select all the columns from dsProduct in the tables and format this as shown below:
Thats all. We are done with a report having one cascading parameter. Click on Preview to view the report. Now you can see that values of Parameter Product Subcategory depends on the parameter Product Category. By Default, Product Category parameter has (Selected All) so Product Subcategory will also display all the values.
Values of Product Subcategory parameter will be filtered as per selection of the values of Product Category parameter, which will filter the actual report data because Product Subcategory parameter is used to filter report data.
SSRS Expressions
Reporting Services Expressions
In Reporting Services, Expressions are used frequently in reports to control content and report appearance. 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.
Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:
* Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression =Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.
* Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <>. You can create complex expressions in the Expression dialog box or type them directly into the Property pane.
Examples of SSRS Expression
Using Built-in Fields
Display Report Execution Time in a textbox:
="Report Execution Time: " & Globals!ExecutionTime
Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:
Date & Time Functions
The Today() function provides the current date. The expression =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can use Format function to display required format. Some of the popular date formats are given below:
Expression
Output
=FORMAT(Today(),"M/d/yy") 8/23/10
=FORMAT(Today(),"MM-dd-yyyy") 08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy") Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy") August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") Aug 23, 2010 01:43:33 PM
Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)
DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())
DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)
There are many other Date &Time functions which can be used expression:
String Functions
• 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
•Format dates and numbers in a string with the Format function.
=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")
•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, "\"))
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)
• Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")
•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")
There are many other function which can be used in expression as shown below:
Conversion Functions
You can use Visual Basic functions to convert a field from the one data type to a different data type.
*
The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)
* The following expression displays the number of values selected for the multivalue parameter MyParameter: =CStr(Parameters!MyParameter.Count)
Decision Functions
The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:
=IIF(Fields!Total.Value > 100, True, False)
Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. 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!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))
A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:
=Switch(Fields!PercentComplete.Value >= 10, "Green", Fields!PercentComplete.Value > 1, "Blue", Fields!PercentComplete.Value = 1, "Yellow", Fields!PercentComplete.Value <= 0, "Red",) A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color. =Choose(Fields!MyIndex.Value,"Red","Green","Yellow") Check the value of the PurchaseDate 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!PurchaseDate.Value, Now())>7,"Red","Blue")
In Reporting Services, Expressions are used frequently in reports to control content and report appearance. 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.
Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:
* Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression =Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.
* Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <>. You can create complex expressions in the Expression dialog box or type them directly into the Property pane.
Examples of SSRS Expression
Using Built-in Fields
Display Report Execution Time in a textbox:
="Report Execution Time: " & Globals!ExecutionTime
Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:
Date & Time Functions
The Today() function provides the current date. The expression =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can use Format function to display required format. Some of the popular date formats are given below:
Expression
Output
=FORMAT(Today(),"M/d/yy") 8/23/10
=FORMAT(Today(),"MM-dd-yyyy") 08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy") Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy") August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") Aug 23, 2010 01:43:33 PM
Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)
DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())
DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)
There are many other Date &Time functions which can be used expression:
String Functions
• 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
•Format dates and numbers in a string with the Format function.
=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")
•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, "\"))
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)
• Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")
•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")
There are many other function which can be used in expression as shown below:
Conversion Functions
You can use Visual Basic functions to convert a field from the one data type to a different data type.
*
The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)
* The following expression displays the number of values selected for the multivalue parameter MyParameter: =CStr(Parameters!MyParameter.Count)
Decision Functions
The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:
=IIF(Fields!Total.Value > 100, True, False)
Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. 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!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))
A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:
=Switch(Fields!PercentComplete.Value >= 10, "Green", Fields!PercentComplete.Value > 1, "Blue", Fields!PercentComplete.Value = 1, "Yellow", Fields!PercentComplete.Value <= 0, "Red",) A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color. =Choose(Fields!MyIndex.Value,"Red","Green","Yellow") Check the value of the PurchaseDate 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!PurchaseDate.Value, Now())>7,"Red","Blue")
Subscribe to:
Posts (Atom)