Print Page | Close Window

How do I modify a report to show new SP fields?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15429
Printed Date: 21 Apr 2025 at 8:08pm


Topic: How do I modify a report to show new SP fields?
Posted By: DBA_Mike
Subject: How do I modify a report to show new SP fields?
Date Posted: 23 Jan 2012 at 8:59am

The server uses Crystal Reports Embedded Server 2008 SP3 (running on Windows Server 2008 R2 / IIS 7.5), and I am using Crystal Reports Developer 2008 SP3 on my PC.

Hello,

I am a MSSQL database administrator with little experience with Crystal Reports. At my job, we have a need to add a couple of fields to a report. The report comes with an enterprise procurement web-based application and displays the details of a given purchase order. They want me to add the supplier’s address below the supplier’s name on the report. I was hoping there might be a fairly straight-forward, not overly complicated answer to my question.

The report works with a stored procedure (OrderForm1) which creates a temp table called #ReportParameters. It then executes another stored procedure (GetReportParameters), in which it uses 'sp_xml_preparedocument' and eventually 'sp_xml_removedocument'. It then executes a stored procedure called (OrderForm1Main) which contains the select statement that brings in the bulk of the fields and joining the pertinent tables. I made a backup copy of OrderForm1Main and modified the original, to include the supplier address fields.

Don't know if I'm on the right track, but here’s what I tried so far:

When I open the OrderForm1.rpt in Crystal Designer, I have found that I can go to Database > Set Datasource Location, create a connection (OLE DB (ADO)) to the database, highlight the name of the stored procedure under ‘Current Data Source’, then find the same stored procedure in the database in the section ‘Replace with’:

 

http://i.imgur.com/KXBuf.png - http://i.imgur.com/KXBuf.png

 

 When I clicked the ‘Update’ button, a ‘Enter Values’ for parameters window pops up:

 

http://i.imgur.com/5uQ9V.png - http://i.imgur.com/5uQ9V.png

 

 I’m confused as to what to do at this part.  Whether I click ‘OK’ or ‘Cancel’, it then adds my database connection with stored procedure to the list in the ‘Current Data Source’ section:

 

http://i.imgur.com/aD4KH.png - http://i.imgur.com/aD4KH.png

 

After completing this data source change, in the Field Explorer under Database Fields > OrderForm1, it then includes the new fields I added to the stored procedure. However, when dragging the new supplier address fields to the report layout, saving the report in this state, and trying to run it in the web application, the report window displays the message ‘Missing parameter values’ instead of the report:

 

http://i.imgur.com/gz8S3.png - http://i.imgur.com/gz8S3.png

 

In Field Explorer > Database Fields > Parameters, I’m seeing a list of parameters that aren’t the exact ones in the stored procedure, which leads me to believe, they are somehow defined in the report itself. Also, under ‘Parameter Fields’ in the Fields Explorer there is the @fParameters listing with a question mark icon, that appeared since the data source change:

 

http://i.imgur.com/llkrk.png - http://i.imgur.com/llkrk.png

 

There does not seem to be an equivalent set of parameters in the database, as there were defined in the report. I imagine that the actual parameter values come from the data held in the currently displayed purchase order (from which you can push a button 'Print PO' that will display the report in question). I have read that when you change a data source in Crystal, you have to complete the task of resetting, or adding back in the parameters. That would be easy if they were in the database somewhere. Whenever I attempt to look at the properties of the ‘ReportInformation’ in ‘Set Datasource Location’, or simply try Database > 'Verify Database' to refresh the datasource, an ‘ADO.NET (XML) windows pops up with a file path in it, something to the effect of: file://myloanerlaptop/ebo%20bk/orderform1.xml - \\MyLoanerLaptop\ebo bk\orderform1.xml :

 

http://i.imgur.com/SEVOE.png - http://i.imgur.com/SEVOE.png

 

I did a search on the entire server for the file OrderForm1.XML and could not find it anywhere. So I’m thinking that the software company’s development team used that file originally, to create the report (did they use a dataset to do this, or is the temporary XML file created during the process actually the schema here?). I think I’m basically trying to change the data source in the report, to an updated version of the original stored procedure referenced, while still somehow leaving the parameters list in the report alone and have them still work?

 

Any and all help would be greatly appreciated. I realize that this type of work most likely is routine stuff that can be learned by taking the time to do so. My team is only interested in allowing me a certain amount of time and resources to delve into Crystal and we do not have a Crystal dev team. So, hopefully you understand my dilemma here.

 

Thank you,

Mike

 


