Reporting Services - SQL Server 2005 - optional parameters

by Roland Schumacher alias GENiALi 1. Oktober 2008 -- 268 words -- 1 mal gelesen

I can create with the reporting service from the SQL Server 2005 reports. This means I have create by my colleague. :-)

We have 7 fields where you can enter values in a Web form. By using these fields to then generate the report. But not all fields must be populated with values. You may also blank. This caused some problems.

The reporting server set the parameters that have no value to NULL. In the query parameter @ BRK = NULL was then set. The results of course always leads to 0.

The solution lies on a time to wait. But I did find one then randomly.

WHERE
(Status.SKey = @ SSK OR @ SSK IS NULL)AND
(KvpLocation.KvpLocationKey = @ KLK OR @ KLK IS NULL)

Most importantly, first check for a value. Check until after the OR to NULL. If it is different rum quite weird errors may occur. Then it can cause SKey allegedly is a GUID and you should please pass a valid value.

If you comply with the order it should run.

Oh, not needed must from the URL call out.

This does not:
http://SERVER/reportserver$development?%2fKVP%2FeinfacheListe ADat=01.01.1900%2000:00:00 BRK = & ASN = & SSK = KAPAU & & KTK % = 2 & PN = & KLK = rs 3aClearSession = true & rs % 3aFormat = PDF & rs % 3aCommand = render & rc % 3aToolbar = false

If you off the Red parameters, which are so empty, then the query works. Otherwise, you will find always 0 of records.

In any case with us.

Or is it easier?