Archive for category Reporting Services

Comparing rows from disparate tables in VB.NET and SSRS

I love using the word “disparate”, by the way.

I needed to compare two fields returned from a SQL query to determine which of the two fields occurred later. The fields were text, and were in the format “Consulting Services – 6/3/2013”. I had two different tables that this data was being returned from, Products and Services. (Yes, the names have been changed to protect the innocent.) And I needed to show this data in a SSRS report.

Here is the VB.NET function I used inside Reporting Services to compare the dates of these two fields. I have added plenty of comments, because everyone loves over-commented code:

Public Function GetLatestProductOrService(ByVal Product As String, ByVal Service As String) As String

        ‘ In SSRS, if the fields are empty, they are sent as Nothing.
If Product Is Nothing Then Product = String.Empty
If Service Is Nothing Then Service = String.Empty

        ‘ Get the space right before the date portion of the string
Dim ProductSpace As Integer = Product.LastIndexOf(” “)
Dim ServiceSpace As Integer = Service.LastIndexOf(” “)

       ‘ If neither products nor services were returned, then don’t display anything
If ProductSpace < 0 AndAlso ServiceSpace < 0 Then
Return String.Empty
End If

       ‘ If there is a value for the product, strip out the date from the string. If there was no service passed to the function,

       ‘no need to go further, so return the product
Dim ProductDate As String = String.Empty
If ProductSpace > -1 Then
ProductDate = Product.Substring(ProductSpace, Product.Length – ProductSpace)
If ServiceSpace < 0 Then
Return Product
End If
End If

       ‘ Like above, just for services
Dim ServiceDate As String = String.Empty
If ServiceSpace > -1 Then
ServiceDate = Service.Substring(ServiceSpace, Service.Length – ServiceSpace)
If ProductSpace < 0 Then
Return Service
End If
End If

Dim LastUpdateDate As String = String.Empty

       ‘ If both products and services were passed to the function, see which was processed last
If CDate(ProductDate) > CDate(ServiceDate) Then
LastUpdateDate = Product
Else
LastUpdateDate = Service
End If

Return LastUpdateDate

End Function

Calling this beautiful function through VB.NET code is simple:

Dim Result As String = GetLatestProductOrService(Nothing, “Widget – 06/17/2013”)

Calling it in Reporting Services is not much harder. I just added an expression to the TextBox where I wanted the text to appear:

=Code.GetLatestProductOrService(Fields!LastProduct.Value, Fields!LastService.Value)

 

,

Leave a comment

Optionally hiding columns in Reporting Services

One of these things is not like the other. My customer was intensely interested in Inspector Gadget, and had a SSRS report to tell them all about it. So we have the three main characters that were selected as the “Character” parameter, and this parameter was multi-select:

Inspector Gadget
Penny
Brain

There was a column that shouldn’t be visible when only Brain was selected, but should be visible if all characters were selected, or if Inspector Gadget and / or Penny were selected. I don’t know why, I just work here, living in the 80s. To toggle on the visibility, I wrote this little piece of code. If the count of the parameters is three (all characters were selected), then set the “Hidden” property to “False”. Otherwise, determine if “Brain” was one of the selected characters, and if it was, set the “Hidden” property to “True” (hiding the column):

=IIF(Parameters!Characters.Count = 3, False, IIF(Join(Parameters!Characters.Value,”,”).Contains(“Brain”), True, False))

As always, serving your Inspector Gadget / SSRS needs.

Leave a comment

Sorting records from an unsorted source in Reporting Services

HACK ALERT! HACK ALERT!

Don’t admit you know this, don’t talk about it with developer friends, and whatever you do, don’t blog about it – you’ll quickly become a laughing stock among people who know better.

On my beautiful SSRS report, I had a parameter called Users. This parameter was populated via a DataSet which in turn was populated from a web service in the form of a comma-delimited list of users. The source was a jumble, a mess, and not in any kind order. Not good is what I am saying. Because I was working in Reporting Services, my options were limited. So I did what I do – I hacked away until it worked.

My solution was to create a DataSet called “SortedUsers”. It has a single parameter, which was the unsorted list of users I got from the web service.

Parameter definition

Parameter definition

DataSet definition

DataSet definition

I then created a stored procedure that took that nasty list, did a little razzle dazzle by converting it to XML and selecting from this XML using an ORDER clause to get them in the order I wanted:

CREATE PROCEDURE dbo.SortThemThereUsers
@Users VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Delimiter CHAR(1) = ‘,’
DECLARE @Results XML

SELECT @Results = CONVERT(xml,'<root><u>’ + REPLACE(@Users,@Delimiter,'</u><u>’) + ‘</u></root>’)

SELECT [User] = T.c.value(‘.’,’varchar(25)’)
FROM @Results.nodes(‘/root/u’) T(c)
ORDER BY [User] ASC
END

(You can test it by running this bit of SQL: EXEC SortUsers @Users = ‘SinatraF,MartinD,DavisS,LawtonP’)

Finally, I bound the Users parameter to the UserSorted DataSet. Voila, perfectly sorted users from an unsorted source.

Parameter

Parameter

Leave a comment

Dynamically modifying parameter values in SQL Server Reporting Services

