Adding counts from subreport to main report

[harrietohs]

Crystal 8.5
ODBC Database
Both Main and Subreport are Cross Tabs.
My main report counts animal ID's based on one set of critera. The subreports counts animal ID's based on a different set of criteria.
The rows and columns are the same for both cross tabs, with the rows being months of the year and one column showing total adoptions for a certain criteria. Looks like:

Adoption Report (Main) Adoption Report (Sub)
Jan 05 2000 Jan 05 300
Feb 05 3000 Feb 05 200

I would like to show a total for each month (i.e. Jan 05 would equal 2,300.
Can a formula be constructed to do this?

Harriet

 

[synapsevampire]

ODBC isn't a database, it's a type of connectivity to a database, check with an IT person at your company to learn what you're really using.

Since you're using crosstabs, the answer is a tentative no.

I would encourage you to build a single datasource to supply all of the needed information in one report/crosstab, perhaps further grouped by the criteria types, but an alternative might be to create shared variables in the subreport for each year/month and pass them back to the main report and then build a manual cross-tab to deomnstrate the totals.

In general subreports are slow and add complexity which should be avoided.

-k

 

[harrietohs]

Sorry, I did mean connectivity = ODBC.

All of the data I require is in one database,linking two tables. Because one set of criteria is based on the date an animal arrived (i.e. all intakes for a calendar year) (main report), and the other the day an animal left (i.e. all outcomes for a calendar year)(sub report)I use a subreport so that I can show both volumes on the same report. The report is used to help determine staffing requirements by month. We are a nonprofit and want to adjust staffing levels based on busiest months.


I can change this to a standard format report, if that makes the difference in adding the totals.

 

[synapsevampire]

Creating a single report using a manual cross-tab would be simple enough.

Cross-tabs aren't spreadsheets, so you can't just add in rows and click something to not do any cross-tab functionality with this field as it's just a number I need displayed.

So did you learn what your database is? Might be simpler to pre-process this on the database, again, check with your IT folks.

You might also use formulas as the source for the report wherein you create formulas something like:

AR-Jan
if month({table.arrival}) = 1 then
1
else
0

AR-Feb
if month({table.arrival}) = 2 then
1
else
0
etc...

and

DE-Jan
if month({table.depart}) = 1 then
1
else
0

DE-Feb
if month({table.arrival}) = 2 then
1
else
0

Then you can base sums on these formulas and lay them out alongside each other and build other formulas against those formulas for additional aggregate functions such as sums or percentages, etc.

-k

 

[harrietohs]

The database is created by a software called "Chameleon", where we enter all of our animal information. (We are a Humane Society/Shelter). I cannot do any processing within the database. Chameleon uses runtime of Crystal to distribute reports. I write the reports which are then picked up via an .ini file.

 

[synapsevampire]

Ahhh, I see, you have a proprietary interface for Crystal, which you should state in future posts as it limits options.

I think I saw a blurb indicating that Chameleon is using SQL Server, but I'm not sure, they have a really weak web site and documentation. It basically says call us...

You may be able to create database objects within their database, but certainly from other databases that are linked to it (it's very common to use MS Access for this, and SQL Serevr can do so as well).

Anyway, the manual approach should work for you.

-k