Set Up a Successful Reporting Solution with Oracle

A Day in the Life of a Report Developer Copyright © May 2004 by

Your customer/end-user Say…Did ya happen to get that Report Req form I filled out?

Typical Report Developer Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Oracle’s TRM Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Defining the query… • Look at requirements to see which base table(s) include the

Base Tables • Budget vs. Actual Report – GL_Balances – GL_Budget_Versions – GL_Code_Combinations Copyright

Now start coding… SELECT ACCT. SEGMENT 5 Product, ACCT. SEGMENT 4 Sub_Account, NVL(ACTBL. BEGIN_BALANCE_DR,

Your customer calls you… Is my report done yet? It’s been a week and

Your customer adds… Oh, and by the way… I want to be able to

Security • May not be an issue if using Discoverer – Use an apps

Not another call… Oh I forgot to mention… Did you see my email? I

At this point Copyright © May 2004 by Noetix Corporation – All Rights Reserved

The GREATEST BARRIER to achieving reporting goals easily is the Complex Structure of the

Key Elements for Successful Reporting for Business Users • Hide Database Complexity – Views

What is a View? Example: Purchase Order tables in the database PO_HEADERSC PO_LINE_TYPES PO_LINES_TL

Key Flexfields as Configured Key Flexfield Base Table Column Names Key Flexfield View Column

Users can use any tool!! • • Discoverer Oracle Reports Crystal Noetix Web Query

Wishing… Wouldn’t it be nice if… • A system exists that has most of

Copyright © © May 2004 by by Noetix Corporation –– All Rights Reserved Copyright

Click on a link to see the Views for that Module Copyright© May 2004

Click on a link to see a description of that View Copyright © ©

Copyright May 2004 Noetix Corporation Rights Reserved Copyright © May 2004 by Noetix Corporation

Noetix solutions for Oracle Discoverer and other reporting tools Copyright © May 2004 by

Login using your Apps username, password & responsibility Copyright © May 2004 by Noetix

View for Budget to Actuals in the GL area Copyright © May 2004 by

Double click the Answer. Point report to run it Copyright © May 2004 by

Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Click on a column header to resort Copyright © May 2004 by Noetix Corporation

Drag column header to grey bar Copyright © May 2004 by Noetix Corporation –

Click “CUSTOMIZE” to modify the report Copyright © May 2004 by Noetix Corporation –

Let’s add the “Account” segment’s DFF to the report Copyright © May 2004 by

Give the new column a friendly name Copyright © May 2004 by Noetix Corporation

Let’s save the results in our Personal folder Copyright © May 2004 by Noetix

New column in the output Copyright © May 2004 by Noetix Corporation – All

Unlocking the Power of Excel Copyright © May 2004 by Noetix Corporation – All

Create an Excel spreadsheet from this report Copyright © May 2004 by Noetix Corporation

Click “Refresh Data” to rerun the report! Copyright © May 2004 by Noetix Corporation

New Period Name for different results Copyright © May 2004 by Noetix Corporation –

How your customer feels after Noetix helps you… I can’t believe how fast you

How you are seen at your company… Copyright © May 2004 by Noetix Corporation

Now you have more time for… Copyright © May 2004 by Noetix Corporation –

Thank you for attending! sales@noetix. com Toll-free 866 -4 Noetix

Slides: 59

Download presentation

Set Up a Successful Reporting Solution with Oracle Discoverer & Other Tools Presented by: Rob Griebel

Defining the query… • Look at requirements to see which base table(s) include the most needed columns • Figure out how best to join the base tables together • Determine which org-id’s to use • Where do I find the flexfields that the user wants • I bet they’re going to want drill down again • Hmm…. what about security Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Now start coding… SELECT ACCT. SEGMENT 5 Product, ACCT. SEGMENT 4 Sub_Account, NVL(ACTBL. BEGIN_BALANCE_DR, 0) NVL(ACTBL. BEGIN_BALANCE_CR, 0) + NVL(ACTBL. PERIOD_NET_DR, 0) - NVL(ACTBL. PERIOD_NET_CR, 0) Actual_Balance, NVL(ACTBL. BEGIN_BALANCE_DR_BEQ, 0) NVL(ACTBL. BEGIN_BALANCE_CR_BEQ, 0) + NVL(ACTBL. PERIOD_NET_DR_BEQ, 0) - NVL(ACTBL. PERIOD_NET_CR_BEQ, 0) Actual_Balance_Accounted, NVL(ACTBL. BEGIN_BALANCE_DR, 0) - NVL(ACTBL. BEGIN_BALANCE_CR, 0) Actual_Begin_Balance, … FROM GL. GL_BALANCES ACTBL, GL. GL_BUDGETS BUDGT, GL. GL_BUDGET_VERSIONS BUDVR, GL. GL_BALANCES BUDBL, GL. GL_CODE_COMBINATIONS ACCT Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Your customer adds… Oh, and by the way… I want to be able to distribute this to many organizations so that they can see their own budgets but not others. Okey dokey? Bye Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Security • May not be an issue if using Discoverer – Use an apps mode EUL • It will be an issue if I use Reports 6 i or Crystal How do I access the Apps Security packages? Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Not another call… Oh I forgot to mention… Did you see my email? I need you to add 5 more columns and incorporate that new DFF we created an hour ago. That’s not a big deal right? Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Key Elements for Successful Reporting for Business Users • Hide Database Complexity – Views • Incorporate Configuration Information • Open Architecture – Diverse audience = different presentation • • E. g. Excel, Discoverer, Dashboards Tune for Performance – – Views tuned for performance Index columns identified Filters & Parameters Training • Document in Business Terminology • Organize Information by Functional Area Copyright © May 2004 by Noetix Corporation – All Rights Reserved

What is a View? Example: Purchase Order tables in the database PO_HEADERSC PO_LINE_TYPES PO_LINES_TL AP_TERMS PO_NUMBERS Your Report PO_Lines MTL_ITEM_CATEGORIES PO_HEADER_TL Simplified Noetix “View” MTL_ITEMS_TL PO_UN_NUMBERS Buyer Currency Item Description List Price Open Flag Line No. PO No. Quantity Terms Unit Price Vendor Open POs Same View… No changes! PO No. Item Description List Price Quantity Unit Price Vendor AP_TERMS_II New database structure… Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Key Flexfields as Configured Key Flexfield Base Table Column Names Key Flexfield View Column Names SEGMENT 12 Account$Company SEGMENT 6 Account$Division SEGMENT 3 Account$Cost_Center Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Wishing… Wouldn’t it be nice if… • A system exists that has most of these • • reports already written It would incorporate our security models I could easily customize the reports I could let the users customize their own reports It would somehow read our configurations like COA and DFFs and use them in the reports automatically Copyright © May 2004 by Noetix Corporation – All Rights Reserved

How your customer feels after Noetix helps you… I can’t believe how fast you built this report for me!! Thanks!!! I love the ability to change things myself without knowing any programming stuff!!! Copyright © May 2004 by Noetix Corporation – All Rights Reserved

Thank you for attending! sales@noetix. com Toll-free 866 -4 Noetix