I am starting to play with Crystal Reports 8.5.  Our database is SQL
Server 2000.  I need to create a report that has serveral table like
sections.  Each cell contains a number.  The number is generated this
way.

1. select count(attribute1) as A
   from   table1
   where  condition1
2. select count(attribute1) as B
   from   table1
   where  condition2
3. A+B is the result that will go to a cell

Some records need to be counted twice, that's why I issue two separate
queries.  Each cell has different condition1 and condition2.  The data
in each cell is a calculated result.  How can I accomplish this with
Crystal Reports?

Should I leave the task at database?  I am new to database
administration, too.  How do I create temporary tables or views whoes
data is calculated result?  Can it be done through SQL syntax or it
can be done programmatically?

Your help will be very appreciated.

About SQL syntax and temporary tables, I can tell you somthing that works in
Informix:

select field1 as field
count(*) as ammount
from table
where (condition)
into temp t1;
select a.field , b.field5
from t1 a , another_table b
where a.field = b.field4
into temp t2;

select * from t2
where (condition)

I don't know if this is helpful for you.

Now, for generating reports, I suggest you to try Rpv Printing System. Since
Rpv doesn't connect to databases, it is easier to generate reports with Rpv.
Rpv is a new-concept tool, once you understand the way it works, is really
easy to use.

If you want, I can send you more explanation, plus a free of charge, small
user manual in pdf format, plus examples.
Just mail me.

Visit http://www.rpvreport.com

Tomas Tokman

One of crystals weaknesses is definately reports that require analysis
with multiple passes over the data. I'm guessing that when you say
sections you are actually refering to groups. You can do it, but it's
ugly. Generally, you should use Crystal as a data display tool, not a
data analysis tool. It fails miserably in ease of use for complex
analysis. I'd approach this in one of the following ways:

1) Easiest: Report off of a stored procedure in the database, that
returns your data in a nice single table. I'll sometimes create a
variety of stored procs that return different analysis of data all with
the same key field or grouping level. I can then recombine in crystal
and use Crystal make it look pretty.

2) If your report data source is ADO, you can base your report off of an
SQL statemement. MS SQL supports subselects and unions, so you could
make an ugly query that would look like: (I don't use SQL 2000 much - so
I don't know that this syntax is correct)
        SELECT keycolumn, (SELECT count(attr1) from table1 where
table1.keycolumn = keycolumn) as A , (SELECT count(attr2) from table 1
where ... ) As B etc...

3) The simplified example you provide could be accomplished with crystal
formulas. You'll have to look into _when_ the various actions are
calulated and make sure that the grouping in the formula is the same as
  the defined groups in your report. Your formula then looks something
like this - beware that I don't attempt to remember Crystal syntax
resultA = count({attribute1}, conditions)
resultB = count({attribute2}, conditions)
formula = resultA + resultB

This approach is the quirkiest: The conditions for your count must be
the same as your grouping level. Because you want to seperate conditions
on your report, I'm not going to recommend this approach. You can make
it work, but only with extreme tweaking of your groups.

Samples query:

SELECT     (SELECT     COUNT(*)
                       FROM          Table1 WHERE ...) +
                          (SELECT     COUNT(*)
                            FROM          Table2 WHERE ...) AS COUNT

You may wanna consider creating a view and using that in your Report which
will be a lot easier. Creating views are pretty straight forward in SQL with
all the GUIs. Just go to the views right click select new view. On the top
pane right click add tables and you wil be on your way to views.

anand