Complicated Report Help - Crosstab, subreports, shared variables, etc.

[ksajovic]

Hi,
I have been trying to create a report but not having a lot of success. It seems complicated, but I feel there should be an easier soltion. Here are the details:

I am using Access for my data and Crystal Reports XI R2 for reporting. My VB experience is limited. Essentially, I am trying to create an income statement. I have a final table which houses most of the main data including balances, a grouping column, month end date, GL, and cost center. I need to group and sum balances for particular line items and then be able to use their results in calculations. For example, if I group and sum Fee Income and Other Operating Income, I then need to be able to sum both results to get Total Non-Interest Income (which would be used in a following calculation to compute Net Income).

I currently have a parameter to select cost center, but I don't care if there is a parameter to choose or if each center just has it's own page. I need to be able to show three most recent months worth of data in columns and the fourth column being a YTD column. I currently have the report with one column (current month) using a subreport for each line item and passing variables to the main report for calclation purposes, but I am having a hard time figuring out how to convert to the multiple columns and whatnot.

I appreciate any help anyone can provide. I only have a few days to build this before they pull the plug and band-aid it with a more manual Excel version. I'm sure I have left out helpful details, so please let me know what additional info you need.

Thanks!

 

[lbass]

Please show how the fields display when placed in the detail section (for multiple rows) and clearly label each field. Then show what some of the summary results would be for that data. It is hard to visualize what you are working with based on your description. For one thing, you list the fields in the one table, and then mention items--but without having indicated the field is in this table.

-LB

 

[ksajovic]

Thanks for the reply! Right now, I have everything in group footers and nothing in the details section. Maybe this makes it more clear - I would like to see it like this in Crystal:

Cost Center: 3010

Interest Income
Cash and Investments [sum balances for several GLs]
Loans [sum balances for several GLs]
Total Interest Income [Cash and Investments + Loans]

Interest Expense
Deposits [sum balances for several GLs]
Borrowings [sum balances for several GLs]
Total Interest Expense [Deposits + Borrowings]

Net Interest Income [Total Interest Income - Total Interest Expense]

That's a condensed version/small sample, but the basic idea. I would need that in four columns - for example May, April, March and YTD

My main Access table currently looks like this:

Month End Account Dept Balance
05/31/2011 266000 3010 $200.00
04/30/2011 266000 3020 $150.00
04/30/2011 266000 3010 $300.00
03/31/2011 266000 3010 $100.00
05/31/2011 325000 3060 $500.00
04/30/2011 325000 3040 $850.00
04/30/2011 325000 3060 $800.00
03/31/2011 325000 3050 $600.00

My GL table has a grouping column I use in order to put GLs into the correct line items.

Thanks!

 

[lbass]

I meant for you to show me detail level data after the tables are linked--and I'm still not seeing what constitutes an "item".

What is the field that returns interest income or expense? the field that returns loans,deposits? What do you mean by "sum balance"--is this something more than the sum of contributing amounts?

I don't think you should use subreports. Instead you can probably use conditional formulas that limit results to each month, e.g.:

//{@currentmo}:
if {table.date} in dateserial(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+1,1)-1 then
{table.amt}

//{@prevmo}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-1,1) to dateserial(year(currentdate),month(currentdate),1)-1 then
{table.amt}

Then you would place these in the detail section and insert sums at various levels. Some of the subtractions would have to be handled with variables, but I think we should see first if you get this part working.

-LB