Solved

Crystal Report

Posted on 1998-07-17
6
263 Views
Last Modified: 2010-05-03
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.
0
Comment
Question by:mallow
  • 4
  • 2
6 Comments
 
LVL 2

Expert Comment

by:percosolator
ID: 1466117
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.

0
 

Author Comment

by:mallow
ID: 1466118
can you please send the codes for the stored procedure to carol@the.net.ph please? thanks :)
0
 
LVL 2

Accepted Solution

by:
percosolator earned 100 total points
ID: 1466119
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.

------------------------------------------

CREATE PROCEDURE SP_TEST AS


      SELECT
            CUSTOMER,
            DATE_                              ORDINARYDATE,
            CONVERT(DATETIME, NULL)            GOLDDATE
      INTO
            #temp
      FROM
            _CUSTOMER
      WHERE
            STATUS = "ORDINARY"


      UPDATE
            #temp
      SET
            GOLDDATE = DATE_
      FROM
            _CUSTOMER
      WHERE
            #temp.CUSTOMER = _CUSTOMER.CUSTOMER
      AND
            _CUSTOMER.STATUS = "GOLD"


      SELECT * FROM #Temp

GO
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:percosolator
ID: 1466120
You never said whether that worked for you, or not.

How'd it go?
0
 

Author Comment

by:mallow
ID: 1466121
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.
0
 
LVL 2

Expert Comment

by:percosolator
ID: 1466122
ooh.

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

hmmm.

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.

Doug


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now