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.