How do I modify a report to show new fields in a stored procedure?

[Former Member]

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 supplieru2019s address below the supplieru2019s 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 hereu2019s 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 u2018Current Data Sourceu2019, then find the same stored procedure in the database in the section u2018Replace withu2019:

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

When I clicked the u2018Updateu2019 button, a u2018Enter Valuesu2019 for parameters window pops up:

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

Iu2019m confused as to what to do at this part. Whether I click u2018OKu2019 or u2018Cancelu2019, it then adds my database connection with stored procedure to the list in the u2018Current Data Sourceu2019 section:

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 u2018Missing parameter valuesu2019 instead of the report:

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

In Field Explorer > Database Fields > Parameters, Iu2019m seeing a list of parameters that arenu2019t the exact ones in the stored procedure, which leads me to believe, they are somehow defined in the report itself. Also, under u2018Parameter Fieldsu2019 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

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 u2018ReportInformationu2019 in u2018Set Datasource Locationu2019, or simply try Database > 'Verify Database' to refresh the datasource, an u2018ADO.NET (XML) windows pops up with a file path in it, something to the effect of:
MyLoanerLaptop\ebo bk\orderform1.xml:

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 Iu2019m thinking that the software companyu2019s 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 Iu2019m 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

Edited by: Mike_DBA on Jan 23, 2012 8:03 PM

Edited by: Mike_DBA on Jan 23, 2012 8:05 PM

Edited by: Mike_DBA on Jan 23, 2012 8:11 PM (2:12 pm EST)

Edited by: Mike_DBA on Jan 23, 2012 8:13 PM

Edited by: Mike_DBA on Jan 23, 2012 8:18 PM

Edited by: Mike_DBA on Jan 23, 2012 8:30 PM

Edited by: Mike_DBA on Jan 23, 2012 10:58 PM

[former_member11696]

Hi Mike,

CRSE is an OEM Product and for use with a custom application they have written. Unfortunately we don't their reports or how they are connecting, it would appear they were originally built off a SQL and then set to a Dataset in the app or they could be adding the data source at runtime. Lots of variations and without access to the source code to see what they are doing not much we can help you with.

You need to contact the OEM Partner for help in resolving this and find out what you can and need to do to update your SP and work with their canned reports,

Thank you

Don

[Former Member]

Hi Don,

I completely understand what youu2019re saying and thought that might just be the case. I will contact our support person who works for the software vendor and has a great deal of knowledge of the product. Hopefully he can point me in the right direction. Otherwise, Iu2019m afraid we would have to weigh the costs vs. benefits for a customization. The requested addition was only coming from one vendor who wanted their address displayed but it just would be nice to add these abilities to our repertoire.

I really appreciate your valuable input on this!

Mike