tag:blogger.com,1999:blog-65447538998106989392024-03-28T09:55:31.226-07:00Eric Erikson's Oracle EPM Blogericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comBlogger88125tag:blogger.com,1999:blog-6544753899810698939.post-36731151265403813192024-03-28T09:55:00.000-07:002024-03-28T09:55:00.141-07:00FCC - Using Entities that are Shared in Insertion Rules<p>When working with insertion rules, sometimes there is a need to calculate something only for specific entities. But, if that entity is shared - in other words, exists in multiple hierarchies or has multiple owners - there can be problems. Here's how to deal with this common problem.</p><p>The need most arises with IF statements. The code usually looks something like this:</p><p>IF(@ISMBR("331"))</p><p> do some calcs</p><p>ENDIF;</p><p><br /></p><p>This code will pass validation and deploy successfully, but when trying to consolidate, an error is generated.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCFm_uOALaDoRCsRCb3rXKk6LRyG8OpXHSkysS4orq6AOasGx8VpcEUGplaV6-7tE6H0XCPErBdkdvei3A7C3dEvqQDDCZLsDoW6xrBX-K_pKLlbfnKHJcD4y14G9ATPOd5FOgypwiqy72HPQhIty2d8NGrFxiNcPIz7aFJk_4xizhYwC79vc0h0-7Xu4/s212/badconsol.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="78" data-original-width="212" height="147" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCFm_uOALaDoRCsRCb3rXKk6LRyG8OpXHSkysS4orq6AOasGx8VpcEUGplaV6-7tE6H0XCPErBdkdvei3A7C3dEvqQDDCZLsDoW6xrBX-K_pKLlbfnKHJcD4y14G9ATPOd5FOgypwiqy72HPQhIty2d8NGrFxiNcPIz7aFJk_4xizhYwC79vc0h0-7Xu4/w400-h147/badconsol.jpg" width="400" /></a></div><br /><p>Clicking the link and drilling into the error message will show this:</p><p>Unable to resolve duplicate member name ["331"] on line ____</p><p><br /></p><p>The problem is the rule doesn't know whether to run for [ParentA].[331], [ParentB].[331], etc. One workaround would be to write the IF statement like this:</p><p>IF(@ISMBR("[ParentA].[331]"))</p><p>But if the entity gets moved, then that creates another problem.</p><p>The solution is to use the @EQUAL function. @EQUAL searches the outline for the specified string within a specified dimension. The result looks like this:</p><p>IF(@ISMBR(@EQUAL("331","Entity")))</p><p>If an additional entity existed like 331abc that should not be included, then add an additional condition to the IF statement to exclude that entity.</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-82767685994819238452024-03-14T06:45:00.000-07:002024-03-14T06:57:29.990-07:00FCC Constant Currency<p>An occasional requirement for an FCC implementation is to translate data at multiple currency rates. There's always actuals at the actual rates, but then you can translate actuals at last year's rate, actuals at the forecast rate, actuals at next year's budget rate, etc. These different translations provide the data to show whether operations performed well against the comparative data or did the exchange rate just work in their favor.</p><p>There are a couple of different ways to set this up. The first question is whether to use a scenario or a custom dimension to hold the translation versions. If the requirement includes the balance sheet, is used occasionally, or the customs are already used, then one or more scenarios is the recommended direction. In the case I'm showing here none of those apply, so this example will use a custom dimension. The requirement is for income statement only, the output is used extensively during close and subsequent reporting, and a custom dimension is available.</p><p>After creating additional members in the custom dimension to hold the results, the next step is to create a custom translation rule (as shown below). The top half of the rule shows where to source the exchange rates. In this case, the rates are sourced from scenario Actual for the prior year. The "current period" is specified, so Dec 23 actuals will translate at the Dec 22 exchange rates, Nov 23 at Nov 22, etc. The second half of the screen controls the source and destination members. The left side shows the source data: no constant rate (custom), descendant accounts of net income, and only applies to the actual scenario. On the right hand side is any redirection (target) members. In this case, the results are stored in LastYrRate (so No ConstantRate is translated and then stored in LastYrRate). The account and scenario dimensions do not need redirection, so those fields are left blank so that those members simply pass through.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNGH4WXhWKbsMEnX_-QKXMt5mvmchImu20rHmF5NMu1RAVegdCUoFYBYASPY9daF5KQmUwhgV7T9-RjfcP3SiNOc0B8YoVAwJpka8UR2B5FCmEYXCRWEvkMg7ZKnxKhymSfecS5TTAkpSNlU7b-LrlKyT5vQSLZdXp0fhwMhQ-hfqyUJoj4iuPKD8vBJk/s1258/constantcurrency1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="597" data-original-width="1258" height="304" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNGH4WXhWKbsMEnX_-QKXMt5mvmchImu20rHmF5NMu1RAVegdCUoFYBYASPY9daF5KQmUwhgV7T9-RjfcP3SiNOc0B8YoVAwJpka8UR2B5FCmEYXCRWEvkMg7ZKnxKhymSfecS5TTAkpSNlU7b-LrlKyT5vQSLZdXp0fhwMhQ-hfqyUJoj4iuPKD8vBJk/w640-h304/constantcurrency1.jpg" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div>Once the rule is created it just needs to be deployed to be in effect.</div><div><br /></div><div>So that will take care of the translations. But if the analysis will include operations from the total company's currency, what to do there? Example: if total company is in US Dollars and there are US Dollar entities that roll up to the total with only US Dollar parents, then there is no translation. As there is no translation, the above translation rule will not include that data. This can be addressed by adding an insertion rule to simply copy the data over from No ConstantRate to LastYrRate. Note that the FIX statement should specify level 0 members of the intercompany and other custom dimensions.</div><div><br /></div><div>One note on the @ISUDA function used. IsUSD is a UDA that is applied to the level 0 entities that have the "home" currency. Apparently there is a function that can read the entity currency directly but it isn't visible as of now in Calculation Manager. Oracle has filed an internal SR to expose the function which will remove the requirement to tag the entities with a separate UDA like this.</div><div><br /></div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4OUHeIzHuy0PzfTjP_nVy6tiu5hA6T5tJkuaz-1cvXOWixb1God5MjEWofbsznFXNI3d4DLoMwe6uvCwiaYLx-xUToNuheyzJbRp66TEUXFc70v9Jt621FDmwFtN-XobBnXlwLUtM_fk12Wb9Y5tHk6wbwVnIrb2isVaGtJ9-YYKYCawguH4ZfR3F1bw/s694/constantcurrency2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="225" data-original-width="694" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4OUHeIzHuy0PzfTjP_nVy6tiu5hA6T5tJkuaz-1cvXOWixb1God5MjEWofbsznFXNI3d4DLoMwe6uvCwiaYLx-xUToNuheyzJbRp66TEUXFc70v9Jt621FDmwFtN-XobBnXlwLUtM_fk12Wb9Y5tHk6wbwVnIrb2isVaGtJ9-YYKYCawguH4ZfR3F1bw/w640-h208/constantcurrency2.jpg" width="640" /></a></div><div><br /></div><div><br /></div><div>The custom translation and insertion rule will run with each consolidation. After consolidating, the users can simply switch their POV in reports, Smart View, etc. between the custom members No ConstantRate (for actual numbers) and LastYrRate (for actual numbers at last year's rates).</div><div><br /></div><div><br /></div><div><br /></div>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-58201821219568593982024-01-17T12:21:00.000-08:002024-01-17T12:23:41.132-08:00Data Exchange - DRCRSplit for Delimited Files<p>Recently had to solve a problem during an implementation and as I've done before, I'm adding the solution here so I can find it again.</p><p>An incoming trial balance file is delimited and has two columns for the amount: a debit column and a credit column. Each record will have a value in one of these but not both. If the file was fixed format instead of delimited, there is a built-in import format expression called DRCRSplit that will deal with the two columns. With the function, you specify the midpoint of the overall character count but with delimited files you don't have a consistent midpoint character count, so it doesn't work with delimited files.</p><p>Like maybe some of you reading this, I did a Google search on this topic and found multiple Oracle forum posts, CloudCustomerConnect posts, etc. asking the same question over several years but didn't find an easy solution. There was a posted answer that combined the DRCRSplit function with the Column function, but as pointed out by someone else that solution causes data problems. So, below is what I did. There may be a more elegant way of solving this problem, but the solution worked for me.</p><p>I mapped the debit and credit balance columns to separate attribute fields and for the regular amount field, I mapped the account field to it. Each record needs a numeric amount; otherwise, it will get dropped on the import. I'm thinking the year could be used as the placeholder amount if it were available on each record. I'm guessing that one of the amount fields would probably work in conjunction with the no zero suppress expression.</p><p>[Editor note: if the right sidebar is covering part of the picture, click it to see the full detail]</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhH8nYh_R4HsKPMYbbiA18U3oSZ25hFRZirLQ3xkCc8iNONCdoRhkFIr1quTxj65YLmb5PXu8IUJTbyeWGA3vZpTC2g_0MNxBYD1GeYcUwZbGOYn13GyRW5CPc4v4NcccWTRrp68pXYg0h9RrHdJdTz1PrvGUbSbSNpFiZEJcJ-Wl_p16IVm9C61eMg7f0/s1016/drcr_1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="430" data-original-width="1016" height="270" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhH8nYh_R4HsKPMYbbiA18U3oSZ25hFRZirLQ3xkCc8iNONCdoRhkFIr1quTxj65YLmb5PXu8IUJTbyeWGA3vZpTC2g_0MNxBYD1GeYcUwZbGOYn13GyRW5CPc4v4NcccWTRrp68pXYg0h9RrHdJdTz1PrvGUbSbSNpFiZEJcJ-Wl_p16IVm9C61eMg7f0/w640-h270/drcr_1.jpg" width="640" /></a></div><br /><p>Next, I used a SQL map on the entity to (a) pass the entity through and (b) to populate the amount. Below, the entity portion isn't shown. The AMOUNTX field (the target amount) is populated from the debit column if it is not equal to zero and from the credit column if the debit is equal to zero. Anything placed in an attribute field is stored as a string, so the TO_NUMBER function converts the string back to a number. If there was a need to deal with quotes, spaces, commas, etc. in the number as thousands separators or decimals, that could be addressed with either a format on TO_NUMBER or using one of the REGEXP functions.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikKYhayAkaRUHTOzsbNxMmNADHY1LZIgeDdBIiRPymh4scQtznMFJNdKrU-9qPq50jBcnjHMHj0rkUeOpUJUWHkoOYqV5YJpKhy4dGZgSJyrhgQko-Ty5iTJqUFMGmkKir1CYf6WEB4Ik1yerHpLllLwgiT5VnvjoYYVwa738GQ7Ot5bRr1wooBV8x9Ak/s1076/drcr_2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="412" data-original-width="1076" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikKYhayAkaRUHTOzsbNxMmNADHY1LZIgeDdBIiRPymh4scQtznMFJNdKrU-9qPq50jBcnjHMHj0rkUeOpUJUWHkoOYqV5YJpKhy4dGZgSJyrhgQko-Ty5iTJqUFMGmkKir1CYf6WEB4Ik1yerHpLllLwgiT5VnvjoYYVwa738GQ7Ot5bRr1wooBV8x9Ak/w640-h246/drcr_2.jpg" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div>I attached this SQL to the entity dimension mapping. If the entity dimension needs more mapping and the SQL gets in the way of using the other mapping types (explicits, etc.) then attach the SQL to another dimension, like Data Source. Any of them will work, so the SQL should be placed somewhere inconspicuous.<div><br /></div><div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p></div>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-85826516359667969452023-10-07T07:55:00.002-07:002023-10-07T07:55:27.169-07:00Cloud Customer Connect - the Importance of Upvoting Ideas<p>The #1 best place to go for Oracle Cloud EPM info (yes, better than this blog) is Cloud Customer Connect. It is part community forum, Oracle training, announcements and guidance from Oracle product managers, and the ONE way to submit enhancements or customer requests for new features, etc. This last component is the focus for this post.</p><p>Idea Lab is where everyone can go in and submit an enhancement request and explain why, the business case, etc. Others can review and upvote to indicate their interest in the same thing or downvote if they don't see the need. When I say "others can review," that includes Oracle product managers, who review each idea and, once a direction has been decided by Oracle, will tag for future inclusion, deny, or say the idea is already in the product.</p><p>The upvoting portion of the process is the most important for the community and is probably the least underutilized portion of the process. This is the mechanism for the community to review and indicate their interest in the ideas. Ideas that receive a lot of upvotes are considered more seriously by Oracle as they have more demonstrated appeal.</p><p>Here's an example. In a navigation flow, you can create a new card to Narrative Reporting report, package, snapshot, or book (see below). All that is great. But what would be much more useful would be to create a card that links to a Narrative Reporting folder. That way, for instance, with one click on a card in FCC, users would be able to access all of the FCC reports. With the current ability, the admin would have to create a card for each one. If there are 50, then that would be 50 cards. Add a new report, add a new card. This does not make sense!</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgFJt9TaOJuLcgcH4FA3AP6qXW1Vvl4TfpNRbT16Ks6Uu-5d729cAuOCx-Oj4Thkf4RpQpfwD9XfW2s_5uTH3fILXCBDpPwkdKB4XHAD1I2rXIG6fCPw4WzUPUgkdi1Yimtyy4HY6_2fcWN78z6S_Fk6DTemgtMxl6EAY3GEtnD7JQMLroZL6PoscNUSs/s1015/NavFlow%20-%20link%20to%20NR%20folder.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="320" data-original-width="1015" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgFJt9TaOJuLcgcH4FA3AP6qXW1Vvl4TfpNRbT16Ks6Uu-5d729cAuOCx-Oj4Thkf4RpQpfwD9XfW2s_5uTH3fILXCBDpPwkdKB4XHAD1I2rXIG6fCPw4WzUPUgkdi1Yimtyy4HY6_2fcWN78z6S_Fk6DTemgtMxl6EAY3GEtnD7JQMLroZL6PoscNUSs/w640-h202/NavFlow%20-%20link%20to%20NR%20folder.jpg" width="640" /></a></div><br /><p>In Idea Lab, there is an idea to add a folder to the above list. Here's the link (login with free Cloud Customer Connect account required):</p><p><a href="https://community.oracle.com/customerconnect/discussion/652748/add-narrative-reporting-folder-to-navigation-flow">https://community.oracle.com/customerconnect/discussion/652748/add-narrative-reporting-folder-to-navigation-flow</a></p><p>Down near the bottom is the place to upvote/downvote. To do so, click the "thumbs up" or "thumbs down" icons. Maybe this navigation, which isn't exactly obvious, is the reason there isn't much up/down voting. Once the choice is clicked, it will fill in (here, I have already upvoted this idea). Users can also add comments to explain their support, add additional business cases, add workarounds, etc.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvVebAjNgks6KI0JAzBs5L3lQsYGy1n2Ae4Jg5qCDl9ru9p2sFkJSBBRhPR8PFXIwwIWAFDh4vqRFqsaMhyphenhyphenkirQU5KOnvO_c36vUyOkRl9lZbDXhU-RjIzmhhVQinByVYChnBNm4IgEn5IqPtFuTtjTYB_CzKWxrgr80Y4eTHUImpl5KNLn0LJGexH1nI/s854/upvote.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="232" data-original-width="854" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvVebAjNgks6KI0JAzBs5L3lQsYGy1n2Ae4Jg5qCDl9ru9p2sFkJSBBRhPR8PFXIwwIWAFDh4vqRFqsaMhyphenhyphenkirQU5KOnvO_c36vUyOkRl9lZbDXhU-RjIzmhhVQinByVYChnBNm4IgEn5IqPtFuTtjTYB_CzKWxrgr80Y4eTHUImpl5KNLn0LJGexH1nI/w640-h174/upvote.jpg" width="640" /></a></div><br /><p><br /></p><p>I think that upvoting ideas is highly underutilized by the community. By upvoting ideas that make sense to them, users can add their influence to the idea which will increase the appeal of the idea to Oracle.</p><p>So, get out and vote! </p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-70550053582103477392023-09-07T13:47:00.004-07:002023-09-28T06:32:28.600-07:00FCC - Doing Away with Separate Loads for Balance Sheet and Income Statement<p>TWO UPDATES - Sep 28</p><p>First, I have to apologize - I got the name of the variable wrong. It should be: NewLoadYTDClosingBalanceInput</p><p>Second, Oracle product management posted on Cloud Customer Connect yesterday that the feature isn't working correctly for profit and loss accounts and it will be addressed. No indication on timeline.</p><p><br /></p><p><br /></p><p><br /></p><p>It's been a while since the last blog post so thought I'd do one on a new feature that's everyone has been wanting to see: loading BS and PL data together instead of separately. The funny (well, not funny) thing is that I missed the announcement - it was listed down in the Actions and Considerations section of the release notes instead of in the regular new features section. A friend pointed it out to me and I'm thankful they did.</p><p>The August release (23.08) introduced a change where you don't have to do separate loads for the balance sheet and the income statement in order for the system to reverse out the prior period closing balance. More specifically, the system would reverse the prior period's periodic closing balance and carried over the income statement from the prior year in the first period. Now, the system reverses the YTD closing balance from the prior period and does nothing for the income statement accounts in the first period.</p><p>I tried this out for myself and got the process to work. There are a couple details that aren't obvious in the current documentation, so I'm sharing them here. One is the next line and one is at the bottom.</p><p>First, there is a substitution variable that is required to enable the process: NewYTDClosingBalanceInput.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2VKepWOdi4uxnJxWi1c5QuFmFUwImTJLV6mnPOzJ_RdqCzGDT19UC0tjqzWicLJZQ0zQAhlO9kVJ6HC897V6AS4JRSyp5tgdidXrgCmO25icrrr3KrCs5UcD6IvWp0Qyo0Frg80l4jBw2_YmWuogN9BjXqQF9cJhnwVd9aZbDYGB1EiAxNnB1tlKOjXg/s1058/2load_subvar.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="202" data-original-width="1058" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2VKepWOdi4uxnJxWi1c5QuFmFUwImTJLV6mnPOzJ_RdqCzGDT19UC0tjqzWicLJZQ0zQAhlO9kVJ6HC897V6AS4JRSyp5tgdidXrgCmO25icrrr3KrCs5UcD6IvWp0Qyo0Frg80l4jBw2_YmWuogN9BjXqQF9cJhnwVd9aZbDYGB1EiAxNnB1tlKOjXg/w640-h122/2load_subvar.jpg" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div>Next, set up the metadata so that income statement accounts have the FCCS_Mvmts_NetIncome member assigned as the default movement. The balance sheet accounts should already have the assignment. Note: it is recommended to keep FCCS_Mvmts_NetIncome as a level 0 member and not make it a parent. I thought about making it a parent with the non cash income statement movements as children as well as a regular income statement movement and was advised against it.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmays_MsRBYd5cIYGG9z3Vzdv4KFUiR_P_eFRfDd0pghnNA6yexeRe6_jRH8u8cgsuvScwdwEToSq7e2cSOVK8MLGN2lpq15P7sH7aFFFPpD2jm3txKpXwdG4XGlbmQU6GftQHh8NWeM0c4lcv8bdRJnPZLFbwU4KiVWApw1i5shcSnxP330NWvIgvmbM/s526/2load_tagacct.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="82" data-original-width="526" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmays_MsRBYd5cIYGG9z3Vzdv4KFUiR_P_eFRfDd0pghnNA6yexeRe6_jRH8u8cgsuvScwdwEToSq7e2cSOVK8MLGN2lpq15P7sH7aFFFPpD2jm3txKpXwdG4XGlbmQU6GftQHh8NWeM0c4lcv8bdRJnPZLFbwU4KiVWApw1i5shcSnxP330NWvIgvmbM/w640-h100/2load_tagacct.jpg" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div>Next, in Data Exchange, populate the movement dimension for both balance sheet and income statement accounts with FCCS_ClosingBalance_Input. Here, I'm using a SQL statement to map multiple dimensions at once as it's faster than mapping dimensions separately, but either way works. Note: your movement dimension may not be UD7, so use the appropriate field. And yes, this is the new Data Exchange interface to which we should all be transitioning.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGD6f2upg8cBaB4w9YYdrV0WGu_yWhgSfvpAi5sfBea7s89ttNy_3gSoKn1tp3AK9bNFtxpC_QCE9n_88OehIxvrhzjYoCTWlnaSyw2paRfYUfA-lChOaH52ZA98x8BnRRaxKZtfh5Tkdu-H1FgyYHocpug4RPju5MPsoz1zulVjUtPSTqnPX94lzxrBk/s680/2load_mapping.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="175" data-original-width="680" height="164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGD6f2upg8cBaB4w9YYdrV0WGu_yWhgSfvpAi5sfBea7s89ttNy_3gSoKn1tp3AK9bNFtxpC_QCE9n_88OehIxvrhzjYoCTWlnaSyw2paRfYUfA-lChOaH52ZA98x8BnRRaxKZtfh5Tkdu-H1FgyYHocpug4RPju5MPsoz1zulVjUtPSTqnPX94lzxrBk/w640-h164/2load_mapping.jpg" width="640" /></a></div><br /><div><br /></div><div>Now, when data is loaded and consolidated, the movement and reversals are properly calculated - top two rows have an asset and the bottom two rows have an expense.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3hCv8S3m6IENuQVdRcOthbycaMv8qIrVMpIrAgaCBaXw6P3eWQaotQeGA1wj5DuegFRodu7w9NJMed_hc2MlZJEsjqW_GeDOIYXGy0kFVqlm4RcflzIIK190Mj-ocdx64FfwszWaOkZekQ_DQzm9kXtT7jgEucITSirohSbcJLm9ZxnfDLfB5GgscN00/s702/2load_result.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="157" data-original-width="702" height="144" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3hCv8S3m6IENuQVdRcOthbycaMv8qIrVMpIrAgaCBaXw6P3eWQaotQeGA1wj5DuegFRodu7w9NJMed_hc2MlZJEsjqW_GeDOIYXGy0kFVqlm4RcflzIIK190Mj-ocdx64FfwszWaOkZekQ_DQzm9kXtT7jgEucITSirohSbcJLm9ZxnfDLfB5GgscN00/w640-h144/2load_result.jpg" width="640" /></a></div><br /><div><br /></div><div>I need to do further testing and verification but so far so good. </div><div><br /></div><div>The other not so obvious detail is that the movement member must be somewhere under FCCS_Mvmts_SubTotal. So if you also have statistical data to load, which normally goes to FCCS_No Movement, that will seemingly need to be a separate load.</div><div><br /></div><div>I've already changed the design for two projects to use this feature. The time for the design change and the extra setup should easily be recouped in the quicker loads (one vs two).</div><div><br /></div><div><br /></div><div><br /></div>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-43330372741056515152023-06-01T18:37:00.000-07:002023-06-01T18:37:04.962-07:00Audit Log - Showing Internal References<p> Just a heads up on an issue with the audit log. When a member is moved from one parent to another, the audit log captures that and shows the "from member" and "to member." Only, it doesn't. What shows is an internal reference, which isn't helpful in determining the member labels that are seen in the application.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnCsUNKiJ_ESed3Io4kfBKZnoXIkDpulk5tuDYjfFZnk7a_A1af5NZUP0_yEZ1TIpbGkGRI-8sQ-FWYWl-IKpuUR6KyG1W9xEbBW_PvplSkQjOygLsBBGeLZPFr27HeivCMHsN01_Qyv1wtikQmEAnGJdvak6HT0NxNjy5pwLGJB3qDI2ilSdBJRlN/s775/auditlog.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="321" data-original-width="775" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnCsUNKiJ_ESed3Io4kfBKZnoXIkDpulk5tuDYjfFZnk7a_A1af5NZUP0_yEZ1TIpbGkGRI-8sQ-FWYWl-IKpuUR6KyG1W9xEbBW_PvplSkQjOygLsBBGeLZPFr27HeivCMHsN01_Qyv1wtikQmEAnGJdvak6HT0NxNjy5pwLGJB3qDI2ilSdBJRlN/w640-h266/auditlog.jpg" width="640" /></a></div>Apparently this is a platform bug, so not just related to FCC. It's also not always been this way, so hopefully it gets fixed soon.<br /><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-70074051746128898922023-03-06T13:46:00.002-08:002023-03-06T13:46:29.078-08:00FCC Release 23.03 - Metadata Validation for Movement Members<p>In the 23.03 release of FCC there is a new metadata validation that requires that all members under FCCS_ClosingBalance have a consolidation operator of Addition. On the surface this makes sense; however, it will cause major issues with cash flow.</p><p>It is common for cash flow to require various types of manual adjustments and calculations that are not part of the closing balance calculation. But, if a movement member is not under FCCS_ClosingBalance, then the data does not consolidate.</p><p>So what I and many others have done is to put these members somewhere under FCCS_ClosingBalance with a consolidation operator of Ignore. That way the data consolidates and does not impact the closing balance.</p><p>With the 23.03 release, when a metadata validation is run, this happens:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvuRSQSnkTgEeSQaPepN3G1Rme_OclRdCEh1iHADoGC-OaG16wmRsta_4tTQ7C_8vsKDKgpYC7W15vA7LQahVYg50u7-J9PDdvWrzi_M8kB4K7SNRYf3ESCRWgXv0hJIgyDyYQ6ElDdDyJgHp93wjkPqRffgQfLKZQ-MuNad7TCTr4pXCviBDUgYGW/s928/movement.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="191" data-original-width="928" height="132" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvuRSQSnkTgEeSQaPepN3G1Rme_OclRdCEh1iHADoGC-OaG16wmRsta_4tTQ7C_8vsKDKgpYC7W15vA7LQahVYg50u7-J9PDdvWrzi_M8kB4K7SNRYf3ESCRWgXv0hJIgyDyYQ6ElDdDyJgHp93wjkPqRffgQfLKZQ-MuNad7TCTr4pXCviBDUgYGW/w640-h132/movement.jpg" width="640" /></a></div><p><br /></p><p>I filed an SR for the company I'm currently working with for this issue. Unless Oracle removes the restriction that members must be under FCCS_ClosingBalance to consolidate, I expect that this validation will get pulled back.</p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-50217318188486349872023-02-27T04:00:00.001-08:002023-02-27T04:00:00.186-08:00Oracle EPM: Hierarchy Calculations<p>Most implementations have some statistical calculations, particularly with accounts. There are multiple levels of calculation complexity. Member formulas is usually the go-to place to write these; however, for simple calculations, there's another option. And it has a benefit that member formulas don't have!</p><p>Create an alternate hierarchy with the parent member representing the result and the children representing the inputs. Then use the consolidation operator to add/subtract/multiply/divide or calculate a percentage. In the below example, FCCS_Gross Profit is being divided by FCCS_Gross Sales to calculate a gross margin percentage. And with the percentage option there is no need to multiply the results by 100.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9MmuM9S3-U_tpbwFTLOnOt-qf3QlSrz2KBJvBlD4VFgGwdLLLGVHYkItE61vBnc3n0mIvTiggSplMakEk84rzIt9gmHqpJJKlwZISl2PhOqiaRoTAOlq_ORATmp7OiXhZaakilUPj0U8qvSOdOctpe6fxB8qug3KPVcT1hxuRSxsam5r4yY_n_YUe/s430/gmp.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="120" data-original-width="430" height="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9MmuM9S3-U_tpbwFTLOnOt-qf3QlSrz2KBJvBlD4VFgGwdLLLGVHYkItE61vBnc3n0mIvTiggSplMakEk84rzIt9gmHqpJJKlwZISl2PhOqiaRoTAOlq_ORATmp7OiXhZaakilUPj0U8qvSOdOctpe6fxB8qug3KPVcT1hxuRSxsam5r4yY_n_YUe/w640-h178/gmp.jpg" width="640" /></a></div><p>If the calculation needs parentheses, then create a mid level parent to represent that part of the formula.</p><p>Now, the benefit. A user can view the result on a report, data grid, etc. and then drill down into it to see the components. The result member is just a parent member, after all. So that's helpful.</p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-38061916907904700082023-02-13T16:05:00.007-08:002023-02-13T16:05:53.495-08:00Applying the Most Basic IT Concept to Oracle EPM Cloud: Reset Service<p>There are different reasons why I write blog posts. Sometimes to illustrate something or to talk about the merits of attending Kscope23. You will be going to Denver, CO, USA this June, correct? If you haven't registered, may I suggest going to <a href="https://kscope23.odtug.com/">https://kscope23.odtug.com/</a> and checking it out.</p><p>This one is a reminder that no matter how impossible something seems, there might be the easiest fix for it.</p><p>Another consultant reached out with the following problem: an admin user who is properly provisioned with the service administrator role logs into an environment and doesn't see the admin cards on the home screen. My response was maybe the navigation flow has been customized or maybe the admin is logging in with a native ID instead of the SSO ID or along those lines. Both of which I've seen. They got back to me with a simple answer: they reset the service and everything is fine.</p><p>Resetting the service is like rebooting a computer. You flip the switch off and on, wait a few minutes, and see if the problem clears up. The thing is, how do you reboot an Oracle EPM Cloud environment? You really can't go to the nearest Oracle data center and start flipping power switches (at least I hope not!). Fortunately, Oracle provided a way to do it: EPM Automate.</p><p>Here are the two (well, three steps needed). Either with a command line or a batch script, log into the environment, run the reset service command, wait, and then log out.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidZTWYUjQsOk5VgD6FEmnUfj5-GhRVPMQuPkWnBoa1dHd2lkbeib1BnD2shtdZQy66bPrnj0TYHbFYVsrYaX2IJBCL32fmiU8NMh402JCm9_oRxWz_8AJIAliIVBwPIHLAt8ux9ZID8l_NEybd9e_p-lim_shDPp4fkkws3MNLDhCBa-huJKkalYjq/s659/resetservice.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="175" data-original-width="659" height="170" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidZTWYUjQsOk5VgD6FEmnUfj5-GhRVPMQuPkWnBoa1dHd2lkbeib1BnD2shtdZQy66bPrnj0TYHbFYVsrYaX2IJBCL32fmiU8NMh402JCm9_oRxWz_8AJIAliIVBwPIHLAt8ux9ZID8l_NEybd9e_p-lim_shDPp4fkkws3MNLDhCBa-huJKkalYjq/w640-h170/resetservice.jpg" width="640" /></a></div><br /><p>AutoTune optimizes the database (use for Planning, FCC, and Tax Provision only) and -f skips the confirmation step. Here's the doc link on how to use the resetservice function.</p><p><a href="https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/epm_auto_reset_service.html">https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/epm_auto_reset_service.html</a></p><p>Resetting the service can also be useful when reports don't run, data forms don't open, or just something seems off. I have reset a service for all of these reasons.</p><p>When in doubt about something not working right (or before trying to resolve seemingly impossible problems), reset. It works for personal computers; it works for Oracle EPM Cloud.</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-16978523090400172092023-01-11T09:18:00.004-08:002023-01-12T07:51:08.760-08:00FCC January 2023 Update - DO NOT CHANGE Dense/Sparse!<p>Hello and welcome to 2023! The first blog post this year is a warning regarding the FCC (Financial Consolidation and Close cloud service) update for this month.</p><p>The 23.01 (January 2023) release somehow opened up the dense/sparse settings for the dimensions in the application. Dense and sparse (if a dimension is not dense then it's sparse) relate to how the underlying Essbase database is structured for saving data. Below, the Period and Movement dimensions are set to Dense (checkmark enabled). If your application shows only the Account as dense, see the bottom of this post.</p><p>The dense/sparse column is normally read-only (greyed out) but as you can see the column is editable. Do not make any changes to this! Untold calamities will occur - seriously, there is no telling what will work and not work. Insertion rules, for one, will have problems. Oracle has already worked through the optimal dense/sparse settings, determined the optimal setup, and built the application around them. A fix to put the setting back into read-only mode is under development.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrMU78AVis3Llj8eTykeThFNEuC1scwyazuzKblJ4qya-_kITXuRWDT4-snKoE97PCTKLpX0OUSnkYmnyss5bzoYENTlK_8WqXxUpcwIxRhNQQaJo_a0WArZwJS5NlGOMInoQeSERQtPxPlssUmKxCXoweN1F9KRwHgrxcQaJe28FrWRLqzVqatZV8/s863/11janpost.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="489" data-original-width="863" height="362" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrMU78AVis3Llj8eTykeThFNEuC1scwyazuzKblJ4qya-_kITXuRWDT4-snKoE97PCTKLpX0OUSnkYmnyss5bzoYENTlK_8WqXxUpcwIxRhNQQaJo_a0WArZwJS5NlGOMInoQeSERQtPxPlssUmKxCXoweN1F9KRwHgrxcQaJe28FrWRLqzVqatZV8/w640-h362/11janpost.jpg" width="640" /></a></div><br /><p>If your application shows only the Account as dense, then consider running the migration to make Period and Movement dense. There are several benefits, the biggest being the consolidation time will be a lot quicker.</p><p>There are several steps to do. The process is not hard, but should be done in test to confirm, particularly if insertion rules exist. Here is the link for the documentation (note if two rows of text show this is one link that is wrapping on the screen).</p><p>https://docs.oracle.com/en/cloud/saas/financial-consolidation-cloud/agfcc/migrating_movement_period_dense.html</p><p><br /></p><p><br /></p><p><br /></p><p> </p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-16506661747601470852022-09-23T07:18:00.002-07:002022-09-23T07:18:40.408-07:00ARCS - Need more data storage?<p>More than the other Oracle Cloud EPM applications, Account Reconciliation ("ARCS") has the most need for file attachments. For each recon, there could very will be a requirement to upload a bank statement or other supporting documentation. If these are stored in the regular EPM database, it can quickly use all of the available space, make snapshots larger (since the attachments are included), increase the time to clone production to test, etc.</p><p>Fortunately, Oracle has thought about this and has a solution ready. Instead of storing the attachments within the ARCS environment, attachments can be stored in Oracle Cloud Infrastructure Object Storage. Basically (and I'm sure that I'm oversimplifying this) it's like having box.com on steroids. The easy part of this is that the product is built to use it, so once it is configured the users do nothing different and have the same experience.</p><p>To enable, go into System Settings and on the left click Object Storage. Provide the URL, ID, and password and click Validate and Save.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1t_1RGZEXVMwFIR187phjthzxfW_9p52S4GfeCkyxqOEnMZbVy6fJ3U9Oovx5JmHiFuN7wgCWHc--cvP4e5lEATt1PzOw3NALpNhDVqBayPMkZHvzTfGsEJ40fLujhHTkvjDhF6SaK3R0RqBqL5_kf023qHps6RGATylj7BpqApz2eit_Y6sYC4SI/s2250/arcs_objectstorage.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="926" data-original-width="2250" height="264" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1t_1RGZEXVMwFIR187phjthzxfW_9p52S4GfeCkyxqOEnMZbVy6fJ3U9Oovx5JmHiFuN7wgCWHc--cvP4e5lEATt1PzOw3NALpNhDVqBayPMkZHvzTfGsEJ40fLujhHTkvjDhF6SaK3R0RqBqL5_kf023qHps6RGATylj7BpqApz2eit_Y6sYC4SI/w640-h264/arcs_objectstorage.jpg" width="640" /></a></div><br /><p>Once enabled, a migration process moves existing attachments to the object storage. Any new attachments automatically go there. The bottom part of the screen above, Attachments and References, provides information about the items in Object Storage.</p><p>Another benefit of switching to OCI Object Storage is that the administrator can choose to increase the maximum file attachment size from 20MB to 100MB.</p><p>In case you're wondering, yes, there is a cost. The cost is US$0.0255 per GB per month. So 150GB of storage would cost $45.90 per year.</p><p>If attachments are not a huge part of account recs, then this probably isn't needed. But if attachments are a way of life, then this option should be considered.</p><p><br /></p><p>Here is the pricing link for details: https://www.oracle.com/cloud/storage/pricing/</p><p>Here is the link in the help regarding setup: https://docs.oracle.com/en/cloud/saas/account-reconcile-cloud/suarc/setup_object_storage_attachments_config_arcs.html</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-83764877828650551972022-05-17T09:57:00.001-07:002022-05-17T10:00:41.133-07:00Consolidation Dimension, DSO, and Solve Order<p>With FCC the change to DSO (where the movement and period dimensions are dense) really speeds everything up. One area that has still seemed slow involves QTD and YTD reporting. The fix for this is to adjust the solve order for the consolidation dimension. All of the dynamic parents should have a solve order of 51 (not 50 or 52 but 51).</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-YC2TbQYcRlw1SiBknlIiXy_X21lvBzOVtO-Q-4JTKjoouV47g8dBhHZY3D7naAArujgip43sRX-lvoufDsSY9fIDViegm6KG4RobWgUep4_nzNWt09eQyuUe68L9xWajTy1pNLwTF0oNzw8XFxSvEM5YGJt68Sc-wo6HTOd9UI-BJfV6qPJKbKB_/s661/solveorder.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="347" data-original-width="661" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-YC2TbQYcRlw1SiBknlIiXy_X21lvBzOVtO-Q-4JTKjoouV47g8dBhHZY3D7naAArujgip43sRX-lvoufDsSY9fIDViegm6KG4RobWgUep4_nzNWt09eQyuUe68L9xWajTy1pNLwTF0oNzw8XFxSvEM5YGJt68Sc-wo6HTOd9UI-BJfV6qPJKbKB_/w400-h210/solveorder.jpg" width="400" /></a></div><p><br /></p><p>This change is scheduled to be pushed to the pods with the June 2022 update.</p><p>In case you think I have a crystal ball or a hotline direct to the development team, you're right, I do. But actually I found out about this on a cloud customer connect post to the FCC forum. So even if you don't have a question to post, there are still benefits to following the forum and finding out what others are saying.</p><p>By the way, here's the original cloud customer connect post. See the bottom of the responses for the details.</p><p>https://community.oracle.com/customerconnect/discussion/610242/making-period-and-movement-dense-report-performance-issues-with-qtd-ytd-views#latest</p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-2326249823066903142022-04-28T09:16:00.007-07:002022-04-29T10:27:46.701-07:00Provide Feedback Not Working with 22.04<p>When there is an issue with Oracle EPM Cloud, one of the steps to work with Oracle support is to use the the provide feedback process to generate a UDR for the environment (which basically lets Oracle support look at the snapshot). But what if that doesn't work? What do you do?</p><p>With the 22.04 release, we found out. Users go through the steps, get to the window below, type in whatever or highlight parts of the screen, and then click Submit. What should happen is a confirmation window pops up with the option to allow the snapshot access and the final submit button. But for some users, the process would stop and go no further - no error, no message, no anything. </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2zdx-b9X6Od9TJQtOs_fetpeEJoZIXsx-3RCV7PeqC1oiSxJlVaBfaaPew3RKWzQuGSRGP98rJUX5-q1QJXqza3werdyxr2NnzvLp9E0BDYuctty5h35S1u7Uv1uiForviwd8dmbZJKhmQKBtY6hInERW7KwKtv5uQfDYrZzzqEbf0KABMFi4IASX/s551/feedback1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="195" data-original-width="551" height="141" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2zdx-b9X6Od9TJQtOs_fetpeEJoZIXsx-3RCV7PeqC1oiSxJlVaBfaaPew3RKWzQuGSRGP98rJUX5-q1QJXqza3werdyxr2NnzvLp9E0BDYuctty5h35S1u7Uv1uiForviwd8dmbZJKhmQKBtY6hInERW7KwKtv5uQfDYrZzzqEbf0KABMFi4IASX/w400-h141/feedback1.jpg" width="400" /></a></div><br /><p>It turns out that 22.04 has an issue with provide feedback if the environment uses an external URL for the background image. Go to the Appearance screen and check the background image. If the background image is a URL, then remove it. Changing to an uploaded file also solves the problem. Click Save and now provide feedback will work.</p><p><br /></p><p>Obviously this is being worked on and should be addressed soon.</p><p>UPDATE: I heard via Oracle support that this will be addressed in the May 2022 release.</p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-54244579582572857482022-04-15T06:00:00.049-07:002022-04-15T06:00:00.199-07:00Using EPM Reporting Books with Smart View - Part 2<p>The previous blog post described the new Smart View extension to bring Oracle EPM Cloud books into Microsoft Excel using Smart View and walked through the installation steps. Please refer to that post before proceeding with the below.</p><p>This post will walk through how to use the extension to work with books. This new capability works with FCC, Planning/EPBCS, Tax Reporting, and Narrative Reporting. Note the books must be new Reporting books and not the old Financial Reporting books. Just another reason to migrate from FR. </p><p>Connect to the application using the Smart View panel and click on the application ID. At the bottom of the screen a new item called Launch EPM Books Home will appear. If this does not show, click More>> and see if it is listed there. If not, then go back to the previous blog post and check the extension installation.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQW-kMPunZry2ZSaGzLJyxnlIwv6v-8aQ-dC5dHfQeMDDp_AJ1pCu4Lk-QOrHHjqnz-nwK83FXixLlMD8RlSuyZ27BGFCTdcvwf0oxkJfhqhMbj8dPDiRjIJLaidoky1xpYmaF8F9amndQgcdlHH7aURQxQvW0fdbcdpjPwBSRb4-cUUaYtJzh3MIJ/s548/extension5.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="266" data-original-width="548" height="194" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQW-kMPunZry2ZSaGzLJyxnlIwv6v-8aQ-dC5dHfQeMDDp_AJ1pCu4Lk-QOrHHjqnz-nwK83FXixLlMD8RlSuyZ27BGFCTdcvwf0oxkJfhqhMbj8dPDiRjIJLaidoky1xpYmaF8F9amndQgcdlHH7aURQxQvW0fdbcdpjPwBSRb4-cUUaYtJzh3MIJ/w400-h194/extension5.jpg" width="400" /></a></div><br /><p><br /></p><p>Once opened the extension will list the available books. The lower half of the screen is the job console. This section will be used after running a book.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYnMhDSB28tzsoB-3yV7W8YYKnOypDGu2RE2dPNlveE3gpGXoqcejzBZf1sF2DflO_Qh2TXJa6I9wM_VMYD4qEZ8ckzGHs6c3fw7D4A61cWlu_Yy0IxOTEowCSet4Uoy6LYjAjupp7EMBuuuS-lctBP11aPe42yFLUDhLAzFgtwUtBe5ws4nGUmBd1/s1028/extension6.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="812" data-original-width="1028" height="316" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYnMhDSB28tzsoB-3yV7W8YYKnOypDGu2RE2dPNlveE3gpGXoqcejzBZf1sF2DflO_Qh2TXJa6I9wM_VMYD4qEZ8ckzGHs6c3fw7D4A61cWlu_Yy0IxOTEowCSet4Uoy6LYjAjupp7EMBuuuS-lctBP11aPe42yFLUDhLAzFgtwUtBe5ws4nGUmBd1/w400-h316/extension6.jpg" width="400" /></a></div><br /><p>Clicking the info bubble to the left of a book will show a few facts about the book.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi52UF_MqukOSTqpo7Rf0jd6LlsdrIljuYu1lhWYh0dAoOh-NkxB_8_uSBrC5GlW5TIMMfi--c9cRx_qocmUIzwG1vLq-Q2XtAOXxmcWI2IF5nXuHae97oGhp26eXr3GLXLWfdKCIYXqnlvpL46l_Ri4cuW4eGRPzAQ4O4lHKfjeYa2Hc1FD0qakqU6/s612/extension7.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="338" data-original-width="612" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi52UF_MqukOSTqpo7Rf0jd6LlsdrIljuYu1lhWYh0dAoOh-NkxB_8_uSBrC5GlW5TIMMfi--c9cRx_qocmUIzwG1vLq-Q2XtAOXxmcWI2IF5nXuHae97oGhp26eXr3GLXLWfdKCIYXqnlvpL46l_Ri4cuW4eGRPzAQ4O4lHKfjeYa2Hc1FD0qakqU6/w400-h221/extension7.jpg" width="400" /></a></div><br /><p>Click the Run icon to the left of a book to run it. Running the book will show as a job at the bottom panel. Click Refresh as needed until the processing is complete. There are also tool buttons to remove a single job or all jobs from the display.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4uXbAWdgiE7JxqCwC2mD5tIrXbE9hW-VpO54KwFtasrNS-5FZ5sD4puznngJRkp5WFbnvSH0R5c-_xd-PLeRLaw2xyzPGmIiFp95d2WDWxdJ93TNOhdR3fXX-_hJ3SSbCJ9HkeqfW2QluU5kEfJQx_pbBxJfOSeBTBpcqli_HRyq07Nw10MeZ_y0i/s990/extension8.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="266" data-original-width="990" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4uXbAWdgiE7JxqCwC2mD5tIrXbE9hW-VpO54KwFtasrNS-5FZ5sD4puznngJRkp5WFbnvSH0R5c-_xd-PLeRLaw2xyzPGmIiFp95d2WDWxdJ93TNOhdR3fXX-_hJ3SSbCJ9HkeqfW2QluU5kEfJQx_pbBxJfOSeBTBpcqli_HRyq07Nw10MeZ_y0i/w400-h108/extension8.jpg" width="400" /></a></div><br /><p>When the processing is completed, click the download icon in the Status column to download. Click Yes if prompted to open the workbook.</p><p>The table of contents will be shown on the first tab. The reports will show on subsequent tabs. The table of contents lists the reports with clickable links to the right (not shown below) to each tab.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsxqENmGmDDJcMjSHxjfocEZxxZlNz237ZQAhwD4TfahOtVFmhIp2zf267tQdaZHTtN5yRthZv_qZg9MYD9UAYu6koL22eRsP633Yll8Jjza_gk_sLL2nYd5R0OqOa3BsrDdMDeSB2AgwxGvawJjTjy6IZeXAUoVIv-cO8M-Hz6oUqvkC-hPf95iWx/s1068/extension10.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1068" data-original-width="730" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsxqENmGmDDJcMjSHxjfocEZxxZlNz237ZQAhwD4TfahOtVFmhIp2zf267tQdaZHTtN5yRthZv_qZg9MYD9UAYu6koL22eRsP633Yll8Jjza_gk_sLL2nYd5R0OqOa3BsrDdMDeSB2AgwxGvawJjTjy6IZeXAUoVIv-cO8M-Hz6oUqvkC-hPf95iWx/w274-h400/extension10.jpg" width="274" /></a></div><br /><p>Going to the other tabs will show the different pages. In my early testing the rows and columns of the report grid are parsed correctly into cells. The report header (not shown below) comes in as a graphic instead of text into cells. The reports in the books I have do not have footers so would have to assume footers would be treated as a graphic as well.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEino3l5E4wRU2qT1HujDbEr6U1-H0C-JmyCUgBvd9PQXYcYi_AVRxK54NG3vLZkDASp9WgDfXNWFBWXYb5YtJMtD52vw-KOhFDLCwxvw5RNFJ6OVoQTeVgmiANgBLEtsS2JoBzY-0PhgL-l4Xk2N_Uf0P3re5vEJlWIFRYgMheKQ_iI5uBSzh4Yv_lN/s872/extension11.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="462" data-original-width="872" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEino3l5E4wRU2qT1HujDbEr6U1-H0C-JmyCUgBvd9PQXYcYi_AVRxK54NG3vLZkDASp9WgDfXNWFBWXYb5YtJMtD52vw-KOhFDLCwxvw5RNFJ6OVoQTeVgmiANgBLEtsS2JoBzY-0PhgL-l4Xk2N_Uf0P3re5vEJlWIFRYgMheKQ_iI5uBSzh4Yv_lN/w400-h213/extension11.jpg" width="400" /></a></div><br /><p>At this point there is a new "Books" ribbon showing. There are three options: Refresh, Edit POV, and Inspect.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUZIL8hysPEFfuRUQSuuLjqo0plhjbuf1oQLUIknpWSYGO-BARmmx7-fE-pz_IloathSMkl7eas9w5UkTp95si0iKTZyF0bFpumF3Xct4BGTzDBKuBTzi9Jrab6gRTgjVicbCg1y-vky9s_W7Qnq49c-3G7IUqtSFEDb3X_kOAnVube6RpLfARCrME/s298/extension15.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="222" data-original-width="298" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUZIL8hysPEFfuRUQSuuLjqo0plhjbuf1oQLUIknpWSYGO-BARmmx7-fE-pz_IloathSMkl7eas9w5UkTp95si0iKTZyF0bFpumF3Xct4BGTzDBKuBTzi9Jrab6gRTgjVicbCg1y-vky9s_W7Qnq49c-3G7IUqtSFEDb3X_kOAnVube6RpLfARCrME/w400-h298/extension15.jpg" width="400" /></a></div><br /><p><br /></p><p>Refresh is self-explanatory. The Inspect option shows the same information as above along with the POV used for the book.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhte1tkrqyDhPFw_QErpXuUeDDFT5VFXOH0BU6hJ2xgNQRpxAjDYACTVejiI5jcy8lb7M6-2t-pL4NH1q4GAyR1P2nz6TrVIUHiISl2dedDUWn3mqKlahe6WTbaCW9WcpVM5e_M4MkwiWiVgc4uqLe_JGnfgoYJtDMD35klQNb6zJbW-UFNj5DNzGW-/s728/extension12.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="728" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhte1tkrqyDhPFw_QErpXuUeDDFT5VFXOH0BU6hJ2xgNQRpxAjDYACTVejiI5jcy8lb7M6-2t-pL4NH1q4GAyR1P2nz6TrVIUHiISl2dedDUWn3mqKlahe6WTbaCW9WcpVM5e_M4MkwiWiVgc4uqLe_JGnfgoYJtDMD35klQNb6zJbW-UFNj5DNzGW-/w400-h198/extension12.jpg" width="400" /></a></div><br /><p>As shown above the book ran for Dec FY19 Periodic. This was my active POV in FCCS (when I ran the report book last through the website it was for Dec FY19 Periodic). The middle option on the ribbon, Edit POV, suggests that the POV can be changed. After clicking it, for each dimension dropdown, there is an option to select. </p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDi8jAV9C6dxIqkxEXkgheea5ds5Qf2zLV1xxbN5n3zKUdx5pGahebMhQvp6YptydmderUXRNIUw078LrH1WA00Z_0Dey43JR38I41dn4WyN9K9VTZ06z9Dj6qtHqhBT9Y1Tc1FtV_A4vBVSz3C3MEaMUvkm6_OGhEg2fuSGmsCuRJzJxAjvP-AbdN/s854/extension13.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="364" data-original-width="854" height="170" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDi8jAV9C6dxIqkxEXkgheea5ds5Qf2zLV1xxbN5n3zKUdx5pGahebMhQvp6YptydmderUXRNIUw078LrH1WA00Z_0Dey43JR38I41dn4WyN9K9VTZ06z9Dj6qtHqhBT9Y1Tc1FtV_A4vBVSz3C3MEaMUvkm6_OGhEg2fuSGmsCuRJzJxAjvP-AbdN/w400-h170/extension13.jpg" width="400" /></a></div><br /><p>When I tried clicking Select..., I got a message that "Member selection not supported." According to the documentation, editing the POV should work. Also, if the user has enabled previewing the report POV in User Preferences on the website, then the POV prompts should appear when initially running the book. The software did correctly identify the dimensions in the POV. The books I'm using have Default as the only member selection in the book POV and are not limiting the members that the user can select. So that may have something to do with it. As I'm working with an early version, I'll assume that this will get cleaned up.</p><p><br /></p><p>Here is the link to the product documentation.</p><p>https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/svuuc/books.html</p><p><br /></p><p>In conclusion, all of us can finally rejoice that report books can be brought into Microsoft Excel. Again, it requires migrating from Financial Reporting to Reporting and the quick installation of a Smart View extension. Looking forward to using this at clients.</p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-30407991789831667382022-04-14T08:26:00.000-07:002022-04-14T08:26:36.079-07:00Upcoming Enhancement to Clear Data Profiles<p>There is a need, sometimes, to clear data. Whether something got loaded wrong or whatever, there is a need. The clear data screen, below, is almost perfect: enter dimension members and then run the clear. There is one thing lacking. If you look closely at this screenshot, you'll notice the period and year dimensions are trying to use substitution variables. Currently, this does not work. You have to specify the period(s) and year(s) being cleared. This requirement makes clearing data somewhat useless in an automated process. </p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjb4DTz_LsIH-kgyz-r4nkt2pg88beQDjKHch06via_6kWGK71LvpM4qqivstGp0huVXs00CoCVXpmOlBpvEgippmTp8wX6WwDdNfz7_Wh0MVd9q4f8SIugyZEWqfbj_OoAH1gnbhai4lusCBfO_J9rVj6yLohfJIn0SqgQiV9r_15CTfk283_5W6Bk/s995/cleardata1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="538" data-original-width="995" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjb4DTz_LsIH-kgyz-r4nkt2pg88beQDjKHch06via_6kWGK71LvpM4qqivstGp0huVXs00CoCVXpmOlBpvEgippmTp8wX6WwDdNfz7_Wh0MVd9q4f8SIugyZEWqfbj_OoAH1gnbhai4lusCBfO_J9rVj6yLohfJIn0SqgQiV9r_15CTfk283_5W6Bk/w400-h216/cleardata1.jpg" width="400" /></a></div><p><br /></p><p>In case you're wondering, where this ability will be the most helpful with automation is the ability to clear a period before loading new data. Even if the data is loaded in replace mode, if an entity was loaded to the period in the past and is no longer in the data load, then the system does not know to clear the data. By clearing the period first, then this would no longer become an issue.</p><p>There is good news. The idea of allowing the use of substitution variables was created on Cloud Customer Connect back in September 2020 and then a duplicate submission was done in March 2022. On April 14, 2022, there was an update to the March idea. "Should be coming in the next few months" was the update. I assume the normal safe harbor statement applies.</p><p>This is welcome news and I look forward to using it. And don't think that the substitution variables have to be manually updated. EPM Automate can populate the substitution variables and then run the clear data profile, so this will be a great addition.</p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-65953099776357118842022-04-12T13:26:00.006-07:002022-04-14T08:58:35.404-07:00Using EPM Reporting Books with Smart View - Part 1<p>One of the Oracle EPM product features that has been requested for <i>decades</i> - yes, decades - is to bring a report book into Microsoft Excel. With the April 2022 update this can FINALLY be done. Granted, the report book has to be the new Reporting book and not the old Financial Reporting book. But think of it as one more reason to migrate from Financial Reporting to Reporting.</p><p>This new feature works for FCC, Planning, Tax Reporting, and Narrative Reporting.</p><p>Using the feature requires enabling a new Smart View extension that is being released with the April 2022 update. This blog post will cover how to install the extension. A future blog post will cover how to use it.</p><p>To install the extension, go into Microsoft Excel and go to the Smart View ribbon. Click Options and then go to the Extensions panel.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9dKF9twi2ASZxY-vaYXmO1vUHFBHnkwWFSVCgcI50Iye-rJMmM4sNQQ1BJPC-3b-QgolX9VFKdTQF_I47B4IPG5BPyG9TNFOo-Z2nIM-NCdEuFXI9zx4JStxP2d5FgbUhaEFST2f3LMbD6QW0EL8dlctQZO0RiHbCzADmgPYupFolVXMnOEvP7qhq/s1032/extension1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="626" data-original-width="1032" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9dKF9twi2ASZxY-vaYXmO1vUHFBHnkwWFSVCgcI50Iye-rJMmM4sNQQ1BJPC-3b-QgolX9VFKdTQF_I47B4IPG5BPyG9TNFOo-Z2nIM-NCdEuFXI9zx4JStxP2d5FgbUhaEFST2f3LMbD6QW0EL8dlctQZO0RiHbCzADmgPYupFolVXMnOEvP7qhq/w400-h243/extension1.jpg" width="400" /></a></div><div><br /></div><div><br /></div>The screen above shows EPM Books already listed but your screen will not. Click the blue "Check for Updates, New Installs, and Uninstalls" link at the bottom. When prompted, select the appropriate environment. Here, I'm using a FCC test environment.<div><br /><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdR1ZdFBevn3iltKwOy3q1dyGaQYd6jkGLxSUGUOrbYQyXKhjf7MvPZTguvzG-gaOmkum8uAg-VsujSyTlGGl1-OCGsyGXVaEsznI95KFHjohFjsh5GYktbF3EixYna-DQrQaehiXfByIglEd5bADSDMN5WvajGQ9qDXcFQ-FH1voVaOsjzEyigEt7/s368/extension2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="308" data-original-width="368" height="335" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdR1ZdFBevn3iltKwOy3q1dyGaQYd6jkGLxSUGUOrbYQyXKhjf7MvPZTguvzG-gaOmkum8uAg-VsujSyTlGGl1-OCGsyGXVaEsznI95KFHjohFjsh5GYktbF3EixYna-DQrQaehiXfByIglEd5bADSDMN5WvajGQ9qDXcFQ-FH1voVaOsjzEyigEt7/w400-h335/extension2.jpg" width="400" /></a></div><div><br /></div><br /><div>Once the update is done, an item for EPM Books will be shown. Click Install on the right to install the extension. Note that administrator level access to the computer should not be needed for this. </div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgitdf9_MOS9OdsL8Vp3e1cw6A7VdR8jVvdQlYtw8yXXHJRpv_qMCLKhvzikFGGEaAE_GmImI0b89i64lhaUNowol2ftpRruU7iSHKHsUQlJnND0MOTtU0_dwAmLKbAq9oVbRbbAcb3lg_X7y6eJ-ANBlgj11Hp86Ufm87LcwmIoi-1jT2If3vjhK-E/s1048/extension3.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="238" data-original-width="1048" height="91" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgitdf9_MOS9OdsL8Vp3e1cw6A7VdR8jVvdQlYtw8yXXHJRpv_qMCLKhvzikFGGEaAE_GmImI0b89i64lhaUNowol2ftpRruU7iSHKHsUQlJnND0MOTtU0_dwAmLKbAq9oVbRbbAcb3lg_X7y6eJ-ANBlgj11Hp86Ufm87LcwmIoi-1jT2If3vjhK-E/w400-h91/extension3.jpg" width="400" /></a></div><div><br /></div><div><br /></div><div>Once the installation is done, quit Microsoft Excel (the installation process will remind you) and restart. Now, use the Smart View panel to connect to the application. Once the application ID is clicked upon, a new link will show for launching EPM Books. Click More>> if the link does not show.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxdt45T_X_zIZyjY-n-ROHLZCTS6avtek6CjzyvRnCkhu8pYMISwM0mCTg4N1qHNddl6FYEwzA1wHcMVAoRdtz2_LHXuXFLC8MXCq7NjWp9px69PimDswJav0Ab4EYfJ3AfBd5WuiRAK3nFd86keoNCJSPiVkUQeb_K_5NWo3qom7vdvM4lil7LCKp/s548/extension5.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="266" data-original-width="548" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxdt45T_X_zIZyjY-n-ROHLZCTS6avtek6CjzyvRnCkhu8pYMISwM0mCTg4N1qHNddl6FYEwzA1wHcMVAoRdtz2_LHXuXFLC8MXCq7NjWp9px69PimDswJav0Ab4EYfJ3AfBd5WuiRAK3nFd86keoNCJSPiVkUQeb_K_5NWo3qom7vdvM4lil7LCKp/s320/extension5.jpg" width="320" /></a></div><br /><div><br /></div><div>The next blog post will go through how to use the Smart View extension to bring books into Microsoft Excel.<br /><p><br /></p></div></div>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-31423689888381134072022-04-05T11:18:00.007-07:002022-04-05T12:33:43.274-07:00FCCS - Parent currency intercompany matching without running translation<p>So, there is a question that comes up with the intercompany matching report. We load data and consolidate. We have entities with multiple currencies and all of the parent entities are a single currency. Why then is there a need to also run a translation step in order to compare all of the multiple currency entities in the same common currency as all of the parents?</p><p>Looking at the intercompany matching report, the solution looks easy. Change the Currency dimension to Parent Currency. It works for regular reports, right? Here's what happens when the report is executed.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx4Bgur9na4GUFszC5k1_shWCkEYIadw1MVaWIooachji3lrJUwr-NBipnZyJpbhbvD7rES_gYvG7FwIX8Q3JNv_XOBirvcl7BcKfLzY-bcwg97x4u_oX2uUDXlO9Zap9fejCaXOCcjZyF6SsQ4kGcep0LKli6KRACRRHlNzRvJaLsfHAfRzQXnCg1/s430/icmatch1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="164" data-original-width="430" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx4Bgur9na4GUFszC5k1_shWCkEYIadw1MVaWIooachji3lrJUwr-NBipnZyJpbhbvD7rES_gYvG7FwIX8Q3JNv_XOBirvcl7BcKfLzY-bcwg97x4u_oX2uUDXlO9Zap9fejCaXOCcjZyF6SsQ4kGcep0LKli6KRACRRHlNzRvJaLsfHAfRzQXnCg1/s320/icmatch1.jpg" width="320" /></a></div><p>You can change the Currency dimension to a reporting currency, but then the translation process is required. If the Currency dimension is left at Entity Currency, then each entity will be compared in its local currency, which is not helpful at all.</p><p>So, what can be done?</p><p>Create a member in one of the custom dimensions but not under any hierarchy, like a sibling of Total Custom or whatever. Tag it as dynamic calc. Then, create a member formula as shown below.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfHV9MX-zyKPdUSwMBo5uOunJaGnb0rH6gV0_nmfW_vW7ZZRDefMA6kJdYYs99P9OlxXhyYYeUIYhL-1ZNc7YZcaPoUS7MmeKrFa-5Ak5AHWY1c-Aq-W_PJ2jzMfS1lVteeO8ils6Zb3T-4U4Pb3brh5qJ5o-tHbQPw-JzYbFA2j_KUgFUxECJ9xOL/s280/icmatch2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="63" data-original-width="280" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfHV9MX-zyKPdUSwMBo5uOunJaGnb0rH6gV0_nmfW_vW7ZZRDefMA6kJdYYs99P9OlxXhyYYeUIYhL-1ZNc7YZcaPoUS7MmeKrFa-5Ak5AHWY1c-Aq-W_PJ2jzMfS1lVteeO8ils6Zb3T-4U4Pb3brh5qJ5o-tHbQPw-JzYbFA2j_KUgFUxECJ9xOL/w400-h90/icmatch2.jpg" width="400" /></a></div><p>This formula pulls the parent currency data of the top level custom dimension member into entity currency for this calculated member. Change "Total Custom" to be the top of the hierarchy.</p><p>Last, in the intercompany matching report setup, use this calculated member for that custom dimension and set the Currency dimension in the POV to Entity Currency. Running the report with this configuration, the parent currency data will be shown, which is perfect and the translation rule process can be skipped. </p><p>The report header will still say "Entity Currency" although the header will also list the new custom member as well. A small price to pay to skip the translation step!</p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-26235379749480955872022-03-27T09:24:00.001-07:002022-03-27T09:24:24.053-07:00EPM Automate - System Status Website<p>As companies implement Oracle EPM Cloud components (FCC, Planning, ARC, etc.), EPM Automate is almost always a part of the process. Typically with data loads and backups, which all of the components need, but also restructures, database refreshes, and more. Along with the growth in these scripts is the need to communicate to users and admins the status of everything. Each script can send an email or a text with the status of each run. But with hourly data loads, backups, restructures, regression tests, etc. the number of emails can easily become overwhelming. An email or text should be sent if there are problems, but if things are going well, is a deluge of emails really the right solution?</p><p>As an alternative, a simple webpage can do the trick. Since EPM Automate typically runs on a server, the server can also serve up a webpage with the status of each job. </p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWMcqDQDcxvDkvnbY-q0hfcxcRHPmwFTRP6-tRmav1sc1WODlc4wZawhqWk-fVchbqgUt_mzSOV9_GsgRQtjLDHy6eIzZLpqlzBMdlxGvvz9Lx5HT4Sd1rOgtfbCN5XcK4Ok3eoR6Zp4zRQK1rbMxXQ_5qln5UIj1cJ1LmZy0iIRZrBmPQCJ8hLa0I/s1200/website.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="654" data-original-width="1200" height="217" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWMcqDQDcxvDkvnbY-q0hfcxcRHPmwFTRP6-tRmav1sc1WODlc4wZawhqWk-fVchbqgUt_mzSOV9_GsgRQtjLDHy6eIzZLpqlzBMdlxGvvz9Lx5HT4Sd1rOgtfbCN5XcK4Ok3eoR6Zp4zRQK1rbMxXQ_5qln5UIj1cJ1LmZy0iIRZrBmPQCJ8hLa0I/w400-h217/website.jpg" width="400" /></a></div><p><br /></p><p>The basic steps are to have each script write the status to a web page. Then use an overall web page that incorporates all of the individual status pages. Users can access the one overall page to see where everything is in real time.</p><p>For more detailed information and step by step instructions, please refer to the Amazon e-book linked here and to the right if viewing this on a computer. </p><p>https://amzn.to/3wHsNAf Clickable version: <a href="https://amzn.to/3wHsNAf">https://amzn.to/3wHsNAf</a></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-1567719668869605112022-03-23T07:43:00.000-07:002022-03-23T07:43:05.745-07:00Kscope22 - June 19-23<p>ODTUG's widely well-regarded conference Kscope is back! Scheduled for June 19-23 in Dallas, TX, it promises to be informative, with sessions on tips and tricks, customer success stories, hands-on training sessions, and more. And the Oracle session on Sunday is always helpful and provides great info to start the week off.</p><p>The conference is being held at the Gaylord which is really, really close to the airport (DFW). Looks like a great property.</p><p>Go to https://kscope22.odtug.com for more information and to register. The early bird pricing ends on March 31, so register soon!</p><p>Pro tip #1 - there are two prices - one for ODTUG members and one for non-ODTUG members. Become a member for $99 and save $150 on the registration. Not only are you saving some money but you also get the full benefits of being an ODTUG member. </p><p>Pro tip #2 - to save ANOTHER $100, use discount code KEYTCH22. The registration process will prompt for this at the beginning so don't miss it. Disclosure: I do not benefit in any way from this code - just passing it along. </p><p>Hope to see everyone there!</p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-55070406633706410702022-03-18T13:03:00.006-07:002022-03-18T13:06:30.410-07:00Reporting - Book POVs and How Different than Financial Reporting<p>The replacement to Financial Reporting (FR), called Reporting, has been in the Oracle EPM cloud environments for a while now. For the most part, everything I've known in FR has worked about the same. Different navigation or UI, maybe, but all of the same concepts. I'm also glad there are improvements.</p><p>With books, one of the common things to do in FR is to set a book POV. So if all of the reports should run for the children of a specific entity, you could simply manage that at the book level. So when I went to Reporting to create a similar book, I tried the same thing. As you might guess, it didn't work. After I got some guidance from Oracle, this blog post shows what it does do, how to do the FR equivalent, and why what to do now is better than FR.</p><p>When you create a book in Reporting, there is a book level POV. If you leave it at Default (which means the user's POV) or just select a single member, it works just like the book POV in FR. But if you use a function or select multiple members, like the Entity dimension below, it's different. Instead of running the report(s) for all selected members, it just limits the POV to the selected items. Users view one at a time and do not get a PDF with all of the members.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgO8mAL-xpBI2hUusYlB17bYEx4KCnUrq8He-xg3SBykSqVC1ndJ5oxMuIHG-UCrDML7HGmUCkcts-pzvdfAIvnNA9IFLviH63NUBbGYP04ZbtVcCG5_PjTaCAalvDyhhrHePc0DghwqmC17wljEYhvT-SCb6sooJmKPgboMzEzvXMfk6clPrnc4IHr=s629" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="185" data-original-width="629" height="118" src="https://blogger.googleusercontent.com/img/a/AVvXsEgO8mAL-xpBI2hUusYlB17bYEx4KCnUrq8He-xg3SBykSqVC1ndJ5oxMuIHG-UCrDML7HGmUCkcts-pzvdfAIvnNA9IFLviH63NUBbGYP04ZbtVcCG5_PjTaCAalvDyhhrHePc0DghwqmC17wljEYhvT-SCb6sooJmKPgboMzEzvXMfk6clPrnc4IHr=w400-h118" width="400" /></a></div><br /><p>You can use the options on the flyout menu to control the display but that's about it.</p><p>So, what do we do? The key is to use a section. Sections are grouping of reports, other docs, and/or other sections. If you think about an outline, they are the first (or more) top level(s) before you get to the report/doc at the bottom level. They do help with managing the groupings better than FR, which only allowed for collating by member or report. You can see the improved management of it all in a table of contents. Speaking of the table of contents, sections allow for better control over the book layout. Want a cover page before the table of contents? In FR, you couldn't do that unless you had a hardcoded table of contents PDF or Word doc where the page numbers were not dynamic. In Reporting, with sections, it's no problem.</p><p>When adding a section, a name field allows for a description to be placed and the dimensions that need a section POV can be selected. These should match what is needed for the report(s).</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEil_7pna9UySrlhKWyoGFb3_Ax3xYMqe64ZVWp-7pxA0c6SxHApIUnBiwr_DUvrbkWRKWDD00W4jGZPFvia48mw3WaiRh2xD2E8r_8gnb8X0z-aWl3dVkbTQzVMdRqnAGhw_LiWn25IHRAt8Q18RaV-4cmbRzGOBtBq8-PbsfGmlzpIZKFzZQXM3T9Y=s585" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="216" data-original-width="585" height="148" src="https://blogger.googleusercontent.com/img/a/AVvXsEil_7pna9UySrlhKWyoGFb3_Ax3xYMqe64ZVWp-7pxA0c6SxHApIUnBiwr_DUvrbkWRKWDD00W4jGZPFvia48mw3WaiRh2xD2E8r_8gnb8X0z-aWl3dVkbTQzVMdRqnAGhw_LiWn25IHRAt8Q18RaV-4cmbRzGOBtBq8-PbsfGmlzpIZKFzZQXM3T9Y=w400-h148" width="400" /></a></div><br /><p>With the section in place, add the report(s) and then edit the section POV with the desired multiple members. Now when the book runs, the report will run for all of the entities in the section POV, essentially meeting the FR book POV functionality.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgHvB8kRygw7_I7kkAU9yLS1_3gPs1jZOLUe7fBY2NcXFPQrwDWwV1DElD8IzXhdISrKFpScyxQ3f7WOF2td6Nz1fwiTIat7ygcKfGbG_PVv_B7DYw6UxZszc1HXkk_1gTSPB0Mr0sJo-MpJcOimlRKWNRh1Jrl3C-tA0ZgJ7CeyhpxLx4IydVFP-wF=s872" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="268" data-original-width="872" height="122" src="https://blogger.googleusercontent.com/img/a/AVvXsEgHvB8kRygw7_I7kkAU9yLS1_3gPs1jZOLUe7fBY2NcXFPQrwDWwV1DElD8IzXhdISrKFpScyxQ3f7WOF2td6Nz1fwiTIat7ygcKfGbG_PVv_B7DYw6UxZszc1HXkk_1gTSPB0Mr0sJo-MpJcOimlRKWNRh1Jrl3C-tA0ZgJ7CeyhpxLx4IydVFP-wF=w400-h122" width="400" /></a></div><div><br /></div><div>At the bottom of the screen there is a section for the TOC headings. For both the reports/documents and the sections, the headings can be adjusted between the artifact (report or document) name, the dimension label, or the dimension alias.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiveN-xWrrRThBSgvIJy98yo8Kct8c7_di8oKOjIP9WMy5YkIc3g1mqVuMk7TjbDMvKue5Q0xupF6u--75ahw8t2TGdVUuMygeP_Xou5jBeKcB0XD9RVehBJbmmx85ED7OI8FA54A4gD4Xqm0HdeSeRp9HUsogU_hS4AC87vgaqA7f85EMpNhGyhDxo=s544" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="258" data-original-width="544" height="190" src="https://blogger.googleusercontent.com/img/a/AVvXsEiveN-xWrrRThBSgvIJy98yo8Kct8c7_di8oKOjIP9WMy5YkIc3g1mqVuMk7TjbDMvKue5Q0xupF6u--75ahw8t2TGdVUuMygeP_Xou5jBeKcB0XD9RVehBJbmmx85ED7OI8FA54A4gD4Xqm0HdeSeRp9HUsogU_hS4AC87vgaqA7f85EMpNhGyhDxo=w400-h190" width="400" /></a></div><br /><div>If you have more than one section, then yes, you'll need to manage each section's POV if there is a change. But this is a small and easy trade for the improved control over the book page order, table of contents format, etc.</div><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-56292268655385387452022-03-15T08:28:00.005-07:002022-03-15T12:20:03.252-07:00EPM Automate - DOS Commands for Checking Available Disk Space<p>I recently answered this question on Oracle Cloud Customer Connect [you are signed up for CCC, yes?] and wanted to share the answer here.</p><p>A user is setting up EPM Automate to do snapshot downloads. There is a need to check the available disk space and then delete prior backups as necessary. The question was how do you check available disk space. Here are the steps.</p><p>The easiest way to find the available disk space is with the DIR command. The output can look something like this.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj8Une5fRhDXyCmIVlhA9jR-HAoxxK_xLZUm7S1ASWrg8LRiSRp1fWM1qjoOR8ngtxqo9vld1RAoDHE6aK7ZBE2oDx8nlF1oBm6p-wtXLE1GgHE_1Dl57pPQqvMI2dF2zfqQT1QcRRWiVKDovfbJFufGqefIzFt18ync0vkKe8oTbJDzeLvHvYcsuAA=s820" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="284" data-original-width="820" height="139" src="https://blogger.googleusercontent.com/img/a/AVvXsEj8Une5fRhDXyCmIVlhA9jR-HAoxxK_xLZUm7S1ASWrg8LRiSRp1fWM1qjoOR8ngtxqo9vld1RAoDHE6aK7ZBE2oDx8nlF1oBm6p-wtXLE1GgHE_1Dl57pPQqvMI2dF2zfqQT1QcRRWiVKDovfbJFufGqefIzFt18ync0vkKe8oTbJDzeLvHvYcsuAA=w400-h139" width="400" /></a></div><br /><p>The output starts with the word Volume and can go for any number of lines based on the contents. The number to capture is 25,998,811,136, but without the separators and as a variable so that a comparison can be made. </p><p>First, use the DIR command with a redirect to write the output above to a file. Use the /-C parameter to remove the separators.</p><p>DIR /-C > file.txt</p><p>The output of this will look like above but without the separators. Next, isolate the bytes free line vs all of the others with a FIND command. Search for bytes free. Redirect to another file.</p><p>FIND "bytes free" file.txt > line.txt</p><p>The output of this will look like the last line above. Next, use the FOR command to find the right part of the line. The parameters allow specifying a delimiter, but if nothing is specified then a space will be used as a delimiter. Note, if the below command is used in a batch file, use %%I as shown. If typing this in manually to a command window, use %I instead.</p><p>FOR /F "TOKENS=3" %%I IN (LINE.TXT) DO (SET FREESPACE=%%I)</p><p>So now we have a variable with a number in it. Is this useful? Maybe, maybe not. The next step would be to subtract a predetermined target amount from the available space to see if the available space is above or below the target. The SET command with the /A parameter will do this. But, it is limited to 32-bit numbers, meaning it doesn't work for numbers larger than 2,147,483,647.</p><p>So, a little more work needs to be done. Assuming the target is 10GB (10,000,000,000) we can reduce everything to GB instead of working with the full number. Use the SET command to pull out just the wanted part of the number (GBs only) into a new variable.</p><p>SET FREESPACEGB=%FREESPACE:~0,-9%</p><p>Now all that is left is a variable with just the GB value: in this case, 25. Then it would be an easy comparison to compare to our 10GB target. In the case that available disk space is under 1GB, then the above truncation may cause an issue. Doing the comparison in MB instead would get around that. Change the -9 above to -6 and then compare the output to 10000 instead of 10.</p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-59344708883649681352022-03-04T08:18:00.003-08:002022-03-04T08:28:38.294-08:00Reporting - Managing the POV - Grid/Global and Hide/Not Hide<p>Just spent five minutes figuring out how to manage the point of view in a Reporting (not Financial Reporting) report. One would think this would be simple. For the most part, it is, but not always.</p><p>When a grid is open, the point of view displays along the top. The word Default (as shown below for the Period dimension) means to prompt the user for the member selection at run-time. Choosing something else will lock the dimension to that member. Clicking the vertical ellipse icon on the right side of the dimension opens a menu. Reset to Default is straightforward - the member gets cleared and the below choices get reset. Hide Dimension suppresses or doesn't show the dimension and the selected member. In case you're wondering, if the dimension is set to Default AND the dimension is hidden, the first member in the dimension (likely the dimension name) gets used.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhE7h42KVquj0CiP-tk0RoeET_l3SiMU0fJ0BQ0wJ3oM26SVZSOCAv20M6xzwvwgMs81R3AoLTY9MwePtEEhVufHR9OIyyoEnbDO4FBYi7Sffjafr3BvK6DgfH5eni_mYCY2ms4s176DdlFljkxnmNZYHdo8csphx1xcionQj2JMADHuRYwpYozkiCT=s588" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="254" data-original-width="588" height="173" src="https://blogger.googleusercontent.com/img/a/AVvXsEhE7h42KVquj0CiP-tk0RoeET_l3SiMU0fJ0BQ0wJ3oM26SVZSOCAv20M6xzwvwgMs81R3AoLTY9MwePtEEhVufHR9OIyyoEnbDO4FBYi7Sffjafr3BvK6DgfH5eni_mYCY2ms4s176DdlFljkxnmNZYHdo8csphx1xcionQj2JMADHuRYwpYozkiCT=w400-h173" width="400" /></a></div><p><br /></p><p>The last selection is whether the dimension is Global or not. If Global, then the dimension and the selected member will apply to all grids in the report. If Global is not selected, then the dimension setting is specific to that grid. If not global and not hid, then the dimension and the current selection will show at the top of the grid within the report when the report is run and users can change the member.</p><p>So far so good. The problem comes when you want to change things. The order of the selections should not matter - set the member, hide/unhide, or global/not global. But that is not the case. Here, the member is selected and the dimension is global, but the option to hide or not hide is greyed out. If you turn off Global, then Hide is still greyed out.</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjr4iHXT1E31-TOPGykOO7yeicnlAXu-GUDNMT9amYFWcu4cj3tO3aioBdmCsUBTc697R7TY-ljWWljMXdQfPFWc7-PAWQDktc_mwCtcIrKh6Ko5SLraII4f_3U_E3hH5xKB9YXRxLmq8dMaTsEforLGhqgntxzPA1rTweQW61mIDamYh0V3v8XUD7m=s580" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="278" data-original-width="580" height="191" src="https://blogger.googleusercontent.com/img/a/AVvXsEjr4iHXT1E31-TOPGykOO7yeicnlAXu-GUDNMT9amYFWcu4cj3tO3aioBdmCsUBTc697R7TY-ljWWljMXdQfPFWc7-PAWQDktc_mwCtcIrKh6Ko5SLraII4f_3U_E3hH5xKB9YXRxLmq8dMaTsEforLGhqgntxzPA1rTweQW61mIDamYh0V3v8XUD7m=w400-h191" width="400" /></a></div><br /><p>The solution for this is to reset to default and start over. After the reset, verify Global is selected, set the member, and then select Hide.</p><p><br /></p><p>In the end, the initial build is easy enough, but if a change is needed, it isn't really a change: it's a start over.</p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-73045212211391139022022-01-17T15:09:00.004-08:002022-01-20T13:55:54.193-08:00EPM Automate: Using GetSubstVar with DOS Batch File<div>Summary: this post is about using the EPM Automate GetSubstVar command within a DOS batch script and then transforming the output into a usable variable.</div><div><br /></div><div><br /></div><div><br /></div><div>With most every FCC implementation there are multiple EPM Automate processes. One of these is typically to load and consolidate the close period. A discussion that typically comes up is how to control the period and year for which the process is running.</div><div><br /></div><div>Over the years I've preferred using a file on the EPM Automate server. The admin would set the period and year there (either within a file or just read the name of a file). There are other controls I like putting here, like to skip the process, to load or not to load exchange rates, etc.</div><div><br /></div><div>There is a way to use the substitution variables within the application if this approach isn't desired. There is an EPM Automate command that gets substitution variables: GetSubstVar. </div><div><br /></div><div>First, log into the environment and use the getsubstvar command to get the variable. The parameters let you get variables from the application level or the cube level. You can also get all of the variables at those levels or just a specific one. Here, these commands are getting specific variables from the Consol cube and writing the output to text files.</div><div><div><br /></div><div>call "c:\oracle\epm automate\bin\epmautomate" login </div><div>call "c:\oracle\epm automate\bin\epmautomate" getsubstvar Consol name=CurrMonth >> currmonth.txt</div><div>call "c:\oracle\epm automate\bin\epmautomate" getsubstvar Consol name=CurrYear >> curryear.txt</div><div>call "c:\oracle\epm automate\bin\epmautomate" logout</div></div><div><br /></div><div>The three line output of the two text files looks like this (excluding the filenames):</div><div><br /></div><div>CURRMONTH.TXT</div><div><div>Processing...</div><div> Consol.CurrMonth=Dec</div><div>getsubstvar completed successfully</div></div><div><br /></div><div>CURRYEAR.TXT</div><div><div>Processing...</div><div> Consol.CurrYear=FY19</div><div>getsubstvar completed successfully</div></div><div><br /></div><div><br /></div><div>The next step is to get the info that's needed from the files, namely the period and the year. Fortunately, the DOS batch command FOR can take care of that. FOR has many different options. Here are the two commands to run to pick out the month and year from the above.</div><div><br /></div><div><div>for /f "tokens=2 delims==" %%i in (currmonth.txt) do (set currmonth=%%i)</div><div>for /f "tokens=2 delims==" %%i in (curryear.txt) do (set curryear=%%i)</div></div><div><br /></div><div>NOTE: if running these commands within a batch file, the variable i is preceded with TWO % and if running manually from a command line use ONE % instead. Tokens=2 instructs FOR to read the second occurrence of what is found. Delims== instructs FOR to use = [the second one] as the delimiter. </div><div><br /></div><div>Last, if the script needs to pass the year to Data Management to run a data load rule, create a new variable to combine the month, a dash, and then the two digit year (strip off the FY).</div><div><br /></div><div>set dmperiod=%currmonth%-%curryear:~2,2%</div><div><br /></div><div>So now there are three variables. To verify, type:</div><div><br /></div><div>echo %currmonth%</div><div>echo %curryear%</div><div>echo %dmperiod%</div><div><br /></div><div><br /></div><div><br /></div><div><br /></div>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-54074303132704993822021-12-31T09:13:00.001-08:002021-12-31T09:13:24.661-08:00Jan 2022 Oracle Cloud EPM Update - new EPM Automate SkipUpdate command<p>I want to point out a new EPM Automate command that's being released in the January 2022 updates. SkipUpdate. What this does is it allows you to skip a monthly update. Before, you had to open an SR to skip an update. Now (or soon), you'll be able to do this yourself, which is awesome. Here's how it works.</p><p>The command has three parameters: add, remove, and list.</p><p>Add means you want to add an update to skip. So you would say epmautomate skipUpdate add version=22.04 to skip the April 2022 release. You can also optionally add a comment.</p><p>Delete means you want to clear ALL previous adds you have submitted. So if you had a typo or whatever, you can clear them all and start over.</p><p>List is exactly what is says: list the active skip requests. You would want to do this before the delete. The list also shows the user that did the request, date/time, and any comment.</p><p><br /></p><p>There are a few things to remember. First, each environment MUST update at least quarterly. So you can't lock in the code as of now and use that forever. And when an update is applied, all features/changes/etc. that have occurred since the last update get applied. There is no skipping of features, etc.</p><p>Second, the setting is by environment. You may be thinking that you could let the test environments do the normal monthly update and focus just on the production environments. But then there's a problem. The snapshots only migrate between environments if they are the same version or one version apart, but not two. You could find yourself where test is two versions ahead and now no longer to accept a snapshot from production.</p><p>If the environment is on the current release then weekly and emergency patches will be applied. But if the environment has a one off patch (like applied by development to fix an issue only for your company) then the next update cannot be skipped.</p><p><br /></p><p>The release notes have a link to a powershell and Linux scripts to run this command. You can also run it manually, with a DOS batch file, or however you run EPM Automate. </p><p>This command will be great to ensure that updates do not happen during quarter ends. Some environments are not calendar fiscal year based, so this would be a huge help for them since the Oracle updates favor a calendar fiscal. </p><p>Here's the link to the EPM Automate help for this new command: skipUpdate.</p><p>https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/epm_auto_skip_update.html</p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.comtag:blogger.com,1999:blog-6544753899810698939.post-18011920492627699272020-11-25T11:52:00.003-08:002020-11-25T12:08:40.518-08:00Did the FCC consolidation work?<p>You run a consolidation in FCC (or FCCS as originally known), wait, maybe wait some more, and then you go to the jobs list and see the status is complete. Great! But then you look and see data isn't there or the data status is unchanged. What gives?</p><p>A consolidation job status of "complete" DOES NOT mean success or fail. It means simply that the process has finished running. But what you really want to know is the success or fail status. Where do you find this?</p><p>In the job listing click on Consolidate to see the details.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJtufVk6SDX5fn9hfT8DQZYl8zZxSjst6eaI-sWrrMFaZzkTzMAGU14YtaQBFLGlbJYn_JcpgeZB1zZK9QFdcg5ac0C7e7zeAkPp81VRKRXi4mFHkKquYungjQox9wK8arcL1gVG92r2U/s610/pre1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="398" data-original-width="610" height="418" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJtufVk6SDX5fn9hfT8DQZYl8zZxSjst6eaI-sWrrMFaZzkTzMAGU14YtaQBFLGlbJYn_JcpgeZB1zZK9QFdcg5ac0C7e7zeAkPp81VRKRXi4mFHkKquYungjQox9wK8arcL1gVG92r2U/w640-h418/pre1.jpg" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><p>Here, you will see some details about the POV and how long the job took. </p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin8NDHLoFN8jHMW36garQQGkdIOGaH132Zg_UAaVKD0F-rnhoagh3b5e1vGZheGR0cDydj8ZN73r7rGsp2S1fCqv-kC-7-Ms5kDQ9K3wMkHvhxTQoaDBeIVv_e01aKdpxzBoA37nVTteQ/s2124/pre2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="790" data-original-width="2124" height="238" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin8NDHLoFN8jHMW36garQQGkdIOGaH132Zg_UAaVKD0F-rnhoagh3b5e1vGZheGR0cDydj8ZN73r7rGsp2S1fCqv-kC-7-Ms5kDQ9K3wMkHvhxTQoaDBeIVv_e01aKdpxzBoA37nVTteQ/w640-h238/pre2.jpg" width="640" /></a></div><br /><p><br /></p><p>But you STILL WON'T know whether it succeeded or not (but if the time duration is really quick then failed is probably the answer). Click Completed at the top left by Job Status.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5-S2G4XXx-8lxHaGseFNlvvqror3z4x4b_tMcOHMuVminw_AeBwr7wbbfd00mQzEnspJFcl-1WGV1TtO-EMOURhhTroWhgNlUdinjiU_Woj2KFtY5B3p7e3CtkKCIj2pjj1ohpVSXBq0/s2270/pre3.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="634" data-original-width="2270" height="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5-S2G4XXx-8lxHaGseFNlvvqror3z4x4b_tMcOHMuVminw_AeBwr7wbbfd00mQzEnspJFcl-1WGV1TtO-EMOURhhTroWhgNlUdinjiU_Woj2KFtY5B3p7e3CtkKCIj2pjj1ohpVSXBq0/w640-h178/pre3.jpg" width="640" /></a></div><p>There, now you get to see whether the consolidation succeeded or failed. Whew!</p><p><br /></p><p>If you think there should be a better way, can you do anything about it! YES! On Cloud Customer Connect there is an idea posted already. What you can do is to "upvote," meaning click the thumbs-up icon. After clicking, either add a comment if you want or click Skip. The more companies that like an idea, the more likely it will be considered. You can also lobby your Oracle contacts when you see them on calls, etc. Let your voice be heard! Here is the link - both as a link and as raw text for those who don't like clicking links.</p><p><a href="https://cloudcustomerconnect.oracle.com/posts/8d62090b81">https://cloudcustomerconnect.oracle.com/posts/8d62090b81</a></p><p>https://cloudcustomerconnect.oracle.com/posts/8d62090b81</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihqSXSIjUBr3xRZ8xPvkRCFo90A3gjB1Nk1AV4X8-S3z2wadsKxKz5mD658rww2Wam8FJjMm4WSpBZWj5eLgclCWLICvDCAjY-crjJ7f5cIWjAcm7BnyuSay8o7I1l7PgVF1WcnfIu7mk/s1598/postjpg1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="598" data-original-width="1598" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihqSXSIjUBr3xRZ8xPvkRCFo90A3gjB1Nk1AV4X8-S3z2wadsKxKz5mD658rww2Wam8FJjMm4WSpBZWj5eLgclCWLICvDCAjY-crjJ7f5cIWjAcm7BnyuSay8o7I1l7PgVF1WcnfIu7mk/w640-h240/postjpg1.jpg" width="640" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>ericeriksonhttp://www.blogger.com/profile/00498605697938128798noreply@blogger.com