Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Reports and Stored Proc and Dynamic Data

Posted on 2011-03-02
8
Medium Priority
?
332 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:kmc10314
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35021291
Are you trying to change the fields selected in the stored procedure or are you changing something in the report?

mlmcc
0
 

Author Comment

by:kmc10314
ID: 35021439
I have 3 differerent sets of datat that has their own number of fields.
The fields will be different for each key
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35021556
Do you have an SP that returns different data (fields) based on the parameter?

Crystal will have trouble with that.

mlmcc
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:kmc10314
ID: 35022858
yeah i do.
so it is not going to work with crystal?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35023208
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
 

Author Comment

by:kmc10314
ID: 35023523
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1800 total points
ID: 35023572
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
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 35025715
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question