Archive for April, 2013

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

Optional validation for MVC fields

I am creating a new MVC site, because that is what the cool kids are doing these days. On my entry page, all the fields were required, and I had a couple of them that I didn’t want to be required. So I went into my view and found the fields:

<div>
@Html.LabelFor(model => model.Latitude)
</div>
<div>
@Html.EditorFor(model => model.Latitude)
@Html.ValidationMessageFor(model => model.Latitude)
</div>

<div>
@Html.LabelFor(model => model.Longitude)
</div>
<div>
@Html.EditorFor(model => model.Longitude)
@Html.ValidationMessageFor(model => model.Longitude)
</div>

Easy, I’ll just remove the Html.ValidationMessageFor control, and it will be fine. Nope, that didn’t work – the validation message disappeared, but the page wouldn’t let me save. I also tried eating Cheez-Its, because they always make me feel better. While they did make me feel better, I was still having a problem.

What I found that did work was making the fields in question nullable. I went into my model and found the fields I wanted to be optional:

public double Latitude { get; set; }
public double Longitude { get; set; }

Through the magic of question marks, I made them nullable:

public double? Latitude { get; set; }
public double? Longitude { get; set; }

And there I was, with two fields that were no longer required. If there is a better / easier / different way, add a comment and let me know.

,

Leave a comment