Crystal Report

hi there,
i'm using crystal report 5.0
i hava a table ....
CUSTOMER      DATE            STATUS
AAA            9/12/97            ORDINARY
AAA            12/12/97      GOLD
XXX            8/8/97            RED
YYY            12/12/97      BLUE
BBB            5/12/97            ORDINARY
CCC            12/12/97      ORDINARY
BBB            6/12/97            GOLD
CCC            12/12/98      GOLD

I need to show on my report only records with gold and ordinary status,
and report on the customers, first column of my report lists all the
customers, no repeating values, second column list the date the customer turned ordinary, and third date the customer turned gold  so for my report i should have something like this:

                ORDINARY      GOLD
CUSTOMER      DATE            DATE

AAA            9/12/97            12/12/97
BBB            5/12/97            6/12/97
CCC            12/12/97         12/12/98

please help, any suggestions will be appreciated. thanks.
Who is Participating?
percosolatorConnect With a Mentor Commented:
This script will create SP_TEST when run through I/SQL

Make sure that Crystal can view stored procedures.  Go to File, Options,   SQL tab and there is a check box for stored procedures in a frame called "Allow reporting on"

I am running Crystal 6.0, but 5.0 should have the setting in the same spot.



            DATE_                              ORDINARYDATE,
            CONVERT(DATETIME, NULL)            GOLDDATE
            STATUS = "ORDINARY"

            GOLDDATE = DATE_
            _CUSTOMER.STATUS = "GOLD"

      SELECT * FROM #Temp

The problem that you are encountering is that you have a table with linear data, but wish to view it 2-dimensionally.

Mucked-about with a cross-tab report for a while... That's not your solution as to it deals with summary information.

What you need to do is transform your data from the one-dimensional view that it's currently in, to a 2d one.  This can be done with a series of queries to which you link your report.

If you would like, I can send you SQL code for a SQL stored procedure, or the Access queries to do this transformation.  Or Post it, if you'd prefer.

mallowAuthor Commented:
can you please send the codes for the stored procedure to please? thanks :)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

You never said whether that worked for you, or not.

How'd it go?
mallowAuthor Commented:
hi there percosolator, i have to try the codes, and is this the correct syntax for stored procedure in  informix because i'm getting a syntax error for my stored procedures, if you know that syntax, please tell me, i have to try these codes on monday, thanks :) i'll tell you.

sorry.  i thought that you were using Microsoft SQL Server.


the syntax is probably close.  i don't know if Informix is ANSI-SQL compiant.  i _think_ that SQL server is....

the syntax might be a little off for Informix, but the logic is sound.


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.