June Reporting Services Guru - Tim Pacl brings us "Using Custom Code in SSRS Reports for Advanced String Operations"
It's time for our next June TechNet Guru winner!
About Tim: Retired from the U. S. Navy Submarine Force. Working in computer industry since 1998. 4 years of that at Razorfish as Senior Developer and the remaining years at Dell, Inc. TFS administration, builds, ALM, web front end development, ASP.Net, Winforms, SQL Reporting. (Amongst others)
Here is Tim's winning article:
Here are all the June SSRS winners (all were from Tim):
|SQL Server Reporting Services Technical Guru - June 2013|
|Tim Pacl||Sorting Elements of a Concatenated String||Peter Laker: "An excellent article with some useful tips" Ed Price: "This is a great article! You have a lot of code samples and a clear overview."|
|Tim Pacl||Adding Links in SSRS Reports||Peter Laker: "Images really help to explain the procedure" Ed Price: "The images help make the steps very clear!"|
|Tim Pacl||Concatenating Data Column Values Into a Single String||Peter Laker: "Useful tip, common problem" Ed Price: "Good formatting on the code in this article."|
And thank you to Tim for 6 great articles about Reporting Services! Find all 6 here.
Here's an excerpt from the article:
SQL Services Reporting Services is a very powerful tool and with each new release, it grows in functionality. However many users find that the expression builder functions do not quite do what they need. Fortunately, Microsoft has provided a powerful means of extending the capabilities of a report, virtually without limit, by using the custom code capability. While custom code can access custom assemblies, the string manipulation functionality discussed here uses only the core .Net assemblies which are available by default on the Reporting server and so requires no explicitly referenced assemblies.
String Manipulation Using Custom Code
The SQL Server Reporting Services, Power View MSDN forum contains numerous questions asking how to extend the string manipulation capabilities of Reporting Services. In some cases, the solution can only be achieved through custom code. We will explore three different string manipulation scenarios requiring custom code in SSRS reports:
- Concatenating Data Column Values Into a Single String
- Counting Occurrences in a Concatenated String
- Sorting Elements of a Concatenated String
Concatenating Data Column Values Into a Single String
Occassionally a report author may have a need to display detail records in a grouped table in a single cell rather than separate rows.
My dataset returns multiple rows of data for a given grouping with only one field (we will call it Grade) varying across those rows. I want my report to display a single line for a group with all of the values of Grade for that group displayed as a concatenated string in a single cell of each group row.
Add the below function to the custom code module of the report:
Private Num AsString= ""Private tempstr AsString=
Function AggregateString(Group asString, Val asString ) as String If Group = Num Then
tempstr = tempstr +", "+ Val
Num = Group
tempstr = Val
Return tempStr EndFunction
With this custom VB.Net function, you can create a delimited string that concatenates all of the specified detail records of a group using the RunningValue function.
Read the rest here:
Also, for the July Guru competition, see TechNet Guru Contributions for July 2013.
Are you a Wiki Ninja? http://technet.com/wiki
- Ninja Ed