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

building reports crystal reports
1

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’:

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

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:

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:

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:

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: \MyLoanerLaptop\ebo bk\orderform1.xml:

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. I have a feeling that the way this company put together this report is not the easiest way to do so. So, hopefully you understand my dilemma here.

Thank you,
Mike

DBA_Mike (BOB member since 2012-01-23)

2

In just looking at the first couple of pictures, it appears that the stored procedure OrderForm1 has a parameter. Just to keep Crystal happy, a value needs to be entered for that parameter.

I hope this helps.

kevlray :us: (BOB member since 2010-06-23)

3

Kevlray is correct. When you try to open the database, the stored procedure wants to execute in order to retrieve the output of the procedure to extract the right fields. You should enter valid parameters for the stored procedure to execute and produce some output.

But if your stored procedure is changed but has the same name, and only some new fields, you should also be able to use the option “verify database”. This checks the stored procedure and updates the report accordingly. The new field should be available to drag into your report.

The thing with the parameters that aren’t in your stored procedure should not matter, they remain unchanged. However I do not get this:
Field Explorer > Database Fields > Parameters
The parameters are right next to the fields, as are the formulas, and they should not be in the database fields. Or is there a database connection called parameters?

martrijkers :netherlands: (BOB member since 2012-01-24)

4

Kevlray & martrijkers,

I think I understand what you’re saying about inputting a valid set of parameters when changing datasources. However, without seeing where the parameters are coming from, such as from tables in the db that I can go find a set for, it doesn’t seem easy. This ties a bit into martrijkers’ question about the ‘Parameters’ listing under ‘Database Fields’. Yes, if you look at the screenshot of it, you can see that it does indeed look like a data table they’ve named ‘Parameters’. The frustrating part about it is, is that I can’t find any of those parameters, such as clientID, currency, etc. in any of the stored procedures. Even if I captured the temp table the SPs create as a real table and looked at the data, I doubt it would point to where, exactly it’s getting it from.

I’m understanding from another forum I posted my challenge, 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.

I’ll contact our support person on the inside who works with me a lot and has a lot of knowledge of the product. I’ll also look thoroughly through the documentation at any information they’ve provided on customizations. Most of all, I’ll keep at it a little while longer, knowing that you never know how close you were until you succeed.

I appreciate all the help!

Mike

DBA_Mike (BOB member since 2012-01-23)

5

Oh that opens some passive memory :stuck_out_tongue: When you select a data source there is an option to enter your own selection from that data source (I’m using a dutch CR, it’s called “Opdracht toevoegen”). I do not think you can change that, but you can enter your own query which is nowhere in the database. Could it be anything like that?

martrijkers :netherlands: (BOB member since 2012-01-24)

6

Hi Crystal Report Experts,

I have a crystal report based on stored proc which has date parameters. Now as we all know, these SP parameters show up as prompts in Crystal report. I want to schedule this report in info-view without changing Stored proc parameters. Is there a way, I can pass values for today and yesterday to these date prompts for scheduling purposes?

Thanks in advance,
Raj

Rajasopa (BOB member since 2007-08-22)

7

Not that I am aware of. But in the select expert you could have formula (it has been a while, so the syntax might be off). But then a user could never enter a date.
{date_field} >= CurrentDate-2 and {date_field} <= CurrentDate-1

You also probably could use the datediff function.

kevlray :us: (BOB member since 2010-06-23)

8

If I am not understanding wrong then this is to create static condition at report level but Stored proc prompts will still show up, right? then how we will fill those prompts while scheduling via info-view?

Rajasopa (BOB member since 2007-08-22)

9

I think the prompts would have to be removed from the Stored proc (if that is possible), otherwise I would need to think this over and try some things.

kevlray :us: (BOB member since 2010-06-23)

10

Yea, we don’t want to change stored proc as some other application using same stored proc. and there are many thats why we are thinking if we can do anything at report level.

Rajasopa (BOB member since 2007-08-22)