Accessing Data Outside of Stored Procedure

[dunkyn]

The viewer we are using to deliver the reports to users in-house requires that we use a stored procedure as a data source. However, in some instances we want to access additional data from another source.

We want the efficiency of the stored procedure, but we are not programmers.

Any tips on how to access data outside a stored procedure in conjunction with a stored procedure? Would a subreport work or does that also require programming in the stored procedure?

Are there other alternatives?


TIA - we are new at this.

 

[synapsevampire]

Please provide an example of what "access additional data from another source" would be.

And what kind of database are you using, SQL Server, Oracle?

Was this a cuswtom made Viewer? If not, post which Viewer you are using.

A stored procedure can contain dynamic SQL, meaning that it could return varying sets of data based on parameters passed. This could allow you to pass in the table(s) you're interested in, and have it return all of the rows.

This might prove horribly inefficient and clumsy for anything complex unless you also used a few other parameters which would dynamically control Joins and the Where clause.

-k

 

[dunkyn]

Our database is SQL Server. We have built a datawarehouse and are using a third party product by Netik to run inquiries on investment accounts and do investment trading. We also offer this application to clients over the internet so they can run inquiries against their accounts. The Crystal Reports will only be used by staff in-house, but the plan is to use this third party product as the viewer and leverage the datawarehouse that was built for other apps. Using this product as a viewer to deliver the Crystal Reports requires that we use stored procedures.

The external data that we also wish to access would be financial data from another source - primarily equity statistics - from Baseline, (the datawarehouse has data from our mainframe application) - either directly from the Baseline files, or Baseline data populated into a PC database. The third party programmer knows how to do this, but I wonder if there are other alternatives.

We don't want a bandaid solution, and then find ourselves back at square one the next time we try to build a new report.

 

[Madawc]

I've used subreports along with stored procedure data, including a subreport showing customer details against a stored procedure file of customers who may be duplicates. It's seriously slow, but it does work.

In my case, Crystal 8.5, I use [Subreport links..] under [Edit] on the menu to pass the customer number in each case. Subreports can also run independently, can be a completely separate report within a report if that's what you need. They are useful, and a lot of us get frustrated that Crystal does not allow sub-subreports, one subreport within another.

If you could figure a way to get the data into the stored procedure, that would be better, of course.

Madawc Williams
East Anglia, Great Britain

 

[dunkyn]

If I use a subreport - Will I be able to place the information in the details section, adjacent or between detail information in the existing report? Or will it need to be in a separate section - say a footer or header?

 

[IdoMillet]

You can link SQL Server to tables in external data sources so that they behave as if they were SQL Server tables. Then, you can create a Stored Procedure that includes that data. This, in most cases, would be much more efficient than subreports.

You can also use SQL Server DTS to schedule imports of the external data directly into SQL Server.

Just curious, why are you restricted to Stored Procedures?
I can't imagine a Viewer that is restriced to stored procedures, so the issue must be elsewhere.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[dunkyn]

I am a technical user, not in the technology dept., but the message from that end is that we must use stored procedures with the viewer we have.

My guess is that because the viewer has multiple functions - investment account inquiries, investment trading, securities inquiries, and potentially Crystal Reports, that the developer used the stored procedures to link the parameters in the view to the parameters in the SQL data. But just a guess....

The tech area has concerns about learning and supporting another application. Are other viewers that complicated to learn and setup?

 

[Madawc]

A subreport can be wherever you choose to place it, report header or footer, detail or group header, probably also page header or footer, though I've never tried this.

Madawc Williams
East Anglia, Great Britain