anon.ms anon ms it guy: more than break-fix

Modifying a Crystal Report

Howdy,

Client of mine wanted me to modify a Crystal Report and the only thing I had was the .RPT file.

I started modifying the report in Visual Studio 2005 and everything was fine until I had to add new database fields.

Unfortunately I did not have access to the database from my machine. I ended up forwarding port 1433 to his SQL Server and allowing SQL Server Authentication. He was only using Windows Authentication.

I needed to create an XSD schema so that I could modify the fields that the report could access.

I found a neat SQL Server query to do this.

Declare @schema xml
Set @schema = (SELECT Top 1 * From dbo.Table FOR XML AUTO, ELEMENTS, XMLSCHEMA(TableBlahRootElement))
select @schema

This returned the appropriate XSD file and I then used it to modify the report. (I removed all the data from the XSD as it was not required).

I did this multiple times for all the different tables/queries and adjusted the XSD appropriately.

As most of the Report tables were in fact queries, I had to change my SQL Query to:

Declare @schema xml
Set @schema = SELECT * FROM (SELECT * From dbo.Table) MyQuery FOR XML AUTO, ELEMENTS, XMLSCHEMA(TableBlahRootElement))
select @schema

I had to log off, then log on to the "database" in Visual Studio, and select the XSD file. I then ran a Verify Database and my fields were viewable. Nice.

anon ms.