Crystal Reports and Stored Proc and Dynamic Data

I wonder if this is possible and if yes, can anyone tell me know to do it?
I'm creating report based on Crystal reports.
I'm going to use one stored proc for three different reports, and their primary parameter is going to be different.

like
order_id for report a,
customer_id for report b
employee_id for report c

while they all have some common fields, rest will be different.
(i'm using if statement to bring out those place holderS)
for exmaple

if order_id = 2
major id = 2 then generation order_number, order_amount, order_tiem

if customer_id is 1
major id = 2  then customer_name, customer_phone, customer_email

if employee_id 1
major id = 2 employee_name, experience... something like this

do you see what i mean?

There are going to be some fields that are common, but not all the fields are going to be the same.
Is this possible?
kmc10314Asked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
If you want 1 to work for all, yes.

You could overlap by putting ones with the same type in the same places but the first one would determine the field name.

mlmcc
0
 
mlmccCommented:
Are you trying to change the fields selected in the stored procedure or are you changing something in the report?

mlmcc
0
 
kmc10314Author Commented:
I have 3 differerent sets of datat that has their own number of fields.
The fields will be different for each key
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Do you have an SP that returns different data (fields) based on the parameter?

Crystal will have trouble with that.

mlmcc
0
 
kmc10314Author Commented:
yeah i do.
so it is not going to work with crystal?
0
 
mlmccCommented:
Can you change the SP?

You could have it return fields for all reports with some of them being NULL or 0 if they don't apply

The SP would be like

if @typeparam = 1 then
   SELECT Name, StreetAddress, City, State, Zip, 0 as ProductID, "" as ProductName, 0 as Cost FROM Address
Else
   SELECT "" as Name, "" as StreetAddress, "" as City, "" as State, "" as Zip, ProductID, ProductName, Cost FROM Address

Crystal could use that in 2 different reports.  Not sure why you would want to go that route rather than having an SP that specifically fits the requirement for the report.

mlmcc
0
 
kmc10314Author Commented:
So basically, i have to use it like an union where i have to have all sets to have same # of placeholders in order for crystal to process
0
 
James0628Connect With a Mentor Commented:
FWIW, if you're going to use UNION, each SELECT is going to have to produce the same number of columns, with the same data type in each column, anyway.  That's just the way that UNION works (at least in MS SQL).

 Like mlmcc, I don't really see the point in trying to create a single procedure that reads different tables and produces different output for different reports, but I suppose you have your reasons.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.