SQL Server Reporting Services (SSRS)– Call a DLL assembly

1) Create an external Assembly DLL (Class Library). You can use Framework 3.5.

a) Name it ‘DataForSSRSReport’.
b) Rename the default class to ‘clsDataForSSRS’. Make sure the class is Public.
c) Use ‘Private Shared’, ‘Public Shared Property’, and ‘Public Shared Function’

Public Class clsDataForSSRS
    Private Shared mGettysburgAddress As String = String.Empty

    Public Shared Property GettysburgAddress()
        Get
            GettysburgAddress = mGettysburgAddress
        End Get
        Set(ByVal value)
            value = mGettysburgAddress
        End Set
    End Property

    Public Shared Function GetGettysburgAddress() As String
        GetGettysburgAddress = String.Empty
        Try
           mGettysburgAddress = "Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal." & vbCrLf _
                     & vbCrLf & "Now we are engaged in a great civil war, testing whether that nation, or any nation so conceived and so dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this." & vbCrLf _
                     & vbCrLf & "But, in a larger sense, we can not dedicate -- we can not consecrate -- we can not hallow -- this ground. The brave men, living and dead, who struggled here, have consecrated it, far above our poor power to add or detract." & vbCrLf _
                     & vbCrLf & "The world will little note, nor long remember what we say here, but it can never forget what they did here. It is for us the living, rather, to be dedicated here to the unfinished work which they who fought here have thus far so nobly advanced. It is rather for us to be here dedicated to the great task remaining before us -- that from these honored dead we take increased devotion to that cause for which they gave the last full measure of devotion -- that we here highly resolve that these dead shall not have died in vain -- that this nation, under God, shall have a new birth of freedom -- and that government of the people, by the people, for the people, shall not perish from the earth." & vbCrLf _
                     & vbCrLf & "-- Abraham Lincoln" & vbCrLf _
                     & "Nov. 19, 1863"

           GetGettysburgAddress = GettysburgAddress
        Catch ex As Exception
         'Error has occurred
        End Try

        Return GetGettysburgAddress
    End Function
End Class

2) Setup a strong name to sign or version the assembly. Do not password protect.

B1 B2

3) Add an import to System.Security and provide the proper permissions in the
AssemblyInfo.vb file.

B3

4) Build the assembly.

5) Copy the DLL you created, ‘DataForSSRSReport.dll’, to the following folder:

C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServerbin

6) Copy the DLL you created, ‘DataForSSRSReport.dll’,  into the GAC by creating and executing a bat file with the following code:

CD 
CD "C:Program FilesMicrosoft SDKsWindowsv6.0ABin"gacutil.exe -i "C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServerbinDataForSSRSReport.dll"
PAUSE 'Any key to finish'

B4

The DLL is now in the GAC and can be viewed 2 ways:

a)  GAC Browser
http://sourceforge.net/projects/gacbrowser/

B5

b)  c:Windowsassembly

B6

7) Using the Business Intelligence Development Studio add-on, create an SSRS report (RDL). Place a textbox on the report that will be populated with data from the DLL you created, ‘DataForSSRSReport.dll’.

B7

8) Right click outside the body of the report, and then click on ‘Report Properties’. In the ‘Report Properties’ dialog box, click ‘References’. Click the ‘Add’ button. In the ‘Add Reference’ dialog box, click the ‘Browse’ tab. Navigate to and select the DLL you created, ‘DataForSSRSReport.dll’, then click the ‘OK’ button. Finally, click the ‘OK’ button on the ‘Report Properties’ dialog box.

Note:

SSRS will actually use the GAC to get the DLL.

B8 B9

9) Add the following expression to the textbox of the report to get the data
from the DLL.

=DataForSSRSReport.clsDataForSSRS.GetGettysburgAddress

Note:

DataForSSRSReport.dll is the DLL (namespace)
clsDataForSSRS is the name of the static (shared) class
GetGettysburgAddress is the function that returns the data

10) Run the report.

B10

Note:

