Home

Data Access Studio User Manual

1. Index Assistant i The Index Assitant lets you create better running queries on large tables The Index Assistant option box shows a list of all valid indices for the currently active object You should show all columns before using the Index Assistant Upon selecting an index from this list Data Access Studio does the following e Moves the columns of the selected index to the front of the column order e Places a key icon in the column caption e Prevents the column from being moved Example Do ma Document m Doc m GIL m LT m Line ma JE Line R MD Accoun Ty Humber Co Date Extension Humber Once you apply the index e o take advantage of an index select an index that most closely matches the query you require e Notice the index columns for the selected index are now on the left hand side with a key symbol e Fill in index values from the left most index column to the right most column Remember the database will not use an index if you do not fill in values from the beginning of the index For instance if your index is Doc Type Document Number and Doc Co the index will not be used if you only specify a value for only Document Number and Doc Co All left most index columns must be specified first for the index to take effect e Depending on the database even if you specify all fields of an index the database will not always use that index In this event the database decides that another execution path will p
2. Quick Start a ES JO Ok gt Cancel Year Trend For each Company show year columna for Date Started from year 2004 through 2009 rollup cum af M Show summaries only Iw Hide unused columna Ge Select the column you want to rollup for the year trend Data Access Studio will automatically populate this field with the column you select in step 1 Select the date column you want to use Select the year range you would like to run Choose how you want to trend Sum Count etc Choose the column you want to use as the ranking quantity If you selected Count in the previous step then this field will be hidden 8 Click Y Ok O 104 Design Reports Organize Your Data into Aging Ranges If your data contains date information you may want to see how your data changes by a user defined time interval To see aging trends in your data 1 Right click a column in your grid that you would like to trend 2 Select Trending Aging Sample screen shot Quick Start a Ok Cancel 3X Delete Row am Aging mY For each Customer calculate aging between today s date and with date difference in Days rollup Sue y O with ranges Begin w Show summaries only mw Hide unused columns 3 Select the column you want to rollup for the year trend Data Access Studio will automatically populate this field with the column you sel
3. need information from the asset master need category codes from the business unit master information The Quick Report lets you specify what kind of report you want and how you want to create it Enter information on a particular tab Click the tab or click 4 Previous and P Next navigate the tab control See topics that follow Create the financial report with the Click Ok parameters you specified l Exit the designer Click gt Cancel 28 Design a Quick Report Specify Type of Financial Report The first step of defining your report is to select what type financial report you want Enter this information on the Main tab 4 Quick Report wf Ok gt lt Cancel d Previous Next dh Capital Asset Management Asset Management seet Management 07706711 need information from the asset master E need category codes from the business unit master information In this version all reports are based on the Posted balances file in JDE E ee Name your report Required Click on the edit box next to Report name and type a descriptive name for your report This is a mandatory step If you need to asset master information Click the check box labeled I need information from the in your report asset master If you need to access category codes in Click the check box labeled need category codes from the the JDE Business Unit table business unit master information Once you are finished click the Perio
4. CC Focused Cell Arial 8 Bold EC Focused Row Arial 8 Regular Footer Panel Arial 8 Bold _ CG Show Vertical Lines WM Auto Width On Find Show Horizontal Lines Word wrap cells Iw Summaries M Auto Uppercase Filters M Summary on group line Show row indicator Sort at Database slower M Show calculation indicator WM Smart Column Filters Column Header Multi Sort Font Size Make your layout look like a clean white Click Report Look report Make your layout look like a spreadsheet Click Spreadsheet Look Change the Font Font Color or Click on the Font Font Color or Background of any cell to background of the available styles modify the property Hide show vertical lines in your grid Uncheck Check Show Vertical Lines Hide show horizontal lines in your grid Uncheck Check Show Horizontal Lines Hide Show summaries in your grid Uncheck Check Summaries 54 Design Reports Customize the Look and Colors of Your Layout continued A T e ue Place summaries on the same line as your group label default Force report to sort data at the database Fit column widths automatically after each Find Keep your manual column widths intact Let Data Access Studio automatically control the character casing for filters Hide Show the row indicator column at the left Hide Show the calculation indicator in the column header Turn off Smart Filters Select a different Column Header type Change
5. El Calculation Editor button on the main toolbar Type in Period Amounts under the Type of Calculation On the Parameters enter 1 for Begin Period Enter any number from 1 through 14 for End Period Optional Specify Ledger Type to only rollup values for a specific ledger type e g AA Optional Specify a Dataitem prefix By default the calculation will rollup ANO1 AN14 or though AN12 if AN13 and AN14 do not exist Some tables however have other arrays of balance amounts that can be rolled up similarly For example the Tax History F016136 table defines BW01 BW12 To use Relative Period on this table enter BW for this parameter If this parameter is blank the calculation will default the AN prefix Note if Begin Period and End Period are the same number then the calculation represents the amount for that period If Begin Period is 1 and End Period is another number then the Period Amount is the through period amount e g Year To Date depending on the year start period 79 Data Access Studio User Manual Show Account Balances by Any Period Quarter or Year In JDE balance files have columns Net Posting 1 Net Posting 14 ANO1 AN14 for each account per fiscal year The challenge with this organization is that it is difficult to 1 Get balances that cross a year boundary in one report 2 Relate the fiscal period to the calendar period 3 Form you query based on accounting time intervals such as fiscal quarter
6. Filter Box Start Date Request Date Complete Date When you click on the filter box you will see the Filter Helper button If the column has a Visual Assist you will also see the kb Visual Assist button To filter for Direct text Values in the Visual Assist A list of values Values you want to exclude from your query Items that begin with specific text Items that contain specific text Items that end with specific text Items that fall into a range Items that fall into a range relative to today Items that are blank Items that are not blank Report Parameter value Click the Filter Box and type in the number string or date you need to find Click the Kl Visual Assist button Select from the valid values Click Y Ok Click and select List In the list form type the values you need on each line and click Y Ok Click and select Not in list In the list form type the values you want to exclude on each line and click J Ok Click and select Begins with In the list form type the begins with text on each line and click Y Ok Click and select Contains In the list form type the contains text on each line and click Y Ok Click and select Ends with In the list form type the end with text on each line and click Y Ok Click 15 and select Range See Filter a Range topic Click and select Today See Filter a Range Relative to Today s Date topic Click L and select Blank Cli
7. h Burst Target Re order items Burst on run pass values to the target report Report parameter T Filter criteria Business Unit MCU Business Unit Column E Column El Specify output types and postprocessing for burst reports FH Grid column Output description Burst Burst Target Result Status lt STATUSCODE w m e Select Burst e For Report enter the name of the report that will show the drill down information for your source report e Connect your source report to the target report by passing values from source to target In our example connect the business unit from the driver to the business unit of the target e Save your calculations You can now right click any row in your source report and you will see 15 Data Access Studio User Manual Business Burst Burst Target Re E Description Unit order items Compressed h af Run target report for row Burst selected rows 20 Hotpr Set filter to cell value e Copy cell contents Not pr RIBUTIONCENTER Copy selection Copy selection to Excel D30 Notpr IONCENTER Grid Styles afl Hide and Show Columns M30 Notpr JFACTURINGCENTER Best Fit All Columns EA S When you click Run target report for row DAS will launch the target report and pass the values for the selected row automatically The result is a Drill Down for the driver row NOTE You will only see the other menu item Burst selected rows if you ena
8. hace Group summary e value 0 00 In the example above if we want to filter for Amount balancing to 0 we cannot filter on the Amount field directly We have to first add a Group Summary calculation 1 Right click the Column Header for the summary you need to filter in this example right click the Amount column 2 Select Quick Calculation System Group Summary Batch Number A Amount Group Summary Amount zla 4 500 00 140 000 00 1 500 00 10 000 00 You can now type 0 in the Group Summary Amount filter box to get only those groups whose summary is O 84 Design Reports Compare String Values To compare two string values add the String Compare calculation 1 Using the CTRL key select two Column Headers 2 Right click either Column Header and select Quick Calculation Text Compare strings Field Field Compare strings 7777 Pz zz The result is a new calculation column where the result is 1 Field 1 is less than Field 2 0 Field 1 equals Field 2 1 Field 1 is greater than Field 2 85 Data Access Studio User Manual Substitute Patterns of Text with a Label To substitute text with a label use the Replace calculation 1 Click the E Calculation Editor button on the main toolbar 2 Type in Replace under the Type of Calculation 3 On the Parameters tab enter values as follows Source string Column to use as the input for the text replace Default value Column value or lite
9. 123 156 which output formats cccccseeeeeeeeeeeeeeeeees 156 Export DIRSCIOLY esas aa 123 Export Presentation E 156 Sei LEE 123 Extract Date Information 88 F A eegen A 16 FO WEEN 16 A A 16 SR EE 16 AM rl otitis 139 FAS lina lacra eegene Ehe 16 Held iteraci n 76 Pl rca 41 47 48 51 95 163 Briet 47 Range Relative cccccccsssscecssssecsesseeessesseees 48 Summary Vall aserto 95 INCI DO torcida rmac ccdanc ide 45 EUR UN P 49 94 Fiter MO Destacadas 41 76 WEE E eet 76 Filter Helper button 45 141 A E A A E e 45 Filter Tasks View 163 Filter Your Data 45 Finance USET E 21 create reports such 21 Financial Column Rollunp 29 FUP ANGLAIS neac a a 20 21 Financials Quick Heports 21 PNG EE 9 41 44 51 63 FUNC Boine n arar aia 44 FINO dde rese aid 110 Fine Tune Grouping sarasini 130 Fiscal Period audacia 24 98 SEHR TC Le EE 24 EISCAl PAra eh o ios 24 91 98 Ped O nates ee seas 32 FONT Re le 62 A A ERE 121 A A EE 98 G ET 44 60 E Minn GE 60 Get Automatic Account Instruction 109 Get DeESCIDUON wee eae 58 Cryptic JDE Dala ivan o 58 Ger IDE PE do ad 109 Get Related WR ri 93 Get Sample Dali acia 70 Get Sample Data button nnnnnnon00annnnnnnenoeeennnnne 12 Gel Total sia 60 E AAA A 4 CV GT Dat aa as 87 Kr 87 Grand Follar 60 Ee DEE 73 163 SEITEN 62 APD eee ela Gears 69 Or UP asadas 52 60 130 Ef ACCOUNTS 33 spa chcecodcienstchis Sanetndesdavet
10. 2 What value to use as a filler teg blank or 0 3 De duplication method 1 First value in group Applies the first value in group once and uses the filler value for the rest 2 Up to first level break Applies all the values until the first group level break occurs In the example above the first level break occurs when the Pay ltem goes from 001 to 002 3 Distinct values Applies a value if it is distinct within the group Otherwise it applies the filler 131 Data Access Studio User Manual 4 Group Column If blank the calculation uses the lowest level group when performing the de duplication If a grouped column is specified the de duplication will use that grouping as it s reference for groups and level breaks 132 Text Calculations Design Advanced Reports The following calculations are available on the Quick Calculation Text menu Pad a string to a given length Trim characters from a string Split one column into several columns Extract pieces of a column Click Layout Edit Calculations Add a new calculation for Pad Select Left or Right side to pad Check Strip blanks if you want to strip blanks before padding Enter a Padding character e g the space character Enter the Total Width of the new string Select an Input Grid Column to pad Click Layout Edit Calculations Add a new calculation for Trim Select Left Right or Both for side to trim Enter a character that you want to trim off
11. 3 Data Access Studio will create a new column called Unit Conversion Double click this new column to edit d You will see that the Short item is already populated with the Short Item Grid Column setting 5 If business units affect your conversions select a business unit column for the Business unit setting 6 Select Input units if your table has transactional units usually the UM field If you leave Input Units blank then the calculation will use the base unit of the item as the input unit 7 Select the desired Output unit This can be a parameter or hard coded value Once you get data you will see that the Unit Conversion column has the correct unit conversion for each item Simply multiply your transaction quantity by this conversion to get the converted quantity 92 Design Reports Get the short item number for a 2nd Item Number For the case where you have a JDE 2nd item number that you want to convert to a short item number use the Get short item number calculation 1 Right click 2nd item number in your grid 2 Select Quick Calculation EnterpriseOne World Get short item number 3 Data Access Studio will create a new column called Get short item number that is the short item number for the second item number Another useful case for this is report parameters Usually you will want your user to enter a 2nd Item number rather than a short item number In this case prompt the user for a 2nd item number and create
12. Find out more information about ReportsNow or Data Access Studio Partner Information Find a quality ReportsNow business partner in your area Also look for ReportsNow at your local user groups and Quest Events Office Locations North America EMEA ReportsNow Inc Zuidzijde 124 Denver Office Headquarters 2411 RX Bodegraven 5299 DTC Blvd The Netherlands Suite 760 Phone 31 0 653 899 736 Greenwood Village CO 80111 USA Email eric reportsnow com Toll Free 1 877 777 0655 Fax 1 303 693 6995 Trademarks e Data Access Studio and ReportsNow are trademarks of ReportsNow Inc e JDE JD Edwards EnterpriseOne World Software are registered trademarks of Oracle Corporation NET Excel Office Word are registered trademarks of Microsoft Corporation Data Access Studio User Manual Getting Started Sign into Data Access Studio JDE EnterpriseOne Your JDE Administrator will provide you a shortcut or web page address to run Data Access Studio Typically this is a link to DASWeb exe Once you run the link you will sign into Data Access Studio with your EnterpriseOne credentials User Password Environment and Role Role is applicable to E1 versions 8 9 and higher You can usually put ALL in the Role field unless you have been instructed otherwise EnterpriseOne Log In REPORISNOW Location 216 139 249 194 User Password Environment Role OF Cancel If you have any issues logging in please co
13. POW dido 52 152 160 UNC ia 52 AH EE 139 AMOUR cocoa asus eee 58 60 95 PIN OA bce i roars A 90 ANOTAN TA consonancia iia 91 ANOS Ch E EE 90 91 ANI DEE 90 ANY DUG Cle A o a 61 O attest 91 Apply Suggested Joins occcccccccocccncccnonancnnnnnnos 134 O EE 122 JA 122 Asset Account Balances occoccccccccncccncnncnnnnns 90 91 Asset Balances Drill Down 16 Associated Uescrptons 58 Ac A A A 58 A A OT oe 58 Back oro ld 62 Balance Seed lia 21 Begin PENGO din 90 91 EECH Materials lada 102 ll 45 135 Bottom Ten Performers ccceceeseeceeeeeeeeeeneees 111 A ene 29 STEI Ve Wale Elle 55 Burst calcula cds 18 BUSINESS Ulla ticas 21 BUSINESS view open 50 C Calculate Percentage between Two Columns 84 Calculation Columns 73 110 dalele eae Si ane O ER Pre 73 Calculado EECH 78 Eo AE 21 56 76 80 137 153 Cancel DUTMO Mia 12 44 LOODA EE 44 Capital Asset Management 32 E ere EE 91 CIEGO E 130 143 TO MOWING EE 143 EE BEE 49 NANTES ii 24 62 139 153 Coumi Capio Maiestas 139 COMPA EE 24 Del VE 62 Change Output Tvpe 144 Change ParaMeterS ccccccncccccccnnnccnnnnccnononccnnnnnnonnos 11 EE EE 109 ACCOM S anaa a AE EAS 109 Check Auto Uppercase Filters 63 Check Auto Width On Find cooccocccnccncccnccnccnnccnn oo 63 CHECK Delete ccoo 147 CG MOCK de cnnan 30 Check Open ne 123 Check Show Filter Values 120 E A ZN 63 q A 147 Check SUMMAIY srta en a as 63 WOO SS
14. Quantity SIP Quick Ge NEE Quick Calculation 58 73 83 84 96 109 110 144 Quick Map Column Quick Map Group Quick Map Summary Index Quek EE 41 QUICK REDOT is 20 21 152 Quick Report Designer 2 8 20 40 129 152 QUICK Report Men A 20 CC OSS O A 20 AAA N 94 110 111 A A A 121 R Kr Late LEE 45 47 EM nea ae rollito ek 47 o A E A A 47 Range Relative dica aaa 48 AS A A A E A EN 48 Rank BOLO Chica ae ee a 111 Rank TOP TON cda 111 Rank Top Ten Trend ica ticas 111 Recently OPENGA eironi 41 51 a leier 161 Star GEN 161 OO A E a tee 50 A EE 3 FOIA OP BEE 135 nt 135 DESUSO ea 135 EE 87 Given Date aiii ion 87 Relative Offset ooooccoccocccocccocccncconcnnnonenoncnns 87 89 91 Relative de Le ME 91 ee EE 91 PROTON i a a n 118 155 REMOVE COUM EE 130 RENAMO eee Sek 71 GOUN EE 71 A nena anneennen 97 USO O UU o E 97 Report 8 12 23 41 50 52 70 74 152 153 155 156 157 158 161 162 SIT E 50 Gl CATING senser A 74 A IN 52 AA A A 12 70 SUBS Sra 8 Report Designers coooccncccocccnccnoconccononccononennnnnnnoncnnos 8 Report LOOK occcccocccccocccoccconccocnconncnoconononanos 62 67 Report Parameter Uesgner 69 wi EI E 69 Report Parameter Designer window 69 Report Parameter mapping snssnesnenneoseneeesennnn 67 Report Parameters 45 67 69 70 90 121 141 EEN 141 Reports Publehed AAA 9 Reports scheduled AA 153 A A seta E 153 REDONS INOW EE 13
15. 652 20 32 406 00 13 aU ULI Right click any column header in you selection and select Quick Calculation Math Sum Net Net Ho ame Posting 01 Posting 02 Posting 03 42 350 27 35 260 00 59 510 00 52 019 73 24 055 23 19 560 00 93 234 00 food 4 652 20 3 650 00 32 406 00 31 403 80 1 546 32 350 00 13 230 00 12 631 68 Note that if your layout is grouped the sum column will show totals for each summary line and grand total as well Also note that if you want to add a grand total at the bottom of the sum Net Net Net SUM Bd Posting 01 Posting 02 Posting 03 4235027 3526000 59510 00 5 24 035 23 1958000 83254 00 o 4652 20 3650 00 3240600 Jo 4 548 32 950 00 13 230 00 2s ss 31 025 00 15 000 00 95 841 00 3 015 00 4650 00 7 372 00 29 8 542 00 2 500 00 19 621 00 Minimum Max Right click here to add a grand total at bottom Count Average Mone Customize 12 Design Reports Calculate Percentage between Two Columns You can easily calculate the percentage between two columns Multi select the columns you need by left clicking the column headers while holding down the CTRL or SHIFT keys het Posting 01 Met Posting 02 6 542 00 2 500 00 6 446 00 12 860 00 16 988 00 15 360 00 Accounts Receivable 24 035 23 19 560 00 26 048 00 23 000 00 50 083 23 42 580 00 33 095 23 27 220 00 Right click the column header on the selection and
16. Access Studio For each subscriber row uncheck the Allow user to change data selection check box By default the Subscriber cannot hide and show columns ina published report Check the Allow user to hide and show columns button to let the Subscriber hide and show columns in the published report Click W Publish Once published your subscribers will be able to see and run your Report Click PR Delete Data Access Studio will prompt you to confirm the delete Click W ok Click Close Press Excel Presentation See the section below 45 Data Access Studio User Manual Assign the report to a specific target If you have the permission to assign the target folder you can folder click the visual assist for the To folder and select a valid folder If you do not have folder permission then the report will go to the Unassigned folder from which a user with rights can move it to the desired folder Excel Presentation Options Publishing options for Embed Excel ABE JO Ok cancel Delete M Show Excel Presentation Only Make Excel Read Only i Hide Excel Menus Hide these Excel sheets DASLink Z Show the Excel presentation Only and Check the Show Excel Presentation Only hide the Data behind it To make the Excel presentation Check the Make Excel Read Only box unmodifiable by the subscriber Hide Excel Menus Check the Hide Excel Menus box Hide designated worksheets Add the names of the worksheets t
17. Access Studio User Manual Format Your Report Now that you understand how to save restore and publish your report we can address modifying your report When you modify the report you will be transforming your data closer and closer to the final form that you need it These transformations are the building blocks for creating any report Y 3 Tip The majority of formatting operations are found on the Column Menu To access the Column Menu on any grid el Presentation SE Clear Filters 2l Sort Ascending 2 Sort Descending DL Trending a Group By This Column Quick Calculation d E Calculations a Show Only Column al Hide and Show Columns KH Best Fit Column Best Fit All Columns Delete Column Rename Custom Date Format Column Caption Print Area See the following sections to see how to use each of the options on this Column Menu and more 48 Design Reports Show Only the Columns You Need JDE tables have many columns In most cases too many columns for a readable report That s why Data Access Studio lets you easily hide and show columns as you need Furthermore as a performance benefit the more columns you hide the faster your query will run over large data sets You can hide and show columns in the following ways e Click Design Columns in the Quick Menu above the grid Run Report yo Get Sample Data gt Get More Data E Refresh Close e Click Design Hide and Show Columns e Right click th
18. EE UE 135 FGI PAIS EE 122 Define Basic Header isc td 121 Define Crtera 76 VS MC Micra dano 76 Delete 9 50 51 52 73 74 81 139 155 163 Calculation Column 73 AY OU a shienit es ints A 50 51 A iat 74 Delete Column 73 Delete Parameter EE 69 Delete Row button 74 76 DEMIO EE 147 A E 70 152 Design Reports n nnnnannnnannoannnenanne 2 8 20 40 129 DESINE aigne aa 69 152 Be 69 A A A 85 BIEN 153 163 REENEN 153 163 Disallow Subscriber 0 2 2c ccc ceceeceeccecceeceeceeceeeeeeaees 52 DIVISIO ui abad 84 UA A eee ere nt 74 76 DB eee ee reer ent ak ner ed 16 18 DCU DOW iis ala a 14 18 DINGO A EN 14 E a PA EE a ea is De leas 6 158 A A 109 Date lena ati 109 A A A 50 139 153 163 Edit Calcula S iniiidnina aaa 73 109 139 147 Edit schedule ad alta 153 163 A reece mee ste ent ern ere ee 3 101 157 Embedded Excel eedd 30 64 nie 64 153 157 162 163 LIME MOCKING dile 163 SEI ei ER EE 153 ENG ele EE 90 141 ENS anos 45 161 ENDUIS Tai 58 English readable ici iia 58 SNOW Sic tenaaeeien sates 58 EniepnrseCneiVorld aia e a 109 EntepriseOne World menu 109 Enterprise Ome nl 58 Enterprisen E 3 6 41 51 133 EnterpriseOne Dala it 41 EnterpriseOne World occccccccocncnnccconoo 109 127 Sleiela ln Dn 6 158 Spec CIMNGSE estaca 158 EXC nuria riadas 3 13 30 64 67 73 156 o O 64 VAMOS ari ee 64 Excel Presentation eins 67 Excel EE 30 EXPO osuisi a eee ee eee 13 119
19. Specify when you want to run your See Specify When You Want to Run it report s To save your changes Click Y Ok To cancel your changes Click Close 138 Schedule Your Reports Specify What You Want to Run To specify what you want to run 1 List the reports 2 Select the output types 3 Select the destination 4 Select advanced options 139 Data Access Studio User Manual The first step in defining a task is specify what reports you want to run You may select one or more reports The reports may be published or private Based on income statement 8 2010 DAS VIEW Add one or more reports del Click the dd Report button DAS will present you with a list of published and private reports You may select more than one report at a time Once you add a report DAS will show you options for specifying output types and destination Remove a report Highlight the report or reports you want to remove Click the let Report button If you remove all the reports then DAS will hide the output types and destination options 140 Schedule Your Reports Once you have added one or more reports to your Task you can specify which output formats to export your report You may select one or more output types CN Export Presentation as Excel HTML PDF E Export Data as Excel HTML POF f TS ML If your report presents data in Excel you may check any of the Export Presentation as check boxes If you w
20. Today s Date cccccessscccccecesecececeeeeceeeceeuseeecesuaaaueceesuaaseeeeeseaaeeeessaaagseeeessaaass 41 Fiter wit Form advanced asc dei eee cat 42 Manage Your e LEE 43 ACCESS OU REDON anida 44 Publish QU Repon tO OME EE 45 Manage Report SUBS Susi da 47 Formal YOU REDO EE 48 Snow Only te COMAS Y OU NEGA airearen ES E reside IAS 49 Place Columns inthe Positions You Need cocoa A ead cc A A AAA 50 Get Description tor GrplcuUDE Dalai OS 51 Get Totals DY COI are ro arian eases cna ees aa ees ns an asec N O ts eee 52 SNOW DIA INAN One Vi A OS 53 Customize the Look and Colors of Your Layout 54 Customize the Look and Colors of Your Layout continued occccccccocccnnccononcncononcnnncnnnnonancnnnnnnnanenennnnnnnons 55 Add Charts and Advanced Formatting esla ion iia ice 56 GOMirol The Excel Presenta Te DEE 59 PROMPLIO Vale Sesion abolir ais 60 Promption Vall s ee nd Te MEET 61 Rename Ee E e WEE 62 COPY SOLIS EE 63 Galculate Valles Over Your RE 64 Maker IS Trote radeon cad ceca 65 Add Column Values into a SUM ColUMin c c0ssseeeeceececcceseeeeseeecccccsesedeeeeseescceseeeteesescccouseeseeseeeeesoesees 72 Calculate Percentage between TWO Columns 73 Table Of Contents Perform Math Operations on Your Data 74 Calculate the Date Difference between Two Dates nnn00nnnnannnnnannnnannnnsannnrennrnnnnrronnrrrnsnnernnrrennnrrrnnrrennne 75 Calculate d Date Relative t0 a Given Dale cuida 76 Ex
21. a Variable that converts the 2nd ltem number to a short item number 93 Data Access Studio User Manual Calculate Work days lf you need to report how many days a resource has worked then the Work Days calculation is for you JD Edwards allows you to define various work day patterns The problem is that this information can be difficult to utilize in a report The Work Days calculation simplifies the information so that you can get relevant information into your report such as Work days holidays or weekends per month Whether a given day is a work day holiday or weekend Work days holidays or weekends per year Calculate by branch plant and default branch plan Calculate by shift To add the Work Days calculation to your report 1 Click Design Calculations 2 Select Work Days Branch Typically the grid column value for a business unit This will be the branch that the calculation uses when looking up the work day information Default Branch If no Branch is specified or if it has a blank grid column value then use this value Year Four digit year that represents the year you are interested in getting the workday information for Month O all months 1 jan 2 feb 12 dec Day of the Month O all days in month 1 day one 31 day 31 Shift Default blank Or enter a valid shift code open F0007 Work Day code W work day E weekend H Holiday Calendar Type Default blank Or enter a valid calendar type open F
22. as Sum or Count Row to Fetch Optional Default value 1 Defines the relative row that you want 1 First 2 Second etc Useful when you need to select rows other than the first row from the target table Define fetch To fetch information you specify fields in the target table and values you want to query against those fields The left hand side of the Define fetch defines the target fields The right hand side defines the values to query for You may add or delete field names as you wish Define result Columns from the target table that you want to return to your report If you selected a Summary Type above then you may only select one column here which is the column you want to summarize Otherwise you may select multiple target table columns 82 Design Reports Conditionally Rollup a Value There are three ways to conditionalize values in your report 1 Only Rollup If parameter of a calculation 2 Conditional calculation 3 Replace calculation Option 1 Only Rollup If Parameter Most calculations will have a parameter named Only Rollup If You can edit this parameter to have any condition you need that specifies whether you want the calculation value to apply For instance let s say you had a calculation that calculates the total price You could edit the Only Rollup If parameter to specify that you only want the total price to apply if the customer has paid on time Once you set the condition then the total price
23. ccccccccccnnncccnnnncnnononenanonnnnnnnnnnanncnnnnnnnos 60 SUMMA aeina ea seer bse 64 Summary Tvpe 93 e o A A ern rae 93 Summary Value ooooonnnccnncccconoconccnnnnncnnanannnenonnnnnos 95 A EE 95 Switch Klee 126 SM 95 144 System Calculations onoo0aannnnnnnoneaanennoeeennnnn 144 System MEN Un eiaa essast Seance gene 144 T Table 43 50 129 133 134 135 148 O EE 43 A A II 135 AA EE 132 Table Lookup occccccoccccncccoccncoconinononos 93 127 142 o ie ce she E 93 RE 153 155 156 157 163 e EE 163 CUS AOS EE 153 163 Task Data recurrence ocooccnccnccnccncccncnncnnnnnnnnnnos 160 Technical Knowledge 2 8 20 40 129 152 A ee tee 96 97 99 159 163 Substitute Patterns incas 97 RR Calcula Suri r 147 MA IN 147 ten EE 162 TIME recurrence win 162 date FECUM DEE geheie sr 162 Time Updated ui aa 109 RR EE A 13 To Tab Separated Values sees 13 TOGAY EE 45 48 88 SO EE 48 Today Range Filter ccccccocnncnnnncccnnnnncnnnnos 48 Reie E VEER 48 88 TOMO taa ii rod 3 Toolbar Gancel DUHOM WEE 44 nie EE 41 51 TOPs 120 TOTANA EE 147 EN eii cena 147 Trending 110 111 112 114 116 117 118 A arenas 110 EE attente EE EE 110 3 dl NEE 147 FAM DelNmiterS conil n 147 Troubleshoot Calculation Error 142 Turn Row Values into Column Values 118 Type 41 90 91 93 94 97 139 153 155 156 157 159 CalculatiON o 90 91 9
24. columns E g if your rows had Union Code information then the Trend will create a new column for each distinct Union Code 5 Choose how you want to trend Sum Count etc 6 Choose the column you want to use as the ranking quantity If you selected Count in the previous step then this field will be hidden 7 You can append multiple Cross Tabular Trends to create Dashboards If you want to replace the previous Cross Tabular Trend click Remove previous CrossTab columns 8 Click Y Ok 106 Design Reports Export and Print Results Data Access Studio provides quick and easy exporting and printing See Export and Print for instructions on how to export your grid data 107 Data Access Studio User Manual Set Default Printing Preferences Data Access Studio provides the ability to specify printing preferences for each layout 1 Open a Layout 2 Click Design Printing Preferences A A Change margins Change the orientation of the printout Hide or show the filter row on the printout Change the paper size Change the Embed Excel Print Driver 108 Enter values for Left Right Top or Bottom Click Portrait or Landscape Check Show Filter Values to show filters Uncheck to hide filters Click on the Paper Size options box Select paper size from list Use the visual assist to select a print driver on your workstation If you are the administrator on your workstation or if your administrator installed
25. find hidden yet important patterns in your data Quick Facts You perform a trend by selecting a column right clicking it and selecting Trending You specify your Trend using business language Once you apply your Trend you will see the results immediately You can undo your trend by pressing the Undo button You can modify the columns in the Trend by using Quick Calculations or Edit Calculations with the Editor Each trend has common options explained below e This section discusses many of the Calculation Columns that let you transform your data into the format you need Common Trend Options The dialog box for each trend operation has the following options in common Show summaries only Default checked When checked the resulting trend will collapse all groupings to the summary level thereby hiding the detail lines When unchecked the Trend will show summaries and details Hide unused columns Default checked When checked the Trend will hide any column that does not have a filter and is not a part of the Trend If unchecked the Trend will not hide any columns 98 Design Reports Show Your Top Ten and Bottom Ten Performers Imagine you want to see your top ten customers or products You can create this type of report with the Rank Top Ten Trend 1 Right click a column in your grid that you would like to rank 2 Select Trending Rank Top Ten Sample screen shot Quick Start af Ok K Cancel Top
26. for the Group Row menu CESSES EST TEE ET ET EE TT TEE SEET GE Full expand e Full collapse Jus Depot el Group all to this level Set grouping level E Clear grouping Hide Collapsed Summaries Group Display Style d Add Count A A Show all details Collapse all details to the top group level Collapse all details to a given group level Always collapse details to a given group level Clear all grouping Hide Show summaries on the collapsed group row Show the value only for the group row label Show the category and value for the group row label Show only the text portion of a label in the form number text Show the group row at the top or bottom of the details Add a quick count to the group row 118 Click Full expand Click Full collapse Click Group all to this level Click Set grouping level On the resulting dialog enter the group level to collapse to If you use the up down arrows the grid will show you a preview of the rollup Click Clear grouping Click Hide to hide Click Show to show the summaries Default Click Group Display Style Value only Click Group Display Style Category and value Click Group Display Style Indexed value For instance if your group text is 1 My account then selecting this option will display it as My Account The indexed style lets you use the number portion to sort the group labels as you wish Click Group Display Style Group rows on top to show
27. for your report ti a a Search for a column by name Type in the name of the column in the Filter Box under Description Select a column Check the columns you want in your join The fewer columns that you select for your report the better your report will perform Hide all columns Click the Hide All Columns To create the join based on all l i Click Y Save and Close Data Access Studio will attempt to information entered create the join with the information provided If there are errors Data Access Studio will popup the errors and how to resolve each To cancel your changes Click Cancel 123 Data Access Studio User Manual Working with Your Custom Table Join After you create your join you may edit it at any time 1 Click Layout Customize View W Save Close Save And Close aK Delete K Help ZE Apply Suggested Join kw Hide All Columns Report Name My report Begin designing your private report by adding tables that you want to query in the grid below Tables Suggested Joins Actual Joins Columns 2 Edit the join as you need 3 Click Save and Close to save changes and submit queries with your view 124 Design Advanced Reports Edit Calculations with the Editor Data Access Studio provides the following ways to edit calculations A TOS Edit a single calculation Double click the Column Header Or right click the Column Header and select Calculations Edit Edit all calcula
28. is a value for Date Fiscal Year If blank calculation will use the current fiscal year of the company Otherwise calculation will apply the period entered Value is ignored if there is a value for Date Date If a date is specified calculation will calculate the fiscal period and fiscal year for the company for this date Ledger Type If no value is specified then all ledger types are rolled up Ifa value is specified then only values for that ledger type are rolled up For example AA rolls up all the actual amounts because the actual amount ledger type is AA Data Item Prefix advanced By default the calculation will rollup ANO1 AN14 or though AN12 if AN13 and AN14 do not exist Some tables however have other arrays of balance amounts that can be rolled up similarly For example the Tax History F016136 table defines BWO1 BW12 To use Relative Period on this table enter BW for this parameter If this parameter is blank the calculation will default the AN prefix Reverse sign criteria In JDE you will need to reverse the sign of the revenue accounts to get a correct income statement To specify the accounts that are revenue click the visual assist and use the Criteria Editor 80 Design Reports Add begin balance For balance sheet accounts you will need to add the beginning balance to get a correct balance To specify the accounts that are balance sheet accounts click the visual assist and use the Criteria Editor Of
29. left click multiple rows Then click the Delete Row button Copy multiple list items to the bottom Hold CTRL key down as you left click multiple rows Then click the Copy Row button The new rows will be placed beneath your selection Move multiple list items Hold CTRL key down as you left click multiple rows Highlight the row you wantto move Then click the Drag and drop grip bar and drag row above or below current position Release mouse button to drop to new position Save your list Click File Save List See topic Save a List 65 Data Access Studio User Manual Load a previously saved list Delete a previously save list Export your list to XML Import your list from XML 66 Click File Load List See topic Click File Load List See topic Work with Your Lists To export your list to XML click File Export Exporting to XML can be useful if you want to do a mass search and replace or other options in an XML editor Once you have modified an XML export you can import it back using File Import Design Reports Define Criteria for the List Item Once you click the visual assist you will see the Define criteria form below Define criteria W ox K cancel Copy Row 3 Delete Row This form allows you to specify what criteria the data has to meet to apply to the label you entered For instance let s say our label was US customers then the Criteria may be Country US In this example Count
30. or which day of the month using the options available Year Select the Year radio button Every i gt earls f Dau January y fi C The first y The of January S of January Gs Select how many years or which day of the year using the options available Sunday 145 Data Access Studio User Manual Specify the Range of Recurrence Once you specified the date recurrence pattern you want select when you want the recurrence to start and end Range of recurrence Stat Taatano 10 00 004m Ce No end date C End after DU lt occurrences End by 912010 el 10 00 00 AM Beet Specify when to start Enter the start date and time by the Start label Run report recurrence with no end Select the No end date radio button date End after a certain number of runs Select the End after radio button and enter the number of times to run the report End by a certain date and time Select the End by radio button and enter the end date and time 146 Schedule Your Reports Specify Time Pattern In addition to the date recurrence you can also specify a time recurrence within the date recurrence For instance you may want to have a report run on weekdays every hour from 8 AM to 5 PM To specify time recurrence 1 Select the Time recurrence tab 2 Check the Enable button on that tab A re Specify hours to run your report Select the Hours tab Check the check boxes for the hours you wa
31. select Quick Calculation Math Division Account E k Het Het Divizion E ID Posting 01 Posting 02 ole te RAR a Dae S446 00 12 860 00 16 983 00 15 360 00 1 11 J Accounts Receivable 24 035 23 19580 00 1 23 26 045 00 23 000 00 50 083 23 42 580 00 1 18 33 095 23 27 220 00 1 22 Notice the division calculation shows divisions across summaries and grand total automatically Now right click the new column header and select Custom Numeric Format Custom numeric format ff OK gt Cancel Format Percent wi Precision 2 i The number le converted to a string that represents a percent If the number is negative the string produced starts with a minus sign The converted number ts multiplied by 100 m order to be presented as a percentage The precision specter indicates the desired number of decimal places If the precision speciher is omitted the default numenc precision given Du the current culture information Preview 341 68 Choose Percent and Click Ok 73 Data Access Studio User Manual Perform Math Operations on Your Data You saw how to sum columns and get the percentage difference between two columns You can also do the following calculations on columns In all cases select one or more numeric columns right click o
32. so that they may benefit from your work List of reports for a given table A a Mame e TO ti OOOO Save your private report Click File ll Save If this is the first time you are saving your report Data Access Studio will prompt you for the report name Enter the name and click J Ok Pick a report you saved from an open Click the List of reports option box on the tool bar Select the table report you want for the table that is open Pick the default report Click the Report List option box on the tool bar Select the blank report at the top Copy your report Click File Save As Data Access Studio will prompt you to enter anew name Once you click J Ok Data Access Studio will copy your Report to the new name Delete a report Click File Delete Data Access Studio will prompt you to confirm deleting your report Click L Ok to delete Restore your Report from the last time Click File F Restore Data Access Studio will prompt you to you saved it restore the report from the last time you saved it Undo the last change you made Click Undo Redo the last thing you undid Click Redo Once you are finished editing your Report you can e Run it privately e Publish it to other users 43 Data Access Studio User Manual Access Your Reports Once you build your collection of Reports you can access them at any time by clicking File Open My Reports Once clicked Data Access Studio will show you all of you
33. the ReportsNow PDF print driver you can select this print driver to print to PDF If you have another PDF print driver you can select it here so that when you print from Embed Excel the print automatically converts it to a PDF You can also click the Preferences button to change the settings of the print driver Design Reports Define Basic Header and Footer Data Access Studio provides the ability to specify a report header and footer to any Layout Once defined the header information such as page numbers and title will export to formatted types such as PDF HTML and printer outputs When you press save on the layout the header footer information will be saved with your layout To define a header and footer for your layout click on Design Header and Footer x Header Ei a oly Font Microsoft Sans Serf 6 25pt Layout Page of Pages H Footer e lt i all Font Microsoft Sans Senf D opt IE HO ee OF Cancel Enter text and tags in these areas Quick tag buttons E Loi E SE pa z This form has six areas for you to add text 3 areas in the header representing left center and right and 3 areas in the footer for left center and right In addition to typing text into these areas you may click the Quick Tag buttons to add dynamic information such as Page number Page number of Pages Date Time User Layout name Report Parameters show
34. will only have a value if the condition is met Option 2 Conditional Calculation You can add the Conditional calculation in most cases using the Quick Starts See Turn Row Values into Column Values You can also add the Conditional calculation manually 1 Click the E Calculation Editor button on the main toolbar 2 Type in Conditional under the Type of Calculation 3 On the Parameters tab enter values as follows Value if within criteria A grid column value or literal value to pass through if the filter criteria is met This is normally the column you want to selectively roll up Value if outside criteria Usually 0 or blank This is the value that is used in the rollup when the criteria is not met Filter Criteria Enter one or more filter criteria to define what conditions you want to rollup For instance if you want to rollup amount where LT AA enter LT in the field box and AA in the value box You may use any valid filter to define your criteria Option 3 Replace Calculation The replace calculation is a good option when you have many cases that you want to substitute for another value See Substitute Patterns of Text with a Label for more information 83 Data Access Studio User Manual Filter on a Summary Value When you Group and Summarize your report and you need to query on the summary values themselves add the Group Summary calculation to your report Batch Number A Amount 711161 1 500 00 10 000 00
35. 00 FinancialDistripution Company Sales Order Youth Sport Bike 6 500 00 Financial Distribution Company Sales Quote Touring Bike Blue 1 450 00 Financial Distribution Company Sales Quote Youth Sport Bike BS 000 00 FinancialDistribution Company Blanket Sales Order Touring Bike Red 2 Hold the mouse key down and move the column to the desired position 3 Letgo of mouse button to drop Sales Order Detail File F4211 in One World Local Extended Order Compary Price Touring Bike Red 9750 00 FinancialMDistribution Company Sales Order Youth Sport Bike 6 500 00 FinancialDistribution Company Sales Order Touring Bike Blue 6 500 00 FinancialDistribution Company Sales Quote Youth Sport Bike 1 450 00 FinancialiDistribution Company Sales Quote Touring Bike Red 65 000 00 Financial Distribution Company Blanket Sales Order j j E RS NNN FinancialMi tribitinn Comeany Alanket Sales Order 50 Design Reports Get Description for Cryptic JDE Data JDE Data contains many codes and numbers that are not readable in plain English Data Access Studio solves this problem with instant Associated Descriptions For instance consider the data in Account ID Data in this column Is cryptic Account Amount ID 0001037 65 00 00018593 25 00 000185631 10 00 00032295 50 00 00046341 20 00 00046015 30 00 mia LaLa oe SA Ent a Clearly the data in Account ID is not understandable in plain English JDE however has an Assoc
36. 0007 Calendar Key Default blank Or enter a valid calendar key open F0007 94 Design Reports Model Business Unit over the Chart of Accounts The Chart of Accounts in JD Edwards is typically tied to the specific account information MCU OBJ SUB rather than a Model account just OBJ and SUB Sometimes it can be handy to view balance information for instance with a different chart of accounts So for instance you may want to compare different business units or consolidations of business units to one master chart of accounts This Model Account calculation solves this issue To use the Model Account calculation 1 Open an amount table that has the following fields Business Unit MCU Object OBJ and Subsidiary SUB E g Account Balances F0902 or Account Ledger F0911 Accounts Payable Ledger F0411 etc 2 Click Design Calculations 3 Enter Model Account 4 Inthe Parameters enter a Business Unit to use as the model account To Create a Chart of Accounts from the model account 1 Once the Model Account is created right click the Column Header and select Quick Calculation EnterpriseOne World Chart of Accounts That should do it You should see a chart of accounts based on the model business unit You can not roll up quantities to this account structure Chart of Accounts Model Account RA Beg Balance PYE Forward H Current Assets 5 317 467 00 Fixed Assets 4 124 761 90 6 439 228 90 _ Liab
37. 10 Rank the top 10 bazed on the Sum W Show summaries only W Hide unused columns Delt Product ol Quantity Shipped 3 Enter the number of rankings you want to see e g top 10 25 100 etc 4 Select the column you want to rank Data Access Studio will automatically populate this field with the column you select in step 1 5 Choose how you want to rank Sum Count etc 6 Choose the column you want to use as the ranking quantity E g Quantity Shipped If you selected Count in the previous step then this field will be hidden 7 Click Ok To show the Bottom Ten repeat the same steps above except select Trending Rank Bottom Ten 99 Data Access Studio User Manual Organize Date Information Imagine you want to see your items sold by last three months current quarter year to date and year and compare to the previous year Organize Dates lets you do this over any reference date as follows 1 Right click a column in your grid that you would like to organize 2 Select Trending Organize Dates Sample screen shot Quick Start Sei EJ B Ok Cancel Delete Row 7 Organize Dates Se 7 For each tern w organize dates S E between control panel date and Order Date Ww rollup Cum s i of Quantity Ordered bu Date Organization Last Months Current Quarter Current YTD Current Year Compare to previous Wears mM Show summaries only w Hide unused columns 3 Select t
38. 3 94 97 139 ODIGCE NaMe E 41 Keele 144 U Nu REH 104 UNC to eRe Re RO oT ePIC 157 Ada 50 127 INDEX ASSISTAN EE 127 Umembed Ge EE 67 Unit Conversion ccceeceeccecceccecceccesceeceeceeeeeeaes 104 Universal Naming Convention ccccccccccconccnncnnns 157 iv en E e 54 Use Paren UC WIG redes cae 102 Use more advanced calculations 143 User Guide Structure ocoocooccnccnccnccncnnncnnnoncnncnnnnnnnnss 2 User lev srera 8 20 40 129 152 Re E e e EE 18 V MATOS UNS EE 104 VION S A NN PT 41 148 163 Visual Aees 45 123 133 139 157 Suri oca cto acta tock 123 Visual Assist DUTON sida adas 45 SOO ee 45 W Want 26 60 94 114 116 117 118 152 153 154 155 156 157 159 160 161 162 163 PONUD noto 26 60 94 114 116 117 118 Ee 139 le dee EE 139 A eee tae 3 WY CC A A 88 160 BUS 88 Which output formats nenaanneaennnneennnnnennnennnnne 156 O 156 WVIICIC AIG A a O 51 Wizards odia 74 VV OV EE 3 VV OKO AG ani dencia 125 126 Ae A OR 81 106 163 e 106 WA ON Gs overeat A O RE 6 58 World Administrator cccecceceeseeceeceeeeeeeeeeeeceeeennes 6 WV OMG Ds 41 Wond SONWatS aca 3 Www Heportehow com 3 X AMM aan 13 Y YOA ta Set ee 48 86 88 91 98 117 160 IAW iwi OO 88 Yeal TONO tee ee ge 116 Yea EE 90 Ye iMON aN alada 110 You 152 153 154 155 156 157 158 159 160 161 162 163 Run Reports Published cccccesseeseeeeeee
39. Capital Syster CSC Corporation Marthe Maud Diet Corber ao Mo 4 Ly n it pa os aaa i Pa o a Satna Leie Bt Dann sa Deg Fier Greater Dardi peure Grewtar Dsg para Greater Cara pS d than D st than Cone y han 2 D st g Dee Coes Bye D gei 1 Days Bye 2 aed Y Days Gresier than 3 Dey Capital System t oe DI 0 DI 3 CSC Corporation D I DI 0 D 3 Morthern Manuti ist Center A 0 0 a d To present your data in Excel right click a column to get the Column Menu Select ial Presentation Data Access Studio will display the following screen 96 Close Ze Map Columns to Excel Presentation Fe Between 2 and 3 Days Greater than 3 Days Add additional columns to present Re order the sequence of columns Map data to embed Excel but do not graphically present it old style Embed Excel Change the target sheet where you want to present the grid data Change the target cell where you want to present the grid data Change upper limit on the number of rows for the presented data Only map data and hence do not graph the values Design Reports One chart per column W One chart per row Chart type ally 3D Column Four columns WM Round corners Click on the visual assist in the From grid list Select the columns you want Click on the grip bar and drag the column name to the position you want Set Sheet to blank or DASLink Change the Sheet text box
40. Change how you want to prompt for date Change the text for prompting date information Define the balance sheet accounts Limit which object accounts your financial report shows Exclude zero balances from the report Present your financial report with Excel capabilities 34 Select By date or By period from the pull down box Select one of the pre defined prompts As of or For the period ending from the pull down box Or you may type in your own date prompt text For balance sheet accounts one must add the beginning balance to get the cumulative actual amount of the account Define the balance sheet accounts in your system here by any criteria you choose object range category code etc Once set DAS will remember the setting as you create new financial reports Enter a begin object account OBJ value and end object account value that define the inclusive range that you want to display in your report Check to exclude 0 balances Uncheck to include zero balances Check Excel presentation Design Reports User level JDE Knowledge Can Design Technical Knowledge wm Subscriber Not needed Minimal Quick Report Designer Minimal Yes Minimal Advanced Designer High Ad Hoc Report Design is one of the strengths of Data Access Studio Data Access Studio empowers you to deliver a large variety of reports in a timely manner by e Allowing you to visually create your report with sample data e Presenting real time dat
41. Click Best fit sheet Check the Auto fit to printer IMPORTANT If you want to add page breaks to your presentation make sure to uncheck Auto fit to printer Check the Filter sheet to hide the spacer rows as defined by the Row Padding Uncheck to show the spacer rows IMPORTANT Show the spacer rows if you want to apply Excel Format Cells Uncheck the Filter sheet button Select the cells and spacer cells and set the Excel Cell Formatting for the cells E g numeric format bolding etc Check the Filter sheet button when you are done Click Report look Click Spreadsheet look Click Toggle menus Click Toggle headings Click Toggle gridlines Click Toggle worksheet tabs Click Toggle status bar Click Clear active sheet Click Map visible columns to Excel Once you click DAS will present the dialog to present columns in Excel Add Charts and Advanced Formatting Click Advance Edit With this option you can append new maps at the end Use this option if it is important to maintain the order and columns of a previous mapping intact You may also add additional mapping such as Report Parameter mapping with this editor Click Un embed Excel DAS will remove the Excel presentation and show the grid showing your query data 99 Data Access Studio User Manual Prompt for Values To prompt the user for values click Design Report Parameters Once Clicked Data Access Studio will open the Report Parameter Designer
42. Data Access Studio User Manual Table Of Contents EE 1 User Gude Re EE 2 ReponsNow Company Into rmalOM EE 3 DA LEE 3 elef EE 3 Geng olane WEE 4 SONIM Dala ACCESS Ulea 4 MS a a a a II a dt 6 RUN REDONS PUDISACATO VOU Sebisa en cate cel 8 SIE e TE 8 R n Repons PuBISNEd TO MO sarita o dd 9 Change Parameters and Data Selection ieimerroiisenoneni na aa a iaa Oa a eiia iai 10 PALMA LS REO Rania tido 11 Export and Placas litio didas 12 BIBI NEE 13 ASP o ee ee ee ee eee eee 13 ACCOUNT Ledger dnll GOWNS ada 14 UserdeMm edDAIIDOWA cacecte tc cteta siete idas 15 DESTA QUICK EE 17 Create a Financial Balances Hepnort naie i e a aa ea 18 Specify Type of Financial REDON orreina aa a a a 19 Specify Financial berode cccccceesssccccceceeeesseeeceeeeecseeeseeeeeeeeeeeseeaeeeeeeeeseaeeaeeeceeeeessaeeeeueeeeeesesueeseeeeeeeesssaaaas 20 Specify Financial ROW Hollunp 22 Financial Column Rollup Opponal 25 Special SUMING eege iii tada 26 Create a Fixed Assets Report cccccccccccsssseseeceeeeeeaeeeeeeeeeeeseueeaeseeeeeeeseseeeseeeeeeeesueeaseeeseeesssaeaaeeeeeeeeessaaagesss 28 Specity 1ype 0t Financial REDON td ita 29 EENEG ee dee e eege ee See Seet 30 Specii Fixed Asset ROW ROUD sra ade 32 Special Setting EE EEN 34 Desi TAC Lei 35 Get me Data You Necesitaria 36 Opera Table tor First TIME seaun ii 37 Opena Table tor the Second Tine esis ie 37 Si A A rT 38 Stee del RE 39 SI WE Le EE 40 Filter a Range Relative to
43. Description Click on the Filter box under Member Description Click the Filter helper button F Choose any of the Filter Helper options such as Begins with Contains etc Type in the search text Click q Find on the toolbar Browse the list of results in the grid Highlight the row s you want to open and click Y Open Open by Quick Open Type the Object Name of the table or view you want to open in the Quick Open box Click d Open Change the data source where you open Click the Data Source option box Select from the list of the table EnterpriseOne only available data sources Note if the administrator revokes this permission you will not see the Data Source option box Open a recently opened table Click the Recently Opened option box Click any item in the list to open the table or business view 36 Design Reports Open a Table for First Time When you open a table or business view Data Access Studio will close the form above and open a new window for the item you want to query For example let s say you opened the JDE Work Order Master File F4801 You would see a form that resembles the following wl Purchase Order Detail File F4311 in Business Data Local gt l meza Run Report yo Get Sample Data gt Get More Data a Refresh Close A Design Order Co Order Or Ty Ord Suf Line Number Business Unit Co Doc Co Original Order Orig Ord Mumber Mo When Data Access Studio opens a table for the first time not
44. English readable nnnnnnannnnnnnennnnnennnnnnnnennenenne 58 NS a a a 122 ELO Dl dico 69 Show Account Balance 91 Show Additional Information c onccncccnccncccn o 129 Show Additional Table Information 148 SNOW COLUMN Siicshens scans daa 56 SHOW AU tia bare 61 Show Month Trends 114 SHOW ONIY fico is A 56 Golumns YOU INGE HEEN 56 SHOW Year Trends tad 116 Show Your Top Ten 111 Sign into Data Access Giudio 4 6 SIMPLE sua ioe ie eit nee et res acid 133 135 Sliding Calendar Date nnnnn000annnnnenoneannnnnnneen 89 SOM ASCO ele EE 61 SOM DESCETIGING DEE 61 A EEN 93 154 Specify 122 152 153 154 155 156 157 158 159 160 161 162 163 AT El A Pe Oe 122 Specify Financial Embed Excel snsesennseennneenn 30 Specify Financial berode oo000000aannnennneeannene 24 Specify Financial Row Rollup ooo 26 Specify Financial Source Data 23 Specily WN a 159 laca 147 Spreadsheet LOOK ia 62 67 E RE 161 FECUIFENCE ii ee 161 o EE 147 EN is 147 dl Te EE 147 Shing EIERE 96 FOG DEE 96 SUDSCHOSI ONY EE 8 SUDSCHIDENS EE 8 54 152 leie 8 Substitute battems 97 A a A a a EAA 97 ei let le eraasi 147 Suggested JOINS ooocccccccccccnccnnonccnnnnnnnnnnos 134 135 SUI asno contri olsen lolita ill 60 83 85 93 o A EEEE 60 Een 60 SUN EC RT EE 83 Add Column Values mto 83 SUM COUN isc 111 114 116 117 118 SLIM Igel dicas 130 SUMMArIZINQ
45. Open file after export If unchecked the export will write file to the export directory and notify you that the export finished only Automatically name the export and Once the print area is defined for a layout you may want to see which columns comprise the Print Area To highlight these columns select Design Print Area Show 111 Data Access Studio User Manual Document Your Report You can place user notes and design notes in any report Additionally DAS generates at a glance design information about any report including Filters hidden or shown Grouping Parameters and variables Calculations Hidden columns Sorting Template information and more 112 Design Reports User notes are for the end user of the report The notes should instruct a user on the e Purpose of the report e Meaning of the report s parameters filters e How to interpret results To add or edit user notes for a report 1 Open a report 2 Click View User Notes In the Notes form you may paste WordPad RTF or Notepad plain text into the edit area You may also type in the edit area You can place hyperlinks to external documentation in the edit area as well This is useful if the documentation is too large to store within you report which could be a concern if people run your report over the web Keeser 1 See Paste text from a WordPad document Select all text in the WordPad document Click Edit Copy Switch to the Note
46. The Relative Period calculation resolves these challenges To add a Relative Period column 1 Open a table or view with ANO1 AN14 such as the Account Balances or Asset Account Balances tables Your table can have any array of XX01 XX14 of columns as well for the Relative Period calculation 2 Click the El Calculation Editor button on the main toolbar 3 Type in Relative Period under the Type of Calculation Once you tab out the calculation caption will read lt Description gt lt Year gt This means show the calendar month description and year for the column caption You can use the lt Period gt tag to show period and the lt Quarter gt tag to show quarter You may place these tags as you wish in the Caption to show the information you need 4 On the Parameters tab is where you define the starting period and relative to period information as follows Parameter ties Relative Offset Amount to offset the period See the Offset Group Periods table below Group Periods by Select Period Quarter or Year See the Offset Group Periods table below Company If blank calculation will use the default company If set to a grid column calculation will use the company for the grid column value for each row The companies you query need to have the same fiscal date pattern Begin Period If blank calculation will use the current period of the company Otherwise calculation will apply the period entered Value is ignored if there
47. Trademarks sacas 3 ReportsNow Information occcccooccncoconcnnccncononnnas 3 Reports NOW siii 3 OOK aona haere Sion Owl rae inte ee ee ner nae pee 3 PROS Ole yor ahs a oe hae oe tate 50 55 ROSSUN BEEN 13 AR 13 PROS UNS ida a idilio dais 157 Ge EE 157 RIG isis 120 147 153 Data Access Studio User Manual RIGHT OUTER aca 135 145 A A O A 135 RIO NECIO iaa 56 73 130 Column Header iii da 56 73 FIG CHICK Dana ds 51 Ee 6 52 158 Rollup 26 60 78 89 94 114 116 117 118 130 CHOOSC oe O 26 O EE 60 Wat 26 60 94 114 116 117 118 Riegel duna os 18 Row Calculation ici did 78 ROWNO EE 144 How mdependence 73 Biel 24 26 30 Uergelen 125 126 Run 9 12 70 152 153 154 155 157 158 159 160 161 162 163 REDON ind lina A 12 70 reports scheduled nnoannnnonennnnennennnnnennnennnne 153 MEIER 12 161 Run Reports Published AA 9 Eege EE 9 S Sales E ut e EE 114 Sample datar V 44 SAVO aon eee 50 55 80 137 138 153 E eebe EE E hated 80 SIVO AS nur ta lis 50 Save Stiller 74 80 Save TOA VOU luso di 9 o se Ean 153 160 163 Schedule oeae a E 152 Second STED serme a oipe sS 44 Second TIME current a 43 SC MMC BEE 157 PO SUNS reae a 157 Set Default Printing breierences 120 Set export OPTIONS ccccccoccocnccnnnnccnononnconnnncnnnnos 123 Set Relative Ose snene 91 Set To Selected Columns 122 Show 56 58 69 111 122 123 130 155 163 BOOM Marat tiles 111 Es ee de 56
48. You can then define cell and row calculations over any label in your list For instance you can sum up several items or take a percent of one label to another You can setup a List with the Calculation Editor or Quick Report Wizards LA Edit List ks Y Save and Close Close e File Copy Row Insert Row x Delete Row pe Help Label Criteria Row calculation Medical Costs FF S Dental Obj 564 and LT AA Medical Costs FFS Inpatient Obj 566 and LT 44 Medical Costs FFS Other Obj 568 and LT AA Medical Costs Fee for Service Total MEDICAL COSTS FFS DENTAL MEDICAL COSTS FFS l Medical Costs Total MEDICAL COSTS SUBTOTAL MMG QA OTHER MEDIC Medical Margin PREMIUM AND OTHER OPERATING REVENUE MEDICAL Direct Medical Margin PREMIUM AND OTHER OPERATING REVENUE MEDICAL C Direct Service Costs Obj 580 and LT AA Add a new row Click Insert Row The editor will create a new row above the row you have highlighted Alternately you may enter text in the new empty row at the bottom in the Description column Define how the label is rolled up Click the box under the Pattern column and click the visual assist See the topic Define Criteria for the List Item for instructions on defining the pattern Define a row calculation Click the box under the Row Calculation column See the topic Define row calculations for the List item for further instructions Delete list items Hold CTRL key down as you
49. a exactly as you see it in JDE e Applying automatic performance improvements e Enforcing read only queries so you can design with confidence To create Ad Hoc Reports in Data Access Studio you need to know how to Get the Data You Need See Sample Data Edit Your Report Format Your Layout Calculate Values Over Your Data Find Trends in Your Data Export and Print Results Publish Your Report to Others 35 Data Access Studio User Manual Get the Data You Need Data Access Studio can open any JDE table business view or logical for which you have access From the main menu click File Open EnterpriseOne Data or File Open World Data Filter Box Quick Open Box SSC Recently Opened Select JDE Tat Band Views H al 3 lose yo Find Open Y bk Data Source OCM y y ly i Recently Opened Ma Member Description Product Product Code Obj mn Name Code Reporting Type Generic Interface Table for BO000194 FO002 Mext Numbers Automatic D D I TBLE FOO02 1 Next Numbers by Company Fiscal Year Automatic 00 09 TBLE FODOZ2 Unique Key File Next Available Unique Key 00 Ai TBLE F00023 Distributed Next Number Table 00 SOCA TBLE TO thi TT Search by Name Click on the Filter box under Object Name Type in a JDE table or business view name Click a Find on the toolbar Browse the list of results in the grid Highlight the row s you want to open and click Y Open Search by
50. adendeiates 109 Group Display Style 0oooooioocccoocioocccconanicnnoso 130 Group Expand DUUMON wasicckicscecehiidavciedsiacosdgheaaeaccene 60 Group REMOS instinto isidienal 91 E e 144 Group ROW INGOX sisi iaaiadia 144 Group SUM MANY siiis 95 STE 8 WEE 95 Group Summary Amounmt 95 H le EE 121 PCAC Cl TOO ET EE 121 insta cielo litio ici 56 130 155 HIS SNOW aura ts 62 63 130 HOU AAA e A 162 o 13 121 I CAEN e TE 163 Calendar standard 163 REX E LEE 127 Ile AIG EE 93 WAC CC asian 127 130 Indexed Month Description nn nnnnannnnnneannnnneennne 88 Input Grid SOMA EE 147 Item Re 57 HEMS EE 56 74 105 J JD Sek Ae EE 3 127 149 JD Edwards CUStOMETS cccccesseeeeesseeeeeesseeees 1 JDE 1 2 3 6 8 20 23 30 40 41 43 56 58 73 91 149 152 157 JDE Administrator 6 JDE Associated Description cccccseseeeeseeeeees 58 JDE Business Un 23 JDE CHAN eee ii ee ee eee 23 ACCOUNTS isolated ratas 23 JDE RE EE 58 JDE Data DictiONaAry occccccccoconnncnoncconancncnonannnonnnos 71 JDE EnterpriSC One soria 1 4 6 SEH trett 88 JDE Knowledge 2 8 20 40 129 152 JDE Software POSSID O usaras 3 OE EE Beete ebe 44 Elei 44 P Bea oy it Be cds to io ted eR 52 leede e 52 101 Data Access Studio User Manual JDE Work Order Master tie 43 opened Ee 43 SDE WV OFC iia oa 1 4 6 JDE Specific CGalculatons 00nnnneeeeeeeneeeeene 109 A ee 133 Se
51. ant to export the Data portion of your query i e non Excel Presentation select one or more check boxes in the Export Data as section For each type you select DAS will export to that type and send all outputs to the destination 141 Data Access Studio User Manual Once you have added one or more reports to your task and specified the output types you want you need to tell DAS where to send the results This is called the destination di To Destinator E mall E mail Provider Reports ow email Ww Ouput directory Encrypt POF ee ees Email the report outputs Enter a valid Email i e yourName yourCompany com and Email Provider IMPORTANT You DAS Administrator must setup a valid email provider first For information on how to setup an Email Provider please consult the Data Access Studio Administration guide Copy report outputs to a directory Click the visual assist and select a valid output directory TIP If you want to update network resources it is best to provide the Universal Naming Convention UNC path to the network resources as opposed to shared drive letters When your report runs it will run on the DAS Web server The path you enter here must be accessible to the DAS Web server Encrypt PDF output If one of your report output types is PDF then this check box will be enabled If you check this box DAS will encrypt the PDF file with your JDE password To open the PDF enter your JDE password If you l
52. as follows aK Delete Parameter wW Accept Changes Visible Define Editor Field Ju Pick an editor type _ Enter editor options OSES E E Allow blank entry Character casing Normal Max Length 30 The Data Access Studio shows the Designer in the region above your Layout grid You can resize the height of the Report Parameter Designer by clicking the splitter line between the Designer and the Layout grid AAA SE ee Add a filter parameter from your Layout Click on the Column Header of any column in your grid Drag Grid the column to the Report Parameter Designer window Drop the column on the Report Parameter Designer Add a parameter manually Click an empty cell beneath the Name column Enter the name for your parameter Tab out of the Name column Select an editor that is appropriate for your parameter If you have a Date parameter select a Date editor etc For each type of editor you select modify the properties of the editor to best match your parameter Organize the top down order of your Click the Grip Bar for any parameter row Drag the row to the parameters desired location Drop the row on the desired location Delete a parameter oo l AR Highlight one or more parameters Click Delete Parameter Make a parameter visible invisible Check Visible default to make the parameter visible Uncheck Visible to hide the parameter from the user Close the Designer and show the
53. at the top Click Group Display Style Group rows on bottom to show at the bottom Click Add Count To remove the count click Remove Count Design Advanced Reports Create a Table Join from Scratch Data Access Studio lets you combine information from multiple tables which is referred to as a Table Join 1 Onthe Homepage click By Table Join Or click File New Private Report from the main menu a Create Table Join for Private Report fo W Save Close Save And Close PQ Delete 7 Help ZE Apply Suggested Join Hide All Columns Report Name Begin designing your private report by adding tables that you want to query in the grid below Tables Suggested Joins Actual Joins Columns Table Description 2 Enter the name of your new layout in the Layout Name box 3 Proceed to define your join as follows 1 Select Tables to Join 2 Select Join 3 Select Columns 4 Working with Your Custom Table Join 119 Data Access Studio User Manual Select Tables to Join The first step to combining multiple tables is to list the tables you want Click the Tables tab D n tor p SZ e Create Table Join for Private Report co EA co Ba ee DE Dalt 2J o BA pty Sy J lean E T Ww Save Close Save And Close aK Delete id Help H Apply Suggested Join k Hide All Columns Report Name My rep ut Begin designing your private report by adding tables that you want to query in the grid below Tables Su
54. ble field Tab from the last editable field of the header row will activate the first editable field in the Parameters grid F4 Open any active visual assist If the visual assist is not active you can press ENTER and then F4 to open the visual assist When selecting from the list of valid values you may type in 125 Data Access Studio User Manual the value you need in the filter box and then press Alt S to select the value 126 Design Advanced Reports Connect a Report Parameter to a Calculation When you create Report Parameters for your Layout you may want to connect the Report Parameter to a calculation input 1 Click on Parameters tab for the calculation you want to connect to a Report Parameter 2 Click on the Filter Helper button 3 Select Report Parameters name of the parameter you want to connect Once a Report Parameter is connected when the user changes the value of the parameter the calculation parameter will change as well This is useful for example in the Period Amounts You could hook up a Parameter called Period to the End Period of that calculation 127 Data Access Studio User Manual Troubleshoot Calculation Errors If your calculation has an error DAS will red out the cell values that have an error in the grid To identify and resolve errors Run your calculation Right click on calculation cells that are red Read the message box instructions and optional resolution Press Edit Calc
55. ble join F0411 left outer FO911 join This is an example of a many to many relationship in JD Edwards Document fa G L Amount See Pay 4 AP Amount G L Amount Difference G L Dup Kei tm Dat 00004 1577 PV Jon 100 00 001 100 00 100 00 0 00 6 3 dron 100 00 0 00 6 3 100 00 001 300 00 0 00 6 3 100 00 500 00 0 00 BK dron 100 00 0 00 6 3 deed 002 0 00 6 3 0 00 Gr 100 00 0 00 0 00 300 00 003 300 00 0 00 6 3 300 00 0 00 6 3 300 00 003 0 00 6 3 300 00 0 00 0 00 500 00 500 00 0 00 4 500 00 1 500 00 500 00 50000 0 00 Notice that when duplication is present in your query you can usually see it immediately Notice that the AP Amount Dup duplicates within the Pay Items 001 002 003 Notice that the G L Amount Dup duplicates the pattern 100 00 100 00 300 00 with each Pay Item Because of the many to many relationship the highlighted amounts duplicate and in two different ways Although the 1 500 totals match the value of 1 500 is in fact the wrong number Now look at the AP Amount and G L Amount columns The AP amount de duplicates its column AP to take just the first values in the group which is correct for the LEFT table in the join The G L Amount de duplicates its column by taking the values only up to the first level break which is correct for the RIGHT table in the join De duplicate bases how it de duplicates on the following 1 Column to de duplicate in the example above AP Amount Dup G L Amount Dup
56. bled multiple row security for your user profile Consult your DAS administrator to set up this permission if you need multi row bursting 16 Design a Quick Report User level JDE Knowledge Can Design Technical Knowledge wm Subscriber Not needed Minimal Ad Hoc Designer some Advanced Designer Yes Yes High Data Access Studio version 5 0 empowers users with limited JDE knowledge to create a wide variety of practical business reports The Quick Report user typically possesses finance payroll sales etc knowledge but does not necessarily know where JDE stores this information The Quick Report system guides you through business language and options so that you can create the reports you need You can access the Quick Report menu as follows 1 Select File Quick Report 2 Select from the available Quick Reports e g Financials Data Access Studio User Manual Create a Financial Balances Report The Financials Quick Report allows a finance user to create reports such as Balance Sheet Income Statement Trial Balance Budget vs Actual Business Unit comparison Period quarter yearly comparison and more To get started sign in then e Click File Quick Report Financials L 7 e e C aw H Pm s e E ee ee be Quick Repon wf Ok gt lt Cancel d Previous Pp Next pee lt D Balance sheet T iii Balance sheet Report name Balance sheet 07 06 11 A need category codes from the chart of account
57. ce in JDE one must negate the values of the Revenue accounts Define the revenue accounts in your system here by any criteria you choose object range category code etc Once set DAS will remember the setting as you create new financial reports Define the balance sheet accounts For balance sheet accounts one must add the beginning balance to get the cumulative actual amount of the account Define the balance sheet accounts in your system here by any criteria you choose object range category code etc Once set DAS will remember the setting as you create new financial reports Limit which object accounts your Enter a begin object account OBJ value and end object financial report shows account value that define the inclusive range that you want to display in your report Exclude zero balances from the report Check to exclude O balances Uncheck to include zero balances Present your financial report with Excel Check Excel presentation capabilities 26 Design a Quick Report 21 Data Access Studio User Manual Create a Fixed Assets Report The Capital Asset Management Quick Report allows a user to create reports over fixed assets To get started sign in then e Click File Quick Report Capital Asset Management A Quick Report af Ok gt lt Cancel d Previous p gt Next dh Capital Asset Management Ss Capital Asset Management Periods R Report name Capital Asset Management 07 06 11
58. cinns 58 Get Descrpton 58 GTA KV cadens 56 61 96 99 leie EE 56 61 ER e e o doc 122 Custom Numeric Format 84 GUSIOMIZS iesen 62 63 E A LE 62 63 CUSTOMIZE Wie Wir 138 D NR ee 142 155 156 157 DAS Web 157 RE aleet CS cassie ica 118 DAA A a ee ees Oe et 64 EE 52 DASWeD E EE 6 Lees oe ek ae ete a ee a ee oe oe 6 Data Access Studio 1 2 3 6 8 9 12 13 20 40 41 43 44 45 47 48 50 51 52 56 57 58 60 61 64 69 70 71 73 74 80 83 111 114 116 117 118 120 121 122 123 127 134 137 139 148 149 153 157 A he cea Steet a ioe 149 Data Access Studio Administration 157 Data in TEEN 93 Dala DOI LEE 156 Data Gelechon 11 Data SOUTO aid tas 41 51 EE 41 51 150 Ree EE 63 Database Administrator 127 ALAS OU CE ii A O deena 93 A A A tienen 93 BEN ias dic d 69 88 109 152 160 161 162 O BEE 109 SO O A 69 Date CoN e 88 Date DITerence ici 86 112 Date Difference between Two Daltes 86 0 EEN 86 Date Difference Column Header 86 Date INMOMMALOM EE 112 Date TECURICMCE iaa 160 162 o EE 162 time recurrence within 2 ceceseeeeeeeeeeeeeess 162 Dale Updated EE 109 RICH Kl TEE 109 Fa PIAS aida aliado 109 E oe 86 88 152 160 MOn WEE 88 WV GOK ltda mida alardes 88 MO ardid idos 88 DV IONAS ce a 115 Days MON EE 117 A A 127 De duplicate cccoconncoccccconnnonoconancnnncnnnanonnnnnos 145 BIEN 76 93 122 135 153 155 Join Oe
59. ck LE and select Not Blank If your report has Report Parameters you can filter using these values Click Report Parameters and select the parameter you want to apply 39 Data Access Studio User Manual Filter a Range When you select the Range filter option Data Access Studio will prompt you with the following form Define filter range S OK Cancel Specify the range of values you need below e From e Do this From a specific value Check the box next to From Uncheck the box next to Through Select From option Type in the value in the edit box Click Ok From and excluding a specific value Check the box next to From Uncheck the box next to Through Select From and excluding option Type in the value in the edit box Click J Ok Through a specific value Check the box next to Through Uncheck the box next to From Select Through option Type in the value in the edit box Click Ok Through and excluding a specific value Check the box next to Through Uncheck the box next to From Select Through and excluding option Type in the value in the edit box Click J Ok Between two values Check the box next to From Check the box next to Through Select From option Select Through option Type values in both edit boxes Click Y Ok 40 Design Reports Filter a Range Relative to Today s Date The Today Range Filter lets you specify date ranges relative to today s date This is very useful i
60. columns right click on any Column Header in your selection and select one of the following date operations To get the Day of the month Day of the week Day of the year First day of the month First day of the year Last day of the month Last day of the year Month description prefixed with a number for sorting JDE Julian date representation of the data Month Month Description Today s Date Year Select Quick Calculations Date Day of Month Select Quick Calculations Date Day of Week Select Quick Calculations Date Day of Year Select Quick Calculations Date First Day of Month Select Quick Calculations Date First Day of Year Select Quick Calculations Date Last Day of Month Select Quick Calculations Date Last Day of Year Select Quick Calculations Date Indexed Month Description Select Quick Calculations Date Julian Date Select Quick Calculations Date Month Select Quick Calculations Date Month Description Select Quick Calculations Date Today Select Quick Calculations Date Year 11 Data Access Studio User Manual Rollup an Amount based on a Sliding Calendar Date This calculation transforms transactional data into organized date groups such as Quarter Year Month Inception To Date etc The rolled up amounts slide with the reference date typically a Report Parameter that you provide You can create this type of calculation automatically with the Organi
61. der box for borders Un check the Border box to create graphs with no borders Check the Round Corners for round corners Un check for Square corners Once you have configured the options you want click the Map Columns to Excel to complete the presentation Whenever you Map Columns to Excel DAS overwrites the previous mapping Sometimes it is a good idea to clear the target sheet before re mapping graphs to it 98 Design Reports Control the Excel Presentation Once you present your data in Excel DAS displays the al Control Presentation menu option on the Quick menu above the grid You can use this menu to control how DAS presents your data in Excel To access these options click on the ial Control Presentation menu ere ce LL Se Adjust the Excel width automatically after each query Best fit the current visible sheet now Auto fit the sheet width to the width of the printer Hide show spacer rows Format cells Show a clean look for your report Show a spreadsheet look so you can edit your report Hide show excel menus Hide show grid column and row headings Hide show grid lines Hide show worksheet tabs Hide show status bar Wipe out the visible sheet i e start with a clean slate Present visible grid columns in Excel Manually edit and refine previous mappings Remove the Excel Presentation Check the Best fit after query button Uncheck this button to keep Excel column widths intact
62. ding on your needs e User Defined List Manually define each account rollup e Chart of Accounts Use the JDE Chart of Accounts to define the account hierarchy e Account Group Rollup accounts by the object accounts User Defined List The User Defined List option gives you a flexible way to define manually how you want your financial information displayed Each row in the grid below represents how to rollup detailed information into one line Initially you must add each row your need Once you are finished you can save your list so that you and others may re use the work you did The editor in this grid lets you e Define as many rows as you want e Define the criteria you need for each row Sample screen shot showing a user defined list A Quick Report af Ok Y Cancel a Previous E Next S Balance sheet Ferioc Organize rows by User Defined List User defined File Copy Row Insert Row Delete Row Criteria Row calculation CONSULTING Object Account 42 Object Account 43 Maintenance Object Account43 Total Software LICENSE MAINTENANCE TEMPLATES Total Income TOTAL SOFTWARE TOAL 1099 See the topic Make a List for directions on how to define the rows you want to rollup Chart of Accounts 22 Design a Quick Report If you use the JD Edwards chart of accounts for your financials this option is a powerful way to represent your numbers Select the Level of detail 1 least detailed 2
63. ds tab 29 Data Access Studio User Manual Specify Financial Periods You can show amounts by period quarter year or Year To Date period You can also compare period amounts to amounts in previous periods or years A Quick Report ff Ok gt Cancel 4 Previous gt Next Pa Capital Asset Management sset Management Periods F Fiscal period YTD Fiscal period F Fiscal QTD E Fiscal quarter E Fiscal year Compare to previous 0 Caption E Split column values by criteria Description and Year A rr Select how to organize the periods in your report Compare a period to previous periods Compare a period to the same period in previous years Compare a quarter to previous quarters Compare a quarter to the same quarter in previous years Compare a year to previous years Compare a Year To Date amount to previous Year To Date amounts 30 Click the Organize by option box Select Fiscal Period Fiscal Quarter Fiscal Year and or YTD Period Change the Compare to previous number to the number of periods you want to compare against Set the option box next to the number to Fiscal Period Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select Fiscal Quarter Change the Compare to previous number to the number of quarters you want to compare against Set the opt
64. dvanced Reports Access Non JDE Data Data Access Studio lets you open any OLE DB or ODBC compliant data source OLE 1 Click onthe icon on the main tool bar or click File Open OLE DB Data Open OLEDB Table H New Connection C Close Recent Connections 2 Click New Connection Bi Data Link Properties Provider Connection Advanced Al Specify the following to connect to ODBC data 1 Specity the source of data e Use data source name Refresh f Use connection string EN 2 Enter information to log on to the server User name Password f Blank password jw Allow saving password A Enter the initial catalog to use el Test Connection Cancel Help 3 By default the Use data source name options box shows all ODBC data sources on the workstation You may select one of these You may also click on the Provider tab to select an OLE DB provider other than ODBC Fill in the User name and Password if the data source requires it Click OK to connect You will see a list of tables that you can access in the non JDE data source Click on any table in that list to create a Data Access Studio report over it Once you open a table Data Access Studio presents the table much like a JD Edwards table with the following exceptions e No visual assist filters are available e Data summary views are not available e Index assistant is not available e You can link non JDE data back t
65. e System Calculations e JDE Specific Calculations e Text Calculations 129 Data Access Studio User Manual System Calculations System Calculations The following calculations are available on the Quick Calculation System menu Get the relative sequence of a grouped Right click on grouped Column Header Click Quick row For example the first row ina Calculation System Group Row Index group has a group row index of 1 the second row has a group row index of 2 etc Get the relative sequence of a row For Right click on any Column Header Click Quick Calculation example the first row in the grid has a System Row Index row index of 1 the second row has a row index of 2 etc Get group ranking from largest to Right click on grouped Column Header Click Quick smallest Calculation System Group Rank Convert a column from one data type to Right click on Column Header Click Quick Calculation another System Type Converter Double click the Column Header of the newly created column Change Output Type to desired value 130 Design Advanced Reports De duplicate The de duplicate is a powerful System calculation that helps you manage data duplication Data duplication is common with LEFT OUTER joins and RIGHT OUTER joins Recall you do a LEFT OUTER join when you have a 1 to many or many to many relationship between one table the left table and another table the right table Example Suppose you create a ta
66. e columns Column Menu e Show Only Columns e Hide Columns e Hide Show Columns A Design When you select Hide Show Columns DAS will display the following form Hide and Show Columns J OK gt Cancel Show All Hide Al Visible Column Caption Alias Data Order Co Or Ty Line Number Ord Suf Lo Doc Co Business Unit Search for a column by name Type in the name of the column in the Filter Box under Column Caption Hide or show a column Check to Show Uncheck to hide Hide or show multiple columns Hold the CTRL key down and click the columns you need Then right click your selection and select Uncheck highlighted items to hide Select Check highlighted Items to show To accept your changes Click Ok To cancel your changes Click Cancel 49 Data Access Studio User Manual Place Columns in the Positions You Need Data Access Studio makes moving columns easy Simply click the column header and drag it to where you want it You may also multi select columns with the CTRL or SHIFT keys and drag and drop columns as a whole To move the Item column below 1 Click the Column Header Click Column Header to drag Ze Sales Order Detail File F4211 in OneWorld Local ES Extended Order Company Or Ty a Price 9750 00 Financial Distribution Company Sales Order T 6500
67. e difference to Months or Years 1 Double click the Date Difference Column Header This will bring up the calculation editor Click on the Days visual assist Pick Months or Years Parameters Value Type ja input parameter values Bap Yalid Yalues 19 Data Access Studio User Manual Calculate a Date Relative to a Given Date To get the data difference between two dates 1 Select a date column 2 Right click either Column Header 3 Select Quick Calculation Data Date Offset Request Date LG 52005 561 2006 5612006 5651 2006 561 2006 561 2006 Ras Mod By default the date offset is the same as the original date To change the offset 1 Double click the Date Offset Column Header This will bring up the calculation editor 2 Change Offset unit to the Days Weeks Months Quarters or Years 3 Change Relative offset to the number of units you want to offset This number may be positive or negative E g if Offset Unit is Years and Relative offset is 1 then the result will be the original date minus 1 year The Relative offset can be a literal or a grid column Use grid columns when your offset is a calculated value rather than a static non changing value 76 Design Reports Extract Date Information from a Date Column You saw how to show the date difference between two columns You can also do the following date calculations on columns In all cases select one or more date
68. e grid and either Suppress or Un Suppress it 92 Show Data in Any Order Design Reports Data Access Studio lets you sort any column or set of columns A PO ue Sort a column from smallest to largest Sort a column from largest to smallest Sort many columns from smallest to largest Sort many columns from largest to smallest Sort by both smallest to largest and largest to smallest for multiple columns Right click the Column Header Click 4Sort Ascending Right click the Column Header Click 4 Sort Descending Hold the CTRL key down and multi select the columns you want The order in which you click the columns will determine which column is sorted first Right click the Column Header Click 4Sort Ascending Hold the CTRL key down and multi select the columns you want The order in which you click the columns will determine which column is sorted first Right click the Column Header Click amp Sort Descending Hold the CTRL key down Right click the Column Header Pick a different sort than the one it shows 93 Data Access Studio User Manual Customize the Look and Colors of Your Layout You can change the look of your layout by clicking File Layout Grid Styles Grid Style wf OK gt Cancel Report Look HH Spreadsheet Look 4 d Property Font Font Color Background Header Panel Arral 8 Bold A bb Group Panel Arial 8 Bold Rm Even Row Arial 8 Regular B TO Odd Row Arial 8 Regular
69. e row Close the Select Reports window Click Close or click the X in the upper right hand corner of the window Refresh the list of reports published to Click Find you Un publish a report If you have this permission left click the report and click Delete Note if you are not authorized to do this operation this button will not appear on this screen Change the subscribers for reports If you have this permission click the Manage Subscribers button Run a report Left click the report and click Run The report will run with the default data selection and parameters and present you with the final result Save the published report to a private Left click the report and click Save To Layout Once saved report which you can modify Data Access Studio will notify you that the save was complete You will now have a private report named the same thing as the report you copied Note if you are not authorized to do this operation this button will not appear on this screen Data Access Studio User Manual Change Parameters and Data Selection If you chose to Open a report you will have the opportunity to change the data selection if allowed and report parameters for that report before you run it You can then run the report and see the result Here is a sample report View report results here Enter parameter values for your report here Click on toolbar buttons to run your report j i As
70. eave this check box unchecked the PDF will have no password Append timestamp to filename Default checked When checked the automator will append a timestamp to your output to make it unique If unchecked the automator will overwrite the existing file with the same name 142 Schedule Your Reports If you need to override or specify either the Environment Library or Role E1 only click the Advanced button Environment DEMOS 0 L Role ALL Enter the Environment or Role that you need to run your reports 143 Data Access Studio User Manual Specify When You Want to Run it The Task Data recurrence allows you to specify any date recurrence that MS Outlook can do Click on the Schedule tab to see the Date recurrence and Time recurrence editors Date recurrence Time recurence le Dau e Ever 1 H days tC Week f Month Co Year f Ever weekday 144 Schedule Your Reports Specify a Date Pattern Click the Date Recurrence tab and setup as follows Day Select the Day radio button e Ever i day s Every weekday Select how many days or Every weekday Week Select the Week radio button Every i weeks Sunday Monday Tuesday Wednesday Thursday Friday Saturday Select either how many weeks or which days in the week you want to run Month Select the Month radio button Every i ronth s Ze Day fi The DS The Sunday weekday Select how many months
71. eceivable Drills downs support multiple rows and drill down on summary values as well E g if you want to drill into a rollup up summary value on a Balance Sheet right click the summary value and select Drill Down gt Account Ledger DAS will show all the transactions that rolled up into that summary value automatically Master file drill downs For any cell that has a master file behind it for instance address book number item number etc you can right click the cell On the popup menu you will see Drill Down When you click the drill down selection DAS will open the master file and show you the details for the item you selected If you select multiple rows in the source table when you click the drill down option DAS will show you master file records for all the items you selected General Ledger drill downs See Account Ledger Drill Down User defined drill downs See User Defined Drill Down 13 Data Access Studio User Manual Account Ledger drill downs These drill downs are automatically available on any new or pre existing report over the above tables DAS provides the following pre defined General Ledger Drill Downs e Account Balances F0902 gt GL over any Net Posting column Relative Period or Period Amount column e Asset Balances F1201 gt GL over any Net Posting column Relative Period or Period Amount column e Purchase Orders F4311 gt GL over Order Number and Amount Received columns Accoun
72. eck the box next to Through Select From option Select Through option Enter a positive or negative offsets Select Days Months or Years option Click Y Ok Data Access Studio User Manual Filter with Form advanced On occasion you will need more advanced filtering than can be specified by the column filters In these rare cases you can define filter criteria using the Additional Criteria form On the Main Menu click Design Additional Criteria Define criteria JO OK gt Cancel Copy Row 38 Delete Row Criteria RE Voie EX Enter OR logic Enter Field and Criteria information for one row On the next row under Field select Chain or Then Enter Field and Criteria information for the following row E g Move a criteria row ae Click the grip bar E and drag row to new position Copy a criteria row Highlight the row to copy Click Copy Row Delete a criteria row Highlight the row to copy Click Delete Row Accept changes Click SE OK Reject changes Click Cancel 42 Design Reports Manage Your Report Now that you have opened a table or business view you can edit it to create the report you need Data Access Studio provides a wide array of ways to format rearrange calculate and group information into the format you need As you edit your report you will be the only one who sees your changes hence the term Private Report Once you are done with your report you may elect to publish it to others
73. ect in step 1 4 Select how you want to calculate the date difference 1 For the first date select between today s date a date grid column or a report parameter 2 For the second date select a grid column Note the second date will be subtracted from the first date 3 Select the units for the date difference Days Months or Years 5 Choose how you want to trend Sum Count etc 6 Choose the column you want to use as the ranking quantity If you selected Count in the previous step then this field will be hidden 7 Select the bucket ranges you want You may edit the existing ones and add or delete ranges as you need 8 Click Y Ok 105 Data Access Studio User Manual Turn Row Values into Column Values When you need to do side by side comparisons you can use the Cross Tabular Trend to convert row values into column values 1 Right click a column in your grid whose distinct rows you would like to convert into columns 2 Select Trending Cross Tabular Sample screen shot Quick Start S Ok x Cancel Cross Tabular For each Employee Ve make columns for distinct values of Union Code and rollup Cum ay W Remove previous CrossT ab columns w Show summaries only mw Hide unused columns 3 Select the column you want to rollup for the year trend Data Access Studio will automatically populate this field with the column you select in step 1 4 Select the column whose distinct rows you want to convert to
74. eees 9 Your Custom Table Jolla 138 Your LAY OWL atan eel reacties 52 62 63 Belle 62 63 PUDISRINO es 52 AQUILES Su ias 81 155
75. elect columns right click any Column Header in your selection and pick Quick Calculation to do the calculation you need See Associated Descriptions for an example Once you add a calculation you will see the results of the calculation in your grid immediately You can edit a calculation by double clicking its Column Header You can edit all calculations by selecting Design Edit Calculations You can delete a Calculation Column by right clicking the Column Header and selecting Delete Column You can filter Calculation Columns just like any other column Note if you have that slow running query Calculation Column filtering won t improve database query performance Data Access Studio re calculates when you find new data or when it detects a change that forces a recalculation Data Access Studio automatically determines dependencies calculation order and circular dependencies This section discusses many of the Calculation Columns that let you transform your data into the format you need Design Reports Make a List When you need to rollup any quantity and calculate and accumulate arbitrary results you should use the List calculation A list can be anything a list of accounts business units ledger types customers etc Data Access Studio lets you define the lists you need and assign the list a label for quick reference For instance you can define your list of P amp L accounts and label the list as P amp L Standard
76. eled need category codes from the the category code information in the JDE chart of accounts Chart of Accounts If you need to access category codes in Click the check box labeled need category codes from the the JDE Business Unit table business unit master information Once you are finished click the Periods tab Data Access Studio User Manual Specify Financial Periods You can show amounts by period quarter year or Year To Date period You can also compare period amounts to amounts in previous periods or years Gamme Eee Quick Report A p ff Ok gt lt Cancel 4 Previous gt Next D Balance sheet Periods F Fiscal period YTD Fiscal period F Fiscal QTD E Fiscal quarter E Fiscal year Compare to previous 0 Caption E Split column values by criteria Description and Year A A Select how to organize the periods in your report Compare a period to previous periods Compare a period to the same period in previous years Compare a quarter to previous quarters Compare a quarter to the same quarter in previous years Compare a year to previous years Compare a Year To Date amount to previous Year To Date amounts If you want to split period values such 20 Click the Organize by option box Select Fiscal Period Fiscal Quarter Fiscal Year and or YTD Period Change the Compare to previous number to the number of periods you want to compare against Set the option box ne
77. ere any other JDE table 136 Schedule Your Reports User level JDE Knowledge Can Design Technical Knowledge wm Subscriber Not needed Minimal This section is for report designers who need to automate report execution Scheduling allows you to specify which reports you want to run and when You may specify any Date pattern and time sub pattern For instance you can run a report on Monday Wednesday and Friday at 9 00 AM and 4 00PM each day IMPORTANT You must have a scheduler license to use the Scheduler features 137 Data Access Studio User Manual Create a New Scheduled Task In Data Access Studio a Task is defined as a set of reports scheduled to run at a certain time To schedule a report or set of reports you must create a new Task 1 On the Main Menu click Schedule gt Edit Schedule 2 On the form that appears click New Task wf OK XX Close Task name Mew Task 5 11 2010 10 19 AM W Enabled Reports to run ls Schedule Cl Type Name Based on S AA AAA Name your Task Type a new name in the Task name edit box Enable Disable your Task Check the Enabled check box to enable the Task Un check the box to disable it You may want to disable a Task so that its definition exists for future use copying etc but the Task will not actually run When a Task is disabled all the edit options for the Task are disabled as well Specify what report s you want to See Specify What You Want to Run run
78. es Save your changes Close the notes editor View at a glance information 114 Select all text in the WordPad document Click Edit Copy Switch to the Notes form in DAS Click Paste From Clipboard Type into the Notes edit window Highlight the desired text in the Notes edit window Click Copy to Clipboard Click Clear Click Save Click Close Expand bottom window of the developer notes to see read only information about the report properties Design Reports Make Your Report Run Faster Data Access Studio uses many automatic performance techniques to make your report run fast You can do the following additional techniques to make your report go faster as well egen TT Report takes a long time to Select or Use the Index Assistant Count Report takes a long time to load large 1 Add more filtering Try to reduce the number of rows number gt 100 000 of rows by adding more filters 2 Hide any unnecessary columns The fewer columns you have the faster a larger data set load will go Calculating takes a long time with Consider using a Table Join instead of a Table Lookup Table Lookup calculations Report loads gt 100 000 records over a If you are loading a large number of records over a slower WAN connection network connection consider scheduling the report to run on the DAS Web Server Once the report runs there the scheduler can email you the result You must have the scheduler license for this option
79. f you want your report to always run on a set time window relative to today s date For example you can setup a range that runs between today and three months ago Every time you run the report the report will always show only the dates that fall into the window relative to the time the report was run When you select the Today filter option on a date column Data Access Studio will prompt you with the following form Today filtering L OK gt E Cancel Specify the range relative to today s date below w From today offset by EI today offset by A AAA A From a specific offset Check the box next to From Uncheck the box next to Through Select From option Enter a positive or negative offset Select Days Months or Years option Click Y Ok From and excluding a specific offset Check the box next to From Uncheck the box next to Through Select From and excluding option Enter a positive or negative offset Select Days Months or Years option Click Y Ok Through a specific offset Check the box next to Through Uncheck the box next to From Select Through option Enter a positive or negative offset Select Days Months or Years option Click Y Ok Through and excluding a specific offset Check the box next to Through Uncheck the box next to From Select Through and excluding option Enter a positive or negative offset Select Days Months or Years option Click Y Ok Between two offsets Check the box next to From Ch
80. fset and Group Periods Current Period Set Relative Offset to 0 and Group Periods by to Period Last Period Set Relative Offset to 1 and Group Periods by to Period Last Quarter Set Relative Offset to 1 and Group Periods by to Quarter Last Year Set Relative Offset to 1 and Group Periods by to Year Next Period Set Relative Offset to 1 and Group Periods by to Period etc 81 Data Access Studio User Manual Get Related Data from Another Table In creating reports you may need to include information from multiple tables into one report The Table Lookup calculation performs this task To add a Table Lookup column 1 Click the HE Calculation Editor button on the main toolbar 2 Type in Table Lookup under the Type of Calculation 3 Onthe Parameters tab enter values as follows Data Name Name of the table or business view to query Index Name Optional Name of the index to use for query When you select and index the calculation editor populates the fields you need to provide for Define fetch Datasource Optional Name of the datasource to use when retrieving data Sort Order Optional Select how you want to sort the target data set Use this option when the target data set has many values you want to query and you need a specific value based on the sort Summary Type Optional If you query a target table with multiple rows you can elect to summarize the values into one value Select the summary you would like to do such
81. ggested Joins Actual Joins Columns Description Add a table by its description Click on the Visual Assist in the Table column From the resulting form type the table description in the Description field Add a table by its object name Directly type in the table name in the Table column Note in EnterpriseOne you can only join up to 3 tables if you use a one to many OUTER join If you only use one to one SIMPLE joins then you may only add up to 5 tables 120 Design Advanced Reports Select Join Once you have defined the tables you want to combine in the Tables tab you can click the Suggested Joins tab Sample screen shot WW Save Close Save And Close JR Delete P Help ZE Apply Suggested Join L Hide All Columns ls To transfer the join definition to the Actual Joins select the joins you want and click Apply Suggested Joins Tables Suggested Joins Actual Joins Columns Join Type Table 1 Field 1 Table 2 1 Header to Detail join between F4201 and F4211 LEFT OUTER F4201 F4211 LEFT OUTER F4201 F4211 LEFT OUTER F4201 F4211 2 Detail to Header join between F4201 and F4211 LEFT OUTER F4211 LEFT OUTER F4211 If Data Access Studio finds a relationship between your tables it will list it on the Suggested Joins tab To select a join highlight the join you would like to use and click Apply Suggested Joins Depending on the tables sometimes information in the Suggested Joins may be bla
82. hat you do not want to show to the subscriber under the Sheet grid 46 Design Reports Manage Report Subscribers Once you have published a report you can conveniently add to or remove subscribers from the report in mass Example If you have 20 reports and want to add a new subscriber to each or remove a subscriber from each To manage report subscribers 1 Click File Open Reports Published to me If you have the permission to un publish Delete published reports you will see the Manage Subscribers button Select one or more reports use CTRL and SHIFT keys to multi select Click the Manage Subscribers button Close P Assign Subscribers to Reports A Delete Manage Subscribers C gt Income statement 10 5 2010 Balance sheet dashboard Charting IMPORTANT If you selected more than one report the Subscribers list is the union or combination of the subscribers of all reports you selected This feature makes it easy to make the subscribers for a set of reports the same TTT Ge ge Remove a subscriber Highlight one or more subscribers and press Delete Add a subscriber Click on the blank cell in the Subscribers grid Click the visual assist button t Select a new user from the valid users Apply your changes Once you edit your Subscriber list click Assign Subscribers to Report to apply the changes to the report list Cancel operation without making Click the Close button changes 47 Data
83. he column you want to rollup for the year trend Data Access Studio will automatically populate this field with the column you select in step 1 4 Select how you want to calculate the date difference 1 For the first date select between a control panel date recommended a date grid column or today s date 2 For the second date select a grid column Note the second date will be subtracted from the first date 5 Choose how you want to trend Sum Count etc 6 Choose the column you want to use as the ranking quantity If you selected Count in the previous step then this field will be hidden 7 Select the date organization you want All choices that begin with Current do not have a Number value All other choices let you specify a Number For instance if you want the last three months enter Last Months and 3 8 Enter a number in Compare to previous years to compare the defined organization for this year to past years 9 Click Ok 100 Design Reports 101 Data Access Studio User Manual Show Month Trends If your data contains date information you may want to see how your data changes by month To see month trends in your data 1 Right click a column in your grid that you would like to trend 2 Select Trending Month Trend Sample screen shot Quick Start S Ok x Cancel Month Trend For each ltem show month columns for Order Date rollup Sum sw Of Sales Quantity Ww Show summaries o
84. he software empowers you to handle the majority of your business reporting challenges Data Access Studio User Manual User Guide Structure This User Guide organizes its chapters by the following user spectrum User level JDE Knowledge Can Design Technical Knowledge Reports AA A Subscriber Not needed No Minimal Quick Report Designer Minimal Yes Minimal Ad Hoc Designer Yes Yes Some Advanced Designer Yes Yes High The user spectrum ranges from less technical Subscriber to the most technical Administrator Refer to the chapters of the manual that best fit your level of JDE knowledge and technical expertise Refer to the user chapter that best fits your level of JDE knowledge Each user section teaches you how to use the software by showing Problems you can solve How to solve those problems with Data Access Studio Screen shots Links to video examples Overview ReportsNow Company Information ReportsNow is an Oracle Partner that provides the high quality JDE reporting software As you use Data Access Studio please utilize the following resources from the ReportsNow web site www ReportsNow com Wal nformaton ReportsNow com See upcoming events and WebCasts See what s new and access all resources on the ReportsNow web site Video Training 24 7 access to skills based video training Email support Email to send support questions or suggestions for future enhancements Information Ask about online or on site training
85. iated Description for Account ID that provides its English description For any column that has a JDE Associated Description simply double click the Column Header Alternately you can click the Column Header and select Quick Calculation Enterprise One or World Associated Description E Quick Start i 2l Sort Ascending Z Sart Descending Ss Group By This Column f Quick Calculation Date Enterprise One F Account Category System V Associated Description Text H Er Hide and Shua Ooh inne Example result Account Amount The Associated Description calculation creates a ID new column next to the coded column The new column shows the English readable description which mak r report more r le E nn 63 00 j EES ESCH SE You may hide the original column once you get its 00018631 10 00 associated description 00038295 50 00 00046341 20 00 If a column does not have an associated 00046818 30 00 description such as the Amount column then you 00008660 32 500 00 will not see the Associated Description on the Column Menu 51 Data Access Studio User Manual Get Totals by Column With most reports you write you will need to rollup information into a summary value Data Access Studio provide this capability with Grouping and Summarizing Account ID A Accounts intercompany Accounts Receiy intercompany Accounts Recejw intercompany Accounts Recejw intercompany Accounts Receiv intercompany Accounts Receiw interco
86. ice the following e The description object and data source appear in the title of the window e The quick menu with Run Report Get Sample Data etc shows at the top of the grid e All columns are shown with the default Description style The column descriptions come from the JDE data dictionary E g you see the column header Order Number instead of the cryptic DOCO See the Customize the Look and Colors of Your Report topic for more information on how to change the column caption and other styles e The grid in the form initially has no data in it Add filter criteria and click A Get Sample Data Open a Table for the Second Time When you open a table for the second time Data Access Studio will open the last layout your were working on for that table See Manage Your Report for more information 37 Data Access Studio User Manual See Sample Data Once you open the JDE table the second step is to get some sample data Data Access Studio is a visual report writing system Getting sample data helps users visualize the report as they design it Refer to the following Data Access Studio main tool bars Find and Find Progress toolbar y St Cancel Quick menu toolbar Run Report A7 Get Sample Data gt Get More Data E Refresh X Close A Design a 1 SE See sample data with no filter O Click the Get Sample Data button See sample data with a filter Enter filter information in meer Boxes underneath each O C
87. ilities and Equity Current Liabilities 2 649 934 00 Long Term Liabilities 1 690 000 00 Stockholders Equity 2 137 694 90 Revenues Direct Costs General and Administrative Other Income and Expense 95 Data Access Studio User Manual Offset between two fiscal periods Imagine a case in a balance table where you may want to show an actual amount if the period meets a certain threshold or a budget amount otherwise Let s say that if the period is in a period threshold range you want to show an amount otherwise you want to show 0 To solve this problem you will need a reliable way to count the number of periods between two points century fiscal year period B century fiscal year period A For instance point could be the reference period and point B could be the current period The Offset Between Two Periods calculation performs this calculation Open a table with period information e g Account Balances f0902 Click Design Calculations Enter Offset Between Two Periods In the Parameters enter a static Company grid column company or blank for default JDE company Enter values for the reference period Period 1 Century 1 Fiscal Year 1 Enter values for the other period Period 2 Century 2 Fiscal Year 2 GE e E When you run your report the Offset Between Two Periods column will contain the number of periods between 2 and 1 96 JDE Specific Calculations Design Reports The foll
88. in Trim Delimiters e g the space character Select an Input Grid Column to trim Click Layout Edit Calculations Add a new calculation for Split Enter a character or a string in Delimiters Check Delimiter is String if the Delimiter value represents a string Select Left Right or Both for side to trim Enter a character that you want to trim off in Trim Delimiters e g the space character Select an Input Grid Column to trim Here s the slightly tricky part For Assign column with 1 based index of split you need to enter a number for each piece of the split that you want For instance if you want the first two parts of the split enter 1 and 2 Click Layout Edit Calculations Add a new calculation for Substring Enter a Start Index that represents where in the String to start the extract Enter a Length for how many characters to extract from that position Select an Input Grid Column to extract 133 Data Access Studio User Manual Show Additional Table Information Data Access Studio provides developer related information about Tables and Views To access this information click the Additional Table Information option box on the main toolbar Additional table information V Data See detailed table information Select Summary See detailed column information Select Columns See detailed index information Select Indices See detailed join information for Select Joins business views 134 Design A
89. ion box next to the number to Fiscal Quarter Click the Organize by option box Select Fiscal Quarter Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select Fiscal Year Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select YTD Period Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to YTD Fiscal Period Design a Quick Report Split period values such as Budget vs Check the Split column values by criteria Once checked see Actuals Financial Column Rollup to specify how to split the period values Change the Caption for the period Select the desired caption setting from the Caption drop down columns 31 Data Access Studio User Manual Specify Fixed Asset Row Rollup The next step is to define how you want to organize your row information You have two choices depending on your needs e User Defined List Manually define each asset rollup e Asset Group Rollup accounts by the object accounts User Defined List The User Defined List option gives you a flexible way to define manually how you want your information displayed Each row in the grid below represents how to rollup detailed info
90. k to See the Preview window This window shows all upcoming run run times for enabled tasks If a task is disabled it will not show in the Preview You may filter for tasks and times you want to see using the filter boxes in the Preview grid Advance Edit Once you have created a task you can specify the task recurrence using the iCalendar standard The advanced editor shows you some syntax for time patterns You need to specify the iCalendar time pattern as text 148 A PIN E 109 ADSOlte Valle cias ocacion 85 A 13 20 55 151 Column MOI sanidad 55 Quick Report men 20 Access Non JDE Data 149 151 Access Your Saved Layouts nnnen00nnnnnneanennn 51 ACCOUNT Balanceo ad 90 91 Account Category cccccccccccnccccnnnccccnnonanenononnnnnnnnnos 109 PACCOUND ID EEN 58 60 Associated Descrptpon 58 Account Ledger drill downs 16 PACC OWING EE 23 109 Eeer 109 IEC eri a acid 23 et lada Dale 21 29 Actuals Jos 134 135 Ad Hoc Designer 2 8 20 40 129 152 Ad HOG Repo mr aio 40 eebe Sinatra lada 64 Add Column Values into ooccocccnnccnnccnnccnnncnnnnnnoss 83 SUN COM dieta debe 83 AA O OU 130 e AA 162 Gale EEGEN E 162 Additional Eta aid 49 Additional Table Information 148 e A O RN 55 ACMINISITALOS EEN 63 157 Advanced button 158 Advanced Designer 2 8 20 40 129 152 Advanced Formattmg 64 After Embedding Excel AA 64 ele EE 117 EE 6 ARGNA Data ic 13
91. ke the SIMPLE join if the Table 2 and Field 2 value do not match then the query still returns a row for Table 1 and Field 1 The Table 2 fields in this instance will all be null you can search for null fields using the lt BLANK gt filter Use a LEFT OUTER join for 1 Drill down reports If you do a LEFT OUTER join between a header table and a detail table then your report will be able to drill down between the header Summary records and the detail records 2 Integrity reports If you have a header table and want to know which headers have no child records then use a LEFT OUTER join A RIGHT OUTER join is the same thing as aLEFT OUTER join except Table 1 and Table 2 are swapped Use a RIGHT OUTER join for instance to identify detail rows that do not have a header row 122 Design Advanced Reports Select Columns Perhaps the easiest part is the last part selecting the columns you want 1 Click the Columns tab Sample screen shot a pa for LU y U PEPI FEI Close Save And Close dt Delete 7 Help Z Apply SuggestedJoin Hide All Columns My report You must add at least one table to this wiew before you can select any columns Tables Suggested Joins Actual Joins Columns Table A Select Description Field Name Sales Order Detail File F4211 Order Company Order Number Order Type Line Number Order Suffix Business Unit This is where you select the columns that you want
92. l On UEL 122 SS enn EE 123 Working with Your Custom Table Jo ln EE 124 Edit Calculations wih the EI A A toes 125 Connect a Report Parameter to a Calculation 0000nnnnnnn0000nnnnnnnoeennnnnrnossnnnnnnnnnssnnnnnnrrensnnnnrnreesnnnnnrereenne 127 TROUDIESNOOECaICUIAUO NM ENS a e 128 Ferom Advanced GC ale ular xa a e 129 SECS 07 8 e EE 130 TACA CUA ss 133 Show Additional Table Into Mal Te EE 134 ACCESS NomJDE DAA ar rcs eee laces ce 135 Method 2 Access Non JDE Dat e iia 136 Schedule VOUT REDONS iia EE ee 137 Greate a New Scheduled a EE 138 Specify What OU VWantio AUN ia at ii tas 139 Specify When YOU Want O RUN KR a A A AS 144 Wonewithochequied TASKS coil 148 TON 149 Overview Data Access Studio is the premium reporting solution for JD Edwards customers Whether you are a business analyst end user or power user Data Access Studio empowers you to create the reports you need when you need them Create and distribute dashboards Schedule and email reports Automate data selection and burst reports Create Alerts over your JDE system Provide the easiest way to visualize and report over JDE data Report over every module table business view and custom JDE object Support a wide spectrum of users Access real time JDE data with the best performance Apply all native JDE security and formatting Provide the easiest install and ongoing software maintenance Work on all JDE EnterpriseOne releases and JDE World releases T
93. l Y 60 Design Reports Prompt for Values continued Once you have designed your Report Parameters and clicked Close Designer Data Access Studio will show the user prompt that you designed For example A Design EA u Enterparameter vales n Report A Get Sample Data gt Get More Data E Refresh Close TG Liar Darin D DUT Fiscal Year been O here Enter a parameter Click on the edit box next to the parameter you want to modify Enter a value and click TAB Run the Report with the parameters you P P 4 Click run entered See some sample rows for the yo parameters you entered Click Get Sample Data Edit and design your parameters en Click bien Once you design your parameters you can also connect the parameters to certain calculations See Connect a Report Parameter to a Calculation for more information You can rename parameters Simply enter a new name and DAS updates all references to that parameter 61 Data Access Studio User Manual Rename a Column By default Data Access Studio names columns based on their JDE Data Dictionary description You can however override this caption to whatever you wish ti Rename a column Change the column caption to pre set value 62 Right click the Column Header Select Rename Type a new name and click OK You can press the button to see parameters and variables that you can also use in your column header For instance let s sa
94. lect Tables asia iio 133 JON Manlio iio 135 A a 135 JO Ke EEN 135 JU EE 88 L ADE A A 97 161 LEMA eee 120 EA oo AA O 91 Last Quarter EEN 91 LaS Y atada 91 Layout 9 50 51 52 55 56 62 64 69 73 109 120 121 130 132 138 139 141 147 67 E 50 CA O 50 51 er aii 64 MOV ios 50 55 OPE 51 120 Elei EEN 50 POD OM uri 52 dl Le BEE 51 73 Layout Print Area ysna kaan 122 123 POV OUT Grid BEE 69 Biet GE 50 Layout Ku 121 132 EE tus totalitario 120 Vale Suit caia bici 120 LEFT OUTER EE 135 145 USO a a 135 LEROU ET 147 Let Data Access Giudo 63 PA tantaeeseeeedenae 6 RE 82 155 A 74 e o EE 80 ISI EE 76 Demencia E 76 E A A A 74 Loading Hecorce AA 44 Beie A E ase 3 62 63 CS WS TOU Zumaia dla cedo 62 63 M Mal dc 101 aigle siods 101 A A en On CE 23 153 163 Mari Medline 153 Main User Interface Areas ocoocccccnccnccnccnnincnnnonoss 2 Main Wmdow 4 Make Multiple Period Amounts Appear 90 Make Your Report Run Fagier 127 Manage Subscribers button 54 Ed DEE 83 84 85 MAXIMUM ccceceecceceeceeceeeeceececeeceeeeeeeeneeeeeeenenes 60 85 Member Description nn000nnnneoeannnnnneannnennnnennne 41 Microsoft Corporation 3 MINI 85 Model ACCOUN EE 107 MOI as 50 55 BU EE 50 55 Month Description saias 88 Month Trend BEER 114 MOVO O 57 Mu see eee Bass oy cen EA TEE EE AE EAE E A 57 EA e EE 160 Multiple tables viral 132 WILE ONG ATOM WE 85 ltr Ee e GENEE 63 Multi Sor
95. ll the data Once you entered the necessary parameters and are ready to run the report click Run Report Stop an in process report gt 4 Click the Cancel button Note you may only the click the cancel button if it is enabled Data Access Studio User Manual Export and Print Once your run completes you will see the progress bar completely filled You will also see the results of your report From here you can print or export your data ti Print your report Click File Print Data Access Studio will format your output to PDF and show you a preview You can then print from the resulting PDF document Export your report data to Excel pony P Click on the main toolbar Once you export Data Access Studio will export format and save the report data to your default export directory Data Access Studio will then by default open the result of the export for you to review save etc If you have the Export menu on the main menu bar for the report you are running then you may export the data in the report as follows See Bee Export your report data to Excel Click Export All Grid Data To Excel Export your report data to a web page Click Export All Grid Data HTML format Export your report data to a PDF Adobe Click Export All Grid Data PDF Acrobat format Export your report data to Access or Click Export All Grid Data To Tab Separated Values other similar third party software Exp
96. lup In this case simply type a minus sign in the Row Calculation 2 Scale the value of the rollup Suppose you want to divide the rollup by 1000 simply enter 1000 in the Row Calculation Case 2 If your row has no Criteria defined In this case the Row Calculation is any supported calculation of the labels The most common scenarios are 1 Summing other labels In this case simply multi select the labels in the visual assist holding down the CTRL key and click Select The result will be the syntax for summing the labels For example LABEL A LABEL B 2 Subtracting In this case change the sign of the operation in the calculation LABEL A LABEL B 3 Percent LABEL A LABEL A LABEL DI In general you can enter any expression using The row calculation also supports built in functions as listed in the visual assist for the Row Calculation column 68 Design Reports Save a List When you click Save List you will see the dialog below Save lis W OK Cancel Enter name for list List category Accounts Publish list Provide a name for your list Click on the edit box next to Enter name for list Enter the name of your list Organize your list into a category Click the down arrow on the List category edit box Select the category that best matches how you want to categorize your lis
97. more detailed 3 more etc Select how you want to see the roll ups labeled Display rows as Check the Dynamic level of detail if you want the ability to change the level of detail on the fly when you work with your report Enter a Model business unit if you want to use the chart of account structure for a particular business unit Quick Report A p S Ok Cancel d Previous Next Ze Balance sheet Organize rows by Create account rollups based on the JDE Chart of Accounts Level Display rows as Model business unit A Dynamic level of detail Account Group Use this option if you want to roll up simply by the object account Select how you want to see the roll ups labeled Display rows as 23 Data Access Studio User Manual Y o x Cancel 4 Previous pp Next Balance sheet Create account rollups by object account Display rors 2s 24 Design a Quick Report Financial Column Rollup Optional The Columns tab lets you show side by side comparisons of financial information For instance suppose you wanted to show a side by side comparison of Actual amounts and Budget amounts In this example you would enter two lines in the grid one for Actual and one for Budget The editor lets you e Define as many side by side categories as you want e Define the criteria you need for each category E 3 di ee d Previous E Next Balance sheet Leave blank for amounts only If you provide a li
98. mpany Accounts Recejw intercompany Accounts Receiv Example Let s say we have the account balance details as shown on the left We want to create a summary total for each account Having this summary will let us know if an account is in balance or not The first step is to group the things we want to summarize by To group by any column right click the column header to get the Column Menu Select Group By This Column Account ID A Accounts Intercompany Accounts Recewn 0 00 Trade Accounts Receivable 12 470 90 12 470 90 When we group by Account ID notice that Data Access Studio collects all accounts with the same name and puts them together Also because Amount is a numeric field DAS automatically sums the values and shows you the result Also notice that the summary operation puts a Grand Total at the bottom To change a summary right click the column header to get the Column Menu and select Summarize Sum Once you group a column you can click the Group Expand bution to see the detail lines This canbea great way to double check your information Also you may group more than one column For each column you group Data Access Studio will create a subtotal automatically You can control how you want to rollup your groups For more information see Fine tune Grouping and Summarization You can do other summaries as well Count Minimum Maximum Average and None You can right click any summary on th
99. n any Column Header in your selection and select one of the following math operations To get the Positive part of one or more columns Average value of two or more columns Difference between two columns Maximum value between two or more columns Minimum value between two or more columns Product of two or more columns Get the division remainder for two columns Standard deviation between two or more columns Sum of two or more columns 74 Select Quick Calculations Math Absolute Value Select Quick Calculations Math Average Multi select two numeric columns Right click the Column Header Select Quick Calculations Math Difference Select Quick Calculations Math Maximum Select Quick Calculations Math Minimum Select Quick Calculations Math Multiplication Multi select two numeric columns Select Quick Calculations Math Remainder Select Quick Calculations Math Standard deviation Select Quick Calculations Math Sum Design Reports Calculate the Date Difference between Two Dates To get the data difference between two dates 1 Multi select two date columns 2 Right click either Column Header 3 Select Quick Calculation Data Date Difference Request Order Date Date 6152005 BA LU 6152005 6Mr2005 DII 22005 DII 2005 62005 DII 2005 612005 612005 612005 612005 DII 2005 DII 2005 By default the date difference is displayed in Days To change the units of th
100. n into a purchase order right click the Order Number and select Drill down gt Account Ledger DAS will show you the two balancing entries for the order in the Account Ledger FO911 The amounts shown in the Account Ledger should match up to the Amount Received column in the Purchase Order header 14 Run Reports Published to You User defined Drill Down You can connect any report to any other report with the Burst calculation This allows you to create any row level drill down you need When designing a drill down you need two DAS Reports e A driver report e A target report DAS drills down from the driver report to the target report e Create a target report that will show the details for a driver For example re ordering items for a business unit The target report takes a business unit as its input and shows if any items need to be re ordered for that business unit e Open adriver report In our running example this would be a list of business units This will be the business units for which we want to drill into the target e Click Design Edit Calculations A Edit Calculations for Business Unit Master FODO6 in Business Data Local Burst Driver business units fo a I gt o eo o E Las 3 Wi SR u Ch mole does aw Save Close Save And Close Fa Delete P Help Copy ap Show All Calculations Typeof Column Caption Psrameters Burst es Burst Burst Target Re String input parameter values
101. nd click File Publish Once clicked you will see the following form E Publish My Report Je Pz W Publish K close PE Delete P Heip wl Excel Presentation Toreport Account dashboard Published by DASADMIN To folder Financials ts Publish to these users User ID Alpha Name User Role Publish options En Description DASADMIN Allow user to change data selection E Allow user to hide and show columns If the report was not previously published the Previously published value will be No If the report was previously published this will say Yes and also show who published the report By default security only the person who published the report last can re publish it This prevents users from inadvertently overwriting each other s published reports AAA A S Change the name of the published Report Select Subscribers for your published Report Disallow Subscriber to change data selection in your report Allow Subscriber to hide and show columns To publish the report with the settings you added Delete a published Report Close this form If you present your data in Excel and want to control it Click the edit box next to To report and type in a new name Click on the selection box under the User ID column Pick Subscribers by their JDE User ID Role or Group Add as many users roles or groups as you need If you select DASUSERS then your report will be published to everyone who uses Data
102. nk In this case click the Actuals Joins tab to Define the join manually 121 Data Access Studio User Manual Define a Join Manually If you have more than one table you must define at least one join condition for each table lf there are no Suggested Joins or you need to edit your join 1 Click the Actual Joins tab A Create Table Join for Private Report ES S Save Close Save And Close Delete oP Hels fo Apply Suggested MA All Color ES E P m pply Sugg _ Report Name My report Select two or more tables before defining joins in the grid below Tables Suggested Joins Actual Joins Columns Join Type Table 1 Field 1 Relation Select the type of join under the Join Type column Enter the first table in Table 1 Enter the first field in Field 1 Enter the Relation this is normally Enter the second table in Table 2 Enter the second field in Field 2 aa Ek Join concepts There are three join types available SIMPLE LEFT OUTER and RIGHT OUTER A SIMPLE join is a one to one relation the value for Table 1 and Field 1 MUST match the value of Table 2 and Field 2 these examples presume the Relation field is When this match occurs the result is one row for that match If the target table does not have a match for the source table row then no row is returned to the grid A LEFT OUTER join is a one to many relation the value for Table 1 and Field 1 matches the value of Table 2 and Field 2 Unli
103. nly Hide unused columns 3 Select the column you want to rollup for the month trend Data Access Studio will automatically populate this field with the column you select in step 1 4 Select the date column you want to use 5 Choose how you want to trend Sum Count etc 6 Choose the column you want to use as the ranking quantity E g Sales Quantity If you selected Count in the previous step then this field will be hidden 7 Click Y Ok 102 Design Reports Show Day Trends If your data contains date information you may want to see how your data changes by day To see day trends in your data 1 2 3 SALES Right click a column in your grid that you would like to trend Select Trending Day Trend Select the column you want to rollup for the month trend Data Access Studio will automatically populate this field with the column you select in step 1 Select the date column you want to use Choose how you want to trend Sum Count etc Choose the column you want to use as the ranking quantity E g Sales Quantity If you selected Count in the previous step then this field will be hidden Click Y Ok 103 Data Access Studio User Manual Show Year Trends If your data contains date information you may want to see how your data changes by year To see year trends in your data 1 Right click a column in your grid that you would like to trend 2 Select Trending Year Trend Sample screen shot
104. nsult your JDE administrator JDE World Your JDE Administrator will provide you a shortcut or web page address to run Data Access Studio Typically this is a link to DASWeb exe Once you run the link you will sign into Data Access Studio with your World credentials User and Password Data Access Studio will automatically provide the default library list for you Overview REPORISNOW Location DENVOULZ User Password Library list OF Cancel Data Access Studio User Manual Main Window Fa j d ep JEP Al JO 1 Home REPORTSNOW Main Menu Main Toolbar Home Page search f File Admin Edit View Export Design Schedulg Window Help r User GF y nual Gi Training y EN New Report By Table Join a My Reports As of AP DASVIEL G L by Business Unit Obrect Sub DASVIEW Asset lookup F0911 Account dashboard DASVIEW as Published Reports Charting Account dashboard My sales join AP GL jde JDE Data HH Non JDE Data CA Scheduler WI e A PO ue Create a new report from a table or existing view Create a new report by defining a custom join Work with your custom reports Open a recently accessed custom report Work with reports published to you Open a recently accessed published report Work with JD Edwards tables directly Click the New Report link Click the By Table Join link Click
105. nt to run your report Specify the minutes to run your Within each hour you may specify which minutes you would report like to run your report Select the Minutes tab and check the minutes in 5 minute intervals that you would like 147 Data Access Studio User Manual Work with Scheduled Tasks Once you have created a task you can work with it From the main menu click Schedule Edit Schedule My scheduled reports la y p PE A wf k Cancel i New Task Task Name Schedule Starts Ends Mew Task 728 2010 1222PHM Mon Tue Wed Thu Frievery week 532010 2 36PM Mo end specified Preview Time Day of week Task name El 01102010 12 56 00 PM Wednesday Mew Task AL62010 1 222 Phi 911 2010 2 36 00 Phi Wednesday Mew Task 202010 1222 Phi 011 2010 4 36 00 Phi Wednesday Mew Task F206 2010 12 22 Phi 641 2010 6 36 00 Phi Wednesday Mew Task 26 2010 1222 Phi am 212010 0 36 00 AM Thursday Mew Task Fte201 0 1 222 Phi am 212010 10 36 00 AM Thursday Mew Task FL262010 1 222 Phi am 212010 12 56 00 PM Thursday Mew Task e201 0 12 22 Phi G i Se a a Fa kA The ed bos Toe ee OP A E ha kA Edit a Task Highlight the Task and click Edit Delete a Task Highlight the Task s and click Delete Note you may also disable a Task by editing it and unchecking the Enabled box Copy a Task Highlight the Task and click Copy Filter Tasks View You may use the filter boxes in the upper grid to limited which tasks you see See upcoming times for the tas
106. ntegrity issues in the table Show Parent Detail Default checked When checked the calculation will show the parent detail for each level Sometimes you will want to show this detail line and other times not To suppress the parent row uncheck this option Auto format Default checked The calculation automatically creates supporting columns When checked the calculation hides the supporting columns and automatically groups the columns Start point Enter any value list of values range or valid filter to define which items to show at the top of the tree If blank the tree top will be all the items that have no parent 91 Data Access Studio User Manual Convert values with Unit of Measure Conversion Imagine that you have transactions for various items in various units What if you wanted to see those transactions in one unit Let s say you wanted to see everything by kilograms or cases This topic explains how to use the Unit Conversion calculation to solve this problem JD Edwards allows users to enter transactions for items in various units Although JD Edwards provides unit conversions for items it is fairly difficult to actually query this conversion information and provide the correct conversion The Unit Conversion calculation lets you convert any unit to any other unit seamlessly To convert the units of an item 1 Right click the item number in your grid 2 Select Quick Calculation EnterpriseOne World Unit Conversion
107. o JDE data using the Table Lookup calculation PLR 135 Data Access Studio User Manual Method 2 Access Non JDE Data If you run EnterpriseOne and your Non JDE Data is one of the databases supported by EnterpriseOne Oracle DB2 MSSQL MSDE or Access then you can use Method 2 Method 2 has more steps but provides several advantages over method 1 1 You can publish your resulting reports 2 JDE manages passwords centrally JDE maps the JDE user to the non JDE user 3 You can schedule your resulting reports 4 You can set up your non JDE datasource once on the DAS Web server ReportsNow can assist you with the details of Method 2 The overview of the steps required for Method 2 are as follows 1 In your non JDE database make your non JDE adhere to JDE standards You typically do this by creating a View in your non JDE database For instance let s say you have a table named Customer List with three fields You would transform the view in your native data source like this Customer List gt JDE Table Name FS5CUST Name gt JDE DD Name p DOA Phone Email 2 Create an E1 table that matches the view you created in the example above create F55CUST with three columns DLO1 DLO2 DLO3 3 Create an E1 data source that points to your non JDE data source 4 Create a data source user in E1 5 Associate the data source user to the E1 user Once this is setup up you can access your non JDE table in DAS as if it w
108. ofOpen Pay AP Date Open Amount Payment Check Amount ttm AS of today Amount Date D 00001 1569 PW 400001 1570 PV 6 000 00 6 000 00 0 00 6 30 2005 400004 1573 PV 2 400 00 2 400 00 0 00 6 30 2005 400004 1579 PV 0 00 0 00 2 500 00 5 34 2005 400001 3124 PV 1 000 00 0 00 4 000 00 7 34 2005 2100001 3127 PV 1 200 00 0 00 1 200 00 7 15 2005 Enter a parameter Click on the box next to the parameter Use the visual assist button kl to assist you in selecting valid values User the filter helper button T for advanced selection such as lists not in list ranges etc Change the filter value of a column You may want to restrict the values you see in a certain column If the column is visible and the change filter criteria permission is allowed you can click the gray box below the column label to enter filter criteria See Filter Your Data for more information Note If the publisher disallowed filter changing you will not be able to modify the column filter 10 Run Reports Published to You Run the Report Once you have entered report parameters and filters you can get sample data or run the full report As the report runs you will see the progress bar fill Progress bar that shows When report is running you your report Is ay can EE it a Y E Cancel P A A See a small preview of the data without Click the Q Get Sample Data button actually running the entire report Run the report over a
109. olumn Header Click the Get Sample Data button See sample data with count of total Enter filter information in the Filter Boxes underneath each records Column Header Click the Sr Find with Count button Note if the administrator restricts this capability you will not see the Kina with Count button Note if you do not specify filter information prior to pressing the Find with Count button Data Access Studio will issue a performance warning Load another page of records gt Click the e Get More Data button Go to previous page of records in grid Scroll to the top using the vertical scroll bar Load all records for quer SR Click the run Report button As the report runs you will see Counting Records Selecting Records and then the progress bar loading the records When the load is done the Progress Indicator will show a full progress bar Go to the first row Scroll to the top using the vertical scroll bar or click CTRL Home on your keyboard Cancel a large row load gt 4 When you load records the Cancel button on the toolbar will be enabled When enabled you may click the Rance button to stop the record load The status bar will indicate that the grid is partially loaded 38 Filter Your Data Design Reports Data Access Studio provides a robust and simple way to get the data you need When you open a table each column has a Filter Box directly beneath the column caption Address Order Date Number
110. omplexity of the internal logic and allow you to focus on the business requirements 89 Data Access Studio User Manual Address Book Calculations For any table that has an address book number in it you can find information about the address number in one click Right click the column header of the address book field Select Quick Calculation JDE and select one of the displayed options Email address Mailing Addres Phone FAX etc JDE User name In each case right click the column header of the address book field Select Quick Calculation JDE Email address Returns the first e mail address for the address number You can edit the e mail address calculation to choose other e mails Current mailing address Returns the current effective address of the address number You can edit the address to show a different address effective from an earlier date Phone Returns the first phone number associated with the address number You can edit the phone calculation to choose other phone numbers or phone types JDE User 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 90 Design Reports Show JDE Tree Structures JD Edwards has many tree formatted data tables Organization Charts Bill of Materials Menu Structure Company structure The common pattern between each of these tables is that the table information has these two columns a parent col
111. ort you report data to Oracle BI Click Export All Grid Data XML Publisher or similar third party software Export your report data to Access or Click Export All Grid Data To CSV other similar third party software requiring CSV format Export your report data to fixed format Use the Pad calculation to make all export columns a fixed width Then Click Export All Grid Data To Fixed Format Export only selected rows or columns Select the columns or rows you want to export Click Export Selected Grid Data and select your output format 12 Run Reports Published to You Drill Down Drill Down Drill downs are links between cells in one table to another table For instance an account balance can drill down into a set of General Ledger entries Data Access Studio supports drill downs as follows e Master file drill downs e General Ledger drill downs e User defined drill downs Account A June 2005 YTD June 2005 June 2005 Budget YTD June 2005 Bi Amount An 1 219 116 50 2342000050 nnn l Z Short Term eel E Drill down Account Ledger Certificates of Deposi 125 0 j 0 00 Certificates of wm E Run target report for row 1250 EL Burst selected rows 0 00 1 093 4 0 00 J Accounts Receivable Y sum Trade Accounts Receivable Minimum 9 0139 Max 0 00 801 9 Count 0 00 _ Allow for Doubtful Accounts Average 1 63 0 00 1 6 None 0 00 Finance Charges Receivable Suppress 0 00 Customize 0 00 Drafts R
112. owing calculations are available on the Quick Calculation EntepriseOne World menu Convert JDE date and time columns to a single DateTime column Get Automatic Account Instruction AAI information Show chart of accounts structure Get JDE Period from a date column or date value Multi select Column Headers for corresponding date and time columns e g Date Updated and Time Updated Click Quick Calculation EntepriseOne World E1 Time to DateTime Open a table with Object account Subsidiary and Company Click on any Column Header Select Quick Calculation EnterpriseOne World Account Category For any business view with the Chart of Accounts table F0901 in it multi select Column Headers that represent how you want to see the accounts displayed e g Description Object Subsidiary etc Click on any Column Header in your selection and click Quick Calculation EnterpriseOne World Group Accounts For chart of accounts to work you need to have the following fields in your grid MCU OBJ SUB LDA PEC To create the Chart of accounts from the reporting wizard see Financial row rollup Click Layout Edit Calculations Add a new calculation for Period to Date Select a value for Company blank is default company Select a literal value or grid column for Date Select Side to specify which part of the range you want 97 Data Access Studio User Manual Find Trends in Your Data Trending gives you the power to
113. ows Highlight the row you want to move Then click the Drag and drop grip bar and drag row above or below current position Release mouse button to drop to new position Save your criteria definition Click OK Cancel your criteria edits Click Cancel 67 Data Access Studio User Manual You can perform two types of calculations on any list row 1 Modify the results of the rollup on the row applies if the rollup Criteria is defined for the row 2 Calculate values from other rows applies if the rollup Criteria is not defined for the row LA Edit Let Y Save and Close Close bel File Copy Row Insert Row x Delete Row ve Help Criteria Label Row calculation Medical Costs Fee for Service Total IERT MEDIC AL COSTS FFS SPECIALTY OUTPATIENT TOTAL Valid Values Medical Costs Total ff Select gt Cancel a Find a Clear Value A Medical Margin Y Variables J Direct Medical Margin Fiscal Year gt lt Company gt lt Sum gt Z Labels ENROLLMENT CURRENT ENROLLMENT RETRO ENROLLMENT TOTAL REVENUE CAPITATION INCOME Direct Service Costs Obj 500 end TI AA Service Margin Total Direct Costs Case 1 If your row has Criteria defined If you place a calculation in the Row Calculation column the value of the rollup will be modified directly Use this feature for the following scenarios 1 Reverse the sign of the rol
114. r Reports as follows W Open K cose O Ena LA New fide JDE Data PK Delete lt gt Move to folder A My Reports Folder RA Private Report Balance sheet 1 13 2011 DASVIEVY 6401 FOSO selfjoin DASVIEW 3919 test offset date with column input FO911 03609 G L by Business Unit tObject Sub DASWEW 10643 test params Foo 15182 A Y Open a report by name Click on the Filter box under Report Type in a report name or wildcard Click Find on the toolbar Browse the list of results in the grid Highlight the row s you want to open and click Y open Sort by most recently created reports Right click Date Select Sort Descending Change the data source where you want Click the Data Source option box Select from the list of to open the report EnterpriseOne only available data sources Note if the administrator revokes this permission you will not see the Data Source option box Open by selecting a report that you Click the Recently Opened option box Click any item in the recently opened list to open the report with which you previously worked Delete a report Highlight the report s you want to delete in the grid Click A Delete Data Access Studio will prompt you to confirm the delete Click W ok Create a new report R Click the New button See Create a Table Join from Scratch Close this form Click Close 44 Design Reports Publish Your Report to Others To publish your report open your private report a
115. ral value to use if none of the text replace criteria matches Replace first string with second value On the left hand side enter text or text patterns you may use any valid filter to define what you want to replace On the right hand side specify the value to replace with if the match occurs 86 Design Reports Convert Fiscal Date to Calendar Date lf you want to see information by Fiscal Period and Fiscal year but the data is stored by calendar year then use this calculation 1 Start with Fiscal Period and Fiscal Year parameters Click the Calculation Editor button Type in Period to Date Range under the Type of Calculation On the Parameters tab enter values as follows Company In JDE each company can have a different date fiscal pattern Enter the literal company or grid column company here If left blank DAS will use the JDE default company for the date fiscal 9 TO pattern Period The fiscal period Century The century for the fiscal year Fiscal Year The fiscal year The output of the calculation will be the equivalent calendar date range for the Company Period Century Fiscal Year you provided You can use the date range on any date field Example Suppose Company 00001 has 4 1 2011 Fiscal period 1 FY 11 CTRY 20 Then the output is the range specifier gt 4 1 201 1 lt 4 30 2011 87 Data Access Studio User Manual Combine Several Columns into One Column Sometimes you will need a col
116. re EE 84 EA A E NN 122 130 A a A ere ne 122 Close Designer 69 70 Beie 62 63 Your Layout 62 63 Column 29 30 60 71 137 Rename siete e EE 71 Column Caption ooooccncnnncnnccccncnonncnnnnnnnnonans 56 139 GIANG eegene eerste 139 Column Header 44 56 57 63 73 85 86 88 96 right clicking sennnnennnnnennnnennnnnnnesnnnnnennnnnene 56 73 Column Melania ara a 55 58 A A eae 55 Data Access Studio User Manual Columns You Need eranan 56 SNOW ODIY eria cela 56 Combine Several Columns into One Column 99 Common Trend Options ccccccssseeseeeeeeeeeeeees 110 COMPA nara 24 96 A ENN 24 Compare String Values 96 Beiler AL ON rita ads 99 DEE 99 Conditionally Rollup sica ici 94 Ee 94 CONE EE ea tata te eens 141 Report Parame t A 141 COP Visitadas 72 139 153 157 163 COPY ROW button EE 74 76 Copy To Private Mapping cccccccccccccccnnccnnncccnnnno 81 COUN EE 93 111 114 116 117 118 127 Count DUO Misiaidssiaci a abia dalla 44 Count Mimimum 60 CHE AC unci n 163 TMS teeter rac A AE eegene 163 Create information derived 73 DOW Cl currando in 73 Create reports such 21 Mance US Sesion bart ronca dansa 21 Craneo 41 50 74 149 Data Access StudiO sarta 149 LODO Urra dee 50 74 Eternidad 49 76 Gross EE EE 94 118 o DEEPA A A A 94 A A A A 94 Gross Tabular Trend usais 118 US Sa ace ATE IA A II 118 FiO SS Ke BEE 118 Cryptic JDE Datta oooooonnccconcccocconcononcconinn
117. report allowed it You cannot modify and save the report You cannot show columns that were hidden Subscriber only users If you are a subscriber only user then you Need no JDE knowledge Choose from reports that were published to you Change report parameters if applicable Run reports Export or print the results This section shows you how to run reports that are published to you in the following topics Run reports published to you Change parameters and data selection Run the report Export and print Run Reports Published to You Run Reports Published to You Once you have signed in to Data Access Studio e Ifyou are a subscriber only user you will see the report selection screen below e lf you are a designer click File Open Reports Published to me to see the report selection screen 8 File Edit Export Window Help E 2 E E Cancel Record 2 of 5 Ww Open Close O Run Find AQ 9 Reports Published to Me Folder RA Report Date E Fs El Unassigned Seni report a 1216 2010 12 45 PM 12 5 2010 2 24 PM 12 3 2010 2 24 PM Income statement 10 5 2010 12 3 2010 2 24 PM My sales join 12 2010 2 24 PM You will see one line for each report that is published to you This form presents the report folder report name and date the report was published to you EA AO Open a report without running it Left click the report and click Open Alternately you may double click th
118. rmation into one line Initially you must add each row your need Once you are finished you can save your list so that you and others may re use the work you did The editor in this grid lets you e Define as many rows as you want e Define the criteria and formulas you need for each row Sample screen shot showing a user defined list Quick Report A p af k x Cancel d Previous E Next dh Capital Asset Management Capital Asset Management Rows Organize rows by User defined C File Copy Row Y Insert Row xX Delete Row E Help Label Criteria Row calculation Cost Asset Account Type 1 MODO Depreciation AssetAccount Type2 ODO 2 See the topic Make a List for directions on how to define the rows you want to rollup Asset Group Use this option if you want to roll up simply by the asset class Select how you want to see the roll ups labeled Display rows as 32 Design a Quick Report ssa x J Ok X Cancel 4 Previous p Next dh Capital Asset Management Create account rollups by object account Display rows as 33 Data Access Studio User Manual Special Settings The Special Settings tab lets you set preferences for your financial report Capital Asset Management Date selection method Add begin balance critena Limit object accounts to this range Exclude zero balances EJS E Excel presentation HIHI Special By fiscal period and year Click to edit EA CT
119. rovide better performance If you notice that a fully specified index does not result in a fast query please notify your JD Edwards EnterpriseOne World database administrator Provide your Database Administrator DBA with the index columns you used and the time the query took to run 115 Data Access Studio User Manual e To undo the Index Assistant simply select the blank Index at the top of the combo box Data Access Studio will remove the key indicators and free the columns to move again 116 Design Advanced Reports User level JDE Knowledge Can Design Technical Knowledge wm Subscriber Not needed Minimal Quick Report Designer Minimal Yes Minimal Ad Hoc Designer Yes Yes Some This section is for more technical users who have mastered the basic Ad Hoc report techniques This section describes more technical calculations and operations that you will need to create more challenging reports Topics in this section include how to Fine tune Grouping and Summarization Connect a Report Parameter to a Calculation Create a Table Join from Scratch Edit Calculations with the Editor Perform Advanced Calculations Show Additional Information about a Table Access Non JDE Data 117 Data Access Studio User Manual Fine Tune Grouping and Summarization Once you Group and Summarize your Layout you can fine tune how you want the grouping to look For this topic you will Right click the group row as follows Right click here
120. ry is the value we would select under the Field column of this form US is the text we would enter in the empty box under the Criteria column You may add as many Field Criteria lines as you need A TOS Add a new criteria row Select from list of fields in the Field edit box Enter the criteria for the field in the Criteria edit box Outside the Criteria edit box you may use the Filter Helper visual assist to define things such as lists and ranges Also each line you enter implicitly means apply this line AND any previous lines before it Add an OR condition Sometimes you may have complicated criteria that requires AND OR logic To add an OR condition click the empty box under the Field column and select or You should have at least one line defined before the or and one line defined after the or Delete a criteria row Highlight the row you want to delete Then click the Delete Row buiton Delete multiple rows Hold CTRL key down as you left click multiple rows Then click the Delete Row button Copy a criteria row Highlight the row you want to copy Then click the Copy Row button Copy multiple rows Hold CTRL key down as you left click multiple rows Then click the Copy Row button Move a criteria row Highlight the row you want to move Then click the Drag and drop grip bar and drag row above or below current position Release mouse button to drop to new position Move multiple rows Hold CTRL key down as you left click multiple r
121. s A need category codes from the business unit master information The Quick Report lets you specify what kind of report you want and how you want to create it EA Cr Enter information on a particular tab Click the tab or click 4 Previous and E Next navigate the tab control See topics that follow Create the financial report with the Click Y Ok parameters you specified Exit the designer Click gt Cancel 18 Design a Quick Report Specify Type of Financial Report The first step of defining your report is to select what type financial report you want Enter this information on the Main tab wd Ok Ka Cancel a Previous Next lt D Balance sheet Report type Balance sheet _ Balance sheet 07 06 11 A need category codes from the chart of accounts E need category codes from the business unit master information In this version all reports are based on the Posted balances file in JDE Bees Type of your report Required Select the type of financial report that you want to create After selecting the basic types you can use the rest of the wizard to customize it further for example you can split an Income Statement into multiple periods or compare actual vs budget Name your report Required Click on the edit box next to Report name and type a descriptive name for your report This is a mandatory step If your business organizes accounts with Click the check box lab
122. s form in DAS Click Paste From Clipboard Edit note text directly Type into the Notes edit window Copy note text Highlight the desired text in the Notes edit window Click Copy to Clipboard Clear all notes Click Clear Save your changes Click Save Close the notes editor Click Close 113 Data Access Studio User Manual Developer notes are for anyone who will modify the report The notes should instruct a user on the Purpose of the report How to interpret results What the calculations do Meaning of the report s variables How to extend modify the report etc To add or edit user notes for a report 1 Open a report 2 Click View Developer Notes Meaning of the report s parameters filters Why columns are grouped sorted hidden In the Notes form you may paste WordPad RIF or Notepad plain text into the edit area You may also type in the edit area You can place hyperlinks to external documentation in the edit area as well This is useful if the documentation is too large to store within you report which could be a concern if people run your report over the web In addition to the edit area on the top DAS automatically generates at a glance information about the report including grouping filters parameters and variables calculations hidden columns sorting template information and more Keel I Paste text from a WordPad document Edit note text directly Copy note text Clear all not
123. s the Report Parameters as a block of field value pairs By default a layout will have the Layout Name in the center of the header and the page number in the right hand corner of the header 109 Data Access Studio User Manual Specify an Area to Print Data Access Studio allows you define a set of grid columns as the Print Area of the layout This feature is useful if you need to see certain columns in the interactive grid but not in the printout ti Define a Print Area Highlight the grid columns that you want to print Click each desired Column Header SHIFT click will select all columns between clicks CTRL click will add each column to the selection Select Design Print Area Set To Selected Columns Clear the Print Area Select Design Print Area Clear Show the Print Area Once the print area is defined for a layout you may want to see which columns comprise the Print Area To highlight these columns select Design Print Area Show 110 Design Reports Set export options To set preferences for each export operation select Export Export Settings Export settings OF gt Cancel Export Directory po l Iw Open file after export A A Change the default directory where Data Enter a path under Export Directory You may use the Visual Iw Automatically name and export to default directory Access Studio will store exports Assist to find the directory visually Open file after export Check
124. st below each amount column will split into the categories below Use this feature to create side by side comparisons such as Actuals vs Budgets or Business Unit comparison el File Copy Row le Insert Row x Delete Row P Ledger Type AA l Ledger Type BA Business units Add a line for each business unit you want to compare Companies Add a line for each company Ledger types Add a line for each ledger type Category code Add a line for each category code criteria etc See the topic Make a List for directions on how to define the rows that specify your side by side comparison 25 Data Access Studio User Manual Special Settings The Special Settings tab lets you set preferences for your financial report amp Quick Report J Ok gt lt Cancel d Previous Pp Next gt Balance sheet Special Date selection method By fiscal period and year Reverse sign criteria Object Account Between 5000 and 5999 Add begin balance criteria Object Account Between 1000 and 4999 Limit object accounts to this range to E Exclude zero balances Es A Excel presentation EA AAA Change how you want to prompt for date Select By date or By period from the pull down box Change the text for prompting date Select one of the pre defined prompts As of or For the period information ending from the pull down box Or you may type in your own date prompt text Define the accounts to reverse sign For financial statements to balan
125. t Make your list available to other users Click Publish list Save the list Click OK Note if there is a list with the same name Data Access Studio will prompt if you want to overwrite the existing list Don t save the list Click Cancel 69 Data Access Studio User Manual Work with Your Lists When you select a list to load you may also do other operations with your lists The select list form organizes all lists in the system as e My Lists e Public Lists Furthermore the form organizes lists by the categories people selected when saving the list Select a list B OK Cancel a Find a Delete Copy To Private Mapping 4 Category E tty lists _ Accounts standard P amp L Accounts _ BusinessUnits My business unt comparison Business Units _ Ledgers Ledger Types Ledgers Miscellaneous List 27 2009 Miscellaneous Map 5 24 2009 Mizcellaneous My business unt comparison Miscellaneous e009 3 12 DM RA A009 2737 DM DAMAO 3 11 Phi DZ A009 2 59 Phi and 2009 9 55 AN 024 2009 10 14 AM A A Select a list to load Highlight the list in the grid Click OK Delete a list Highlight the list in the grid Click Delete Copy a public list to your private list Highlight the public list in the grid Click Copy To Private Mapping 70 Additional List Properties Design Reports You can edit additional list properties by opening the Calculation Editor and highlighting your list item Filter database Allow d
126. t Balances Drill Down F0902 For any report that runs over the Account Balances table F0902 which includes Financial Quick Reports you can drill into amounts To Drill down into an amount right click the amount and select Drill down gt Account Ledger Account A June 2005 YTD June 2005 June 2005 Budget YTD June 2005 Bi Amount An j 1 219 116 50 2 420 900 En AnA E Drill down Account Ledger zl Short Term Investments Certificates i 125 0 r 0 00 So E Run target report for row Ben Burst selected rows wmd 1 093 4 0 00 _ Accounts Receivable v Sum Trade Accounts Receivable Minimum 8 013 95 Max 0 00 8 013 9 Count 0 00 El Allow for Doubtful Accounts Ave rage 16 0 00 1 6 None 0 00 Finance harges Receivable Suppress 0 00 Customize 0 00 Drafts Receivable DAS will open the account ledger and show you the transactions and transaction totals for the item you drilled into Note you can drill into a summary as shown above or into a detail cell Asset Balances Drill Down F1202 For any report that runs over the Asset Balances table F1202 which includes Fixed Asset Quick Reports you can drill into amounts To Drill down into an amount right click the amount and select Drill down gt Account Ledger Purchase Order Drill Down F4311 For any report that runs over the Purchase Order Detail F4311 which includes Fixed Asset Quick Reports you can drill into amounts To Drill dow
127. t Font Size 63 My EY OUTS ni ds 51 MW E 81 N NAM a n 41 69 93 153 157 AS O E 93 NET alo edo 3 VIE 132 153 NEW DUTION A 51 New Conpnechon 149 NEW Tas EE 153 d nne ClO ATS inicio 153 VIE EN VIe CHO ME 91 K NN del le 139 Non Excel Presentation cccceceeceeeeseeeneeeeeeees 156 NON RE 149 Not Blank EE 45 Ve 125 NOW EE 50 55 NUDO EE 24 121 161 FISCal ee MEET 24 Fiscal QUANG EE 24 FISCAL TEE 24 Kee 121 O OD CO 109 EEN deeg eegene ciones 41 A O 41 EE EE 149 Office LOCATIONS tivo ici cas 3 Offset Between Two berode 108 Offset MEASUN EC i snsiidivcsidcntcndiwicastaakonslancdatiannasoienns 89 Oe PA 71 76 80 81 149 OLE DB Data EE 149 SIN E EE 149 SOI E E COPA A 149 One Period Amount Column 90 Open 9 11 41 43 44 51 69 120 149 157 o arane IA 44 JDE Work Order Master File 43 E EE 51 120 Report Parameter Designer nannnannnnn0nn0anenani 69 RE e AAA A A 43 leie E EE 29 30 EE 76 Ada A A Te a DA 76 Order Number 43 Organization Charts DU 102 Organize cocococccncncccccnncnnnnononncnncnnnnnnnnnnnnnons 24 89 112 Organize Your Data into Aging Ranges 117 A eebe 157 Output TYDE ccccccccccnccccccnonccocononononnnononnnanncnnnnnnnnnss 139 So e ee 158 P EE E 74 Perform Math Operations on Your Data Pick Quick Calculator Pe Ce eC ee a a ia Pick Subscribers Place Columns Preview WiNdOW o sccccccsscssssssssssssssesesesssssssssseee Q
128. the My Reports link See Design Ad Hoc Reports Click hyper link of the report name under My Reports Click the Published Reports link See Run reports published to yOu Click the hyper link of the report name under Published Reports Click the JDE Data link Work with tables external to JD Edwards Search for an application report view or table Access online help Access online video training Overview Click the Non JDE Data link Type text in the Search window As you type the search window will show JDE applications views tables and reports that contain the text you type This is a very effective way to see what the data is behind any JDE application or report To open the data double click the row in the search window Alternately you can highlight the row and click Y The Search window includes Templates Templates are pre written documented reports written by ReportsNow You can use Templates directly or as a starting point for your own reports Click the Help button Click the Video Training button Run Reports Published to You User level JDE Knowledge Can Design Technical Knowledge Reports Data Access Studio lets Report Designers publish reports to Subscribers Once a report is published to you you are a Subscriber to that report As a Subscriber You see the list of reports published to you You can run the reports as you need You can change the report data selection if the Publisher of the
129. the indicator font for Multi Sort Check Summary on group line Check Sort at Database This option is for advanced users only If the Administrator denies this privilege from you this check box will have no effect Check Auto Width On Find Uncheck Auto Width On Find Check Auto Uppercase Filters Uncheck Check Show row indicator Uncheck Check Show calculation indicator Certain calculations such as Relative Dates and Relative Periods can control the filters in the grid Also if you have a one to many join checking this box will automatically correct your query If you want to disable this behavior uncheck this box Click the Column Header options box and select the option you want to see Uncommon Click the Multi Sort Font Size options box and select new size 55 Data Access Studio User Manual Add Charts and Advanced Formatting You can present your report with all the features of Excel With Excel Presentation your query comes alive with Graphs Dashboards and pixel perfect formatting Work Order Estimations Ealimalred Amel tmaled Haara Le Ale d Dauslimr imaled Loki Bleiw Hall 44 000 00 205 10 Lu LI Carparalr Din iwisle ales Lu Lu Lu LI Escalera Hannfanlaring Cralrr 11 267 75 111 31 IR TA EFH Frajral 455 000 00 Lu Lu 155 000 EFS Gielrikelion Craler Lu Lu Lu LI EgeigaralCralpe 9 109 25 110 11 gau AE A Jak Cusl EFH Frajral d Lu Lu Lu LI HG HI H il II OH RI 1 1 RI IR Sales order fullfillment
130. tions Click Design Calculations Edit Calculations for Balance sheet 1 13 2011 in 1ezs Dat al Bal eg sheet 1 13 2011 pe El E Save Sg Close o Da Save And Close e Delete gt Help Copy sw Show All Calculations Type of Calculation Column Caption Output Type Parameters Option BI put parameter values Strip blanks P Boolean Relative Period YTD Fiscal period 12 Decimal Skip delimiter for blank values Boolean Relative Period YTD Fiscal period 0 Decimal Multiple Input G rid Column Literal Grid Column Add new calculations A here i Grid Column Add a calculation Click on an empty box under Type of Calculation Click on Visual Assist to select from list of available calculations Or type in the name of the calculation directly Click TAB to apply Once applied the calculation specific parameters will show under the Parameters tab Delete a calculation Highlight one or more calculations on the left hand side Click Delete Edit a calculation Highlight the calculation row on the left Edit the values under the Parameters tab You may also change the Column Caption and Output Type if the particular calculation allows it Copy a calculation Highlight the calculation and click Copy The copied calculation will appear at the bottom Typing shortcuts TAB SHIFT TAB Tab to the next previous edita
131. to the name of the Excel sheet you want Change the Cell column and row values Change the Row Padding value The Row Padding value sets the upper limit on the number of rows Excel will present Pick the largest number that you know will be bigger than the number of rows you expect from your query and grouping For instance if your grid groups business units and you know that the maximum number of business units for any given query will not exceed 500 then set the Row Padding to 500 If you make this number smaller it will be easier to format your Excel presentation later Select None for Chart Type 57 Data Access Studio User Manual Create a chart for each column X axis rows and values of the chart are the values in the grid Create a chart for each row X axis columns and values of the chart are the values in the grid Specify how you want the graphs to be laid out in the presentation Specify borders for your graphs Specify round corners for your graphs Check One chart per column Select the visual assist for Chart Type and select the type of chart you want DAS will create an array of that chart type in the Excel presentation Check One chart per row Select the visual assist for Chart Type and select the type of chart you want DAS will create an array of that chart type in the Excel presentation Click the Pattern option and select how many columns to use when laying out the resulting graphs Check the Bor
132. tract Date Information troma Date Collie 77 Rollup an Amount based on a Sliding Calendar Date 78 Make Multiple Period Amounts Appear as One Period Amount Column 79 Show Account Balances by Any Period Quarter or ear 80 Get Related Data trom Anomer tablas aldo 82 Conditionally Rollup a Valen ad 83 Piller On a SUMA EE 84 Compare ona Val Sr A SS 85 substitute Patterns Of Text with ALA A EA A A A ADA 86 Convert Fiscal Date to Calendar Date ainia aia 87 Combine Several Columns into One Column 88 JD Edwards Specific Calculations EE 89 End Trends in Your RE ME 98 Seite end OPUONS TEE 98 Show Your Top Ten and Bottom Ten bertormmers 99 Organize Date Info MAlON EE 100 SOW MOMI ITENG S nto te aa 102 SNOW DAV MONA PA a a A 103 SNOW Year CL 104 Organize Your Data into Aging Hanges 105 Turn Row Values into Column Valls siii ia 106 EXPOrrand MIU Ie Sul ounscra starts eae bone 107 Set Default Printing PreferencCOS iii ida 108 Define Basic Header and Foole orenen r E a e 109 DDECIIV Ail Area OH I EE 110 EE EE OD UOINS naiean ai eegen Eege a a a aa 111 DOCUMENT Your AAA II NA AI a ea r aa 112 Wake Your Report RUM East lid 115 A A eae laa 115 Design Advanced Reports cccccccsssseeccceceeceececsesseceeessuueceeessauaeeeeseaaseceesssaaeeeeseeaseeceessaeeceesseageeeesessagseeeeseas 117 Fine Tune Grouping ald SUMIMALI ZATION in 118 Create a Table e re trom Serate iia ett cti 119 Selec TaADIES Od Ol ii 120 NSO Led OM EE 121 Rit RE ai g
133. ulation to correct the error 2S Common errors include Invalid type conversion Missing inputs Blank inputs Missing options Circularly dependent calculations Performance considerations The calculation architecture is designed to provide maximum performance This includes 1 Only re calculating necessary columns 2 Caching results when appropriate associated descriptions and table lookups 3 Taking minimum steps in repetitive calculations Overall the result should be that adding new columns has little performance impact on your layout Calculations can however impact performance if they are used improperly The section that follows outlines best practices for using calculations Delete any calculation that you are not using e Limit the number of Table Lookup calculations A table lookup will query data from other tables If this is done excessively the overall performance of your report may suffer e lf you are querying against a large table gt 1 000 000 rows make sure that your report filters on at least one database column You may filter calculation column results However the database itself cannot recognize these filters Because calculations are derived data filtering calculations will not result is a smaller set of rows retrieved from the database 128 Design Advanced Reports Perform Advanced Calculations This section describes how to use more advanced calculations in the following categories
134. umn This is what the Period Amounts calculations does Het Posting Net Posting Net Posting Net Posting Net Posting Period 01 02 12 13 14 Amounts 20 564 00 20 564 00 de 25 709 00 0 00 0 00 p 267 336 00 2 300 00 0 00 3 950 00 0 00 0 00 23 350 00 202 00 79 00 219 00 0 00 0 00 3 000 00 266 00 266 00 268 00 0 00 0 00 3 200 00 5 000 00 5 000 00 5 000 00 0 00 0 00 65 000 00 So what are the advantages of using Period Amounts over summing the amount columns manually Plenty 1 Easier to change the period value Any time you want to change which period or which through period just change the value of the Period Amount calculation If you manually summed the columns then every time you needed a different period you would have to delete the old sum re select the columns you wanted to add and add the new sum calculation Easier to build a report foundation With Period Amounts you only have to create one calculation This means that any calculation you need to do off the period value can reference the one calculation This makes maintaining and building your amount based report much more simple and maintainable Hook up to Report Parameters You can hook up the Period Amount parameters to your Report Parameters This lets you prompt the user for the period number when they run the report To add a Period Amounts column 1 ae Open a table or view with ANO1 AN14 such as the Account Balances or Asset Account Balances tables Click the
135. umn and a child column In this pattern the parent column can have multiple children Each child in turn can be a parent of even more children Furthermore in some cases such as the Bill Of Materials table a child can belong to more than one parent In general this pattern looks like The Parent Child calculation in Data Access Studio transforms the linear data in the table to a dynamic tree structure like above Once in the tree form you will immediately see the structure of the information in your tables Furthermore you can create reports that roll up information per this structure For example you can do head count by supervisor To use the Parent Child calculation 1 Open a table with a parent column and a child column eg the employee master F060116 address number and supervisor Optional but recommended get the Associated description for the child column and parent column Click Design Calculations Select Parent Child Hierarchy In the Parameters tab select the parent grid column in the Parent column Select the child grid column in the Child column Select the Associated Descriptions you created in step to for the Parent label and Child label Click Save and Close AS SIS E d When you click Run Report you will see the structure of the information in the table Max Tree Depth To control how deep the tree will go this parameter defaults to 10 levels Having this safeguard protects your report if there are data i
136. umn that joins other columns into a single column For instance let s say you have the business unit code and the business unit description in separate columns You may want to combine two into a single column that has both business code and description To create a combined column use the Concatenation calculation 1 Using the CTRL key multi select the columns you want to bring together The order that you select each Column Header will determine where in the final string the text goes 2 Right click on any Column Header in your selection Pick Quick Calculation Text Concatenation Example result combining business unit and its description Business Unit ADMODEL AEMODEL AFMODEL APMODEL ENMODEL JOMODEL UAMODEL AMMdCHTE 88 Description Development AF E Model Exploration AFE Model Facility SFE Model Plug amp Abandon AFE Well Zone Model Joint Operating Agreement Mode Unitization Agreement Model Winkkower AFF Aeolel Concatenation ADMODEL Development 4FE Model AEMODEL Exploration AFE Model AFMODEL Facility SFE Model APMODEL Plug amp Abandon AFE ZNMODEL vell Zone Model JOMODEL Joint Operating Agreement Mode UAMODEL Untization Agreement Model Dn EH i Design Reports JD Edwards Specific Calculations The calculations below apply specifically to the JD Edwards system As you know the JDE system has internal logic that can be difficult to calculate These calculations hide the c
137. uplicate maps Smart collapse Guarantee Row Default false If true the calculation will apply the list criteria to your data selection This can improve performance in some cases Default true If checked your list can have rows that map to multiple list labels For instance if you label A has range 1000 2000 and label B has range 1500 3000 then the overlap of 1500 2000 will be handled automatically If this option is not checked then overlapping ranges are not allowed and the first occurrence of the range wins Default true If checked and a group has only one row then the list will not allow a drill down as it is not necessary In this case the group button will be hidden and only the result of the row is shown This usually makes for a cleaner look If unchecked then the group button always allows a drilldown Default true If checked the list will guarantee that the row label appears in the order of the list whether or not there are data rows for that label If unchecked then the label will only show if there are rows for that label 71 Data Access Studio User Manual Add Column Values into a Sum Column Data Access Studio lets you easily sum up multiple columns into a new totals column To sum columns multi select the columns you need by left clicking the column headers while holding down the CTRL or SHIFT keys Pet Posting 03 Pet Posting 02 het Posting 01 42 35027 24 035 23 053 204 00 4
138. xt to the number to Fiscal Period Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select Fiscal Quarter Change the Compare to previous number to the number of quarters you want to compare against Set the option box next to the number to Fiscal Quarter Click the Organize by option box Select Fiscal Quarter Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select Fiscal Year Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to Fiscal Year Click the Organize by option box Select YTD Period Change the Compare to previous number to the number of years you want to compare against Set the option box next to the number to YTD Fiscal Period Check the Split column values by criteria Once checked see Design a Quick Report as Budget vs Actuals Financial Column Rollup to specify how to split the period values If you want to change the Caption forthe Select the desired caption setting from the Caption drop down period columns 21 Data Access Studio User Manual Specify Financial Row Rollup The next step is to define how you want to organize your row information You have three choices depen
139. y you had a column that shows period amounts and the user can enter different period amounts via a parameter Then if you select the period amount parameter tag in the Column name as the period number changes the column header will change too Parameter tags are in the form of lt tag gt You can place the tag anywhere in your column header For instance Current Period lt Period gt See Column Caption in the Customize the Look and Colors of Your Layout topic Design Reports Copy Column s You can copy multiple columns To copy one or more columns 1 Multi select the column headers you want 2 Right click on the Column Header for any column in your selection 3 On the popup menu select Copy DAS will create new copies for the selected columns and highlight the new columns when the copy is complete You can proceed to edit the new copies by double clicking the column header 63 Data Access Studio User Manual Calculate Values Over Your Data Calculation Columns give you the power to create information derived from your grid Calculation Columns differ from Excel cell calculations in that Calculation Columns work for any number of rows This row independence means you can design reports that are more useful and re usable on JDE data than Is possible with Excel Quick Facts 64 Each Calculation Column lets you transform your data visually Many calculations support Quick Calculation With Quick Calculation you multi s
140. ze Dates Trend Oi PAIN From the Main Menu select Design Edit Calculations see Edit Calculations with the Editor Add a new calculation for Relative Dates For Date assign a date column in your grid For Compare Date assign a literal date typically a Report Parameter date value Offset Measure is the type of rollup you want to do Month Quarter Year etc Relative Offset is how many units of the Offset Measure you want to move Positive values go forward Negative values go backward E g if you specify Offset Measure as Quarter and Relative Offset as 1 then the time period will be the prior Quarter as defined by the Compare Date 7 Value to roll up is the amount filed that you want to rollup to the date organization Example To rollup the last quarter of Quantity Ordered where the reference date is a Report Parameter named Date lt Date gt do the following z input parameter values Date Compare to date Relative offset Offset measure 78 Quantity Be Order Date Grid Column lt Date Literal 1 Literal Guarter Literal Design Reports Make Multiple Period Amounts Appear as One Period Amount Column When working with balances tables such as the Account Balances FO902 or Asset Account Balances F1202 it can be a challenge to work with the amount fields ANO1 through AN14 Rather than deal with 14 amount columns it would be much simpler if you could combine all the columns into one col

Data Access Studio User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents