Does Crystal Report support multiple database calls?

<N00b question, very N00by>

CR8, SQL2K Stored procedure, VB6 front-end that calls it.

Is it possible to perform Stored Procedure calls other than the main data source in Crystal Reports?

For example, I have a single parameter of broker_id that I pass to the report.  I'd like to have the report take that ID and make a separate SP call that returns the broker name, instead of passing it as another parameter or returning it as a field in my main data source.

Thanks in advance.
-Jim
LVL 67
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gopatincCommented:
I am not sure I fully understand what you are trying to accomplish here, but it is possible to have multiple stored procedures that are not linked. The result set will be a cross join between the two stored procedures
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
My main database call, which works for my entire report:

brkr_id
plcy_ref_no
plcy_status_date
plcy_status_desc
plcy_ndb
plst_id
quarter_id
      
Each brkr_id (say 18) has a brkr_description (SomeCompanyName), which I would like to pass to my report and display in the most efficient manner.  

I could just add brkr_description to my table above, but I'm trying to avoid making the result set unnecessarily huge by grabbing 'SomeCompanyName' 1 to {number of records} times when I only really need to grab it once.  

An additional stored procedure can be used, which accepts brkr_id, and returns brkr_description.  My question is is this possible using CR?

Thanks.
-Jim
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
'SomeCompanyName' will only be displayed in the report header as a title, and is not needed in the details of the report.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

mlmccCommented:
It depends on the version of Crystal.  CR9 and later can handle multiple data sources but I don't think in the way you are trying to do it.

You could do something like this

SELECT TableMain.*, TableDescript.brkr_description
FROM TableMain LEFT OUTER JOIN TableDescript  ON TableMain.brkr_id = TableDescript.brkr_id

The other way would be to use a subreport to get the TableDescript.brkr_description and link on the TableMain.brkr_id

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gopatincCommented:
the subreport option looks like a better choice as the company name is a single value displayed in the Report header.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks everybody.  The least painful way to pull this off was to create a subreport that only displays a broker name, based on a SP that receives the broker ID and returns the broker name, and use that subreport in my main report.   I linked the subreport to the main report by the broker ID parameters.

This should also be fairly reusable...

Thanks again.
-Jim
0
mlmccCommented:
Glad i could help

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.