I have already written T-SQL Function to convert a string into Camel Case. In this article I am going to share same function but in Visual Basic code. This function can be used directly in SSRS code.
Here is Visual Basic code to convert a string into Camel Case (Proper case):
Public Function CamelCaseInVB(ByVal str as String) as String
Dim strFinal As String
Dim i As Integer
scrstr = Trim(LCase(str))
i = 1
If str.Length > 0 Then
While i <= str.Length
If i = 1 Then
strFinal = strFinal + UCase(Mid(str, i, 1))
i = i + 1
ElseIf Asc(Mid(str, i, 1)) = 32 Then
strFinal = strFinal + Space(1) + UCase(Mid(str, i + 1, 1))
i = i + 2
ElseIf Asc(Mid(str, i, 1)) = 40 Then
strFinal = strFinal +"(" + UCase(Mid(str, i + 1, 1))
i = i + 2
Else
strFinal = strFinal + Mid(str, i, 1)
i = i + 1
End If
End While
End If
Return strFinal
End Function
Thursday, September 30, 2010
Tuesday, September 21, 2010
SSIS Script Task to Find and Replace File contents
In my previous article Script Task to Read File I explained how to read the contents of a file and store in a package variable.
Few folks from my followers had been asked about Find and Replace mechanism using Script Task. In this article I am going to explain how to replace contents of a file.
I'll show following operations in this solution:
STEP2: Drag and drop Script Task and rename it as Script Task - Read File and then Find and Replace String. Double click on Script Task to open Script Task Editor, Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWrite Variables. Now Click on Edit Script... and copy & paste below code:
public void Main()
{
String ErrInfo = "";
String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
try
{
String FileContent; //Variable to store File Contents
FileContent = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while reading File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
DateTime currDate, StartDate, EndDate;
currDate = DateTime.Today;
StartDate = currDate.AddDays(-(currDate.Day - 1));
EndDate = StartDate.AddMonths(1).AddDays(-1);
//FileContent Before Replace;
MessageBox.Show(FileContent);
//Find and Replace --> Modify WHERE clause
FileContent = FileContent.Replace(
"D.DateKey BETWEEN 20080101 AND 20080131",
"D.DateKey BETWEEN " +
String.Format("{0:yyyyMMdd}", StartDate) + " AND " +
String.Format("{0:yyyyMMdd}", EndDate)
);
//FileContent After Replace;
MessageBox.Show(FileContent);
Dts.Variables["User::FileContent"].Value = FileContent;
//Write the contents back to File
WriteToFile(FilePath,FileContent,ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while writing File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return "";
}
}
public void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
StreamWriter sWriter;
try
{
sWriter = new StreamWriter(FilePath);
sWriter.Write(strContents);
sWriter.Close();
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
}
}
STEP3: We are done with package changes. Execute the package to test the output.
Messagebox for actual file contents
Messagebox after REPLACE (modified WHERE clause)
Now if you look at the actual file, the query has been modified as shown below:
Cheers!!!
Few folks from my followers had been asked about Find and Replace mechanism using Script Task. In this article I am going to explain how to replace contents of a file.
I'll show following operations in this solution:
- Read contents of .sql file and store in a variable.
- Modify the WHERE condition of T-SQL query to fetch current months data
- Store the contents back to the file.
STEP1: Create a new ssis package and add two package variable as shown below:
STEP2: Drag and drop Script Task and rename it as Script Task - Read File and then Find and Replace String. Double click on Script Task to open Script Task Editor, Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWrite Variables. Now Click on Edit Script... and copy & paste below code:
public void Main()
{
String ErrInfo = "";
String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
try
{
String FileContent; //Variable to store File Contents
FileContent = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while reading File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
DateTime currDate, StartDate, EndDate;
currDate = DateTime.Today;
StartDate = currDate.AddDays(-(currDate.Day - 1));
EndDate = StartDate.AddMonths(1).AddDays(-1);
//FileContent Before Replace;
MessageBox.Show(FileContent);
//Find and Replace --> Modify WHERE clause
FileContent = FileContent.Replace(
"D.DateKey BETWEEN 20080101 AND 20080131",
"D.DateKey BETWEEN " +
String.Format("{0:yyyyMMdd}", StartDate) + " AND " +
String.Format("{0:yyyyMMdd}", EndDate)
);
//FileContent After Replace;
MessageBox.Show(FileContent);
Dts.Variables["User::FileContent"].Value = FileContent;
//Write the contents back to File
WriteToFile(FilePath,FileContent,ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while writing File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return "";
}
}
public void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
StreamWriter sWriter;
try
{
sWriter = new StreamWriter(FilePath);
sWriter.Write(strContents);
sWriter.Close();
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
}
}
STEP3: We are done with package changes. Execute the package to test the output.
Messagebox for actual file contents
Messagebox after REPLACE (modified WHERE clause)
Now if you look at the actual file, the query has been modified as shown below:
Cheers!!!
Thursday, September 16, 2010
Script Task to Read File
Problem: Reading a file using Script Task
Solution: I'll explain this by creating a new SSIS package, reading the contents of the file and store the results in a package variable using Script Task.
In this article, I have covered about how to Read File in Script using Visual Basic 2008 and Visual C# 2008. Below are the steps to achieve the goal:
STEP1: Create a new SSIS package (I have renamed it as ReadFile.dtsx) and add two package variables FilePath and FileContent as shown below:
FilePath is used to store the path of the file being read by script task. FileContent variable isa used to store the contents of the file.
STEP2: Drag & drop Script Task and double click to open Script Task Editor. Enter Script Task - Read File using C Sharp 2008 as Name in General tab. Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWriteVariables, and Microsoft Visual C# 2008 in ScriptLanguage in Script tab.
Note: Here I am using two script tasks to explain the solution in both Visual Basic and C# 2008.
STEP3: Click on Edit Script... button of Script Task Editor and paste following code:
using System.IO;
public void Main()
{
String ErrInfo = "";
String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
try
{
Dts.Variables["User::FileContent"].Value = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while reading File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
MessageBox.Show(Dts.Variables["User::FileContent"].Value.ToString());
//Here do whatever you want to do with file contents
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return "";
}
}
STEP4: Thats all. We are done with package development. The content of File will be stored in the package variable FileContent while executing the package. To test the output, execute this package, a message box will popup (I didn't comment the Messagebox in the code) as shown in below:
And here is the snapshot of actual file:
Below is the code for Visual Basic 2008.
Imports System.IO
Imports System.Text
Public Sub Main()
Dim dataBytes() As Byte = Nothing
Dim ErrInfo As String = "" 'Variable to store Error Information
Try
Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString
Dts.Variables("User::FileContent").Value = ReadFile(FilePath, _
ErrInfo).ToString()
If ErrInfo.Length > 0 Then
Dts.Log("Error while reading contents of file " & _
FilePath, 0, dataBytes)
Dts.Log(ErrInfo, 0, dataBytes)
Dts.TaskResult = ScriptResults.Failure
Exit Sub
End If
MessageBox.Show(Dts.Variables("User::FileContent").Value.ToString)
Dts.TaskResult = ScriptResults.Success
Catch Ex As Exception
Dts.Log(Ex.Message, 0, dataBytes)
Dts.TaskResult = ScriptResults.Failure
Exit Sub
End Try
End Sub
Public Function ReadFile(ByVal FilePath As String, _
Optional ByVal ErrorInfo As String = "") As StringBuilder
Dim strContents As New StringBuilder
Dim objReader As StreamReader
Try
objReader = New StreamReader(FilePath)
strContents.Append(objReader.ReadToEnd())
objReader.Close()
Return strContents
Catch Ex As Exception
ErrorInfo = Ex.Message
Return strContents
Exit Function
End Try
End Function
Solution: I'll explain this by creating a new SSIS package, reading the contents of the file and store the results in a package variable using Script Task.
In this article, I have covered about how to Read File in Script using Visual Basic 2008 and Visual C# 2008. Below are the steps to achieve the goal:
STEP1: Create a new SSIS package (I have renamed it as ReadFile.dtsx) and add two package variables FilePath and FileContent as shown below:
STEP2: Drag & drop Script Task and double click to open Script Task Editor. Enter Script Task - Read File using C Sharp 2008 as Name in General tab. Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWriteVariables, and Microsoft Visual C# 2008 in ScriptLanguage in Script tab.
Note: Here I am using two script tasks to explain the solution in both Visual Basic and C# 2008.
STEP3: Click on Edit Script... button of Script Task Editor and paste following code:
using System.IO;
public void Main()
{
String ErrInfo = "";
String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
try
{
Dts.Variables["User::FileContent"].Value = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while reading File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
MessageBox.Show(Dts.Variables["User::FileContent"].Value.ToString());
//Here do whatever you want to do with file contents
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return "";
}
}
STEP4: Thats all. We are done with package development. The content of File will be stored in the package variable FileContent while executing the package. To test the output, execute this package, a message box will popup (I didn't comment the Messagebox in the code) as shown in below:
And here is the snapshot of actual file:
Below is the code for Visual Basic 2008.
Imports System.IO
Imports System.Text
Public Sub Main()
Dim dataBytes() As Byte = Nothing
Dim ErrInfo As String = "" 'Variable to store Error Information
Try
Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString
Dts.Variables("User::FileContent").Value = ReadFile(FilePath, _
ErrInfo).ToString()
If ErrInfo.Length > 0 Then
Dts.Log("Error while reading contents of file " & _
FilePath, 0, dataBytes)
Dts.Log(ErrInfo, 0, dataBytes)
Dts.TaskResult = ScriptResults.Failure
Exit Sub
End If
MessageBox.Show(Dts.Variables("User::FileContent").Value.ToString)
Dts.TaskResult = ScriptResults.Success
Catch Ex As Exception
Dts.Log(Ex.Message, 0, dataBytes)
Dts.TaskResult = ScriptResults.Failure
Exit Sub
End Try
End Sub
Public Function ReadFile(ByVal FilePath As String, _
Optional ByVal ErrorInfo As String = "") As StringBuilder
Dim strContents As New StringBuilder
Dim objReader As StreamReader
Try
objReader = New StreamReader(FilePath)
strContents.Append(objReader.ReadToEnd())
objReader.Close()
Return strContents
Catch Ex As Exception
ErrorInfo = Ex.Message
Return strContents
Exit Function
End Try
End Function
Monday, September 13, 2010
Groups in SSRS 2008
In Report Designer, Groups are created to organize data on the report or to calculate aggregate summaries. An understanding how to define groups and use group features helps you design reports that are more concise and communicate summarized data in a more compact format.
We also use Groups in SSRS reports to provide Drill-down features. In this article, I'll walk you through SSRS Groups to create a report shown in below image:
STEP1: Add new report in Report Server project and rename it to GroupsInSSRS.rdl.
Create new Shared DataSource for database AdventureWorksDW2008R2.
Click here to download AdventureWorksDW2008R2 database.
STEP2: Create a new dataset dsMain with Shared Data Source. Use below query for this dataset:
SELECT
D.CalendarYear AS [Year]
,D.CalendarQuarter AS [Quarter]
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,P.EnglishProductName AS [ProductName]
,C.FirstName + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,F.SalesOrderNumber AS [OrderNumber]
,F.SalesAmount
FROM FactInternetSales F
JOIN DimProduct P
ON P.ProductKey = F.ProductKey
JOIN DimCustomer C
ON C.CustomerKey = F.CustomerKey
JOIN DimDate D
ON D.DateKey = F.OrderDateKey
JOIN DimSalesTerritory ST
ON ST.SalesTerritoryKey = F.SalesTerritoryKey
STEP3: Drag and drop a table control and select CustomerName, OrderNumber, and SalesAmount fields in the table.
STEP4: Select table detail row --> Right click --> select Add Group --> Row Group --> Parent Group
STEP5: Select ProductName in Tablix group window and check add group header check box. Click OK and sace changes.
Now you can see Product Group in the table as shown below:
Delete Group1 Column and rename Customer Name as Particulars. Select ProductName in Product Group as shown below:
STEP6: Select ProductName group and add one more group for SalesCountry in similar way mentioned at step5. Delete the group column and select SalesCountry in CountryGroup as shown below:
STEP7: In the same way add Groups for the following Fields:
STEP8: we are done with report Groups. You can Preview the report:
STEP9: Format Groups: This is the final step. Without formatting the tabular data look meaningless and its very difficult to understand the report. You can do following formatting to make it easier to understand:
Similar way toggle other report items.
Also Toggle Order Number column with Particular text box.
Now click on Preview and generate the report:
Thats all about Report Groups...
Cheers!!!
We also use Groups in SSRS reports to provide Drill-down features. In this article, I'll walk you through SSRS Groups to create a report shown in below image:
Create new Shared DataSource for database AdventureWorksDW2008R2.
Click here to download AdventureWorksDW2008R2 database.
STEP2: Create a new dataset dsMain with Shared Data Source. Use below query for this dataset:
SELECT
D.CalendarYear AS [Year]
,D.CalendarQuarter AS [Quarter]
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,P.EnglishProductName AS [ProductName]
,C.FirstName + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,F.SalesOrderNumber AS [OrderNumber]
,F.SalesAmount
FROM FactInternetSales F
JOIN DimProduct P
ON P.ProductKey = F.ProductKey
JOIN DimCustomer C
ON C.CustomerKey = F.CustomerKey
JOIN DimDate D
ON D.DateKey = F.OrderDateKey
JOIN DimSalesTerritory ST
ON ST.SalesTerritoryKey = F.SalesTerritoryKey
STEP3: Drag and drop a table control and select CustomerName, OrderNumber, and SalesAmount fields in the table.
STEP4: Select table detail row --> Right click --> select Add Group --> Row Group --> Parent Group
STEP5: Select ProductName in Tablix group window and check add group header check box. Click OK and sace changes.
STEP6: Select ProductName group and add one more group for SalesCountry in similar way mentioned at step5. Delete the group column and select SalesCountry in CountryGroup as shown below:
STEP7: In the same way add Groups for the following Fields:
- SalesRegion
- Date
- Month
- Quarter
- Year
| Group | Left Indent | BackgroundColor |
|---|---|---|
| Year | 2pt | #7c73c8 |
| Quarter | 12pt | #9894ca |
| Month | 22pt | #b4b4c8 |
| Date | 32pt | #c7c7d8 |
| SalesRegion | 42pt | #dadaeb |
| SalesCountry | 52pt | #e7e7f0 |
| ProductName | 62pt | #f4f4fc |
| CustomerName (detail row) | 72pt | White |
Toggle Groups:
Select Quarter Group and click on Group Properties. Select Visibility in Group Properties and select Hide option button. Check Dispaly can be toggled by this report item check box and select Year from combo box.
Similar way toggle other report items.
Also Toggle Order Number column with Particular text box.
Now click on Preview and generate the report:
Thats all about Report Groups...
Cheers!!!
Saturday, September 4, 2010
Conditional Formatting in SSRS 2008
Alternate Background Color in Tabular Report:
Alternate background color of rows in a table make it easier to read horizontally. Also it makes report look and feel better. This is very common but simple task to achieve in SSRS report using expression.
Select the detail row of the data table in the layout tab.
Go to property window and select expression of BackgroundColor and enter below expression:
=IIF(RowNumber(Nothing) Mod 2 = 0,"Transparent","#cdfcbe")
Now click and Preview the report:
Format Phone number using REGEX function:
Suppose you want to display Phone no in (ddd) ddd-dddd format instead of ddd-ddd-dddd. To do this, Right click on the required textbox --> click expression... and enter below expression:
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
Now click on Preview and see the report:
Changing Font Color dynamically:
Sometime we need to format data based on the their values. Below is an example for Base rate color selection:
To do this, Enter C (Currency format) in Format and below expression in Color property of BaseRate field:
=SWITCH(Fields!BaseRate.Value <= 15,"Green",
Fields!BaseRate.Value <= 25,"Blue",
Fields!BaseRate.Value <= 40,"Black",
Fields!BaseRate.Value > 40,"Red")
Now click on Preview and see the report:
Swapping Font Color and BackgroundColor:
Suppose you want to swap Font Color and BackgroundColor of Gender field. Use below expressions in BackgroundColor and Color properties respectively:
=IIF(Fields!Gender.Value = "M","Tomato","Teal")
=IIF(Fields!Gender.Value <> "M","Tomato","Teal")
Now click on Preview and see the report:
Alternate background color of rows in a table make it easier to read horizontally. Also it makes report look and feel better. This is very common but simple task to achieve in SSRS report using expression.
Select the detail row of the data table in the layout tab.
=IIF(RowNumber(Nothing) Mod 2 = 0,"Transparent","#cdfcbe")
Now click and Preview the report:
Format Phone number using REGEX function:
Suppose you want to display Phone no in (ddd) ddd-dddd format instead of ddd-ddd-dddd. To do this, Right click on the required textbox --> click expression... and enter below expression:
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
Now click on Preview and see the report:
Changing Font Color dynamically:
Sometime we need to format data based on the their values. Below is an example for Base rate color selection:
| Base Rate | Color |
|---|---|
| <= 15 | Green |
| > 15 AND <=25 | Blue |
| > 25 AND <=40 | Black |
| > 40 | Red |
To do this, Enter C (Currency format) in Format and below expression in Color property of BaseRate field:
=SWITCH(Fields!BaseRate.Value <= 15,"Green",
Fields!BaseRate.Value <= 25,"Blue",
Fields!BaseRate.Value <= 40,"Black",
Fields!BaseRate.Value > 40,"Red")
Now click on Preview and see the report:
Swapping Font Color and BackgroundColor:
Suppose you want to swap Font Color and BackgroundColor of Gender field. Use below expressions in BackgroundColor and Color properties respectively:
=IIF(Fields!Gender.Value = "M","Tomato","Teal")
=IIF(Fields!Gender.Value <> "M","Tomato","Teal")
Now click on Preview and see the report:
Wednesday, September 1, 2010
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:
Using Built-in Fields
Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Date & Time FunctionsThe 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:
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)
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:
You can use Visual Basic functions to convert a field from the one data type to a different data type.
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:
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.
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:
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.
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.
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
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)
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)