Any time a change is made to the DLL, repeat steps 4 through 6, and then you must close and reopen the Business Intelligence Development Studio to test it again locally.

Active Reports – How to access a public variable on a parent report from a sub report.

In the ActiveReport-ReportStart section of your parent report, you can assign any object type to the Report object variable, ‘UserData’

UserData is a public object of the DataDynamics.ActiveReports.ActiveReport.

Scenario:

Let’s say you are using a Watermark in your parent report. The Watermark is embedded into your parent report at design time. You want to share this Watermark with your sub reports. To do this, store the image of this Watermark in the UserData object. Now it can be accessed by a sub report.

You can write the code in vbscript like this:

Sub ActiveReport_ReportStart 
    rpt.UserData = rpt.Watermark
    . . . . . . .
End Sub

In your sub report, you may want to display the Watermark depending on some condition.

You can access the UserData object that was set in your parent report from your sub report like this:

If "Some Condition" = True Then
    rpt.ParentReport.Watermark = Nothing
Else
    rpt.ParentReport.Watermark = rpt.ParentReport.UserData
End If

If would like to learn how to access controls and their properties on a parent report from a sub report, you can check out my article on the subject here:

http://www.jamesandchey.net/?p=559

Active Reports – Sub reports overlapping each other when rendered.

If you are like me and have a report that dynamically calls a sub report that then calls sub reports (using the sub report control), you may have run into the occasional sub report overlap nightmare. The latest encounter for me had to do with textboxes on the sub reports having properties of CAN GROW, MULTILINE, and WORD WRAP. Because these textboxes can grow, depending on whether these textboxes are populated with data less than 5 characters or many characters, overlapping the next sub report can occur.

With trial and error, I have found two solutions to the problem. You could even use both solutions to cover all bases. Before I get into explaining the solutions, let me give you more background on what I am trying to accomplish with these reports.

The image below represents a sub report that is called dynamically from the parent report. Based on my experience and knowledge (I could be wrong), sub reports cannot call sub reports dynamically. If they could, I would not have written this article and you most likely would not be reading it. Instead, I had to drag ten sub report controls to the surface of this sub report.

subreportflush

Notice how all the sub reports are flush to the left and bottom of the previous sub report (Well, except the last one for the purpose of presentation). This could potentially cause overlapping nightmares, especially if these reports have textboxes that CAN GROW.

Without going into the whys and how comes, I am simply going to offer my solutions.

The image below represents a small amount of padding I placed between each sub report control. The reason I have such a small amount of padding is because the white space will take up real estate on the rendered report and can look ugly if the gap is too big. This small gap is exactly what you need to fix the problem.

RENDER-ISSUE-01

Do you want double insurance? The second solution expounds on the first solution. The image below represents placing a line control (make it INVISIBLE) between each sub report in the gap we provided in the first solution. The line control ensures that the previous sub report renders completely.

RENDER-ISSUE-02

 

Hope this helps!

Active Reports 6 – Inputbox

Instead of passing a parameter to a report with a value set to preexisting data, you can instead prompt the user running the report with an inputBox (Microsoft.VisualBasic) in order to capture the data on the fly to be used in the report.

For example, let’s create a report that displays three textboxes (First, Middle, and Last Name) as such:

Inputbox-001

In the VB script behind the design, dim three variables that will hold the values entered via the inputBox.

Dim mFirstName As String = String.Empty
Dim mLastName As String = String.Empty
Dim mMiddleName As String = String.Empty

In the ReportStart sub routine section of the report,  write the code that will prompt the InputBox.

mFirstName = inputBox("What is your first name?", "FIRST NAME")
mMiddleName = inputBox("What is your middle name?", "MIDDLE NAME")
mLastName = inputBox("What is your last name?", "LAST NAME")

Executing the report will display the inputBox like this:

Inputbox-002

After clicking “OK”, two more inputBox’s will appear for the Middle and Last Name.

After entering John Michael Doe, the report will then render in the viewer and look like this:

Inputbox-003

You can get the code here. The file is called TestInputbox.rpx.
http://sdrv.ms/17yvYud