Where did my budget go??

[thedaacct]

I'm writing a report comparing budget to actual by account number. My budget data shows up fine until I add data from the actual table; at which point my budget disappears if I did not have actual activity. Can anyone help??

Thanks much.

 

[Soleil525]

Change the linking between the tables to left outer join instead of equal join.

 

[Nuffsaid]

Hi,

Not much to go on.... but I'll give it a shot.

I assume your report started off with just budget numbers that were stored in a "Budget" table. You then added the "Actual" table to the report to obtain the numbers you require. You must of had to join these two tables in the visual linking expert. Check the join type. If the join is equal. Records in the budget table will only show up if there are corresponding records in the actual table.
Therefore no "Actual" = no "Budget".

Link from budget to actual with a left outer join. All budget numbers should appear wether there has been actuals or not.

Hope this helps....

Nuffsaid.

 

[thedaacct]

Nuffsaid...you got the idea right, but I tried changing the join type and I still have the same trouble.

Beth

 

[Nuffsaid]

Hi Beth,

Okay, need a little more info. Can you describe the steps you've taken so far? What tables and fields are involved. Also, what type of database are you reporting off of?

Nuffsaid.

 

[thedaacct]

Hi Nuffsaid.

I'll do my best to describe what is happening, I'm only a lowly accountant.

I am reporting off of an Oracle database. My tables are coming from Lawson, which is our accounting program. I am trying to report actual vs budget.

I've taken the report down to the most basic tables to try to figure out the problem, so I just have two tables--FBDETAIL (my budget) and GLAMOUNTS (my actual). Each table has the debit and credit totals for each month. I am linking them with a left out join on FISCAL_YEAR, ACCT_UNIT, ACCOUNT, VAR_LEVELS, and CHART_NAME.

From there, I am doing the following formulas:
@Jan-budget---if {FBDETAIL.FISCAL_YEAR} = {?YEAR} THEN
{FBDETAIL.DB_AMOUNT_01}+{FBDETAIL.CR_AMOUNT_01}

@Jan-actual---if {GLAMOUNTS.FISCAL_YEAR} = {?YEAR} THEN
{GLAMOUNTS.DB_AMOUNT_01}+{GLAMOUNTS.CR_AMOUNT_01}

(Note: I'm not using the selection expert to choose the year because I'm going to want to also include prior year data on my report)

On my report I put in the ACCOUNT field from the FBDETAIL table and the @Jan-budget formula. At that point, each account with a budget shows up. When I add the @Jan-actual formula to my report, the accounts without actual January activity go away, leaving the budget totals off.

I hope that explains my mess a little better.

Thanks for your help, Nuffsaid.

Beth

 

[Naith]

This still smacks of linking to me.

Can I ask a couple questions? It sounds as if you're linking from GLAMOUNTS to FBDETAIL? Are you? From your examples, it appears that you might want to be performing a left outer join from FBDETAIL to GLAMOUNTS instead of the other way around.

What's the data dependency here? Can a budget exist without there being any corresponding actual records? How about vice versa?

Naith

 

[thedaacct]

Hi Naith.

Currently I am joining from FBDETAIL to GLAMOUNTS, but we have tried it each and every possible way.

As far as dependencies go, I'm hoping I understand what you mean, but I'll explain what I've noticed so far. In either of the tables, the account number does not exist unless there is some activity. The budget table only includes the account number if there is a budget amount, same with the amounts table. If there is no activity, there is no account in that table.

I'm going to try to link in a table that contains all of my accounts but doesn't have any of the detail.

Got any other ideas??

Thanks much.

Beth

 

[Naith]

I'm back in the office now, and am about to try to mirror your get up. In the meantime, I'm curious as to how your problem is impacted if you strip a component out of your linking; i.e. chart_name.

Naith

 

[Naith]

Beth,

I've recreated your problem here with a couple of tables in my own database. The only way I can get this problem to arise is by linking incorrectly.

By this, I mean that I let Crystal assume the fields to link on by checking for identical field names between the two tables.

Then, if you drop a field from one of the tables into your report, and then check the sql Crystal generates, you'll find that Crystal ignores the other table(s), so does not need to reflect the joins that were set up in the Linking Expert.

However, if you put in a field from the other table, you get that occurence with the one table appearing to over-ride the other.

This tends to happen when tables are being joined on fields which were never meant to be used as joins between other tables. You should have a look at the database schema, or have a chat with the person who knocked your data schema up to ascertain if it's necessary to join GLAMOUNTS and FBDETAIL on all the fields you are using to join on. Perhaps you can get the unique results you require by joining on less fields.

