First - my thanks to everyone on this forum. You have no idea how much you help people who rarely/never post - but glean vast amounts of useful information from the replies to those who do post.


Our general ledger is Great Plains. GP does not use a hierarchy for accounts (for rollup purposes). The grouping of the accounts is strictly a reporting function. For instance "Operating Expenses" is composed of accounts with a second segment of 6000,6001, and 7000. There is no "account code" itself that those accounts roll up into.

I have done some financial reporting using defines to create the grouping – but I would like to use FML. Unfortunately, I am having no luck trying to actually create a hierarchy to use in FML – and I have no idea what else to do.

The hierarchy in the following master file is just for the segments of the account. The first segment denotes location, the second segment is the actual account and the third segment is the department.

My master file looks like this:
FILENAME=GP_ACTUALSANDBUDGETSUMMARYVIEW, SUFFIX=SQLMSS , $
SEGMENT=GP_ACTUALSANDBUDGETSUMMARYVIEW, SEGTYPE=S0, $
FIELDNAME=YEAR, ALIAS=Year, USAGE=I6, ACTUAL=I2,
WITHIN='*Date', $
FIELDNAME=MONTH, ALIAS=Month, USAGE=I6, ACTUAL=I2,
WITHIN=YEAR, $
FIELDNAME=ACCOUNT_CODE_FIRST_SEGMENT, ALIAS='Account Code First Segment', USAGE=I11, ACTUAL=I4,
TITLE='Account Code First Segment', $
FIELDNAME=ACCOUNT_CODE_SECOND_SEGMENT, ALIAS='Account Code Second Segment', USAGE=I11, ACTUAL=I4,
TITLE='Account Code Second Segment',
WITHIN='*Account', $
FIELDNAME=ACCOUNT_CODE_THIRD_SEGMENT, ALIAS='Account Code Third Segment', USAGE=I11, ACTUAL=I4,
TITLE='Account Code Third Segment',
WITHIN=ACCOUNT_CODE_SECOND_SEGMENT, $
FIELDNAME=BUDGET, ALIAS=Budget, USAGE=P21.4, ACTUAL=P10,
MISSING=ON, $
FIELDNAME=ACTUAL, ALIAS=Actual, USAGE=P21.4, ACTUAL=P10,
MISSING=ON, $
FIELDNAME=ACCOUNT_DESCRIPTION, ALIAS='Account Description', USAGE=A51, ACTUAL=A51,
TITLE='Account Description', $
FIELDNAME=CURRENT_BRANCH_NAME, ALIAS='Current Branch Name', USAGE=A50, ACTUAL=A50,
MISSING=ON,
TITLE='Current Branch Name',
WITHIN=CURRENT_REGION_NAME, $
FIELDNAME=CURRENT_REGION_NAME, ALIAS='Current Region Name', USAGE=A50, ACTUAL=A50,
MISSING=ON,
TITLE='Current Region Name',
WITHIN='*Location', $
FIELDNAME=ACCOUNT_CODE, ALIAS='Account Code', USAGE=A11, ACTUAL=A11,
MISSING=ON,
TITLE='Account Code', $
DIMENSION=Dimension1, CAPTION='Dimension1', $
HIERARCHY=Account, CAPTION='Account', HRY_DIMENSION=Dimension1, HRY_STRUCTURE=STANDARD, $
DIMENSION=Dimension2, CAPTION='Dimension2', $
HIERARCHY=Location, CAPTION='Location', HRY_DIMENSION=Dimension2, HRY_STRUCTURE=STANDARD, $
DIMENSION=Dimension3, CAPTION='Dimension3', $
HIERARCHY=Date, CAPTION='Date', HRY_DIMENSION=Dimension3, HRY_STRUCTURE=STANDARD, $

I have a second file that contains the hierarchy. They are just names, not codes except for the actual account code. I had no luck using quick hierarchy on this file. I assumed it was because it was alpha and did not have codes.