Replies:
Posted By: asam
Date Posted: 25 Jan 2012 at 4:39am
When you click that "update" button, Crystal attempts to execute the "datasource" which you have defined as a sproc.  Cyrstal finds that the sproc requires a parameter and Crystal has probably added that parameter to the list in Field Explorer > Database Fields > Parameters.  I have not found a system sproc named 'sp_xml_preparedocument' and don't know what the other sproc's do but somewhere in there something is asking for a parameter.  Can you run the sproc's in sqlserver and get a dataset?

Posted By: dbodell
Date Posted: 25 Jan 2012 at 6:40am
Have you simply tried "Verify Database"? I do quite a bit of work with stored procedures in crystal and usually this is all it takes. Just make sure that your stored procedure is not returning multiple datasets. You can dump data into temp tables all you want but the final outcome has to be a select statement. If I have a lot of data, I create an overnight job, dump the data into a table and then read that table from Crystal. Otherwise, I just create the stored procedure and make sure that the last thing it does is present the data for the report. Verify Database goes out and retrieves the specifications for the sp again and determines if anything has changed. If so, it fixes up the data to include the changes.

-------------
Thanks,
D. Bodell

Posted By: DBA_Mike
Date Posted: 26 Jan 2012 at 5:47am
Hi D.Bodel,

Yes, I did try ‘Verfify Database’. When doing so, the ADO.NET (XML) connection window pops up. At this point, I’m thinking they somehow used the .xml file as the input for the parameters? The reason I say that is this:

‘sp_xml_preparedocument’, while I don’t find it in any of the system or application database stored procedures, it is recognized by MSSQL. MSDN (Transact-SQL) definition: “Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption”. So, it loads the data into memory, then ‘sp_xml_removedocuemt’ should be used to free up the memory used, once the XML has been parsed.

I was able to set the temporary table to a physical table and was able to capture the results, which were populated into the table when the report was run. The result set seems to be related to the one purchase order I had displayed in the application web console and for which I initiated the ‘Print PO’ report from:

http://imgur.com/wuvmI - http://imgur.com/wuvmI

In one of the stored procedures referenced, I found that the @fParameters that comes into the report under ‘Parameter Fields’ is listed in the stored procedure as:

@fParameters NTEXT = '<report><parameters></parameters></report>'

So, that’s when I started thinking I could possibly enter in a string into the ‘Enter Values’ parameter input window when prompted. First I tried just entering in the XML string above (into the parameters prompt window). While I did not receive the numerous Crystal pop-up errors I did before when entering in the wrong parameters, still the ADO.NET (XML) window popped up again and when saving the report and trying to run it, still the missing parameter values error on web application report display.

To go the extra mile necessary, I’m thinking that either:
A)     A full, XML parsed, properly formed string (including tags and data), must be passed into the parameters prompt. For which case, I’ve been trying to somehow use ‘sp_xml_preparedocument’ to create that string, to no avail.
B)     Somehow use ‘sp_xml_preparedocument’ (or other method), to get the proper string into an XML file, so that when the ADO.NET (XML) window pops up asking for that ‘orderform1.xml’ file, I could point to the one I created.

I’m no expert, but I don’t think they used a data set, because I’m under the impression that for those, they would be using a .xsd file, not .xml

I’ve gotten some responses on other Crystal forums which indicate that the embedded server is OEM, there are a lot of variables here, such as if they are adding the datasource at runtime and/or to a dataset. So, without access to the source code, it might be near impossible to reverse engineer. It was suggested that we contact a partner or someone, who could reveal how to add the desired fields. Otherwise, I’m afraid we would have to pony up the money for a customization. And for something like this, it’s only one vendor that has requested the address be added to our POs, so costs vs. benefits would have to be weighed. It would just be nice to add these abilities to our repertoire.

UPDATE: I did send an email to our support person and put in a support call asking for that type of help. Because in their documentation, it outlines how to add a stored procedure and update it using ‘verify database’, however there is no mention that the ADO.NET (XML) source would come into play (it was on as many of their canned reports as I’ve actually checked in designer). So we’ll see what they say about us doing our own customizations and getting them to work.

I’m taking the time to put all this information out in the forums, because you just never know how close you actually were to victory until you find the answer down the road. So, if I keep giving details to people who have experience with these dark corners of Crystal (with XML, stored procs and such), maybe we can crack it (so-to-speak) and the information might even help someone else down the road who might be struggling with it.


Again, I appreciate any and all help!

Mike

Posted By: dbodell
Date Posted: 26 Jan 2012 at 6:02am
Oh geez, I wish it had been a lot simpler! Sorry I couldn't help. I haven't worked with any XML with Crystal at all. Good luck!

-------------
Thanks,
D. Bodell


Print Page | Close Window