Alternatively, check the schema to find out if there should be additional tables you need to use to link these two tables adequately - even if the additional table(s) aren't going to have any fields placed in the report.

Naith

 

[thedaacct]

Hi Guys.

Ok, I tried removing most of my links. I just left the link on ACCT_UNIT and ACCOUNT with a left outer join and I still get the same results.

I tried removing each of those links individually too, but that gave me a bigger mess.

Haven't given up hope yet though.

Beth

 

[Nuffsaid]

Hi Beth,

Sorry, been busy....

By The Way, There's NO SUCH THING as a lowly accountant!

I'm one, and someone has to keep them "On The Straight And Narrow"

I agree with Naith.... This still smacks of linking to me.

Would it be possible to post all the fields in both the budget and actual tables? I'm a "Visual" type.

Nuffsaid.

 

[Nuffsaid]

Hi,

Me again, just another thought...

Is this a one to one relationship? Total budget vs total actual? Or, are we dealing with multiple actuals (i.e. monthly) accumulated, then compared to a budget figures?

Still Thinking....

Nuffsaid.

 

[kenhamady]

Create a new report with just 2 tables.
Link from Budget to Actual
Use a left outer join
Add one field from each table to the detail band (select one that is always filled in)
Don't use any ACTUAL fields in the select expert or the selection formula.


Now, do you get data from both tables? If so, start adding in your formulas. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[N22S]

Beth,

Sorry that I'm late to the conversation. I've written and supported Crystal financial statements using Lawson. It does sound like an issue with joins.

Try a left join from GLMASTER to FBDETAIL and a left join from GLMASTER to GLAMOUNTS. Link on the Company, Accounting Unit and Account fields.

Left join from GLMASTER to other possibly necessary tables such as GLNAMES, GLCHARTDTL, GLUNITS

You may want to do an equal join from GLCHARTDTL to GLCHARTSUM

E-mail me if you need more help on this report. I'll be glad to give you some free advice and then post any additional information here.

Nick

nick.stechschulte@smed.com

 

[lyanch]

thedaacct,

I suspect using any of these joins with either on the "left outer side" is going to cause problems. You will probably at one time or another have budget numbers without actual and/or actual without budget.

I would most likely create a stored procedure for this with a temp table something like

acctno
budgDr
budgCr
actDr
actCr
acctname
anyother info you need

then:

select into temptable chart.accno, sum(budinfo.budgDr), sum(budginfo.budgCr), 0, 0, chart.name, xxx
from
chartofaccounts chart,
BudgInfo budginfo
where chart.accno *= budginfo.accno
group by chart.accno

then update temp.actDr, temp.actCr with sum(actinfo.actDr) and sum(actinfo.actCr).

That obviously isn't correct SQL but you should be able to get the idea. I also have assumed that you have a table that has your chart of accounts in it. If you don't, you still can do this, you just have another step or two to get there.

Lisa

 

[thedaacct]

Thanks everyone for your help.

Nick, you got the closest. When we used the GLMASTER to link the FBDETAIL with an equal join and then linked GLAMOUNTS to that with a left outer join, we got our data to show. I'm not sure it is going to work in reverse, if I have actual, but no budget, but it is month end so that is put on hold for a couple days.

Thanks again everyone, I appreciate your help.

Beth

P.S. Nick---I'm going to keep your email address handy for other Lawson-Crystal issues.

 

[RJFost]

As a lowly non-accountant (just a Crystal guru & reluctant Lawson analyst), I know that, at our facility, everything is tracked by the combination of ACCT_UNIT, ACCOUNT & SUB_ACCOUNT. That aside, I think the suggestions to check/test your JOIN criteria are your best bet.

Also, from a strictly Oracle perspective, remember that the size of the tables in a JOIN relationship can have a noticeable effect on your query (both processing time & return data), based on the order you've built the JOIN. As an example, assume 3 tables: SMALL (100 rows), MED (1000 rows) & LARGE (10,000 rows) [and that's nothing compared to Lawson; we've got 750,000+ rows in the ICTRANS table alone]. If your JOIN translation* lists them in the order "...SMALL.field = MED.field(+) AND SMALL.field = LARGE.field(+)" [or the like], the fact that LARGE is listed as the outermost table in the JOIN results in the query optimizer using an inefficient HASH JOIN when it processes the query request. See if you can troll around & lay your hands on an Oracle DBA [or the moral equivalent; I'm a half-assed DBA at best] & let them take a look at your query.

*I say "translation" because the query you see if you click "Show SQL query" in Crystal is the version generated by Crystal's internal query engine; if you tried to copy & paste it into SQL+ (native Oracle), it'd crash & burn before it ever got off the ground.