cancel
Showing results for 
Search instead for 
Did you mean: 

Linking stored procedure to a view on a unique identifier field type

Former Member
0 Kudos

Post Author: lennis

CA Forum: Data Connectivity and SQL

I have a report that was created and utilizes several views. The views are linked by inner joins on a unique identifer field type. I need to add a stored procedure to the report, and the stored procedure needs to be linked by a left outer join to the last view in the linking. The stored procedure is also linked on a unique identifier field type back to the view.

As soon as I link the stored procedure, the report begins to function strangely. Only a couple of the records are returned and appear linked.and appear to be linked. Does anyone know of any issues with linking stored procedures to views in a report?

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Post Author: vanishing11

CA Forum: Data Connectivity and SQL

The problem I have is a little different, I have a report that uses a Store Procedure and I need to add a parameter so I want to add the Tables necessary to be able to use the parameter.

I was able to do it and it works to an extent. The parameter that I'm using gives the client data for a specific cost center and it works when I provide client id and cost center both, but if I want ALL the clients for a cost center then the report gives me all sorts of wrong data.

I'm using a sub-report to display the details and I pass the client id and the cost center. The SP provides the client id and my parameter provides the cost center. Since I'm trying to get all the clients in a cost center then the SP gives me client ids that do not correspond to the cost center and I'm not sure how to correct that.

I believe there should be a way to filter the data once is retreived from the SP based on the specified cost center but I'm not sure how to do it.

The main issue for me is that I'm trying to avoid changing the SP since is from a third party, otherwise the easiest thing would be to just change the SP to accomodate the tables I need.

Any ideas on how to solve this or changing the SP is the only way??

thanks,

Marco

Former Member
0 Kudos

Post Author: pvierheilig

CA Forum: Data Connectivity and SQL

Not sure if you have been able to resolve this (if so, please post the resolution), but here is what I would attempt:

First, use some other SQL tool (Query Analyzer, for example, if the db is MS SQL) and run the queries with the stored proc included (your post didn't state if this had been attempted yet or not). If the result set is as expected...

Create a sub-report in your main report. Tie the sub to the stored proc. Create a parameter in the sub and link it to the column it should be joined to in your main report. This should alleviate the issues you are experiencing. Please post back if it works.

Former Member
0 Kudos

Post Author: SusansDesk

CA Forum: Data Connectivity and SQL

I have a similar problem. I would like to know if it is possible to use a stored procedure AND tables/views together in the same report. To create the report I started with just the sp and Iit was fine. WhenI added and linked a table the sp data was replaced with default values (0's) on the report. So, again, is it possible to use both sp's and tables in the same report?