kmc10314
asked on
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?
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?
ASKER
I have 3 differerent sets of datat that has their own number of fields.
The fields will be different for each key
The fields will be different for each key
Do you have an SP that returns different data (fields) based on the parameter?
Crystal will have trouble with that.
mlmcc
Crystal will have trouble with that.
mlmcc
ASKER
yeah i do.
so it is not going to work with crystal?
so it is not going to work with crystal?
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
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mlmcc