Home

To Hide Columns

1. Parent Child For parent child data structures in JDE there Is a Parent Child calculation which will organize the data in a hierarchical format First create the Associated Descriptions for the parent and child Next create the Parent Child calculation Design Calculations Column Caption Output Type Parameters ated Descri i No Associated Description nee Chid column 2nd Item Number o Parent ltem No Fill in Parent column and Child on column then the fill in the labels Show parent detail Auto format with the associated descriptions Start point l Only rollup if T Clickto edit Get short item number e Purpose of this calculation is to locate the Short Item Number when you only have 2 Item Number or you want the user to enter 2 Y Item N
2. determines grouping of the fiscal periods YTD Year Quarter etc Group periods by company _ Begin Period ine Peine Company Begin Period and Fiscal r Year are dynamically linked to the Fiscal Year Fis l gt parameters Date will only be ne o populated if By Date is chosen during Dataltem prefix advanced the wizard the other parameters Reverse sign criteria T Obi Acct Between 500 would then be blank Add begin balance criteria S Click to edit Only rollup if Designations for Reverse sign criteria Add begin balance criteria and Only rollup if a LT for example are applicable only to this calculation List Calculation Facts AList Calculation enables you to define rows on your report Lists can contain title rows rows defined by database criteria blank rows and rows with calculations such as Subtotals and totals e The List can be created through the Quick Reports or simply through the Calculations editor e Lists do need to be grouped when ungrouped a list will show an order number concatenated with your description e Lists can be the highest level of grouping or grouped underneath other layers of your report List Calculation Options Filter database will build the filter at the database SQL level a good choice if you have a ve
3. Character casing e Use Report Parameters as a header for filter criteria The Report Parameter establishes a link between the parameter header and grid column s you used to create them those filters apply to the entire report gt lt lt bate Add parameters by dragging and dropping grid columns into the parameter rows OK if columns have filters Make a parameter mandatory by unchecking the Allow blank entry checkbox Field 1 Pick an editor type Editor type 12 Enter editor options Allow blank ent Character casing Column e Click the Back or Accept Parameters button to save changes or deletions depending on DAS version Report Parameter lips When you see a parameter name bracketed by carets or tags alligator clips in the filter box that column filter is linked to a lt Parameter gt If you break the link between the parameter and its grid column click the Filter Assistant funnel in that grid column and select Variables to re connect the appropriate parameter To re order parameters click the blue box to the left of a parameter and drag It to the desired location Create Your Own Parameter lo create your own parameter click Design Parameters Type the parameter Name e Check the Visible check
4. DAS lists your user ID first so that you can publish to yourself recommended to see what you have published and how it is viewed by others To make parameter values required when others run your published report remember to uncheck the Allow blank entry box in Parameters If you have checked the box for Save filter values when you first save your report those values will be included in your published report Publisn with Embed Excel e When you publish a report with Excel Embedded you can click on the Excel Presentation button to hide work sheets make the Excel read only or set the report to only show the presentation E Publishing options for Embed Excel J Ok X Cancel X Delete i Show Excel Presentation Only Make Excel Read Only Hide Excel Menus Hide these Excel sheets Folders amp Subscribing e Folders can be created by the ReportsNow administrator Folders are global for all users in both My Reports amp Published Reports When publishing you can assign the report to a folder a Unsubscribe Pn Resubscribe You can Unsubscribe from a report which will remove it from your Published Reports view To Re subscribe click the Re subscribe button and choose the report to retrieve the version you unsubscribed from Copy amp Rename Reports To make a copy of your own report click File Save As and rename To make a copy change a report published to you trom th
5. Templates with are standard DAS reports those with wl have Excel embedded in the report CREATE A BASIC LAYOUT Basic Layout Actions Open a Table or Business View Get Sample Data vs Run Report e Working with Columns Hide Show e Resize Column Widths e Rearrange Rename and Sort Columns e Associated Descriptions and Concatenation Filtering Data o 3 Dots and Filter Assistant funnel for Valid Values and Calendar Technical Syntax for Filters e Viewing and Clearing Filters Additional Criteria for Hidden Filters View Design Notes vs How to Use Save and Rename Layouts e Grid Styles Default Preferences Open a lable or Business View jde When you know the table or business view to open Click JDE Data cylinder on the home page or in the toolbar Type the business view or table name in the Open field Click Open If you click Find or press Enter on your keyboard look for your selection below in the grid highlight it and click Open Adding Data to your Report Run Report _J Get Sample Data gt Get More Data Refresh e Running a report performs a full run of the report over the database Get Sample Data shows the first 100 rows Get More Data displays an additional 100 rows Refresh updates calculations within the current report Note File Default Preferences are wh
6. r Trending a Group By This Column 8 Quick Calculation Calculations d Quick Media Object d Hide Column Show Only Column 8 Hide and Show Columns 4 Best Fit Column Best Fit All Columns E Copy Column Rename Delete Column Custom Numeric Format Column Caption j Print Area Where are Quick Media Objects Price Adjustment Ledger Media 014074 Sales Order Header GT42014 sales Order Header GT42012 Sales Order Detail GT4211A Sales Order Detail GTI42112 Purchase Order Header GT4301 Purchase Order Header Change Order GT4301A Purchase Order Detail GT4311 Quote Suppliers Entered GT4330 Requisition Header GT43E01 Requisition Detail GT43E11 PO Acknowledgement SRM Comment Attachment GT43501 PO Inquiry Comment Attachment 6143505 Bid Maintenance Header GI44H601 Bid Maintenance Detail GT44H601D Extra Purchase Order Media Object GT44H 7114 Equipment Work Order Generic Text GI4801A F4801 Generic Text GT4801T Delivery Instructions GT4829A Delivery Detail Information GT48296 Contract Text GT5201A EPM Recurring Billing 15 GT74U1502 Media Object Steps Locate column for which you want related text gt Right click the column header e Click Quick Media Objects e Select appropriate media object Work with your new column Sales Order Header GT4201A Text Sequence Language Adjust Row Heigh
7. Fill in the Input or source column e Select Left or right side Peres Fill in Padding character linput parameter values O for example a Fill in Total width of new ine Total width column Only rollup if T Click to edit i Jinput Add a conditional rollup if desired Cat 011 Column Bl Trimming Column Values Trim e Shorten values using delimiter s e Can trim from the left right or both sides e Substring e Splits values based on Start Index starting position and Length from X position and X characters long Split Splits the value into multiple columns based on a delimiter s Number of columns for output is specified as the Split Number fill in 1 on the first line then 2 etc for the total number of columns of split How do know Join or Lookup If you can do a join do Table Lookups have more limitations e g no Associated Descriptions and Joins run faster Table Lookups do solve the problem of not having exact matches or needing to manipulate the returned data Always tile the tables first to compare data coming from multiple locations You should know the following before doing either What tables What fields you want from each tables What fields match Simple or Left Left Outers are common because of the Master or Header to Detail scenari
8. REPORTSNOW DESIGNER TRAINING GUIDE iLEARNERP 2013 iLearnERP All rights reserved No part of this publication nardcopy or electronic format may be reproduced or transmitted in any form or by any means electronic mechanical photocopying recording or otherwise without the prior written consent of iLearnERP ReportsNow and No fuss design extraordinary results are registered trademarks of ReportsNow Inc used with permission www iLearnERP com Designed and printed in the United States of America 1 2 2013 D54 www iLearnERP com eLearning videos cover core DAS concepts in short engaging videos Read our FAQ or Ask A Trainer your questions from getting Started to advanced How To s DAS trainers share insightful solutions and best practices in the Training Blog Find your own answers with the online repository of cheat sheets in Training Resources e Join us each month for Webinars on advanced topics or get started with DAS Fundamentals and Intermediate sessions Table of Contents JDE Basics Commonly Used Tables DAS Basics DAS Home Page Create a Basic Layout Grouping amp Summarizing Report Parameters Quick Calculations Trending Working with excel Publishing Data from Other Tables Quick Reports Scheduling Drill Down Bursting Global Variables Performance Access Non JDE data More Calculations mb mb D Na O IN O 01 01 A N N S 6 le Slo 2 IR
9. T Calculations Present Data to Excel Table Joins Quick Calculations Date Calculates the difference between two date columns Offsets the date by a specific number of days years etc Day of Month and Day of Year provide the corresponding numeric value for the day of the month or year Day of Week provides the text description e g Wednesday rirst day of month Provide the first day of month or year relative to the First t day of year identified date Month Description Numeric representation of the day Provides the two digit month with text description for sorting months in sequence e g 06 June Provide the last day of month or year for the identified date Pulls the month off of a calendar date Creates the text description of the month Creates a column with the current system date Pulls the year off of a calendar date Quick Calculations Math All standard math functions are available inside of DAS using these calculations often means you do not need to export or embed to Excel To use multiple columns highlight the columns in the order to be processed If you only highlight one column and use a calculation such as multiplication or division DAS will multiple or divide by a value of one 1 Standard Deviation Sum Quick Calculations JDE Account Category Associate Description Current mailing address JDE email address JDE phone number JDE user name
10. 0 00 0 00 0 00 0 00 0 00 0 00 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 Net Posting Net Posting Net Posting 01 3 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 16 17 18 19 20 5 CA 6 CA 7 AA 7 CA 8 AA 8 CA 5 AA 6 AA 5 AA 6 AA 7 AA 7 AA 8 AA LT Co 00001 00001 00001 00001 00001 00001 00001 00001 00001 00001 00001 00001 00001 0000 00001 00001 00001 Business Dad fd fl fd fd fl fd fd fe Business View Beg Balance PYE Sub Forward BEAR 871 350 00 BEAR 1 474 767 00 BEAR 0 00 BEAR 1 096 117 05 BEAR 20 000 00 0 00 0 00 125 00 83 55 83 34 650 00 96 112 05 3 9 3 0 Net Posting Net Posting Net Posting 02 01 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 03 0 00 19 666 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 REPORTSNOW it s about time oa Maelo E ee T on Aes be m mm ee ee a mi re ea pm g Taim ii a pge Ce AA P eee cn DE u TE ull LE E TE a EE GE AL Th 5 ET p bis iG a ai F wi MA maa Se a
11. Balances 11 Detail What s ina Table or Business View To see what columns are available in a table e Open the table using JDE Data cylinder Get Sample Data scroll to see columns or click Design Columns review list To see what tables and columns are in a business view e Open the business view e Click Design Table Joins Review the tabs for tables joins and columns Remember you can add additional columns or tables COMMONLY USED TABLES Finance F0911 General Ledger Detail e F0902 Account Balances e F0901 Chart of Accounts amp Account Cat Codes F0006 BU Category Codes e F0010 Companies e F0008 Date Patterns e F0012 AAls F0011 Batch Header F0411 A P Detail F0413 Payment Header F0414 Payment Detail e F0401 Supplier Master FO3B11 A R Detail E1 F0311 A R Detail World F03B13 Receipts Header F03B14 Receipts Detail F03012 Customer Master F1201 Fixed Asset Master F1204 Location Tracking F1202 Equipment amp Asset Account Balances F1212 Parent History e F1205 License Master F1207 Maintenance Schedule e F5108 Job Master F1501 Lease Master F15017 Lease Master Detail F1501B Lease Master Header e F1507 Unit Master F1514 Area Master Supply Chain amp Manufacturing F4301 Purchase Order Header e F4311
12. Brings Automatic Account Instruction AAI information use within a table with Object Account subsidiary and Company Get text description for a code Brings in the current effective address of the address number Returns the first e mail address for the address number You can edit the e mail address calculation to choose other e mail Locates the first phone number associated with the address number You can edit the phone calculation to choose other phone numbers or phone types Returns the JDE user name for the address number For E1 this is the E1 sign on name for World this is the World sign on name Quick Calculations Text Compares the string in two identified columns If the string is the same then the output is 0 if they are not the same then 1 shows field 1 is less than field 2 and a 1 shows that field 1 is greater than field 2 You can then use these values to filter Use to combine multiple columns into one new column To edit the delimiter use Design Calculations Note you can combine columns and literal values as needed Provides the numeric value of the string length of the specified column Use to compare columns from different tables or identify data issues Removes the identified delimiter from the left right or both sides of the column By default the calculation removes spaces from both sides useful for Business Units Quick Calculations System De duplicate
13. Group Rank Group Row Index Row Index Type Converter JDE Calculation Data is often duplicated with left and right outer joins use de duplicate to manage the data There are three types of duplication that can be used First Value in Group is the default from the Quick Calculation Ranks the values in the specified column Numbers the rows within a grouped section if you have multiple groupings the level referenced can be changed Get the sequence of the rows when not grouped in other words this calculation numbers the rows Convert a column trom one data type to another by default the system will convert to a String edit as needed TRENDING Trending BI Functions Aging Summarizes data into defined aging buckets Day Trend Sorts data into individual day columns Cross Tabular Flips row values into column values to do side by side comparisons Month Trend Totals data into 12 months and naturally rolls up data by month regardless of the fiscal year Year Trend Ages data across years Organize Dates Summarizes data into months quarters YTD and compares to previous Rank Bottom amp Top Ten Ranks a group of data and totals currency values or counts Trending Wizard Basics Wizards create calculations for you You can edit delete calculations created by the wizard You can add and copy calculations to the layout e You can modify your report with other DAS functions s
14. 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 P P P P 03 J 863 86 863 5 48 6046 6046 6046 6046 Sh 7229 266 2007 2007 2007 2006 2008 2008 2008 2008 IE IE IE IE P p P P IE IE IE IE IE IE IE IE IE IE IE Net Posting Net Posting Net Posting 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 K Quick Report Financials The Finance Quick Report will automatically do the following e Creates Relative Period calculations and if selected a List Calculations Adds Parameters Fiscal Period Fiscal Year Company Business Unit Fills in Ledger Type LT AA additional LT if added while Splitting columns and Object Account range If filled in on the Special tab Joins FO902 and if selected the FO901 for COA Cat Codes and the F0006 for BU Cat Codes Quick Report Capital Asset Mgmt The CAM Quick Report will automatically do the following e Relative Period and depending on selections Conditional and List Calculations Parameters Fiscal Period Fiscal Year Company Business Unit Fills in Ledger Type LT AA additional LT if added while Splitting columns Joins F1202 and if selected the F1201 for the Asset Master and the FOOO6 for BU Cat Codes Quick Reports Steps 1 Click File Quick Report Financials or Capital Asset Management
15. Bank Accounts By Addre subscription Set Up Multiple VendorBank Accounts PO0304 WOOSDAC VOD3DA Work With Bank Accounts By Address POOS0AVVO030AD Reporting Templates Getting Assistance Launches the online ReportsNow manual Contents Use to launch email to support reportsnow com Turn DAS Log On To turn on the DAS Log click the link then click Open DAS Log to read the 00 in the DAS Notepad y Report an Issue Click to import a DAS license A browser will open for you to locate and open Open DAS Log After you are done click a Turn DAS Log Off Import License the folder About Data Access Studio Locate version environment user and date format information search Google Like e Use when you dont know the Table or the Business View Fill in Known information such as the table name the application name key words Collapse sections by double clicking on the section header Applications Tables etc or by clicking the sign To open double click the selection note you are opening the business view or table not the report application etc Note This field references multiple JDE sources and must be regenerated if custom tables are created for your system for example F55 59s But Know where It Is in JDE Enterprise ne Menus gt In EnterpriseOne E1 hover over the JDE menu item e Record the
16. F06156 Payment History e F08042 HR History F00092 Supplemental Data DAS BASICS DAS Facts e DAS uses your JDE Sign On and Password e DAS displays real time JDE live data if you enter a JE It immediately displays when you run the report e You cannot update any JDE data with DAS Once you build a report it is immediately ready to share and use No waiting e Uses your JDE security e Security settings can be added within DAS by the DASADMIN DAS Terminology ReportsNow the company that makes DAS e DAS Data Access Studio e Layout the design specification object that produces the report Report the output of a layout with real JDE data Designer a full DAS license with the ability to design and publish layouts e Subscriber a limited DAS license with the ability to run a published layout Administrator an identified support person who sets user IDs security creates folders DAS RULES e Columnar Logic Calculations always create a new column e Only two kinds of columns Data amp Calculated Filtering on Data columns improves performance filtering on Calculated columns is for informational or display purposes only Its all about FOCUS Column Header right click to work with the column Filter Box to limit the data 3 Dots and Filter Assistant funnel e Row working with grouping levels THE ART OF DAS DAS report writin
17. User Settings window Navigate to the das file on your desktop and open It click Open button e Click OK on the Import Successful button e DAS automatically opens the report for you filter and Get sample Data or run the report as appropriate To rename the report click File Save As and give it another name Then you can delete the one that you imported from inside you re My Reports Report Admin Functions e Manage Subscribers Publish multiple reports to multiple people Highlight report selection first and click button e Delete for all To remove the report for all users highlight ana click button e Export Links Export HyperTags Click appropriate choice to export links and tags that can be used to link reports from locations such as webpages IN Report Admin a Manage Subscribers a Delete for all aa Export Hyperlinks Export SSO Hyperlinks HTML Exports 50 HyperTags L Steps for Manage Subscribers N First publish the desired reports e Open the Published Reports list and highlight the reports you want to publish e Click Report Admin Manage Subscribers this option is secured through the regular Admin Security checkboxes In the Manage Subscribers window you ll see users listed on the left Add your new user to this list the list will be the aggregate of all users associated with the reports that you have selected Ihe reports are listed on the r
18. application number P and Form W Type the P and W numbers in the Google like search field The Business View V will be at the end of the object name Foundation Systems Address Book Daily Processing Word e Address Book E gt Self Service Apply gt Periodic Processing Application P01072 Form WOIOL2B ersion p AB Advanced amp Tech 3 F Address Book a p A P01012 W01012B E Hide templates WE JDE Applications Work With Addresses rororaworor ioe e Double click the business view it should contain the fields you found in the JDE application Note In E1 click the f button to identify the P and W Use F1 or Item Help button to identify field aliases Financials Templates gt ales A Account Totals by Customer Supplier A GIL Detail by Account A General Journal by Account amp Payroll Templates Te M D ales a re a U tO M at cal y AA Pay Check History By GL Bank Account visible via the Google like search RT 4 hide them by checking the Hide Templates checkbox To access you must have a template license from Suppori reportsnow com All templates have directions on how to use and customize them located under View How lo Use e Upon purchase all 200 templates are owned along with all new templates as they are added When you open a template it automatically places a copy in your My Reports
19. by second Revenue m Revenue Gross Margin COGS AEXP Revenue COGS Column Revenue AEXP Column 17 415 66 13 395 00 10 397 41 61 617 00 Note Margin is not the same thing as Gross Profit i e Gross Profit Net Sales Net Margin E1 lime to Date lime e Use this calculation to combine separate JDE date and time columns into a single Date Time column Date Updated UPM Time Updated El time to DateTime E UPMT 8 3 2000 110715 8 3 2000 11 07 15 AM Steps 1 Multi select the columns that contain the corresponding date and time columns e g Date Updated and Time Updated 2 Click Quick Calculation JDE E1 Time to DateTime Model Account Use to build a Chart of Accounts report based on a master model 1 121 761 90 6 439 228 90 Z Liabilities and Equity input parameter values Current Liabilities 2 649 934 00 Model Business Unit Long Term Liabilities 1 690 000 00 Stockholder s Equity 2 137 694 90 Revenues Direct Costs General and Administrative Other Income and Expense oteps ana 1 Open an amount table that has the following fields Business Unit MCU Object OBJ and Subsidiary SUB Examples Account Balances F0902 Account Ledger F0911 Accounts Payable Ledger F0411 Accounts Receivable F0311 or FO3B11 etc 2 Create the Model Account calculation 3 Specify the Business Unit to use as the model account 4 Group by the res
20. e With Advanced Edit you can Add Report Parameters e Create a Filter from Excel Review padding and update settings for grouped columns FE Map column summary Auto fit column Map Group Summaries to Bottom Group level Indentspaces Row padding Up to level option Group text option 2 Multiple Input Add Report Parameters If you report has parameters you will see a checkbox for Report Parameters at the bottom of the Embed Excel wizard check the box to map your parameters to excel If you forget to map your parameters or have added them later click Presentation Advanced Edit On the left hand side tena wen Type of Mapping column use the three dots to select Map report control Complete Mapping Parameters on the right side be sure to Mhi fill in the Excel column ana Excel Row fields to a location that is not yet occupied Filter From Excel If you would like to populate a filter box using a list you maintain in Excel you can add this through Advanced Edit Click Design from the menu bar Embed Excel Fill in data in Excel note Excel is blank Go to Presentation Advanced Edit Go to the first blank line under Type of Mapping Use the 3 Dots to select Filter from Excel On the left side Parameters complete the following a Filter Mapping Option chose a single cell range a list etc o Input from Excel the location of where you are going to define
21. has a filter you can see It In the filter box as long as the column Is showing not hidden To display all columns with filters click Design Columns To see a list of all filters used in the report click View Design Notes To clear a filter in one field click the Filter Assistant funnel For Additional Criteria or hidden filters 22222 see the next page Hide Excel Toolbars Group Panel Adaitional Criteria Additional Criteria Additional Criteria is used to create an Or statement using two or more filter criteria sets Click Design Additional Criteria e Complete first line with your first criteria set e On the second line select Or Complete the third line with your second criteria set Note The ways to see that Additional Criteria exists are to go back to reopen that option or open the Design Notes Design Notes vs How To 59 view Under the View menu there are two options to record of notes and directions on the reports e Use How To to include directions for the user on how to filter run and interpret the report Note How To is not visible in the Published Report if it is blank e Use the top section of the Design Notes to record the background purpose and design details for the report Design Notes also contains the build detail for the report including your filters grouping visible columns and calculations It will snow you your l
22. just collapsed with drill down capability Right click near a subtotal to Suppress unwanted subtotals in a column 1 Revenue or Selling Gross P Price AEXP 29 250 00 678 390 00 55 000 00 35 000 00 90 000 00 R 17 415 66 13 395 00 10 397 41 61 617 00 9 617 60 66 975 00 7 798 06 5 848 54 193 064 27 15 000 00 15 000 00 30 000 00 rofitt Gross Profit E C a2 va Right click the column header you want to group the data by and select Group by Column s e You can multi select columns by holding the Ctrl key while highlighting each column in the desired grouping order Expand and collapse sections USING the and signs e Right click a grouped row to select grouping options sae M To ungroup right click any A column header and select en Ungroup Column or right click and select Clear All Grouping Grouping Steps 888388888 Hide Collapsed Summaries About Summarizing e Summarizing is used to display what you want to be visible at the grouped level Right click the column and select Summarize and the appropriate choice e g Max cinta m ii Grand Totals automatically PETT Be display at the bottom dE Calculations e oc to remove right click near total m2umn Count Show Only Column an a C n 005 N one ef Hide and Show Columns nei ha Best Fit Column Customize To suppress or un suppress rei Subtotals right click on any Sub
23. output addresses by row to send if desired Bursting Calculation Bursting Is created through the creation of a Burst calculation Type of Calculation Parameters cme fw Burst ill Burst Balance sheet amp String input parameter values checkbox can be turned off while developing the burst Pick Burst as the type of calculation tput types and postprocessing for burst reports Feria column Output description As many parameters as you like can be Multiple burst functions can be EBzancesn created to feed to created a column will be shown on the report for each one result status will automatically be created the target report Bursting Menu Once a Bursting calculation has been created the Bursting Menu will be visible on the far right of the run bar Reset Burst Status will reset the burst calculations to Not Processed without having to close and reopen the report Will burst only the highlighted rows you have chosen in the report Edit Workspace options Edit th determines how the target i peer ie Reset Burst Status report will appear during the Calculations Editor bursting process Simulate output Workspace options None fee Tile Horizontally I Tile Vertically Simulate output defaults to checked or on so DAS will only simulate the burst s until you determine you a
24. 1 2005 747 ATAT Ar Pua Bi IR Ald PMA Examples Right click on an Address Number detail from F0101 e Right click on Business Unit detail from FOO06 Right click on 2 Item Number detail from F4101 Account Ledger Drill Down Drill Down provides detail from the account ledger and show you the transactions and ee transaction totals for the item Copy selection Lopy selection to Excel you drilled into Grid Styles ef Hide and Show Columns Set filter to cell value Best Fit All Columns Doc Co Do Ty Document G L Date JE Line Line Extens Number Number F0902 GL Balances to F0911 Right click on an amount F4311 PO Detail to FO911 Right click on Order Number t F1202 Asset Balances to F0911 Right click on an amoun User Defined Drill Down You can connect any report to any other report using the Bursting functionality This allows you to create any row level drill down necessary To do this you need two reports e Driver Report e Target Report And to create bursting calculation for the individual row See bursting section for additional detail BURSTING What is bursting Ability to create versions of reports by linking two reports together Driver Report Target Report Defines the list or e Master report you versions of the want to run for report that you gt each row in the want to run NY Driver Report Defines email This is the
25. 11 9 47 00 AM Tuesday Boumuszma ns una CAN be seen In the preview window Helpful Scheduling Calculations Remember your report must be designed to be automatically run on the server The following calculations are helpful with reports that are automated First Day of Month First Day of Year e Last Day of Month Last Day of Year Todays Date Concatenate e Date Offset Period to Date Date to Period DRILL DOWN Drill Down Drill down provides links between information in one table to information in another table Drill downs support both multiple rows and summary values There are 3 types of drill down e Master file drill downs Account Ledger drill downs e User defined drill downs After selecting a drill down the associated detail screen will open in a tiled view so that you can see both the source and the detail information Master File Drill Down For any cell that has a master file behind it like item number you can right click the cell chose Drill Down and the master file will open and show you the details for the selecte er 1 6 1 2005 N 6 1 2005 12008 6 1 2005 i Copy selection Dait Zatti eN Ja a 6 15 2009 on a py selection to Excel 47 2 61820009 61 a Grid Styles 2 6 15 2009 6 15 2008 6 15 2008 6 15 2009 6 ot 2 0115 2009 a a1 5 2006 4 6112005 6 1 2005 611 2005 4242 4242 4242 51 6 1 2005 6 1
26. BEAN 1105 1105 1105 1105 1105 Details C D Doc Co Do Ty Doc Line Account Number AE 15 1 0 1 1210 AE 20 1 0 1 1210 AE 30 1 0 1 1210 AE 37 1 0 1 1210 AE l2l 1 0 r 1291 AE l2l 1 0 r 1291 AE 121 2 0 0 1291 AE 121 2 0 0 1291 AE 1000 1 0 1 1210 AE 1001 1 0 1 1210 AE 1002 1 0 1 1210 AE 106 1 0 Islz210 Organiz Organiz AE 102 DD 121210 AE 1030 10 1 1210 AE 1031 1 0 1 1210 AE 1032 1 0 1 1210 AE 1033 1 0 1 1210 F 1202 Asset Account Balances Beg Balance PYE Forward ed by 01 02 871 350 00 0 00 1 474 767 00 0 00 0 00 0 00 1 096 117 05 0 00 34 650 00 0 00 1 096 117 05 0 00 34 650 00 0 00 1 096 117 05 0 00 34 650 00 0 00 _ 000 g eriod 125 00 0 00 55 83 0 00 125 00 0 00 55 83 0 00 125 00 0 00 55 83 0 00 F1201 Asset Master Gi 6 30 0 L Date G6 30 05 G6 30 05 G6 30 05 G6 30 05 G6 30 05 6 30 05 6 30 05 6 30 05 6 30 05 6 30 05 6 30 05 6005 bf D 6 30 05 6 30 05 6 30 05 6 30 05 y Transact Amount Explanation Alpha Name 2 664 62 Post Due To Account 121 1 700 00 Post Due To Account 121 664 62 Post Due From Account 121 1 700 00 Post Due From Account 121 75 00 1001 100 00 1002 550 00 Offset By Document RR 1029 5 875 00 Offset By Document RI 1030 5 795 00 Offset By Document RI 1031 59 36 Offset By Document RI 1032 45 00 Offset By Document RI 1033 1 ee ee Dl ls a gt D 0 00 19 666 00 0 00 0 00
27. N IS i h k h aah NO n mb en gt mb O CO aah i gt n aah Ol N mb O1 mb mb JDE BASICS JDE Facts JD Edwards has more than 6 000 tables All data lives in a table Financial data is NOT in one table nor is Sales HR Payroll Manufacturing etc e JDE has 13 000 business views that join data from multiple tables you can create your own in DAS e JDE reports and applications access data via business views Atable contains all possible data columns in the table Abusiness view might contain data columns from more than one table or it might only have 50 of the 200 columns from a table Accounts Payable data Is not In one table FO411 F0413 F0414 Voucher Detail Payment Header Payment Detail e Voucher e Payment e Payment doc e Gross e Voucher Hs e Voucher Type Payment Paid doc type Amount e Voucher Supplier e Payee Amounts e Gross Amount e GL Date e Voucher Date e Due Date e Pay Status Starts Unit Business Du pudo fd pu fd fd fd pu fd fd ua pu pu fd fd fe Obj Application Where DAS Beg Balance PYE Forward Sub BEAR BEAR 871 350 00 1 474 767 00 0 00 1 096 117 05 34 650 00 96 112 05 3 9 3 0 20 000 00 0 00 0 00 125 00 55 83 125 00 55 83 125 00 55 83 Data Dictionary 02 0 00 0 00 0 00 19 666 00 0 00 0 00 0 00 0 00 0 00 0 00
28. Purchase Order Detail gt F43121 Purchase Order Receiver F4101 Inventory Master gt F4111 Inventory Transaction Detail F4102 Item B P Master e F41021 Location Master F4105 Item Cost Ledger F41113 Lot Ledger F4201 Sales Order Header F4211 Sales Order Detail F42119 Sales Order History e F4229 Sales Order Summary F4801 Work Order Master F4802 Work Order Instructions F30006 Work Center Master F30008 Work Center Rates F3102 Production Costs e F3111 Work Order Parts List F3112 Work Order Routing e F3002 Bill of Material Master e F3003 Routing Master F31122 Work Order Time Transactions e F1207 Maintenance Schedule Master F13411 PM Projections gt F1383 Equipment Analysis Costs OR amp Payroll F060116 Employee Master F060120 Employee Master Additional Information F0101 Address Book Master e F0115 Phone Number F0116 Physical Address FO111 Who s Who F01151 Email Address F0719 DBA Transaction F0618 Timecard Transactions e F08330 Benefits Enrollment F06106 Employee DBA Instructions F06116 Employee Current Timecards F06116Z1 Employee Batch Timecards F0709 One Time Overrides F08001 Job Information F08101 Position Master F08102 Requisition Master F06166 Tax Detail History
29. The Quick Reports Wizards from any screen in DAS 2 On the Main tab Name your report and check the appropriate boxes for category codes and Asset Master amp X Quick Report Se J Ok X Cancel 4 Previous p Next S Income statement Report name Income statement 1 17 2012 need category codes from the business unit master information e Checking these boxes indicates that the wizard will complete the necessary joins to include the additional information Quick Reports Steps 3 Create your columns on the Periods tab by selecting the checkboxes for Fiscal Period YTD Fiscal Period wor Income statement etc If you are creating a Balance sheet DAS will select YTD Fiscal Period for you e You can add additional columns by using Compare to previous select the duration such as Fiscal Period and the number of months years quarters for comparison e Customize the headings by changing the Caption field Fiscal period YTD Fiscal period Fiscal QTD Fiscal quarter Fiscal year Quick Reports Steps 4 To split your columns Into criteria such as Actual to Split column values by criteria DOX Anew tab called Columns will appear On this tab fill in Label the name of your new column such as Actual or Budget and then Criteria the definition of the column such as LT AAor LT BA a rows for eac
30. ariables Calculations Zero Balances To Exclude zero balances at the row W Exclude zero balances level check the box on the Specials tab Define criteria E WwW OK bq Cancel Additional Criteria EE U are created with Not 0 for all Net Posting columns and the Prior Year Balance column To view or revise go to Design LE Additional Criteria IC Eh Zero Balances at the Grouped Level Highlight all columns to be included in the zero balance criteria Right click the column header select Quick Calculations Group summary This will create a new Group Summary column for each column e Verify that the correct summary value is in each Group summary column to revise use Design Calculations Highlight all the Group Summary columns right click the column header select Quick Calculations Text Concatenate In the concatenated column right click in the filter box select the 0 0 combination ype an exclamation point in front of the zero combo to create a Not 0 O filter Hide the Group Summary and concatenated columns ra Edit Calculations for Balance sheet 8 30 2011 in Business Data Local Balance sheet 8 30 A Save Close Save And Close aK Delete The Quick Reports create Relative Period calculations you can add as many of these calculations as you need Offset value determined by the Group periods by designation P Help Lopy gt
31. ast query or select Statement as well as the runtime performance of your calculations save and Rename Layouts gl Data Access Studio DEMO900 It e Click File Save to name your report ial File Edit View Export Design Click File Save As if its already been MR Quick Report a named 7 P New e Save often there is not an auto save I If you click File Save and have not yet named your report the Save Private gt Report As window enables you to do so Save Private Report As W oK Save filter values with layout Fill in unique report name A unique name is needed to publish Grid Style Features Design Grid Styles applies to the report your have open Use File Default Preferences to change global settings for your User ID Disable counting the database prior to launch of report Change Column Header to change the Style of all columns to Alias tor example W OK Ed cancel 7 Spreadsheet Look P Help Report Look Show Vertical Lines Show Horizontal Lines Summary on group line Sort at Database slower Database count enabled Multi Sort Font Size Use Spreadsheet look to have Excel style presentation Report Look for basic layout Turn off auto width to a tas Background manually size the u RE your report Show calculator icon to show calculation col
32. ave Kos E sg para lt gt Delete 7 Help EE Copy MB Show All Calculations QU Refresh On Save Type of Calculation Column Caption Dutput Type Parameters Zlin nput parameter values Skip delimiter for bl Only rollup if T Click to edit Multiple Input Values amp Mz Supplier Number Column Literal Supplier Name Column E Group Summary Calculation To write logic off of group subtotals you need the Group Summary calculation Group Summary takes the subtotal or grand total value if selected and places it in a new column so that it can be used with other calculations like division to create percentages spp i Sam ms To change the subtotal being omr aBcommon A displayed change the Column u to group by To use grand totals check the Column total box 2 500 00 5 500 00 4 500 00 REPORT PARAMETERS Report Parameters Report Parameters provide filter field s above the grid to prompt the user to enter values before running the report Report Parameters can be mandatory or allowed to be left blank using the Allow Blank Entry checkbox Field Editor type Grid column valid values 12 Enter editor options Allow blank entry
33. box _ 2 Enter editor options Allow blank entry e Uncheck Allow blank entry to make mandatory if desired e Click Back or Accept Changes depending on your DAS version Link the new parameter to the appropriate grid column by clicking the Filter Assistant funnel in the target column and clicking Variables to select the correct parameter verify the lt Parameter gt link Is correct QUICK CALCULATIONS About Quick Calculations Quick Calculations are calculations that can easily be processed by DAS when it only knows the column s you highlight within the report Quick Calculations display by right clicking the desired column header s Quick Calculation options Quick Calculation only show those calculations Calculations that are relevant to the type Swick Media Object of column s you right clicked Hide Colum e g date currency business entity person etc Editing Calculations To display and edit all calculations Click 4 Design Calculations Right click a data column header and click 9 Calculations Editor e Click the top level menu Design Edit Calculations To edit one specific calculation e Double click the calculated column s header Right click a column header and select Calculations Edit pencil icon To view all calculations in the Edit Calculations window click the Show All Calculations button Variables
34. containing their Associated Descriptions Right click and Rename the new column To concatenate bring together two or more columns into one new calculated column o Highlight the columns in the desired order o Right click Quick Calculations Text Concatenate o Rename the new concatenated column o Hide original columns optional Filtering Data For each column you can add a filter for the report e Click in the filter box directly under Sem the column header la 3 dot provides a list of Valid Values for that field you can select values from this list For a date field you will get a calendar that can be expanded to select date ranges _ Funnel If list of options to help filter data Y Clear List e Note once you have created a filter and vot in le you click to another column the filter will BR read in text format For example Ends with Today Not Blank Technical Syntax for Filters Greater Than gt e Greater Than and Equal To gt Less Than lt Less Than and Equal lo en Range colon List of values semicolon e Not in List or Exclude lt gt Is the same as Literal value in carets or tags lt TODAY gt or lt TODAY 1D Note Some hot key or keyboard combos work such as Ctrl V to paste a cell value into a filter field Students often find some during class so feel free to play Viewing and Clearing Filters If a column
35. e Published reports list highlight the report you wish to copy and click Save to Private Report _ El save To Private Report e You will get a confirmation pop up and the report will open a version of the report with the same name will be in your My Reports To rename a report you must actually save a new report open the report click File Save As Move Your Report to a Folder If your Administrator has created folders you can now go to your My Reports to move your saved report to a specific folder location Click the My Reports icon on the tool bar or go to the nome page and click My Reports Highlight the report s e Click Move to Folder Highlight the appropriate folder e Click Save Export DAS Object s Export DAS Objects To export a das file to your Y Expor Bq lose desktop to email to another selected items Press Close to return to application person S E Exportable items for user DEMO q From any location In DAS click on File Export Reports to open the pa Export DAS Objects window practice add book era Click the plus signs and check e ens the das file s you want to export amp 2 F4801 O Click the Export button amp 2 F060116 nn Name the file s and choose the Seer save to location e g desktop 1d Click Save Import DAS Object s Y Import Reports Open DAS and from any page click File Import Reports to open the Import
36. e above the G L Date column will receive the concatenated date variable to filter the data when you run the report PERFORMANCE Calculation Best Practice e Filter on Data Columns not Calculations za e Delete any calculations that you are not using e Limit the number of Table Lookup calculations e Atable lookup fetches data trom other tables If this is done excessively the overall performance of your report may suffer e If you are querying against a large table gt 1 000 000 rows filter on at least one database column e Remember because calculations are derived data filtering calculations will not result is a smaller set of rows retrieved from the database Improving Performance Indices e Check the Index ss i e Use the drop down to review available indices and select the one that best matches your query The index will place the appropriate columns on the left of the grid and prevent them from being moved older DAS versions display key icon on the column s e Enter filter values for all key column s If possible start on the left e Select the blank value in the drop down box to remove e To review all indices and the associated fields select indices in the Data drop down when done reselect Data Additional Speed Strategies e Remove the count for the database by selecting Design Grid styles and then unchecking e Remove the Grand Totals for the report e Increase
37. ere Get Sample Data 100 records and Valid Values list length 20 are set Get Sample Data vs Run Report Fa m ul ee Run Progress Bar for completed report Fi i iT ESE a sample Data 100 Records Partially Loaded Get More Data 100 More Records Partially Loaded Records partially loaded Progress Bar for partially run report Working with Columns To Add Show Columns To add columns to your report click Design Columns to display the Hide Show Columns window Click Hide All Click desired column checkboxes to show and click OK The Hide and Show Columns window can also be opened from the top Design menu or by right clicking a column e You can also multi select desired columns and right click to select Show Only Columns all other columns will be hidden To show columns that contain filters click Show Columns With Filters To Hide Columns To hide column s right click the column s and select Hide Column s Resize Column Widths To auto resize column widths A single column can be quickly resized by right clicking the Individual column and choosing Best Fit Column All columns can be auto resized with right click Best Fit All Columns To manually resize column widths Hover over the gridline separator between two column headers e When you see the double arrow button drag to
38. g is an art It is the art of playing with raw data thinking through the heart of the objective and combining the calculations into a masterpiece Calculations conditionals table look up relative period Table Joins size of files many to many how many travels Trends aging organized dates rolling or month year Bursting use one report to drive another reporting output DAS HOME PAGE DAS Home Page Create a New Report Starting with a table through a table join REPORTSNOMW Access to online user manual Access to online video tutorials User Manual s Training A Rn N Je By Table Join Hide templates Layouts you have oe o My Reports written and saved D Financials Temph 3 mice practice 3 F0911 AC MLCC Practice join 2 DASVIEW A GIL Detail m Balance sheet 3 29 2011 DASVIEW search Google Like mlcc example DASVIEVW A Pay Check History NBank Account A Published Reports A Payroll AAI Detail ew ad JDE Applications test publish Copy Accounts to Business Units 90046 WODOGO Financial Account Level of Detail Rov an P91430 1491430 VO00EG Access reports published to you Direct access to JDE data tables and Non JDE Data through an ODBC connection General Accounting Constants Accounts Payable Constants PC Accounts Receivable Constants available with Work With AutomaticAccounting Set Up
39. ganization selections made in the trend To edit the Relative Date calculation s created by the wizard click Design Calculations or double click the column header Relative Date Calculation Steps To create a Relative Date calculation without using the wizard 1 Create a Report Parameter for your reference date gt Click 4 Design Calculations and select Relative Date 3 Do not change the Column Caption information this will remain dynamic and automatically update as you run the report over time click the 3 Dot to see avallable values 4 Do not change the Output Type Relative Date Calculation Steps contd 5 Fill in Date with the table column to use tn the calculation 6 Fill in Compare to date with the link to the report parameter that you created this will be the current date to the system 7 Fill in Offset Measure with Months Years etc 8 Fill in Relative Offset to look forward or backward by your offset For example to go back one month fill in 1 9 Fill in Value to roll Up input parameter values with the amount to Date GIL Date use in the new column lt Reference Date gt a E a m Relative offset 10 Fill in Only rollup if a with conditional criteria c for the calculation Onlyrollup if T Click to edit aD Trend Steps Month Year Day N e Create Associated Description s and filter and run t
40. h split you would like to occur olas Pr Ok ET q Previous gt Next Income statement Main Periods Rows Special Columns Leave blank for amounts only If you provide a list below each amount column will into the categories below Use this feature to create side by side compar sons such as Actuals vs Budgets or Business Unit comparison Quick Reports Steps Na y File Copy Insert Row x Delete Row Pp Label Criteria Row calculation 5 On the Rows tab there are many choices for Row designation you can choose Asset Group or other options that pull criteria from your JDE setup You can define your own rows by selecting List under Organize rows by The List is completely free form definition of the rows of your report Fill in the Label with the row name fill in Criteria with the definition of the row such as an object account range and add Row Calculations as needed to complete your list See additional slides later in this section Quick Reports Steps The Special tab provides options for customizing your report further Date selection method defines the parameters that will be created at the top of the report e Reverse sign criteria only in Financial Quick Report allows you to define criteria such as a range of object accounts for which you want the report to automatically reverse the sign Add begin balance criteria allows you t
41. he report to limit data to the desired year or month as needed Highlight the desired column header and right click to select Trending and Month Day or Year per examples gt Month For each Address Number show month columns for G L Date rollup Sum of Gross Amount Year For each Address Number show year columns for G L Date from year XXXX through YY YY and rollup Sum of Gross Amount Day For each Address Number show day columns for Order Date to rollup Sum of Estimated Price e Click OK and review the calculations Create Report Parameters if desired Trend Steps Month Year Day ye Quick Start El es WwW Ok bq Cancel BE E Quick Start ll A Year Trend ED G x arca Day Trend W Ok BI Cancel Month Trend For each Override existing grouping Trend Steps Aging a2 vn First select the column to age e Next fill in the date parameters selecting Control Panel Date will create a Report Parameter that can be filled in when running the report Fill in the rollup information with type of rollup e g Sum and field to roll up Last fill in the desired numeric ranges for your aging buckets e g blank to 30 days 30 to 60 days etc Trend Steps Cross Tabular Cross Tabular pivots the rows to be columns on the report First select the item
42. if the AA AA condition is true You can also have the calculation multiple or add values alue if tre olumr columns if the condition is true The same columns values and logic can be applied if the condition is false Note If you dont change then a 0 will default for false conditions you can replace with lt BLANK gt A Trend Steps Rank Top Bottom 1 0 j Enter parameter filters and create Associated Descriptions and concatenate them as needed Highlight a column header and right click to select Trending Rank Top 10 or Rank Bottom 10 For example Rank the top bottom XX Address Number based on the Sum of Gross Amount E e Click OK e Hide Show columns and add parameters as desired To reverse the order edit the calculation e g Largest to Smallest Override existing grouping Hide unused columns WORKING WITH EXCEL Export to Excel Embed Excel Exporting vs Embed Excel Export to Excel Embed Excel in DAS e Snapshot of JDE Data Live Data link to What you see is what JDE Data you get e Saved centrally e Saved on a local Full Excel functionality workstation Inside of DAS e Full Excel functionality Do not edit DASLink e Outside of DAS tab Export to Excel Steps Ra Run Report To export a specific selection Highlight the rows for the selection e Click Export Selec
43. ight If the lists is OK click the blue arrows to Assign subscribers to Reports A Success pop up window displays DATA FROM OTHER TABLES Table Joins Table Look ups Joins gt 7 Left Outer e Brings in all records from primary table and matching records from secondary table s e EnterpriseOne up to 3 tables using any Left Outer joins e Use for one to many or many to many relationships Simple Brings In only records that exist in both tables e EnterpriseOne up to 5 tables using all simple joins Right Outer e Used primarily for development purposes e Used for many to one relationships Table Joins Simple FO 1 0 1 records that are in BOTH tables Address Name Address Number search Type Left Outer all records from primary table amp Y BERAYA Address Name Number Search Type Phone Number and Type matching from secondary table s r0115 Address Number Phone Number Phone Type Join Steps NG Tile Tables e Determine fields to match e Decide what columns you want from each table e Search for existing Business View with Google like search Use wizard to create new layout Tables tab Fill in tables to be used e Suggested Joins tab DAS recommended joins double click to select Actual Joins tab review selected join s or create your own Columns tab select columns must pick at least one from each
44. nnected to a parameter This calculation can allow the user to enter Fiscal Period and Fiscal Year into the parameters for a report over data stored by calendar dates Fiscal Year Column side Both Only rollup if ff Click to edit First create the Fiscal Period and Fiscal Year parameters Create the Period to Date Range calculation e Connect the Period and Fiscal Year inputs to the parameters remember to change Column to Literal Connect Company to the Company grid column or fill in with a literal value if blank DAS will use the default company Fill in Century and select the Side of the date range that you want In the output column Date to Period To get the JDE period from a calendar date use the Date to Period calculation to translate It is important to designate the Company if you have alternate fiscal date patterns by company If left blank the calculation will pick up the default company Fill in the Date with the grid column for which you want the JDE period Margin e Use this calculation to create a column containing the net margin percent In your report First identify the columns that reflect Revenue and COGS e g Extended Price AEXP versus Extended Cost ECST Then select the correct columns for the Margin calculation Gross Margin Revenue Cost of Goods Sold Revenue Divide first entry
45. o Simple Joins are often overlooked because of the One to One relationship Review the data to help decide the best join you can always modify the Join Type later QUICK REPORTS Financials Capital Asset Management Quick Reports A tool that enables the creation of finance and asset reports answering real business questions with only having business knowledge not technical knowledge Report Writing Wizard makes complicated calculations easy through predefined formulas Reports on Posted Balances reports over FO902 or F1202 but you can add tables using lable Lookup Does some work for you creates parameters and calculations hides columns joins tables and can automatically map to excel Bonus reports run fast F0902 Accou i A B NAAR TARDAR ll lll F0911 Acc 2 3 4 5 6 Fi a 10 11 12 Pala Aaa D u o 01 Lu E LT Co EEBEEEESEECEOROE OE 00001 00001 00001 00150 00050 00050 00050 00050 00150 00001 00001 00000 00000 00000 00000 00000 00000 00000 00000 oooo1 OoO00O1 ool D000 ool OO0OLlL Oo00o1 Oooo 1 OOOO 1 OOOO 1 OoO001 oooo1 oooo1 oooo1 ooo0o1 ODoDo1 OOOO 1 ooo0o1 000l rganiz ount fuel fu fl fd om fd fe fl pal je pd je pul Business Unit nt Balances Obj Acct Sub L110 BEAR 1110 BEAR L110 BEAR 1110 BEAR L110 BEAR 1110 BEAR 1110 BEAR 1110 BEAR 1110 BEAR 10
46. o define criteria such as a range of object accounts for which you want the report to automatically bring in the beginning balance this is common for assets in both the CAM and Financial Quick Reports wizards Limit object accounts to this range sets the range of Object Accounts for the report and can be found in the filter box for Object Account after the report is generated e Exclude zero balances checkbox creates Additional Criteria in the report excluding all zero balances for that report Excel Presentation checkbox automatically embeds Excel in the report Quick Reports Steps Ne You can review your selections on the different tabs and when complete click OK Note your report is automatically saved All of the functionality that you have in other reports Is all available with your new report To add columns return to Design Table Joins and on the Columns tab check the boxes for o the additional fields gm Edit and add calculations through Ss Parameters Design Calculations Remember the Object Account filter box is automatically filled in if you populated an Object Account range in the wizard Present Data to Excel To edit unhide the Object Account columns g Ledger Type or other criteria will automatically 22 be populated if you provided specific criteria to Additional Criteria Split the columns To edit double click the column header and edit in Calculation Editor V
47. on to the server Username Password VC a ane E Blank passworc 3 Enter the initial catalog to use Non JDE Connection to Excel On the home page click HH Non JDE Data e Click a New Connection e Use data source name will default e From the Use data source name drop down list select Excel Files Fill in Enter the initial catalog to use with the exact path and extension for the file e g C Temp Sample_Data xls Click OK EE Data Link Properties ME Use data source name Use connection string Connection string 2 Enter information to log on to the server User name Password 3 Enter the initial catalog to use MORE CALCULATIONS Media Objects Calculation The Media Object calculation will bring in the Mai Qi selected associated media object text e Like all calculations a new column Is created Basic formatting can be done using Design Grid otyles Embed to Excel for more elaborate formatting rt 3 Get Sample Data 3 Get More Data aa Refresh A E Orly L 231 Customer requests orderdelivery on palles en 334 eee merrequests orderdelivery on palles SO 23188 am mer requests orderdelivery on palles Sn The Media Objects Quick menu will be available when you right click on the column header of a field that has associated media object text e Note all possible options are listed review for the text wanted
48. ovider Output directory Check the encrypt PDF button to put JDE password on PDF Use Advanced button to override or specify the environment Edit Task W x Eg close Task name Sheet 7 Enabled Fi s Ton 7 Schedule F Lues Name Based on epo DASWEW Export Presentation as Y Excel JHTML WIFDF N E 5 Excel HTML PDF MTsv MxML Mcsv E Fixed width Output directory Encrypt PDF Append timestamp to filename a S 2 Scheduling a Report Steps cont y hi Note this is similar to creating a a recurring appointment in Task rane a Outlook 1 Click on Schedule 2 Select Date and Time recurrence 3 Define the start and e a ee end ranges of the ine recurrence O tote 10 Ej aree E Endby 1 12 2012 3 00 00 PM E 4 Click OK to save Task a2 va Continue to add New Tasks or highlight a task and click Edit to revise scheduling a Report Steps cont C My scheduled reports Time a Day of F To preview the 124132011 3 47 00 AM Tuesday as a 1247011 94700AM Wednesday schedule highlight 12 15 2011 9 47 00 AM Thursday 12 16 2011 9 47 00 AM Friday T h e las k a a Q T h e 12 17 2011 9 47 00 AM Saturday 12 18 2011 9 47 00 AM Sunday z 12 19 2011 9 47 00 AM Monday U DCO M N Q S C N e q U e 12 20 20
49. pn pr pre e E zo pe a epp om Teg Fii A A A i LT a TE E F E TE a TE T ME Am rm ne bi a Lan ba is ha BEE poa E 7 Ea uu Bue 2 oe en ee oe m here c Le Lg Ti ae A e As i Bo cu TH i je u mm E rm E 1E zus HE mam m ima oy TE TEE E na ES Bae a Bo on CAE EE num A f A e ee a ra La Lp ne ha ii a g if a if a a Ea ia nome om ml 1 um 9 Em Gia if mif a a Fii 5 5 mn m zum JDE Naming Convention A BB CC A Type of Object BB system Code CC Type of file Putting it together F4311 PO Detail F4101 Item Master F0902 GL Balances JDE Naming Convention BB Sample system codes A JDE objects start with a letter defining the type of Object F Table File where the data is stored e V Business View pre defined columns from one or more tables gets data from tables P Application Program interactive program that displays data or allows data entry W Form Screen specific screen within an app R Report batch application 03B Accounts Receivable E1 04 Accounts Payable 06 Human Resources 07 Payroll 09 General Ledger 12 Fixed Assets 41 Inventory 42 Sales Order 43 Purchasing Procurement 48 Work Orders CC After the system code come more numbers which may be one of the following 01 Master 02
50. re ready to fully run the driver targets Bursting Output Types Step1 6 Burst Output Settings z Excel Result Status HTML PDF lt STATUSCODE gt lt STATUS gt select the format that you want for the report output Remember you can have multiple outputs you will just need to create an output type for each one ey csv img TSV XML a e a2 e Cc 9 A E W OK Ed cancel Outputnottully speched Bursting Output Types Step 2 Burst Output Settings Determine the name of the file when it is distributed Use the three dots to see options Use the lt TIMESTAMP gt to be sure the file name is unique File Name lt REPORT gt _ lt TIMESTAMP gt E Zip Compress amp Only distribute if not empty Aa a Chose whether or not you want the report distributed posted if there is no data that is generated ad eS O Sy C N em Output Types Step 3 gt h Select whether to Email or Save to Destination Note if you chose email it is best to have pulled a Type TE iy Destination emails using the JDE Email calculation or a Replace calculation where you key in associated emails Outputnottully sp Create text for the email distribution of the report Use Insert Token for dynamic things Such as name or business unit lf Email Provider is blank contact your DAS admin to se
51. rect table or business view and fetch one or more columns of data e Choose Table Lookup when e Data in matched fields are not exact e g match a padded F0911 SBL Subledger to F4801 DOCO WO to fetch that number To display a summary of the data versus transaction detail e When you want to add a filter column although Table Lookup columns fetch data more slowly being calculated columns Example The F0911 contains Subledger which could be a Business Unit Work Order Address Number etc Get that description with a Table Lookup Table Lookup Steps e Click Design Calculations and select Table Lookup A Complete Parameters are Average i Identify target table ni e g F0115 pesam nn 2 Fields to match Row oft AN8 AN8 dem gt Phone Type Literal blank mm Ts 3 Summary Type see inset pos E Fields to fetch Prefix AR1 CU O Phone Number PH1 I a n Grid Columns already e e named renaming optional Phone be e Phone Number PHI Table Lookup columns are calculated therefore they do not have 3 Dot buttons e g Valid Values etc Pad Calculation If you are using a Table Lookup because you do not have an exact match Pad and Trim next slide are helpful calculations Padding adds characters and or numbers to a columnar value It is often used to create a new column that has a specific length to then match to another column in another table
52. resize the column width as desired To enable manually column wiath resizing for the active layout you have open click top level Design Grid Styles and uncheck the box for Auto Width On Find and click OK Manual sizing is now enabled for the entire report Note The Auto Wiath on Find checkbox option also exists in your File Default Preferences if you want to manually resize columns in every layout going forward Rearrange Rename and Sort Columns Rearrange Columns To reorder columns in a report drag and drop them to the desired location Press Ctrl or Shift to move multiple columns If the report is grouped plus signs next to rows you cannot move a column to the left of that grouping Rename a Column e Right click a column caption header and select Rename Type the new column name and press Enter or click OK Custom column caption Run Report p Get Sample Data Sort Columns e Right click the column header and select Ear Sort Ascending or Descending To sort multiple columns hold the Ctrl key as Trending vou apply each sort complete sort order as desired Note Grouping column s overrides manual sorting 5 Associated Descriptions and Concatenation There are often data columns that you ll want the description for such as Address Book AN8 Business Unit MCU Item Number ITM LITM etc Double click these columns to create calculated columns
53. ry small number of objects items if you list is long then leave unchecked Allow duplicate maps if checked data can be Sl input parameter values pulled from the same isa dened account multiple times in NONE BERNER Guarantee Row ls 5 the report List Click to edit Guarantee Row is Shown if not checked the report will hide rows with no data or zero values note It does hide title and spacer rows e Smart Collapse Rows if checked removes the plus sign next to groups that only have one level 07 BLE MINO Scheduler e Scheduler is a separate module in Data Access Studio e Runs on the server Outputs report to email shared folder printer e Can produce group reports to run in a single task Can program to run anytime day week month year with any repetition e Can secure PDF output by requiring JDE password login to open the document My scheduled reports WF Ok ES conc In L lame chedu 121132011 9 47 00 AM 121141201 12 15 2011 9 47 00 AM 1 19 47 00 AM 12 1 19 47 00 AM 12 10 19 47 00 AM 1 9 47 00 AM on the My scheduled reports window 4 To edita Y scheduled task highlight the task amp click Edit scheduling a Report Steps cont Name the Task 2 Select Reports to include 3 Specify report formats as many as needed 4 Detail the destination Email Email Pr
54. s Click Design Variables e Click Edit Calculations Create Global Variables the same way as you would create calculations in your report Note that the only data that you have to work with is that which comes from the Parameters or the Today s Date Calculation e Click Save amp Close on the Edit Calculations window Review Global Variable Output amp Click Close Link Global Variables into your report through the Filter Assistant funnel on column filters column headers and in calculations Date Example Step 1 amp Global Variables oe Ff 8 Close 54 Edit Calculations H de and Show Lolumns Refresh Today s Date First day of Last day of Concatenation month month 12 6 2011 12 1 2011 12 31 2011 2 12 1 2011 4 12 91 2011 In the above example the Global Variables have been used to pick up the current date and create a date range for the current month by concatenating the First day of month and Last day of month calculations JE Line Line Extension PC Number a Vanables lt Today s Date 12 6 2011 lt First day of month gt 12 1 2011 lt Last day of month gt 12 31 2011 lt Concatenation gt 12 1 2011 lt 12 31 2011 M P P P P P P P Not in list Begins with Contains Ends with Today Blank Not Blank All of the Global Variables will be available to use as filters from the Filter Assistant funnel in columns or in calculations In the exampl
55. sma be Calculation JDE Chart of e A Accounts Note You can also use Quick Reports to create the Chart of Accounts calculation Use the Work Day calculation to report how many days a resource has Work Days worked Display work days W holidays H or weekends E for a given month or year Open the F0007 table to verify Branch Specify per desired calendar Default Branch Leave blank Year 4 digits e g 2013 Month 0 All Months 1 Jan etc Day of Month 0 All days in month 1 Day One 2 Day Two etc Shift Leave blank for default or Specify appropriate shift code WorkDay Code W H or E Calendar Type Leave blank or enter valid type code per F0007 Calendar Key Leave blank or enter valid key code per F0007 Year Month Day of Month Shift WorkDay Code W Calendar Type Calendar Key Only rollup if T Click to edit
56. ss Number Description column The Group Row Index is filtered to 1 to get one line per Address Number Note we also sorted by Effective Date to get the most recent address Address Number EA Naddress Line 1 Postal City ST Address Number b Code Not blank A Project Management Company S Stanford Office Center 200 80134 Parker co A amp E Job 1 345 Main Street 65034 Birmingham AL AB Common 1 6055 Tufts Avenue Suite 1331 00437 Denver CO Copy Column co Group By This Column col Clear All Grouping col Show Only Column gH Hide and Show Columns e Copy column creates an exact copy of the column that is selected You can copy a database column but may not filter on both columns use additional criteria You can copy a calculated column and then edit the new column for a different set of criteria Period Amounts The Period Amounts calculation sums the period columns in a balances table such as the F0902 or the F1202 The benefit of this calculation is more flexible than Summing columns because the ending period Is dynamically input into a parameter First create a parameter for the ending period co Ganga BSS Next create the calculation using the ending period parameter mput parameter values
57. t Only rollup if T Clickto edit Map values to the Generic Text Data structure Order Number DOCO Order Number OrderType DCTO olum olum OrderCompany K Order Co Replace The Replace calculation literally takes values in a column and replace with new text in a new column This calculation is often used to categorize information that Is not already identified in the system ra Edit Calculations for Accounts Payable Ledger FO411 in Business Local Lo El W Save Close Save And Close x Delete gt Help Copy MB Show All Calculations Type of Calculation Column Caption Output Type Parameters Replace Replace fe El input parame Source string Default Value First fill in the Source String with the Only rollup if column that will be replaced fill in the o Default Value equal to the value to be used if the calculation does not find a match Fill in the values to be replaced in the left column and the new text value on the right You can create as many lines as needed Group Row Index The new column created by the Group Row Index counts each row In the group and begins again with 1 when the group changes This calculation is useful to eliminate duplicate records first sort the grouped data for the desired row on the top of the group and filter by 1 to snow only one row Example below there is a Group Row Index on the Addre
58. table e Save amp Close Get Sample Data To return to the Join wizard to update retrieve additional fields Design Table Joins The My Reports view shows the Based On table as DASVIEW De duplication A many to many join will produce duplications in the data Duplications are easier to locate If you first narrow your data to a small set concatenate and group on the data items in your join in other words what makes the records unique e De duplication Calculation Parameters ameter values Tele teat De duplicate m First value in group Grouped Column Only rollupif T Clickto edit EJ Input pe Value if false De auplicate Parameters First identify Column to de duplicate Next input what value to use as a filler blank is default De duplication methods First value in group Applies the first value in group once and uses the Tiller value for the rest Up to first level break Applies all the values until the first group level break occurs e Distinct values Applies a value if it is distinct within the group Otherwise it applies the filler Group Column blank uses the lowest level group if filled In de duplication will use that grouping as Its reference for groups and level breaks Table Lookup Calculation Table Lookup is a calculation use as many as needed Use Table Lookup to map to the cor
59. ted Grid Data To Excel To export entire report click Export All Grid Data To Excel Remember column headers will automatically be exported with your report To export with headers and footers export to PDF to edit headers and footers click Design on the menu bar and Headers and Footers Other Export Options To Excel and PDF the most common Export outputs To Excel Exports report data to a notepad format to be imported Exports report as an HTML file viewable as a web page into Access or other similar To PDF third party software To HTML la TERETE SPOR abres Exports report data to a To KM notepad format to be imported into Access or other similar third party software To Freed Width Exports report data in XML format ready for use with Oracle BI Publisher or similar third party software All columns must be Pad calculations to produce a fixed with text file Embed Excel Steps e Determine columns to map to Excel Highlight all the columns you want to see In Excel Right click Presentation Review mapping columns If desired select graphs chart type If desired check the box to map Report Parameters Click double blue arrows gt Click Presentation Soreadsheet Look to see Excel toolbars Pl Presentation Advanced Edit To edit advanced settings for your Embed Excel click Presentation Advanced Edit
60. the Bulk Fetch Keys for Table Lookup calculations Bulk Fetch Keys ACCESS NON JDE DATA ODBC Driver vs JDE Table Creation ODBC Table Setup No Joins Joins to JDE Data Unless at database level Table Lookups to JDE like SQL Publishing Capable Table Lookups to JDE Setup Is required and e No Publishing can be complex e ODBC Driver required No data movement on each machine Not affected by accessing data e Easy to setup No data movement upgrades JDE Table Creation Setup tables in external database e Create views for external database with JDE aliases and table name Create table definitions of external table in JDE e Create JDE data source to connect to the external database Add the data source to security table ide JDE Data Appears as regular table in DAS and can use table Joins and be published Create a Non JDE Connection On the home page click Sas Non JDE Data 0 Click 7 New Connection e Use data source name will default and you can select an existing ODBC data source that exists on the machine To change ODBC providers switch to the Provider tab Fill in User name and Password if the data source requires it Click OK ER Data Link Properties _ 3s Specify the following to connect to ODBC data 1 e gt _ mia 1 We whi Use data source name Use connection string Connection string 2 Enter information to log
61. to be organized this is the rows Next select the columns to be created distinct values Last select the type of roll up and value to be Included in the columns Note columns will be created for distinct values visible in the report create additional columns If required using Copy Column Ok X Cancel Cross labular IR Zn u pain Remove previous Cross Tab columns Show summaries only Override existing grouping Hide unused columns A Conditional Calculations The Conditional calculation copies the values or copies and multiplies adds from one column to another based on identified criteria Conditionals allow you to pivot data in rows into separate columns or create logical if then statements to show or hide information To edit conditional calculations created by the wizard click Design Calculations to open the Edit Calculations window Often additional conditional calculations are needed after the use of a trending wizard click Copy and edit the parameters as needed Fill in all criteria for the condition this can include multiple columns and literal values The Column Caption will default to the Value if true value or column name Edit as necessary Change Output Type to match your resulting values First choose Conditional as the Type of Calculation Conditional Within o III Vf hii Designate a column or value
62. total and select Suppress Un Suppress Grouping amp Summarizing Tips Right click to fully expand or __ eg 7 lips Full Coll apse collapse the view of your report oF Group All to this Level Set Grouping Level To preserve your layout at a e Clear All Grouping certain grouped level right click eles ees and choose Set Grouping Level Ml di de A Add Count Set grouping level a W OK Ed nce Enter group level to use after find 0 full expand 1 first group row etc e Consider hiding additional grouped columns to gain space Try concatenating key values before grouping Make a consistent grouped value visible by using Summarize Max Concatenation Calculation After creating an Associated Description column you can combine the data column containing the number or code with the description column into one new column The concatenation calculation combines multiple columns and or literal values together into a single new column e Steps Hold the Ctrl key and highlight the columns to combine Right click and select Quick Calculation Text Concatenation To modify the concatenation such as the default delimiter or separator of space dash space open the Edit Calculations window Modify or rename the Column Caption as desired Edit Calculations for Accounts Payable Ledger FO411 in Business Data Local W S
63. tup email on the server Completing the Burst To complete the burst of the entire driver report uncheck the Simulate output option and click Run e Note if you have unchecked the Burst on Run option within the calculation you will need to recheck that box first The driver report will run and complete each burst output one at atime You will see the target report run either below or to the right depending on your Bursting Workspace Options settings Each row column will provide a status of the burst example data simulated PDF data copied Email etc Note if you have grouped your report it will attempt to run the report for every line use sorting and calculations such as Group Row Index filtered by 1 to limit the list to individual rows For full automation you can schedule the driver report GLOBAL VARIABLES What is a Global Variable A Global Variable is a section or tool used to complete a translation or process between what the user inputs in parameters and the run of the report z be Variables In other words Variables are used to have DAS take what is in the Parameter and calculate a new value to put In the filter box in the grid a column header or use in another calculation Creating a Global Variable Steps Create Parameters It is also helpful to complete the basic report steps of Hide amp Show columns Associate Description Save and main calculation
64. uch as grouping summarizing additional columns associated descriptions calculations etc Trends are available on any table To start a wizard right click a column header and select Trending and the appropriate trend Trending Calculations Trend Calculations Created Displayed Behind the Scenes Month Conditionals Month Year Conditionals Year Day Conditionals Day of Month Aging Conditionals Date Difference Organize Dates Relative Dates Top 10 Bottom 10 Group Rank Group Summary Pad Concatenation Cross Tabular Conditionals Trend Steps Organize Dates First select the column to Est be organized o 5 De a e Second enter the date Organize Dates parameters selecting Foreach Control Panel Date will create a Report Parameter sna that can be filled in when rnp running the report Fill in the rollup fields with the type of rollup e g Sum and field to be used Last select Date Organization values if Compare to previous years necessary fill in a Number 4 spew summaries only for any Last months years etc that are desired Override existing grouping Relative Date Calculation The Relative Date calculation is a dynamic column that Is defined by a date you identify in the report that will be compared to a reference date which Is typically a report parameter The Organize Date trend creates Relative Date calculations based on the Date Or
65. ulting Model Account column input parameter values Company Co CO Column Period 1 Net Posting 01 ANO1 Column Century 1 20 Literal Fiscal Year 1 FY FY Column Period 2 Net Posting 02 ANO2 Column Century 2 120 Literal Fiscal Year 2 FY FY Column Steps 1 Create Offset Between Two Periods calc 2 Enter a static Company grid column company or blank for default JDE company 3 Enter values for the reference period Period 1 Century 1 Fiscal Year 1 4 Enter values for the other period Period 2 Century 2 Fiscal Year 2 The new column contains the number of periods between 2 and 1 Offset Between Two Periods e Use this calculation in a balance table like FO902 or F1202 to show a period amount if it falls within a threshold range if its doesnt display zero or budget amount e This calculation counts the number of periods between two points where B Century FY Period minus A Century FY Period Example Point A could be the reference period and point B could be the current period Use this calculation to group data per your Chart of Accounts po Start Level of Detail e For this calculation to work you sii Dynamic Level Of Detail must include these columns from oman the Account Master FO901 table sms MCU OBJ SUB LDA PEC n Highlight columns to group by e g Description Object Subsidiary etc nn ee e Right click and click Quick
66. umber in the parameter and use the Short Item Number in the report Note the next page covers a calculation called Unit Conversion if you want to use this calculation you need the Short Item Number If you dont have Short Item Number do this calculation first nad Item Number setshoritemg E number 210 Unit Conversion The Unit Conversion calculation brings in the unit conversion for each Item The purpose is to allow you to see a variety of transactions which are input in various units into a single unit Note you need to multiply the Qty by the unit conversion to get the converted quantity Business Unit only needs to be filled in if your Unit of Measure Type of Calculation Column Caption Output Type conversions are designated by BU Unit Conversion Short Item No Column Short item number needs to be connected to the Short Item in the table or the calculation Get short item number Input units Column Output units Literal T Clickto edit Input Units is the source unit of measure coming from the table If left blank it will use the base UOM Output Units is the desired output unit this can be hard coded or co
67. umns Auto Width On Find Word wrap cells Auto Uppercase Filters Show row indicator Smart Column Filters Smart Column Filters automatically Y populates fields such 7 as G L Date when using calculations such as Relative Date Default Preferences Default User Preferences wW ox Did cancel Report Look HH Spreadsheet Look Additional tabs provide options for setting Print Export and other settings for your user profile Default Preferences apply to all future report layouts not Froperty to any current or Header Panel Group Panel past My Reports OF Even Row Odd Row Published Reports Focused Cell To change their Se formats open them and edit their Grid I Auto wicih On Fina Show Horizontal Lines Word wrap cells Sty les Summaries Auto Uppercase Filters W Summary on group line E Show row indicator Sort at Database slower Show calculation indicator Grid Styles tab is the same Database count enabled Smart Column Filters under default preferences Column Header Multi Sort Font Size GROUPING amp SUMMARIZING About Grouping e Objective of grouping is to make your data useful e Other terms for grouping include roll up drill down collapse organize e Creates automatic subtotals and grand totals Remember after you group no data Is gone it Is
68. your input c Output what column you want to populate in your report 2 i A od A Embed Excel Cautions e Without grouping Sheet1 view is limited to 1000 rows For more rows Embed Excel then copy links trom DASLink With one grouping level and set to Group Level 1 there Is no limit on rows but you do need to put total number of possible grouped rows in Advanced Edit Padding With one grouping level setto Group Level 2 fully expanded you are limited to 200 rows e With two grouping levels set to Group Level 2 you are limited to 50 rows m With two grouping levels set to Group Level 3 fully expanded you are limited to 40 rows PUBLISHING All Users all DAS users Group by named group security group Publishing Choices Individual select individuals by user name Publishing Steps Nr After creating and saving your report from inside the report 1 File Publish As Report 2 Locate user s groups Can designate that no data selection can be changed at the user level un check the box 3 Excel Presentation Options 1 Show Excel Presentation Only No DAS Data tab 2 Make Excel Read Only Cannot edit Excel cells 3 Hide Excel Menus Excel toolbars not visible 4 Hide these Excel sheets can list DAS Link and or other sheets Publishing Tips e Manage Subscribers under Published Reports will allow you to do assign a group of reports to multiple people

To Hide Columns

Contents

Download Pdf Manuals

Related Search

Related Contents