Thursday, August 16, 2007

Exporting SQL Services reports to Excel

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.

Wednesday, August 15, 2007

Reports Server subscriptions and Dates

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.

Monday, August 13, 2007

Spiceworks

Due to the fact we are going to get an Audit, we have recently installed Spiceworks on our network. For those who have never heard of it, Spiceworks is a web based appliction that gathers information ion all the devices on your network.

Using WMI it gets hardware and software information for each device, and records it all in a database. We are going to use it for its software auditing purposes, it will give you lists of sortware installed on your network, and you can upload details of your licences into it to keep track of what software you have that is legal. I think it will even warn you if some new software appears that's new or unlicensed.

The thing that makes Spiceworks different from its rivals, is that fact that it is completely free. The only thing that it does do is display advertisements in the browser console. Now, call me daft but i'm quite prepared to put up with an advert (they dont take up much room or distract you much) if quality software like this is free.

Some of the auditing tools we have used in the past haven't been very good, there main problem used to be picking up windows components and listing them as separate software, so you ended up with a huge list that meant nothing. Spiceworks manages to filter all this out into a list that actually makes sense and work with.

The only problem we had was upon running a scan, there were quite a few errors (about 20% of machines). They said something like cannot get authenticated on the client. There is a page you can call up on the client machine that will attempt to diagnose the problem (urlofspiceworks/fix), upon running this we could see it was trying to authenticate on a different machine. A quick look in the reverse DNS lookups told us that the IP address of our problem client was in DNS numerous times with different names.

A quick clean up of DNS and a re-scan in Spiceworks, and virtually all the problems had gone. For small to medium sizes networks, I would recommend downloading it and giving it a try. Get it from www.spiceworks.com