FILENAME=GP_CHART, SUFFIX=COM ,
DATASET=CHRIS/GP_CHART3.TXT, $
SEGMENT=GP_CHART, SEGTYPE=S1, $
FIELDNAME=_ACCOUNT_CODE_SECOND_SEGMENT, ALIAS=_ACCOUNT_CODE_SECOND_SEGMENT, USAGE=I6S, $
FIELDNAME=TIER_3, ALIAS=TIER_3, USAGE=A50, $
FIELDNAME=TIER_2, ALIAS=TIER_2, USAGE=A50, $
FIELDNAME=TIER_1, ALIAS=TIER_1, USAGE=A50, $
FIELDNAME=FST__ACCOUNT_DESCRIPTION, ALIAS=FST__ACCOUNT_DESCRIPTION, USAGE=A50, $


So, I created another file in which I made up codes for each level of the hierarchy. The file contains each child with the accompanying parent.

FILENAME=GP_CHART_FINAL, SUFFIX=COM ,
DATASET=CHRIS/GP_CHART_FINAL1.TXT, $
SEGMENT=GP_CHART_FINAL, SEGTYPE=S1, $
FIELDNAME=CHILDID, ALIAS=CHILDID, USAGE=I6S,
WITHIN=PARENTID, $
FIELDNAME=CHILD, ALIAS=CHILD, USAGE=A50, $
FIELDNAME=PARENT, ALIAS=PARENT, USAGE=A50, $
FIELDNAME=PARENTID, ALIAS=PARENTID, USAGE=I6S,
WITHIN='*Level',
REFERENCE=CHILDID, PROPERTY=PARENT_OF, $
DIMENSION=Dimension1, CAPTION='Dimension1', $
HIERARCHY=Level, CAPTION='Level', HRY_DIMENSION=Dimension1, HRY_STRUCTURE=STANDARD, $

When I try to use this file in the "quick hierarchy" program, I get the following error - "No Data Found for GP_CHART_FINAL".
Can anybody point me in the right direction?

This message has been edited. Last edited by: cbrady,

CB

I think your datafile should be a focus database and not a text file.
Create the focus files from the flat file and then I think this would work.

Frank,

Thanks for the suggestion, but, I must still be doing something wrong. Or did you mean for me to try the second file I had listed?

I did create a focus database with this master:
FILENAME=gp1, SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S5, $
FIELDNAME=_ACCOUNT_CODE_SECOND_SEGMENT, ALIAS=E01, USAGE=I6S,
WITHIN=TIER_2, $
FIELDNAME=TIER_3, ALIAS=E02, USAGE=A50,
WITHIN='*Level', $
FIELDNAME=TIER_2, ALIAS=E03, USAGE=A50,
WITHIN=TIER_3, $
FIELDNAME=TIER_1, ALIAS=E04, USAGE=A50, $
FIELDNAME=FST__ACCOUNT_DESCRIPTION, ALIAS=E05, USAGE=A50, $
DIMENSION=Dimension1, CAPTION='Dimension1', $
HIERARCHY=Level, CAPTION='Level', HRY_DIMENSION=Dimension1, HRY_STRUCTURE=STANDARD, $

This was the file I used in the "quick hierarchy" program. This is the resulting error message - "No File Found for GP1 - Error 0."

You definitely need to use either a FOCUS format file or a RDBMS.

If you want to use FML with a hierarchy, I would have used Parent Child relationships.

PROPERTY=PARENT_OF, REFERENCE={account field},

This way you could join to the GL and report with FML using ACCOUNT_CODE.

The master has been set up with dimensions, I would have thought this was more for OLAP type reports.

The issue with the Great Plains Struture is that it is not level based. However, with some manual massaging to convert it to a true level based structure you can use Quick Hierarchy to convert it to a parent child structure. Please let me know if a converted version of the spreadsheet sent to me by Lori would be helpful for you.


Great Plains Structure:
level1 === level2============= level3====================== level4====== desc====
Liabilities Current Liabilities 1234 Accounts Payable

Structure usable by Quick Hierarchy
level1 === level2============= level3====================== level4====== desc====
Liabilities Liabilities
Liabilities Current Liabilities Current Liabilities
Liabilities Current Liabilities 1234 Accounts Payable

Parent/Child Struture

Parent============= Child=============== Caption ============
Liabilities Liabilities
Liabilities Current Liabilities Current Liabilities
Current Liabilities 1234 Accounts Payable

Harry,

