I am in the process of writing a reporting service that will be used across our whole company. The problem I have is there is not one central database all the reports will run of, each division has its own separate one. Lucky the database schema’s are consistent, but I needed a way of:

a) setting the report data source at run time and
b) letting the users control the database they want to connect to. Some high level user need to be able to access multiple databases, while others will just need access to their local one

So, here’s how I went about doing it.

Setting Data source at Run time

Normally, when you setup a reports data source in reporting services, you just point it to the database you want it to use (probably using a shared data source) give it some credentials and off you go. You would probably end up with a connection string looking something like this:

Under normal circumstances that would work fine. But not this time. I need an easy way to replace the information held in the connection string with some sort of variable.

Turns out the easiest way to do it is by using a parameter in the connection string. You can create the connection string as an expression as if you were working with any other part of the report. However, before doing this you first need to create the parameters and fill them with some data.

Data Source Table

In the reports database, I created two new tables, one that will hold the name of the reports servers, database names and Active directory group with access to this data source. The other one will contain all the user names and what Database they are currently using. Download the SQL scrip to create the databases below

hello

Run this script on your reports sever database, and you will have two new tables, database_details and user_details. In order to do the tests you will need to create an entry in the database table (database_details) for your database, and an entry in the user table (user_details) for your username (include the domain name so it should read dom\joe.bloggs) and point it using the site_id filed to the database. You are now ready to create the SQL in the report that will populate the parameters

Dataset Containing the database connection details

Create yourself a test report, and in that report create a new dataset that points at your reports server database. The SQL needs to be the following:

SELECT     jp_user_details.username, jp_database_details.server_name, jp_database_details.database_name
FROM         jp_user_details INNER JOIN
jp_database_details ON jp_user_details.site_id = jp_database_details.site_id

This will return all the names in your database, what we need to do now is make sure at run time it only returns data for the user running the report. To do that we need to put a filter on the report. The filter will be:

Fields!username.value=User!UserID

and should look like this in your Dataset properties

The Parameters

Once you have done this dataset, you need to create a couple of parameters. One will be for the server name, one for the database name. So go into your report parameters and create the following, with these options:

Name – ServerName
Datatype - String
Hidden - Yes
Available Values from Query: Select your dataset , then both values fields need to be server_name
Default Values From Query – Select your dataset, value field is server_name

Name – DatabaseName
Datatype - String
Hidden - Yes
Available Values from Query: Select your dataset , then both values fields need to be database_name
Default Values From Query – Select your dataset, value field is database_name

What does this do? Well, when you run your report now you will have the two parameters you have just created filled in with the server name and database name the person running the report wants to report on. The parameters are set to hidden so the user knows nothing about them.

Final Step, create your dynamic dataset

Now we have everything we need in place, the last step is to create a dataset that will look into the server held agaist the user, and retrive us the actul information we want. Create a new dataset, and in the connection string of this data source put the following:

= “data source=” & Parameters!ServerName.Value & “;initial catalog=” &  Parameters!DatabaseName.Value

It should look like this in the window

Now when you run the report, you data source will be based on the users choice held in the table

A couple of things to note

You will find it hard (probably impossable) to write the report with the parameterized data source. You’ll need to work with your local copy of the database hardcodded into the connection string first, then when you are happy with it change it to the parameter driven one. If you dont do this the report designer wont be able to read the dataset.

You also need to be carful the order in witch you put your user paramters on the report. I found that if i did’nt put data driven parameters (ie a list of sales areas the user can select) at the top of the list, they were greyed out when you tried to run the report. Not sure why, but i asume its down the the order the datasets are exceuted in

If you have any problems with this, or have any different ways of doing it let me know!

I’ve been in the situation a few times with ASP.Net applications where someone says to you ‘I got this error on screen when I did x, I don’t know what it said but i couldn’t work.’