Since most of my work involves queries of 80’s TV stars using SQL Server Reporting Services (SSRS), I needed to dynamically modify a Yes / No parameter so that if the country I was searching for wasn’t “Germany”, than they should not have the option to choose David Hasslehoff as their favorite start. Otherwise, “Yes” isn’t an option, because Hasslehoff can’t get no love in those countries

To review:

Is the country selected Germany? Then the user’s choices for a parameter should be “Yes” or “No”. If not, then the only option should only be “No”.

In this example, Countries is a multi-select parameter with a data type of “Text”:

Multi-select parameter

Multi-select parameter

Now I define the DataSet that will be used to populate my Yes / No values. Normally, I would just create a parameter and manually put Yes and No as values, but because I am dynamically changing the values that are displayed in my parameter, I have to use a DataSet.

Because the @Countries is a multi-select parameter, in my DataSet defintion, I use COALESCE and JOIN to smush the parameters together, then use CHARINDEX to see if there is an instance of “Germany” in the string:

DataSet definition

DataSet definition

In case you want to copy and pasted the SQL code, here it is:

SELECT 1 AS ID, ‘Yes’ AS YesNo
WHERE CHARINDEX(‘Germany’,COALESCE(@Countries,”)) > 0
UNION
SELECT 0 AS ID, ‘No’ AS YesNo

I’m ready to define my Yes / No parameter. Here is the main declaration screen. For “Available Values”, I use the DataSet name I created above, “IncludeCountriesThatLoveDavidHasslehoff”, using the value field of “ID” and the label field of “YesNo”. I do the same for the “Default Values” screen, too.

Parameter definition

Parameter definition

I put the parameter that populates countries before the “IncludeCountriesThatLoveDavidHasslehoff” parameter so that when the first parameter is changed, that change can cascade to the second parameter. Now, whenever the “Countries” parameter is selected or changed, this query will determine if Germany is one of the selected countries. If it is, “Yes” magically appears in the list of possible values for “IsDavidHasslehoffTheFavoriteTVStar” parameter.

,

Leave a comment

Out of control cell height in Reporting Services

I was in working Reporting Services and one of my cell kept growing to be humongous. Only one cell, and only if I included the value from a certain table. So imagine you have this setup:

SELECT c.Name, o.OrderId, p.ProductName
FROM Customer c
INNER JOIN Order o
ON o.CustomerId = c.CustomerId
INNER JOIN Product p
ON p.OrderId = p.OrderId

Very advanced stuff here.

I had a basic Tablix, and when I included all the fields, the table containing ProductName grew to prodigious height. Pages were consumed by the evil cell as it tried to take over the world. I tried restricting the size, trimming the data in the report, and trimming the data in the query – nothing worked. But when I excluded that field from the query, everything looked fine.

The problem was my query. If you didn’t notice the error in the query, look at the last line:

ON p.OrderId = p.OrderId

Oops. Once I changed the query to what it should have been:

ON o.OrderId = p.OrderId

All was well – the cell no longer had a height of a scazillion. So if you have an out-of-control cell, make sure your query doesn’t suck like mine did.

Leave a comment

Fixing the “This report requires a default or user-defined” error in SSRS

I was running a Reporting Services report through a WinForms application and I started getting an error when the ReportExecutionService.LoadReport method was called. The error was:

This report requires a default or user-defined value for the report parameter ‘ParkName’.

I looked at my ParkName parameter definition, and here is what I had:

<ReportParameter Name=”ParkName”>
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Select All</Value>
</Values>
</DefaultValue>
<Prompt>Park Name</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ParkNameDataSet</DataSetName>
<ValueField>FullParkName</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

All looked well, but obviously something was not. I looked through similar reports that were not having parameter issues and found that when I pulled the default value from a DataSet, then all was hunky dory. So I modified the parameter definition to pull from a DataSet:

<ReportParameter Name=”ParkName”>
<DataType>String</DataType>
     <DefaultValue>
       <DataSetReference>
       <DataSetName>ParkNameDataSet</DataSetName>
     <ValueField>FullParkName</ValueField>
   </DataSetReference>
</DefaultValue>
<Prompt>Park Name</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ParkNameDataSet</DataSetName>
<ValueField>FullParkName</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

This did the trick. I also modified the query populated the ParkNameDataSet to return “Select All” as the default value.

1 Comment

Reporting Services – HtmlViewer styles not being displayed

There is no shame in it. You make a change to your HtmlViewer.css style sheet, and the changes are not being reflected in Report Manager. It happens to lots of guys. But, help is on the way. Maybe.

This whole situation came about when I made a change to my HtmlViewer stylesheet on my development machine, which worked. When that same change was made to a test server, it didn’t. The style I changed was pretty straight-forward – it just hides the Atom icon in Report Manager:

.ToolbarAtomDataFeed
{
display: none;
}

While poking around, I looked in the rsreportserver.config file, which was located in the “C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer” folder on my machine. I found that in the machine that worked, the “HTMLViewerStyleSheet ” element was there, and the one that didn’t, that same element was missing:

<Configuration>
<HTMLViewerStyleSheet>HtmlViewer</HTMLViewerStyleSheet>
<Dsn>blah</Dsn>
<ConnectionType>Default</ConnectionType>
<LogonUser></LogonUser>

After making this change, the Reporting Services service will need to be restarted.

See your doctor if your style changes don’t last more than four hours.

Leave a comment