Crystal Report Sub report Field not updating when deployed

[stephen-gnagie71]

This one is driving me crazy. Please Help!

I am using Crystal Reports for .NET version for MS Visual Studio Pro 2022. I have a report which obtains data from an Oracle DB. The report and all of its sub reports are fine. The issue is that I have modified a Sub report query to display an additional line of data to show when an invoice has a particular charge associated with it.

This issue I am running into, and the reason I am reaching out for help is because, when running/testing locally, the new line in the sub report displays as expected. However, when deployed, the new line refuses to display. In the img below example, cleaning is the additional charge we are wanting.

What I have done (multiple times)

• Updated Oracle views and Procedures - verify data is present.
• In Crystal - "The database is up to date" (preformed both in sub report and main report)
• Attempted 'Browse Data Field' but this doesn't seem to work in VS - runs until VS becomes unresponsive.
• Set Data Source Location - Updated
• Modified sub report with other changes to verify they display (e.g. Visual Text changes) - passed
• Right Click -> Show SQL Query -> ran in Oracle directly to verify data - passed.
• Save ->Test report locally -> copy rpt to test server -> run = no new data; why no new data?

Any idea of what I could be missing?

[DonWilliams · Accepted solution]

Hi Stephen,

That is odd... So hitting the refresh button in the Viewer from your app doesn't show the data?

Open the report up in CR Designer, in the Set Location, right click on your connection and select Options:

Uncheck all of the options under Data Explorer except the type you are connecting to.

The problem Oracle has is in production there can be 100's of Table, Views etc. and that uses up memory, CR only has a limited amount of DB info it can show or access.

Possibly what is happening is there is so much data being retrieved CR can't file in the ones used.

Another possible problem is the Oracle client itself, how are you connecting, ODBC, OLE DB or using CR's native Oracle connector?

CR does save the client dll in the RPT file, there have been issues before when using one flavour when designed and a different one on the app server. CR will sometimes internally convert the client dll when viewed.

Try this, CRLogger is CR's database logging component, see if it is accessing the View for that subreport:

Userapps.support.sap.com

https://userapps.support.sap.com/sap/support/knowledge/en/1603398

1603398 - How to trace data source connections in Crystal Re...

How to use crlogger to generate a log when using Crystal Reports? Read more... Environment. SAP Crystal Reports 2013; SAP Crystal Reports 2016; SAP Crystal ...

As long as the SQL is short you should see what CR is passing.

Don

[stephen-gnagie71 · Accepted solution]

Don, thank you for your comment. I always find your answer helpful.

The logger worked as advertised and produced the results as expected on my local. That said, I am still having the issue.

The answer to your question: "So hitting the refresh button in the viewer from your app doesn't show the data?"

The opposite is true. Hitting refresh in my VS viewer does show the data. It is when I move that rpt to test environment and run it through the app there that the data doesn't show. Or maybe I am reading your question wrong.

For full disclosure, the app we are using for clients to run this is FIS Fuels Aligne client. It is an energy trading risk and logistics platform. We have close to a 100 CR's being used for this to print reports and they all work fine when updated and I have never had an issue until now.

Using the logger as you suggested, I was able to grab the query which is exactly as I thought it would be.

I think my next step is to open a ticket with the vendor as I am all out of ideas at this point.

Thank you for your help.

[stephen-gnagie71 · Accepted solution]

Oh, one more tidbit of info I was able to discover today. If I add a new sub-report and set it up similar to the current one having an issue and link it to the same Oracle view, it comes back as 100% blank.

So I am thinking it might be a settings issue possibly.

[ido_millet]

Did you make the subreport change to an independent subreport rpt file (outside the main report)?
And you are relying on Crystal to reimport that subreport?

[stephen-gnagie71]

Thank you for the response.

No, this is a Sub report that is inside the main report (not a sperate file, that I am aware of).

The sub report was added by right click -> insert subreport and created with the Report wizard.

Other changes I make to this wub report are displaying. i.e. text changes, placements, etc.

Also, if you notice my original img, there is a '0' present. I added this in for testing the missing data. Display '1' if cleaning is present, else, 0. During testing on my local, the rpt will display a 1 because the data is being presented (No '1' is present in above img due to taking snapshot before adding that in). Once moved to the test server, the 0 displays, which lets me know the data is not coming through, but I am not sure why, which is what is confusing me.

IF {VW_INVOICE_OTHERS.COMMODITYSERVICE} = 'Cleaning'

Then '1'

ELSE '0'

[DonWilliams]

Does hitting the refresh button on the test PC work?

