CAML queries, SSRS, and datetime fields

I’ve been using Enesys Software’s product RS Data Extension to query data out of SharePoint 2007 lists in SQL Server (2005) Reporting Services.  One quirk with the queries is that they use CAML and the dates must be in a particular format, ISO8601.

I wanted users to use the standard date-picker, but there is no way to format the date inside the query.  My work-around is a classic Reporting Services pattern – create an internal parameter that does the formatting for you.  The format string for VBScript is “yyyy-MM-ddTHH:mm:ss”.

Below is a screenshot of the Report Parameters dialog. I used the following code to just grab today’s date (no time).

     =CDate(CDate(Now()).ToString("MM/dd/yyyy"))

For the second parameter, I create an internal parameter that is dependent on the first.  This will prevent it from showing on the report and it will not be filled in until the start date is chosen.  Default value:

     =CDate(Parameters!StartDate.Value).ToString("yyyy-MM-ddTHH:mm:ss")

I created a test report to show how this works.  Here is the report definition:

Below is the report preview:  You can now use the parameter in your CAML query.

One other note on CAML date fields.  The time is ignored by default.  If you want to include the time, you must specify another attribute.  Example:

<Geq>

<FieldRef Name="LastModified" />

<Value Type="DateTime" IncludeTimeValue="TRUE">@LastModified!</Value>

</Geq>

I found a helpful post on CAML & DateTime that might be useful for you too.

SharePoint “User not found” error with Reporting Services in integrated mode

I encountered an error the other day while trying to get a SharePoint 2007 (WSS 3.0) site running with SQL Server Reporting Services 2005 in integrated mode.  While trying to access any report .RDL files inside a document library, I got the “User not found” error from SharePoint.  After looking in the logs, I could not find anything.  What I did note, was that the development site worked, but the production site did not.

One difference between them – the names of the Application Pool that SharePoint was running under.  In development it was just “spsite” but in production it was “www.spsite.com”.

I renamed the application pool and *poof*, the site began to work.  I don’t understand the mechanism, but just thought I’d post in case someone else out there encounters it.

UPDATE: I’ve blogged about another reason why you might get the “User not found” error and possible solutions.

How to enable the XPS viewer for Windows Server 2008 when using Firefox

When you are running Firefox as the default browser in either Windows Server 2008 & Vista, it is impossible to view XPS files without some work. Windows opts to always open XPS files in a browser. Firefox doesn’t know how to handle XPS, so it opts to open them with the registered app: XPSViewer, which happens to open the file in a browser. Thus, you get a never-ending loop.

I searched for some solutions and came up with two that were pretty slick. I really would like a standalone lightweight XPS viewer, but they don’t seem to exist.

Solution 1: Use the IETab extension

Instructions from http://channel9.msdn.com/ShowPost.aspx?PostID=259840
?

Install the IETab extension from http://ietab.mozdev.org/

Add a filter for xps extensions or modify the mht filter to read:

/^file:\/\/\/.*\.(mht|mhtml|xps)$/

Presto – XPS docs open in an IE tab within Firefox.

Solution 2: Associate the .XPS extension with Internet Explorer

Hold down shift, right-click on an XPS file and choose “Open With…”. The default should be “XPS Viewer”. Select Internet Explorer and then check the box “Always use the selected program to open this kind of file”.

For some reason this doesn’t seem as elegant as solution 1, but it gets the job done without having to install any additional plug-ins.

How to add new real-time blacklists (RBLs) to Endian Firewall 2.1.2

One of the great things about Endian Firewall is the ability to use real-time blacklists (RBLs) to keep spammers off your mail server.

Endian Firewall 2.1.2 ships with a number of good RBLs. I’ve seen good results with Spamcop and the Spamhaus Zen list.

I wanted to add the Passive Spam Block List, but it is not an option in the Endian UI. Here is how I did it:

1. Log in to the shell as root. You can do this via SSH or by logging into the console.

2. Move to the /var/efw/smtpd/default directory

3. Edit the file RBL. I would not recommend removing any entries, only adding new ones. I don’t know how Endian works behind the scenes and wouldn’t want to break it.

The format is pretty simple:

RBL url|RBL name|<IP or DOMAIN>|Link url|

I’m not totally positive about the RBL url coming first or the name coming first. It is safest just to use the same value for both fields.

So an entry for the psbl.surriel.com looks like:

psbl.surriel.com|psbl.surriel.com|IP|http://psbl.surriel.com

After I edited the file RBL, it showed up the next time I went to the Endian administration site.

Here is a screenshot of the newly added real-time blacklist in the Endian Firewall UI:

I chose the Passive Spam Block list because of the excellent review it received.

The review site seems to be a good resource for evaluating the usefulness of RBLs.