Odds are you have got an unhanded exception in your web application, but how are you meant to debug it when the error has long gone? Elmah (error logging and error handling modules for asp.net) may well be your saviour. Download it out now from http://code.google.com/p/elmah/

So, what does it do?

Well, once you have it configured on your application or server, every time something causes an unhanded exception it will log it into a database, send you an email alert and fix the code for you (last part was a joke!). So, now when the user tells you they have had an problem you can look in the system and see exactly what happened.

The application is open source, and is based on the article at http://msdn.microsoft.com/en-us/library/aa479332.aspx, check that article out if you want to know more about how it was written

Installing it

You can run it in two ways, on a particular web application or at the server level. As I was a bit confused at first as to how to set it up, here is a quick guide to installing it for an individual application and log the errors onto a SQL 2000 server.

1. Download the zip file from the website, if you are using dotnet 2.0 you will need the latest beta version.
2. Extract the files, in the src/Elmah/ folder find the Database.sql script. This is the script that will build the tables it needs. Create a new database and run this script on the database
3. Next, go into the bin/net-2.0/Release/ folder, and copy the Elmah.dll and Elmah.xml files into you applications /bin folder
4. Once you have the assembly, you need to configure your application to use it. You do this by adding some sections into your applications web.config file. In the /samples folder there is an example web.config file you can get all the information out of to put in your file. For the most part it is easy to follow, but I had a couple of problems.
5. The first one was the database connection, make sure you add a new connection to your <connectionStrings> section that points at the database you have created.
6. You then need to point to this connection in the connectionStringName section of the elmah errorLog.
7. Once you have added the other sections into your config file you should just be able to browse to your site /elmah.axd to see your errors

You can also configure options such as getting it to send an email when an exception occurs, subscribe to the log as an rss feed etc.

So now you should have a handy reference point for all your exemptions. You can tell the user you are looking into there problem before they even report it to you!

I’ve been doing a bit of tweaking on a SQL Reporting Services install this week, trying to get it to run a bit better, get better logs out of it and generally try and improve its performance. So, what did I do?

Reports Timing out – This is a pain. I have a couple of reports that people run huge queries for, and sometimes the reports can time out while running. This was a fairly easy fix, just locate your rsreportserver.config file (mine was in (Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer). Open it up and alter the key:

DatabaseQueryTimeout- This is the amount of time the reports server will wait until it thinks the query to the server has timed out. You can set it to 0 (zero) for no timeout, but this is probably not a good idea. There is another key in there I thought might also help called SQLCommandTimeoutSeconds but according to the MS docs this is not used (so why the hell is it in there?)

Better Client Side Errors – Fault finding errors in the report viewer can be a bit painful, but you can enable remote errors to help you out a bit.
There  is a table in the reports server database called ConfigurationInfo. In here there is a EnableRemoteErrors property. If you set this to true and restart the reporting service clients will get better errors, helping you try and work out what is actually happening.

My next step with errors is to try and handle them properly in the reports. There is a ReportError event witch I think will do the trick. When I have worked out the best way to use it i will post about it here

Logs – As well as writing to the application event log, reporting services also write more detailed information into its own log files. These (along with crash dump files) can be found in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles.

Using all this info I now find it easier to troubleshoot and tweek my environment. If anyone else has any handy hints let me know and ill add them to this post!

Hot on the heels of the last release of Spiceworks 2.0, they are already getting version 3.0 ready. The preview page is up now at http://www.spiceworks.com/3.0/preview/

They are not giving to much away at the moment but it looks to have some cool new features such as active directory integration (to be fair that should have been in from the start) and monitoring of exchange servers (that looks really interesting).

As i did when version 2 came out I will do some tests and compare how it performs against previous versions, hopefully its network scanning and device identification has got even better.

Also due out soon is the new version of Ubuntu (8.04), i keep meaning to try and use Linux more when i’m at home. Hopefully this will be the version that prompts me to do it. I’ve had problems with encryption on my Wireless network card before, I’m hoping this will cure it.