BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
SQL Server Reporting Services allows you to write (and use) custom functions written in Visual Basic within a report. This blog considers the pros and cons, and shows how to do this.
- Embedding Code in Reporting Services
- Embedding Code in a Report to Show a Film's Status (this blog)
- Truncating a Film's Description using a Function
- Writing VB Code in a Class Library
Posted by Andy Brown on 13 July 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Embedding Code in a Report to Show a Film's Status
As promised, we'll create a function which will give us the Oscar status of a film:
Each film can have one of three status descriptions, depending on whether it won or was nominated for Oscars, or neither.
Writing the Function
To embed the function to show a film's Oscar status within a report, you first need to write it! Here's a suggestion:
Function OscarStatus(Wins As Integer, Nominations As Integer) As String
'determine whether an Oscar winner, nominee or neither
If Wins > 0 Then
Return "Winner"
ElseIf Nominations > 0 Then
Return "Nominee"
Else
Return "N/A"
End If
End Function
This function will take two arguments as follows:
| Argument | Type | What it means |
|---|---|---|
| Wins | Integer | How many Oscars the film won. |
| Nominations | Integer | How many Oscars the film was nominated for. |
Given these two bits of information, the code uses an IF statement to return the required status (which is of data type String).
All functions that you create must return one - and only one - value, which they should do using a Return statement.
Types of Argument Allowed in VB
Here's a list of the main types of data you can use in a VB function:
| What to use | What it denotes | Example |
|---|---|---|
| Boolean | Anything either True or False | Whether an actor is still alive |
| Integer | Any whole number | The length of a film in minutes |
| Double | Any other number (eg 3.14) | The budget for a film in dollars |
| Date | Any date or time | The date a film was released |
| String | Any string of text characters | The name of a film |
There are a lot more types, but if you're just starting out with VB these will do for now.
Embedding the Code in your Report
Now that you know what you want to say, how do you say it? First show your report's properties in Design view:
|
|
| Right-click on the report's edge ... | ... or use the Report menu. |
You can now type in your code:
Type your code into the Code tab, as shown here.
If you've written your code in another software application, any formatting will be lost when you save your code in this dialog box.
Finally, select OK to confirm the new code.
Using Embedded Code
Once you've embedded code in a report, you can call it within any expression by using the prefix Code. This is usually best done in SSRS by adding a calculated field to a dataset:
Right-click on any dataset and choose to add a calculated field.
You can now give your new field a name and an expression:
Here we've called the new field OscarStatus, and are about to give it an expression.
Type in the function you want to use, prefixed by code.:
SSRS won't give you any help, and will leave your function name underlined.
Was it worth it?
The above example allows us to create a function which spits out the status of a film given two parameters, but you can do this in at least 3 other ways, all of which are probably better!
Firstly, you could include the expression in the underlying SQL view or stored procedure. This would have the advantage that you could then use the result of the expression in any report based on this view/procedure:
-- return status given Oscar performance
CASE
WHEN FilmOscarWins > 0 THEN 'Winner'
WHEN FilmOscarNominations > 0 THEN 'Nominee'
ELSE 'N/A'
END AS OscarStatus
Alternatively, you could have used a SWITCH expression in a calculated field in SSRS:
This expression would also return the Oscar status.
This calculated field could be used in any table, matrix, list or chart based on this dataset.
Finally, you could create the function in a separate class using either VB or C# and reference it as a custom assembly (something I'll blog about soon):
public static string OscarStatus(
int Wins,
int Nominations
) {
if (Wins > 0)
{
return "Winner";
}
else
{
if (Nominations > 0)
{
return "Nominee";
}
else
{
return "N/A";
}
}
}
This would allow you to use indentation, comments, Intellisense and - if you were that way inclined - C# as above, and would be appropriate for more complicated functions.
For simple functions like the one on this page, it's hard to see how embedded code is worth it (an SSRS or SQL expression would be a better idea).
Given the hint above, let's look at a more complicated example!
- Embedding Code in Reporting Services
- Embedding Code in a Report to Show a Film's Status (this blog)
- Truncating a Film's Description using a Function
- Writing VB Code in a Class Library