Steve Hutchins wrote:The crystal report use a stored procedure for the data and I've tried to link the debtor specific field to the stored procedure and onto the report.
Obviously I'm doing something wrong as I can get it to show the debtor specific part number.
Hi Steve,
If I understand your question correctly, you've managed to modify the stored procedure to include your new field, but Crystal Reports does not see it?
I've encountered this many times - Crystal is somewhat stubborn / senile / recalcitrant when it comes to recognising new fields in a stored procedure - but it can be done!
I'll update this post with some step by step instructions with screenshots to show how to achieve what you need - so standby!
Mike
EDIT: Here are the steps:
NOTE: The image preview on these forums may not display the entirety of images - click on any images to see the full imageStep 1. Firstly, you should not modify our stored procedure, but make your own from a copy of ours. This is recommended because whenever Jiwa is updated we will re-create all our stored procedures - so any changes you make will be lost. The same applies to the report file - copy that and upload it with renamed file name via the Reports form.
Doing so is quite simple, if Management Studio, just right click on our stored procedure and select "Modify" - you'll see something like this:
- Step1-Modify.PNG (9.25 KiB) Viewed 12970 times
Now, edit the code and change the ALTER to a CREATE and name your stored procedure to be identifiable an unique. In this example, the standard stored procedure is named "usp_Jiwa_Invoices_Invoice", so I'm going name my modified stored procedure to be "usp_Jiwa_MYINVOICE_Invoice"
- Step1.1-AlterAndRename.PNG (8.4 KiB) Viewed 12970 times
You will also (obviously) need to modify the stored procedure to output your new field - in this example I just added a field named "MyField".
Run your script to create your new stored procedure.
You will also need to grant permissions - run the following command within management studio:
grant_all_user_tables
Step 2. Set Datasource Location. Open the report to edit, and choose "Set Datasource Location" from the database menu. You'll need to click and highlight the stored procedure in the current datasource area, then find your new stored procedure in the Replace with section (to find your new stored procedure you will be prompted for connection details) - then press the Update button
- Step2-SetDatasourceLocation.PNG (19.74 KiB) Viewed 12970 times
Crystal will prompt you to enter a value for the invoicehistoryID parameter - enter one if you like, but at this point you don't have to provide a valid parameter
Step 3 Database Expert - rename stored proc. Despite you telling Crystal in step 2 to update the data source to be your new stored procedure, it will still show the name of the old one. From the database menu, choose "Database Expert" - and right click the "table" in the selected tables to be match your stored procedure name.
- Step3-DatabaseExpert-RenameSP.PNG (19.55 KiB) Viewed 12970 times
Step 4 Verify the database. Choose from the database menu the "Verify Database" option. Crystal may (or may not - it's fickle) again prompt you for parameter values - enter in valid or dummy values.
- Step4-VerifyDatabase.PNG (8.83 KiB) Viewed 12970 times
Step 4.1 Crystal will then prompt for connection details AGAIN - enter them as you did in step 2.
- Step4.1-VerifyDatabase-ConnectionDetails.PNG (7.58 KiB) Viewed 12970 times
Step 4.2 Crystal will then give it's first hint that it recognises there is something different by showing this dialog:
- Step4.2-VerifyDatabase-FixUpDatabaseConfirmationDialog.PNG (5.09 KiB) Viewed 12970 times
Press ok to that dialog.
Step 5 Crystal still won't be showing your new field, so perform Step 2 (Set Datasource Location) Again.
Now, you should see your new field appear.
- Finally.PNG (4.44 KiB) Viewed 12970 times