That could be it, CR fully supports database security, so if that user doesn't have rights the data will be missing or an error will pop up.

Try logging in with your log on info.

It could be your report has saved data, until you hit the refresh it won't populate the new info.

Open the report in CR Designer and under the File menu option there is a Saved Data option, uncheck it if it's checked on save that report to the App Server.

Is this a WEB or Desktop app? If it's a WEB app it could be the report is using Cached data.

[stephen-gnagie71]

Thank you for the reply Don.

The Save data Option is unchecked and I force it to prompt for params every time I hit the refresh button. That way, I am sure it is giving me a clean copy every time I run it.

This is being used in a desktop application. What is weird is that the other 99% of the data in the report is there. It is this one new modification to the query that is not populating, yet, when I run the query directly it returns the data. When I run call the view from a query to test it, it returns the data, and when I run the report locally, it returns the data. It is only when deployed and running on the desktop app that the data is suppressed like in the image.

I will keep try different things and suggestions though.

Appreciate the response.

[DonWilliams]

What CR for VS SP are you using? Not the framework version 14.0.yyyy.0 but the actual SP number?

Are you using the same Oracle Client on your DEV PC as the App server?

You can get the latest SP 35 from here:

https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html

If you hit the Refresh button in the Viewer does that show you the data?

[stephen-gnagie71]

SP 34 installed on Sept 9th, 2023.

Yes, hitting the refresh button on the viewer does show me the correct data.

It is only when deployed to the test environment does the data not show.

When using test, I run the same parameters as local. User/InvoiceNum/etc. It hits the same DB & stored proc as my local.

The only difference that I see at the moment (though I could be missing something) is that the application which runs this uses a different user than my local though I have set permissions for this user. That said, I am wondering if there is a function or sub-view that doesn't have the permissions set.

[DonWilliams]

Hi Stephen,

Did you look at my test app?

Only reason I can see as to why the second connection is using the DB1 connection info is possibly in your code.

Make sure you follow the work flow in my test app for setting logon info, one set for the main report object and a different set for the subreport Object, you must be using ReportClientDocument (RAS), the Engine will use the one connection, I think, its been a while since I looked at that part.

The Overridden is something you can add in the connection property bag, same as in CR Designer. My sample app shows you how to, use the same name as what you see in CR Designer.

Don

[DonWilliams]

Hi Stephen,

So that's good the SQL is being sent properly so it must be something in your app or report related causing the issue.

When setting location with Oracle you need to map each table in CR Designer, other DB's you can just click the before and after Connection, Oracle needs to do one at a time.

See if my test app works for you:

how-to-parameters-in-crystal-reports-for-visual-studio-net

If you look at the code it sets location using ForNext

Look at this routine:

private void SetLogonInfo_Click(object sender, EventArgs e)

Note you must search for each subreport and set the connection info as well.

What I meant by clicking the refresh button was doing so on the Server app not on your development PC. what this does is verifies that your connection info is correct as set in code, if it fails there there is something that's not being set properly in code so all of the4 values are discarded.

Set Location in CR Designer for each subreport also and view the reports last page, drill into each subreport also to verify it is returning all data and no connection issues.

You did mention you are checking the report in VS, do you or anyone one you know have a full version of Crystal Reports Designer you could verify the report in?

Don

[stephen-gnagie71]

HI Don, again, thank you for the response.

With the help of a DB eng yesterday we were able to isolate the cause of the issue, though I haven't yet found the solution, but truth be told, I haven't had time to get back to it today.

This report uses 2 DB's to pull data.

DB1 (main report)

DB2 (sub report)

With the help of tracing it, we were able to see that the SR is in fact running the correct SQL <db2.subreportProcedure> but then it runs a second query of <db1.subreportProcedure> and overwrites my data causing the expected output to change.

I am not sure why this would be happening yet but I do know this report has been modified a few times and it's possible this was always happening and was never noticed until a change was requested. That said, I am going to scour the procedures to make sure everything is fully qualified.

The SR only uses the one DB2 and only pulls a couple of fields. I am confused why it would qualify it with the DB1 name but if I knew that answer, I would be past this issue. lol

Is there anything in the SR that would need to be edited to ensure it is pointed at the correct DB?

When checking Data source location, it shows the correct procedure and the correct DB2 name in the properties.

If I try to add the procedure again, it gives me the warning that it is already been added to the report.

thank you

Steve

[stephen-gnagie71]

Also, is there a way to change the 'Overridden Qualified Table Name' value? Just curious as I noticed this in the Data Source window.

All of our queries are fully qualified but curious as to what the override does and if it can be enable/disabled.

Thank you.