Good Reporting Tool for VB to SQL (Currently Use Crystal) or Crystal w/SQL Help Appreciated

Good Reporting Tool for VB to SQL (Currently Use Crystal) or Crystal w/SQL Help Appreciated

Post by Gary Glickma » Wed, 22 Oct 1997 04:00:00



SQL 6.5, VB 4.0 (32 Bit)

I am currently using Crystal Reports 4.x to report against SQL data.  ( I
took the Crystal Reports from VB5 and installed it with my VB4 - can't use
VB5 for other reasons on this app).

The reporting tool seems Ok for some of our solutions, but on others it
fails or just can't perform the task.  For example, with TEXT data types,
we are getting a result on the printout where the contents of one field are
displayed in another.

Also, when we have one parent table to two or more repeating children
tables we can not setup a report that will display the appropriate results.
 For example:

Table ORDER has one row
Table DETAIL has three rows
Table RATES has two rows

Now we want to create a report that includes information from all three
tables.  A SQL Query might look like this:

Select ORDER.NUM, DETAIL.QTY, RATES.EFFECTIVEDATE
From ORDER, DETAIL, RATES
Where ORDER.NUM *= DETAIL.NUM AND ORDER.FEESCHEDULE *= RATES.FEESCHEDULE

The result could be:

ORDER.NUM               DETAIL.QTY              RATES.EFFECTIVEDATE
0001                    1                       Jan 01, 1997
0001                    4                       Jan 02, 1997
0001                    1                       (Null)

The problem is that no Crystal has no way of reporting on the DETAIL.QTY
field in a repeating value setup and then go back and report on the
RATES.EFFECTIVEDATE (with a repeating value setup for that section as
well).  Crystal has some options that can do this with ACCESS, dBase,
etc... But not SQL.

The option I am faced with is to attach the SQL tables to a Jet Database
(mdb) file and report against that.  It is not a good solution because some
of the data is large and would take too long.

Any other suggestions about Crystal and SQL or another tool and SQL.

Any help would be appreciated.  Please feel free to email me directly at:

--
Thank You,
Gary Glickman
InterWave Computer Software, Inc.
http://www.interwavesoft.com

 
 
 

1. Question: Outer Joins in MS SQL-Server when using Sub-Reports of Crystal Reports

This question mainly concerns Crystal Reports 8.5 but the group is no
longer archived by Google, and hey, I am using a MS SQL-Server
database, so it's not totally off-topic on this ng either, is it?

OK, here's the problem. I am using Crystal Reports to design a report
that contains sub-reports. Lets say the sub-report is going to show a
list of accounts. These accounts may contain a set of arbitrary data,
but then again, they may not; hence the outer join. Lets say the
arbitrary data contains a value. A value is nothing without a
currency, and therefore the arbitrary data contains a currency ID. The
SWIFT code however is to be found in yet another table. The following
statement would be a valid SQL statement that gives me the desired
result:

SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
FROM ACCOUNT LEFT OUTER JOIN ARBITRARY_DATA ON
 ACCOUNT.ACCOUNT_ID = ARBITRARY_DATA.ACCOUNT_ID
 LEFT OUTER JOIN CURRENCY ON
 ARBITRARY_DATA.CURRENCY_ID = CURRENCY.CURRENCY_ID

This is not what I get in Crystal Reports. In CR I get

SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
FROM ACCOUNT, ARBITRARY_DATA, CURRENCY
WHERE
 ACCOUNT.ACCOUNT_ID *= ARBITRARY_DATA.ACCOUNT_ID AND
 ARBITRARY_DATA.CURRENCY_ID *= CURRENCY.CURRENCY_ID

This is not a valid statement. Not would it be a valid statement if I
were to design a inner join relation between ARBITRARY_DATA and
CURRENCY.

I can manually change the query that Crystal Reports sends to the
database, but then I lose all information about the parameters, which
means I cannot manually change the query and still get the desired
result.

My question is if anyone knows a solution that does not involve
creating a godawful lot of database views (many reports will require
many views...), but rather getting CR to generate valid statements
even for my problem. Your help would be very much appreciated.

Regards,

Marcus Seeck.

2. sp_attach_db question

3. Using crystal Reports Web to report on SQL database requires password

4. Backing Up Data - Tape Drives

5. Crystal Reports ANd VB 6.0 Printing reports using CRPEAUTO Object, specifying paper orientation problem

6. US-IL-CHICAGO-ORACLE DEVELOPER-OPTIMA PARTNERS

7. Printing report from Dynaset using Crystal Report and VB

8. Table Full- Paradox 7

9. Editing Crystal reports without Crystal Reports

10. Crystal Report 6 and Crystal Report 8

11. Announcement: Easily integrate Crystal Reports with Remedy with UFLTimeZone for Crystal Reports

12. Development of datawarehouse applications using Oracle, Visual Basic, Crystal reports, Java and various EIS tools

13. VB+SQL+CRYSTAL REPORT PROBLEM