Just a quick blog about something i’ve noticed over the last couple of weeks. Have you ever tried exporting your reports to Excel spreadsheets, and got unpredictable results?

You know what its like, most accounts type people insist on working Excel (even though you could just change the report to suit them), but sometimes when you export the report to excel extra columns appear. Then, you can export the same report again and the extra columns you had before will no longer be there. The problem accounts people have with the extra columns is it stuffs up any sorting they want to do, and any macros they have written that depend on specific data being in specific places.

The best way to get round this seams to be to remove all the dynamic text boxes etc from the headers and footers of the report. I think what must happen is if the info in the headers doesn’t align exactly with the tables in the main section of the report, it throws off the export.

I’ve now got two copies of the reports that the accounts people tend to use, one with the headers and footers still in, and one they can use to export into excel.

Ahh, the joys of SQL reporting services. While it is a very powerful piece of software, it has some obvious (in my opinion) features missing.

Problem: User wants to schedule a report to run, the dates need to move on every time it runs but the different parameters mean that they dont move in a consistent way, ie on the same report they want to run for one customer for Monday to Wednesday, for another they want just one day etc.

The subscriptions in SQL server reporting services lack the ability to move dates on within the subscription. You can run it for the default, or specify specific dates. When you want to run the same reports for across different ranges for different customer this is not very helpful, so we came up with a clever solution.

Within the sql on the report, instead of the sql reading something like:

startDate >= @par_StartDate AND EndDate <= @par_EndDate

We have create two parameters, one called par_StartOffSet and one called par_EndOffset both being integer data types. We then change our SQL to look like this:

startDate >= CONVERT (datetime, DATEADD(dd, CAST(@par_StartOffset AS int), DATEDIFF(dd, 0, GETDATE())), 103) AND
endDate <= CONVERT (datetime, DATEADD(dd, CAST(@par_EndOffset AS int), DATEDIFF(dd, 0, GETDATE())), 103)

So, what does this allow us to do. Well, the SQL we have now always start from the current date, it then takes our parameter and adds/subtracts this from the date today. We do this on both the start and end date.

This means that now, when we create a subscription we dont have any date parameters, just two offset ones. So, if you scheduled your report to run on a Friday, and wanted it to run for Monday to Wednesday of the current week you would set the startDate param to be -4, and you endDate param to be -2. Now with the same report you can make it run for any date ranges you can think of!

Just a quick side note, you might notice in the sql we are specifically casting the 2 params to be ints. Even though we told the parameter to be an integer in the parameters menu, i got this error when trying to run the report

I’m not sure if this is a bug, but in the IDE (I cant imagine it is), but I dont see why I specifically cast the parameter when it’s already an int?

That tripped me up for a bit but I guess the fix is kind of obvious.

Found out a very useful tip (thanks to Bob at Microsoft – http://blogs.msdn.com/bobmeyers/) that allows you to add a heading to a column in a SQL reporting services Matrix.

You simply need to add a new column group to the top of your Matrix, and set its expression to be something like ‘DummyExpression‘, and make sure you omit the normal leading ‘=’, like below:

Now, you can move the group to be the top one in the properties of the Matrix, and type in the text box what ever header you need.

Simple but effective, I didn’t even realise you could put dummy data into an expression, I thought the IDE would kick it back

On an entirely different note, I was very excited(being the big geek I am) to discover today that Buffy the Vampire slayer season 8 is coming back in comic book form, and is written by no less than Joss Whedon himself!
Check it out at Dark Horse comics here http://www.darkhorse.com/
I cant wait till my next visit to the comic shop!

Listening to: Saves the Day, Stay what you are.

I’ve had a problem with inserting sub reports into a cell of a table. Every time I inserted the sub report into a cell. The size of the report ballooned out of control and i couldn’t work out why.

Well i’ve found out, and its kind of obvious really, the size of the sub report was too large! I kept thinking it was the size of the filed on the sub report, but then i finally realized the entire report was being inserted not just the filed.

I cant believe I didn’t sus that one out right away!