Posts Tagged ssrs

Missing Parameters in SSRS

For you SSRS report developers out there, have you ever received the error “Must define scalar variable @<variable name>” and then drove yourself crazy trying to troubleshoot it? You probably even checked your Report Parameters a dozen more times to make sure you weren’t crazy or blind?

I have experienced this issue on several occasions while working in SQL Server Business Intelligence Development Studio. I have yet to zero in on exactly what causes this to happen, but it appears to happen during times when I am making several changes to a report in fairly quick succession, toggling between the Data, Layout, and Preview tabs.

In this example, I received the error “Must declare scalar variable @StartDate”

I looked at my input parameters (Report >> Report Parameters) and all parameters were defined. I looked at my SQL statement and all parameters were included in the query and had the correct names. Nothing seemed out of the ordinary. I then checked the Dataset settings and on the Parameters tab noticed that all of my parameters were missing. They just disappeared!

All you have to do is re-enter your parameters in this window, and your report should run just fine.

No Comments

SQL Reporting Services (SSRS) CSS Workaround

I recently did a report for a client who could run the “same” report (a customer monthly statement) for any one of five separate companies that they own (in this case they are using Microsoft Dynamics GP and have five companies within it).
Each statement had a variety of branded things including a different logo and different color scheme for the text throughout the report.
SSRS doesn’t have this type of CSS support, so I used embedded code to make this easier for me.

First I have a parameter on the report for what company the report is for. In this case the parameter is called companyInits and it’s a 3 character string.
Then on each textbox that needs to be colored I have this expression:
=Code.CompanyColor(Parameters!CompanyInits.Value)
This calls the embedded VB code below.

In the report menu, under report properties, in the code tab I added this (this is just a sample, the real code has five separate colors, one for each company):

Public Shared Function CompanyColor (CompanyInits As String) As String
 Select Case CompanyInits
  Case “NWD”  
     CompanyColor = “#64B6B9″
  Case “XXX”
   CompanyColor = “#FF0000″
  Case Else
   CompanyColor = “#000000″
 End Select 
End Function

Basically on any text in the report that needs to be colored based on the company, I change the color property to call this custom code. Be careful, because if the custom code won’t compile, it will just give you a generic reportservice error when you try to run a preview of the report.
I have an additional function that hides controls that I need to show based on which company the report is being run for. For example, the company logo. I have five images added to the report with the following expression in the Visibility, Hidden property:
=Code.HideImage(“imgLogoXXX”,Parameters!CompanyInits.Value)

For me “XXX” would be the initials of the company that the logo is for.
Then in the code window I have this code:

Public Shared Function HideImage (ControlName As String,CompanyInits As String) As boolean
 If Right(ControlName,3) = CompanyInits Then
  HideImage = False
 Else
  HideImage = True
 End IF 
End Function
I know that this isn’t as good as a CSS that could be applied based on a parameter, but it works for me! Also, you can easily imagine how this can be used for font-type, style, etc. Also, by going directly into the code of the rdl it probably wouldn’t be that hard to apply this to many fields pretty quickly. For a lot of different functions it may be best to avoid embedded code and instead do a .net assembly (this may depend on client preferences).

Thanks
Dwight Brown
Systematica Inc.

, , , , ,

No Comments

Displaying Email Text in a CRM Report

If you have ever built a custom report Dynamics CRM that references the Description field of the email entity, you probably realized that the body of an email is stored in the database in HTML format. Thus your report may have looked something like this:

CRM Email Format

In order to display an email correctly in an SSRS report you need to add a function into the code library of the report and then reference that function in your data field. To drop in the code, navigate to Report -> Report Properties -> Code. The following function can be used to strip HTML from an input field: Read the rest of this entry »

, , , , ,

2 Comments