I finally got around to seeing the movie Adaptation. For those of you who didn’t get to see it, it’s a movie by Charlie Kaufman, the writer of Being John Malkovich. I’ve wanted to see it since it came out but never got around to it. Without giving much of the movie away, it’s sort of a “movie within a movie” where you will want to watch it again to see the things you missed the first time.
Anyway, this movie reminds me a little of one of the reports that I did during the BI Power Hour session at TechEd on Wednesday (see Sean Kaye’s description of the session). I promised some folks I would post the reports from both demos here.
The report is an implemention of Report Manager inside of a report. It uses the new XML Data Provider that makes it’s debut in the June CTP of SQL Server 2005 Reporting Services. Here is the RDL source.
If you load it into the designer, you will see that the connection string of the query is http://localhost/reportserver/reportservice2005.asmx (the URL of the Reporting Services 2005 management endpoint). The content of the query itself is calls the SOAP method ListChildren. The ListChildren method requires one parameter, the item path. I bound this to a report parameter called Item, which I set to hidden and defaulted to “/”.
The report itself is pretty simple, just the of the item, the type, and the modified date. I then added some interactivity in that if you click on the name of a folder, I wanted to show the contents of the folder while if you clicked on the report, I wanted to launch the report. Since you can’t have both a drillthrough and a URL action on the same item, I had to create a second detail row with the same information and then conditionally hide each row with an expression, either:
=IIF(Fields!CatalogItem_Type.Value <> “Folder”, True, False)
=IIF(Fields!CatalogItem_Type.Value <> “Report”, True, False)
Then I added the drillthrough action on the name of the item for folders and a URL action on the name of the report with a expression of:
=Globals!ReportServerUrl + “?” + Fields!CatalogItem_Path.Value
on the report. I then published this to the server and ran it. I could browse the folders and click on a report to view it. Just for fun, I took it one step further and used URL access and the XML rendering extension to output the contents of this report in XML. I then built a second report using the XML data extension that looked like the first. Fun, eh?