Am I using too many subreports? Is there a better way?

[jollyreaper]

I've got a report that I need to put together. I pretty much know how to do it with subreports but I'm wondering if I'm making myself a lot more trouble than its worth.

I'm pulling this information from a package that does our scheduling and accounting. Business Objects XI, OBDC=topspeed 5.0.

Here's how the report looks.

Sales
$1,000,000.00

Cost of Goods Sold
$50,000.00

Gross Margin = Sales - Cost of Goods Sold
$950,000.00

Starts, Closings, Backlogs
Starts: 10
Closings: 20
Backlog: 153
============
Total: 193

Staging
Permit: 50
Slab Pour: 50
Framing: 50
Cabinets: 33
============
Total: 193


The main report really doesn't do anything, it just serves as a holding vat for all the sub-reports. Sales and Cost of Goods Sold has to come out of the accounting system and it looks like I have to do each one as a sub-report since I need a different SQL query to gather sales accounts vs. cost accounts.

For the areas after that, we're all reading off the scheduling database but once again it looks like I'm stuck doing sub-reports to get that data.

Is there any smarter way of doing this or is this what I'm stuck with?

 

[synapsevampire]

I think that the general rule is that if it's a different SQL Steatement (slight differences may allow for consolidating), then you will end up with subreports.

Obviously what you posted doesn't allow for analysis as one would need to know the database and the current SQL, not the current output that you wish to alter.

You may find that you can use MS Access to LINK (not import) to your odbc data source, and build out queries to build a single data source that you then expose as a single query on top of those queries to Crystal.

The advantages might be that you have a reusable data source now, and Access allows for a good deal more flexibility. You might use other databases as well.

My thought would be that a UNION ALL type query might prove the fastest and simplest to maintain, while allowing for other tools to also share the data, but it's impossible to say with such limited information.

-k

 

[jollyreaper]

Ok, let me provide more info.

These are the report headings and where the data is coming from.

**accounting module**
Sales
Cost of Goods Sold
Gross Margin
**scheduling module**
Starts
Closings
Backlog
Staging

For accounting, I'm reading from two tables, GLACCT and GLACCTA. The first one has all of the data about our general ledger accounts and the second one contains all of the numbers data and the periods.

This is the query I need to use for Sales:

SELECT "GLACCTS"."AN", "GLACCTS"."DESC$", "GLACCTSA"."BALEND", "GLACCTSA"."CAP"
FROM "GLACCTSA" "GLACCTSA", "GLACCTS" "GLACCTS"
WHERE ("GLACCTSA"."IDGLACCTS"="GLACCTS"."IDGLACCTS") AND (("GLACCTS"."AN">='40010' AND "GLACCTS"."AN"<'40030') OR ("GLACCTS"."AN">'40030' AND "GLACCTS"."AN"<='40090')) AND "GLACCTSA"."CAP"=24072

AN=account number
DESC$=description string
BALEND=balance end
CAP=cost accounting period

The accountant gave me a range of general ledger accounts and what they correspond to. Any account number beginning with a 4 is a revenue account. Any account with a 5 is a cost account. The Where clause excludes accounts they don't want to see in the total and ensures we are using the most current accounting period.

From what you said, it looks like I'm stuck doing sub-reports. I need to show Sales, COGs, save their sums to a global variable, and then display the difference.

It's a bit tough trying to provide enough info to describe the question but not so much as to make a body give up on coming up with an answer.