Solved

Crystal Reports XI sub report will not update stored procedure

Posted on 2011-03-01
10
801 Views
Last Modified: 2012-05-11
I have a report, that fires a sub report.  The sub report's datasource is a stored procedure on a SQL Server 2008 database.    It works fine.
Then I realize I need the stored proc to return additional data elements.  So I add the new fields to the result set of the stored proc, and alter the stored proc in the database.     I can run the stored proc in the database directly (via management studio). it works fine.
So I attempt to refresh the datasource, using the usual methods, and I just cant get the sub report's datasource to see the new columns.  
In "normal Crystal", when I modify a table, and rerun, I get the "table XYZ has changed! Proceding to fix the report!"   I can't get this to happen in this scenario.

Aside from rewriting the whole damn thing, what am I missing?    
0
Comment
Question by:fhcdaver
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35010655
Did you try VERIFY DATABASE when editting the subreport?

mlmcc
0
 

Author Comment

by:fhcdaver
ID: 35011004
yes.  
We also tried pointing the sub to another database.
the only thing we know works is that we change the sp to a new alias name, and re-work all the formulas, summaries, calculations, etc and basically rebuild the whole thing from scratch.
Even SAP's website says that.  I just have such a hard time believing that this is the best answer.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 167 total points
ID: 35011159
That is the purpose of VERIFY DATABASE to force Crystal to go and read the stored procedure and update it.

Have you tried logging off the database in Crystal
Close Crystal
Open Crystal and logon the database

mlmcc
0
 

Author Comment

by:fhcdaver
ID: 35011655
I've even tried shutting down the whole computer and starting up again!
But i have not tried that exact sequence.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35011755
I recall having an issue when the report is just opened.  The connection seems to still be there so it doesn't really try to read the new one.

mlmcc
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 167 total points
ID: 35015006
Could the subreport be using an old/different stored procedure?  Go to Database > "Set Datasource Location", open up the datasource under Subreports, open the Properties for the stored procedure and make sure the name is correct.

 If the name is correct, you could try using "Set Datasource Location" to change the datasource for the subreport, but just point it back to the same stored procedure, and then do a "Verify Database".  Or create a copy of the stored procedure, temporarily change the subreport's datasource to the copy, do a "Verify Database", change it back to the original stored procedure, and then do another "Verify Database".

 James
0
 
LVL 4

Assisted Solution

by:musalman
musalman earned 166 total points
ID: 35015053
Adding James Comment:

Try to Update Sub Report separately. I mean Open Sub Report apart from Main Report in Crystal and then try to Update the Data Source.
0
 

Author Comment

by:fhcdaver
ID: 35060625
Hi guys,
I got detoured from this issue with another couple of fires. I will give the datasource within the sub a try.
Dave
0
 

Author Comment

by:fhcdaver
ID: 35214873
Hi,
So we tried verifying the sub as if it were independent, as well as verifying it all, setting datasource, etc.  it just wouldnt work. Separately, the SAP blog suggested that the only thing to be done was to rewrite the entire thing, including all the formulas, etc.  very disappointing.  It's not the fault of the experts who tried to help, so I've apportioned the points accordingly. thanks for trying.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35221466
Sorry we couldn't be of more help.

 James
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now