Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] using FML with Great Plains (or other GL with no hierarchy)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] using FML with Great Plains (or other GL with no hierarchy)
 Login/Join
 
Silver Member
posted
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,


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Virtuoso
posted Hide Post
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Silver Member
posted Hide Post
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."


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<HarryL>
posted
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
 
Report This Post
Silver Member
posted Hide Post
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


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Silver Member
posted Hide Post
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,


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Silver Member
posted Hide Post
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, $


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
<HarryL>
posted
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.
 
Report This Post
Silver Member
posted Hide Post
"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,


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Silver Member
posted Hide Post
"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


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
<HarryL>
posted
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.
 
Report This Post
Silver Member
posted Hide Post
The hierarchy master file had the parent as A50 and the child as A32. When both were changed to A50 - the rollups worked.


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] using FML with Great Plains (or other GL with no hierarchy)

Copyright © 1996-2020 Information Builders