SQL Server – How to inner join a table to receive just one row of that table where a field is MAX. For example, has the greatest date.

This SQL statement will produce one row for each record found on the account_table for a given account number

We want also want to join the name and address table to the row. We only want one row from the transaction table (there are many)

that has the greatest transaction date and add it to our row as TransactionDate.

Here is how:

SELECT a.account_number, b.name_last, b.name_first, c.address, c.phonenumber, TransactionDate
FROM account_table a
LEFT OUTER JOIN
    (
    SELECT MAX(transaction_date) AS TransactionDate
    FROM transaction_table
    GROUP BY transaction_number
    ) groupedtt ON a. account_number = groupedtt. account_number 
LEFT OUTER JOIN name_table b on a. account_number = b. account_number
LEFT OUTER JOIN address_table c on a account_number = c. account_number 
where a. account_number = 'ACCT12345'

Active Reports 6 – Sort a collection in Active Reports

In this example, we take an unsorted Product Collection called ProductCollection that is assigned

to our report Data Source. We instantiate another Product Collection for sorting called

sortedProductCollection. Using a Do Loop, we read the Product Collection and build the

sortedProductCollection in sorted order. When done, we overlay the ProductCollection

in the Data Source with our sortedProductCollection

‘Sort the Product Collection

Dim sortedProductCollection As New ProductCollection

‘A class that defines a collection

Dim mCounter = 0
Dim mLoopComplete = False

Do Until mLoopComplete = True
    For Each item As product In rpt.DataSource(0).ProductCollection
        If item.product_number.Value = mCounter + 1 Then
            sortedProductCollection.Add(item)
            Exit For
        End If
    Next
	
    mCounter += 1
	
    If rpt.DataSource(0).ProductCollection.Count = sortedProductCollection.Count Then
       mLoopComplete = True
    End If
Loop

rpt.DataSource(0).ProductCollection = sortedProductCollection

SQL Server Reporting Services (SSRS) – Multiple IIf statements in an expression behind a textbox

Instead of using a code behind for simple decisions, you can use unlimited multiple IIf statements in an expression behind a textbox.

For example, let’s say you have a parameter called HoldType that can have 3 different values, (1, 2, or 3). Depending on the value, you may want to make an entire row of a report visible or not depending on these values.


To do this, use an IIf statement to interrogate a value and then assign an action to that value. In the case of only 2 different scenarios, you would simply type a comma and the alternative action. But what if like in this example there is more than 2 scenarios. You simply begin another IIf statement after the comma. You can keep adding multiple IIf statements until all scenarios are handled, and then simply add the default action after the comma of the last IIf statement as you would normal do with a single IIf statement.

The statement below demonstrates the visibility of a textbox on the report.

If HoldType is “1” then don’t hide the row, ELSE if HoldType is “2” then hide the row, ELSE don’t hide the row for any other value.

=IIf(Parameters!HoldType.Value = "1", FALSE, IIf(Parameters!HoldType.Value = "2", TRUE, FALSE))

Microsoft Silverlight – Setting Initial Focus on Silverlight Control

Even though you may have set focus to your first enterable textbox in a Silverlight Control, it will not truly receive the focus until the actual Silverlight control has focus first.

In order to do this, add a few lines of javascript in the the section of the main *.aspx document (Web Client) and you are good to go. The default name of the initial Silverlight XAML document will be ‘Xaml1’.

The code:

<script type="text/javascript">
window.onload = function()
{
document.getElementById('Xaml1').focus();
}
</script>;

SQL Server Reporting Services (SSRS) – How to Receive an Image into a Report Parameter to a SSRS Report

In this example, I will demonstrate how to convert an image in an Windows Form Application to a Base64 String that can be used to populate a report parameter that will be received into a SQL Server Reporting Services report as a report parameter.

For example, in my Windows Forms Application, I take an image that is already stored in an image variable called mImage, and convert it to a base64string with the following code:

Private mSignatureString As String = String.Empty
mSignatureString = ImageToBase64(mImage , System.Drawing.Imaging.ImageFormat.Jpeg
Public Function ImageToBase64(ByVal mImage As Image, ByVal format As System.Drawing.Imaging.ImageFormat) As String
     Using ms As New MemoryStream()
         ' Convert Image to byte[]
         mImage.Save(ms, format)
         Dim imageBytes As Byte() = ms.ToArray()
         ' Convert byte[] to Base64 String
         Dim base64String As String = Convert.ToBase64String(imageBytes)
         Return base64String
     End Using
End Function

I then pass the variable mSignatureString as a parameter called ‘Signature‘ to the process that calls my SSRS report.

My code to that looks like this:

Dim myparams As New List(Of ReportParameter)

Dim myparam As New ReportParameter("some_guid", mSomeObject.some_guid.ToString)
 myparams.Add(myparam)

Dim myparam1 As New ReportParameter("Signature", mSignatureString)
 myparams.Add(myparam1)
 
 Dim mNewReport As New frmReportViewer(e.ClickedItem.Text, e.ClickedItem.Tag, True, False, myparams)

NOTE: I don’t cover the reportviewer control that is placed on a windows form and the code behind in this post.

NOTE: If no signature is required on the report, just pass something like ‘NOSIG‘ into the parameter instead so the report can make the image control invisible so not to produce the ‘red X’ box. You will see how I use the text value of ‘NOSIG‘ when building the report below.

Building the Report

Define a parmater in SSRS and call it Signature

 

Add an Image control to the report.

Open up the Image Properties. Set the ‘image source’ = “Database” and MIME type¬†= “image/bmp”.

 

In Image Properties, click the ‘Fx’ button under “Use this field:” and type the following expression:

In Image Properties, click ‘Visibility’. Click radio button ‘Show or hide based on an expression’ and enter the following:

If the parameter value passed from visual basic = “NOSIG“, then nothing will appear in the signature line else the image will appear.