That same spreadsheet back in the format that is acceptable to Quick Hierarchy would be terrific. I find it much easier to grasp a visual example and could then be able to use it as the example for creating the other hierarchies we would like to use.

Thanks so much,
Chris

Harry,
Thank you for the example. I created 2 new tables in sql and joined them on the child and now get a lovely hierarchy in FML. But, as no data exists except at the lowest child level - how does anything roll up? For example, "long term assets" has two children - "fixed assets" and "other assets". The children of "fixed assets" are accounts that exist in Great Plains and have actual financial data. If I put "fixed assets" on the row in FML and show children, it lists all the accounts with their "actual" financial data. If I select consolidate, it gives me a number which is the sum of all those lower level accounts. If on the row, I go up one level, and use "long term assets" - show all children will only go down one level - it will not show children down to the level where the where financial data exists. Consolidate will show no financial data. I understand why this would be so - as the "actual" field does not exist in Great Plains for any level other than the lowest level. But - I assumed that was what "consolidate" did - added up all the lower level data.

This message has been edited. Last edited by: cbrady,

Waz,
Thank you for the reply. That master had the dimesions because that was the first requirement for using "quick hierarchy".
The master I am now using is:

FILENAME=CHRIS_TEST_GP_CHART_IBI, SUFFIX=SQLMSS , $
SEGMENT=CHRIS_TEST_GP_CHART_IBI, SEGTYPE=S0, $
FIELDNAME=CHILD, ALIAS=Child, USAGE=A32, ACTUAL=A32, WITHIN='*HIERARCHY1', FIELDTYPE=R, $
FIELDNAME=CAPTIONS, ALIAS=Captions, USAGE=A50, ACTUAL=A50, PROPERTY=CAPTION,REFERENCE=CHILD, FIELDTYPE=R, $
FIELDNAME=PARENT, ALIAS=Parent, USAGE=A50, ACTUAL=A50, PROPERTY=PARENT_OF,REFERENCE=CHILD,FIELDTYPE=R,
MISSING=ON, $

Yes, CONSOLIDATE should roll the numbers up. Please try adding SET ALL=ON to your report. Also, please ensure that you are joining from the parent-child file to your data file. If that doesn't correct the situation, it would be helpful to see the code and a snippet of output.

"Long Term Assets" has two children that do not exist in the Great Plains data. Their children have children that exist in the Great Plains data.

 SET ALL = ON
JOIN
 LEFT_OUTER CHRIS_TEST_GP_CHART_IBI.CHRIS_TEST_GP_CHART_IBI.CHILD IN
CHRIS_TEST_GP_CHART_IBI TO MULTIPLE
 CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW.CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW.CHILD
 IN CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW AS J0
 END
TABLE FILE CHRIS_TEST_GP_CHART_IBI
SUM
     ACTUAL/D12.2CB
FOR
     CHILD
'Long Term Assets' WITH CHILDREN 5 ADD AS CAPTION LABEL R1
HEADING 

OUTPUT:
ACTUAL
Long Term Assets .
Fixed Assets .
Other Assets .

This message has been edited. Last edited by: cbrady,

"Fixed Assets" has accounts under it that are in the Great Plains data.

SET ALL = ON
JOIN
 LEFT_OUTER CHRIS_TEST_GP_CHART_IBI.CHRIS_TEST_GP_CHART_IBI.CHILD IN
CHRIS_TEST_GP_CHART_IBI TO MULTIPLE
 CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW.CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW.CHILD
 IN CHRIS_TEST_GP_ACTUALSANDBUDGETSUMMARYVIEW AS J0
 END
TABLE FILE CHRIS_TEST_GP_CHART_IBI
SUM
     ACTUAL/D12.2CB
FOR
     CHILD
'Fixed Assets' WITH CHILDREN 5 ADD AS CAPTION LABEL R1  

OUTPUT:
ACTUAL
Fixed Assets 150.00
1130 100.00
1135 100.00
1140 100.00
1145 100.00
1230 -100.00
1235 -100.00
1240 -50.00

I get nervous when fieldname references go over 64 and yours (in the join) is 84 characters. Please try some shorter names and re-run. If this does not solve the problem please open a case and ask that I be notified. Thanks.

The hierarchy master file had the parent as A50 and the child as A32. When both were changed to A50 - the rollups worked.