GLAMOUNTS and GLUNITS Tables

Brian:

Can you use an RW100 report as an input to your crystal report? We used to do this and with RW100, you don't need to do the SQL linking. It does take more processing time but if the report is small, it's not too bad.

If you need to do it in SQL here are some things that might help:
1. If your company has more than one defined COA in Lawson, be sure to choose 1 chart name in the Where clause.
2. Make sure you are linking Company, Accounting Unit, Account and Subaccount
3. Is fiscal year the problem? Try running for just one FY and see if that's the reason for duplicate entries.
4. You may need to do this in two queries that are then aggregated using a union and a third select clause:
4a. One query to get GLMaster and GLAMOUNTS - when you do this, do you get multiple records still - if so, see below)
4b. One query to get GLMaster and GLUNITS
4c. Join those two with a UNION
4d. Create a top level Select to combine those to (or do that in crystal).
5. If you are getting duplicate records with just GLMASTER and GLAMOUNTS query it sounds like you might want GLAMOUNTS to be on the left and do a left outer join.

Sorry if some of this is vague but hopefully it gives you some new ideas to try. Send me an e-mail if you'd like me to take a look at your query. There have been many times that my computer was at risk also! :)

Caroline

Have you tried pulling the values from GLCONSOL? It will give you the totals for both units and amounts based on the trans type. You would have to link in the GLNAMES to get AU name as it pulls it by Var level but this might get you closer than where you are.

Mark
Thank you for the idea. I have used GLCONSOL a lot with my financial reporting as it is an easier table to use since I am wokring with company totals. I am looking for department totals on the bottom level and teh GLConsol does not go to that level, it goes to the level right above there so the AU does not show up. There may be another away around using that table to get to the AU amounts and units and I may relook at the linkage you suggest to see if that works.
Thanks
Brian

Caroline
Thank you for the information. I can do RW100 reports, but can not use it as a datasource output as other end users will be running this report so this would have to be run over and over, so is not an option since I have other end users who do not have access to it.

By UNION are you referrring to subreports?

If so, I have thought about this and am not that good wtih subreports, but am under the impression it is hard to do calculations off a subreport information.
I have to also do some complicated formulas using the units for some additional ratios off the amounts.

The duplication issue is when there is an AU that has both an amount and a unit in the account, then I hit the duplication and wall. I think it is a linkage issue and have tried I believe all the linkage to find which one would work and none done. Let me take a look at your suggestions around number 4 down and see if it helps to figure it out further.
Thanks
Brian

I was thinking about a union in the SQL itself. Not seeing the FROM clause, I thought it might be possible that you were getting duplications from the linking there that, if split, might be eliminated. Then, put the data back together in SQL with the union. Just a thought...

I may be teaching grandma to suck eggs here, but have you considered adding GLMaster twice to the report, Linking one copy to GLAmounts and one copy to GLUnits, then doing a full outer join on the two GLMasters?

Outside of brute forcing it in SQL, that would be my first go at it.

This is what I do...

SELECT nvl(gam.company, glu.company) AS company,
nvl(gam.fiscal_year, glu.fiscal_year) AS fiscal_year,
nvl(gam.acct_unit, glu.acct_unit) AS acct_unit,
nvl(gam.account, glu.account) AS account,
nvl(gam.sub_account, glu.sub_account) AS sub_account,
nvl(gam.db_amount_01 + gam.cr_amount_01, glu.db_units_01 + glu.cr_units_01) AS amount_01
FROM lawson.glamounts gam
FULL OUTER JOIN lawson.glunits glu
ON gam.company = glu.company
AND gam.fiscal_year = glu.fiscal_year
AND gam.acct_unit = glu.acct_unit
AND gam.account = glu.account
AND gam.sub_account = glu.sub_account

This is what you need to do:

SELECT
X.COMPANY,
X.FISCAL_YEAR,
X.ACCT_UNIT,
X.ACCOUNT,
X.SUB_ACCOUNT,
SUM(X.AMOUNT) AS AMOUNT,
SUM(X.UNITS) AS UNITS
FROM(SELECT
GAM.COMPANY,
GAM.FISCAL_YEAR,
GAM.ACCT_UNIT,
GAM.ACCOUNT,
GAM.SUB_ACCOUNT,
GAM.DB_AMOUNT_01+GAM.CR_AMOUNT_01 AS AMOUNT,
0 AS UNITS
FROM lawson.GLAMOUNTS GAM

UNION ALL

SELECT
GLU.COMPANY,
GLU.FISCAL_YEAR,
GLU.ACCT_UNIT,
GLU.ACCOUNT,
GLU.SUB_ACCOUNT,
0 AS AMOUNT,
GLU.DB_UNITS_01+GLU.CR_UNITS_01 AS UNITS
FROM lawson.GLUNITS GLU)X
GROUP BY X.COMPANY,X.FISCAL_YEAR,X.ACCT_UNIT,X.ACCOUNT,X.SUB_ACCOUNT

The FULL OUTER JOIN will perform much better than a union.

Thank you everyone for the response.
As a note, I do not know SQL so not sure how to try those items.
I just tried the linkage with putting the table in their twice and linking it as such and the salary data returned wacked up numbers once I added the units.
Everything is okay until I add the units then it blows up. So it tells me it is a linking issue possibly. I do not think I am trying to do the impossible but there may be another table needed but all the data is there between the three.

As a note, I do not need units for the salary accounts, it is just there because it is collected, but have limited by GLUNITS to the range needed. THese unit accounts being used do not have any dollar amounts associated with them.

It sounds as folks have been able to link these two tables without blowing up the units when there are units tied.

Here is what I have used as joins:
Company
AU
Acct
Var_Level

I have tired all of the different joins a an FYI as well and each can give different results.

I have tried using GLMaster to join GLAMOUTNS and GLUNITS, I did the double GLMaster and joined as suggested, and I have tried only using GLAMOUNTS and GLUNITS.
Right now none of the ways are working.

Any other possible ideas or thoughts?
Thanks

My suggestion, take one of the SQLmethods suggested above and have one of your DBAs create a view, then point your report at the view instead of the tables.