Data Access Studio Manual by xit16869

VIEWS: 67 PAGES: 255

									Data Access Studio Manual
                                                 Table Of Contents
User's Guide ..............................................................................................................................1

Overview ..................................................................................................................................1

       General Capabilities .....................................................................................................1

       Grouping and Totaling ..................................................................................................2

       Column Operations .......................................................................................................3

       Calculations ....................................................................................................................4

       Reports and Printing .....................................................................................................5

       Security ............................................................................................................................6

   List of Features ..................................................................................................................6

   Getting Started ......................................................................................................................8

Running Data Access Studio ..............................................................................................8

Main Window ..........................................................................................................................9

Open Published Reports ....................................................................................................10

       Open reports.................................................................................................................11

       Close the report selection form ................................................................................11

       Find desired reports ....................................................................................................11

       Run reports ...................................................................................................................11

       Delete (Un-Publish) reports ......................................................................................11

       Save a report to a Layout ..........................................................................................12

Open Layouts .......................................................................................................................13

       Opening layouts ...........................................................................................................14

       Close the selection form ............................................................................................14


                                                                                                                                         iii
Data Access Studio Manual


        Find desired tables and views ..................................................................................14

        Create a new Layout ..................................................................................................14

        Deleting Layouts ..........................................................................................................15

Open OneWorld Data .........................................................................................................16

        Open tables and views ...............................................................................................16

        Close the selection form ............................................................................................17

        Find desired tables and views ..................................................................................17

Open Snapshot Data ..........................................................................................................19

        Open one or more snapshots ...................................................................................19

        Close the selection form ............................................................................................20

        Open a recent snapshot ............................................................................................20

        Delete snapshots .........................................................................................................20

Open OLE DB Data .............................................................................................................21

        Open a new connection .............................................................................................21

        Close the selection form ............................................................................................24

        Open a recent connection .........................................................................................24

     Creating Reports .................................................................................................................26

Creating Reports ..................................................................................................................26

     Layouts...............................................................................................................................26

        Creating Layouts ............................................................................................................27

Creating New Layouts ........................................................................................................27

Creating a Table Join Layout ............................................................................................28

        Layout Name ................................................................................................................29


iv
                                                                                                                Table Of Contents


       Tables ............................................................................................................................29

       Suggested Joins ..........................................................................................................29

       Actual Joins ..................................................................................................................29

       Columns ........................................................................................................................30

       Toolbar Buttons............................................................................................................31

Example of Creating a Table Join Layout ......................................................................32

Maintaining layouts ..............................................................................................................36

       Open a Layout..............................................................................................................36

       Save a Layout ..............................................................................................................36

       Save As Layout ............................................................................................................36

       Customize View ...........................................................................................................37

       Restore a Layout .........................................................................................................37

       Delete a Layout ............................................................................................................37

       Layout Styles ..................................................................................................................38

Layout Styles ........................................................................................................................38

       Restore Defaults ..........................................................................................................39

       Presentation Property Table .....................................................................................39

       Font.................................................................................................................................39

       Font Color .....................................................................................................................40

       Background Color........................................................................................................41

       Grid Lines ......................................................................................................................41

       Sort at Database ..........................................................................................................41

       Auto-Width On Find ....................................................................................................42


                                                                                                                                            v
Data Access Studio Manual


         Word Wrap Cells .........................................................................................................42

         Auto-Uppercase Filters ..............................................................................................42

         Column Header ............................................................................................................42

         Multi-Sort Font Size ....................................................................................................43

Default Layout ......................................................................................................................44

         Navigating Data..............................................................................................................46

Navigating Data ....................................................................................................................46

Finding Data ..........................................................................................................................47

         What happens during a Find operation ..................................................................48

Record Navigation Buttons ................................................................................................49

Filtering Data.........................................................................................................................50

         Filter Box Properties ...................................................................................................50

Filter-as-you-type .................................................................................................................51

         Tips .................................................................................................................................52

Filter Modifiers ......................................................................................................................53

     Wildcards ...........................................................................................................................53

     Equality Operators ...........................................................................................................54

     Blanks .................................................................................................................................54

     List of Values ....................................................................................................................56

     Range of Values ..............................................................................................................57

     Today's date .....................................................................................................................58

Filter Distinct Values ...........................................................................................................59

Sorting ....................................................................................................................................60


vi
                                                                                                              Table Of Contents


       Multi-Sort .......................................................................................................................60

Index Assistance ..................................................................................................................62

       Working with Columns..................................................................................................64

Working with Columns ........................................................................................................64

Selecting Columns...............................................................................................................65

       Selecting a single column ..........................................................................................65

       Selecting a range of columns ...................................................................................65

       Selecting a set of columns ........................................................................................65

Column Renaming ...............................................................................................................67

       Custom Caption ...........................................................................................................67

       Alias Caption ................................................................................................................67

       Description Caption.....................................................................................................67

       Additional EnterpriseOne Captions .........................................................................67

Column Sizing ......................................................................................................................69

       Manual Resizing ..........................................................................................................69

       Best Fit Single Column...............................................................................................69

       Best Fit All Columns ...................................................................................................69

Column Hiding ......................................................................................................................70

       Hiding a Single Column .............................................................................................70

       Hiding and showing multiple columns - method 1 ...............................................70

       Hiding and showing multiple columns - method 2 ...............................................70

       Partial Columns............................................................................................................72

Column Re-arranging .........................................................................................................75


                                                                                                                                      vii
Data Access Studio Manual


       Grouping .........................................................................................................................76

Grouping Data ......................................................................................................................76

       Group a column by right-clicking the column caption .........................................76

       Group a column by dragging it onto the Grouping panel ...................................78

       Grouping multiple columns........................................................................................79

       Group Row Popup Menu ...........................................................................................81

Summaries ............................................................................................................................83

       Example 1: Creating an column amount total for the F0911 file ......................83

       Example 2: Summaries and groups ........................................................................84

       Example 3: Summaries and collapsed groups .....................................................86

       Example 4: Adding group counts .............................................................................88

       Customizing summary text ........................................................................................89

       Calculating Data .............................................................................................................91

Calculating Data ...................................................................................................................91

   Creating calculations.......................................................................................................91

       Quick calculation .........................................................................................................91

       Add new calculation with editor ................................................................................92

   Editing calculations..........................................................................................................94

       Edit single calculation .................................................................................................94

       Edit all calculations......................................................................................................95

       Using the calculation editor .......................................................................................95

   Resolving calculation errors ..........................................................................................96

   Performance considerations .........................................................................................97


viii
                                                                                                                Table Of Contents


Database Lookup .................................................................................................................98

Table Lookup ........................................................................................................................99

       Overview........................................................................................................................99

Date Calculations .............................................................................................................. 103

Date Difference ................................................................................................................. 104

       Overview..................................................................................................................... 104

Day Of Month..................................................................................................................... 105

       Overview..................................................................................................................... 105

Day Of Week...................................................................................................................... 106

       Overview..................................................................................................................... 106

Day Of Year ....................................................................................................................... 107

       Overview..................................................................................................................... 107

Julian Date ......................................................................................................................... 108

       Overview..................................................................................................................... 108

Month ................................................................................................................................... 109

       Overview..................................................................................................................... 109

Month Description ............................................................................................................. 110

       Overview..................................................................................................................... 110

Today's Date ...................................................................................................................... 111

       Overview..................................................................................................................... 111

Year...................................................................................................................................... 112

       Overview..................................................................................................................... 112

EnterpriseOne Interoperability ....................................................................................... 113


                                                                                                                                          ix
Data Access Studio Manual


Account Category ............................................................................................................. 114

       Overview..................................................................................................................... 114

Associated Description .................................................................................................... 115

       Overview..................................................................................................................... 115

E1 Time to DateTime ....................................................................................................... 116

       Overview..................................................................................................................... 116

Group Accounts ................................................................................................................ 117

       Overview..................................................................................................................... 117

Math ..................................................................................................................................... 120

Absolute Value .................................................................................................................. 121

       Overview..................................................................................................................... 121

Average ............................................................................................................................... 122

       Overview..................................................................................................................... 122

Difference ........................................................................................................................... 123

       Overview..................................................................................................................... 123

Division ................................................................................................................................ 124

       Overview..................................................................................................................... 124

Max ...................................................................................................................................... 125

       Overview..................................................................................................................... 125

Min........................................................................................................................................ 126

       Overview..................................................................................................................... 126

Multiplication ...................................................................................................................... 127

       Overview..................................................................................................................... 127


x
                                                                                                                  Table Of Contents


Standard Deviation ........................................................................................................... 128

        Overview..................................................................................................................... 128

Sum ...................................................................................................................................... 129

        Overview..................................................................................................................... 129

Uniform Buckets ................................................................................................................ 130

        Overview..................................................................................................................... 130

System ................................................................................................................................ 132

Group Row Index .............................................................................................................. 133

        Overview..................................................................................................................... 133

Group Row Summary ...................................................................................................... 134

        Overview..................................................................................................................... 134

Row Index ........................................................................................................................... 135

        Overview..................................................................................................................... 135

Type Converter.................................................................................................................. 136

        Overview..................................................................................................................... 136

Text ...................................................................................................................................... 137

Concatenation ................................................................................................................... 138

        Overview..................................................................................................................... 138

Pad ....................................................................................................................................... 139

        Overview..................................................................................................................... 139

Split ...................................................................................................................................... 140

        Overview..................................................................................................................... 140

String Compare ................................................................................................................. 141


                                                                                                                                            xi
Data Access Studio Manual


       Overview..................................................................................................................... 141

String Length...................................................................................................................... 142

       Overview..................................................................................................................... 142

Substring ............................................................................................................................. 143

       Overview..................................................................................................................... 143

Trim ...................................................................................................................................... 144

       Overview..................................................................................................................... 144

       Printing and Exporting ................................................................................................ 145

Header and Footer ........................................................................................................... 145

Printing Preferences......................................................................................................... 147

Print Areas .......................................................................................................................... 148

       Define a Print Area ................................................................................................... 148

       Clear the Print Area ................................................................................................. 148

       Show the Print Area ................................................................................................. 148

Printing ................................................................................................................................ 149

       Column Print Options .............................................................................................. 149

       Print Preview ............................................................................................................. 149

Exporting Data ..................................................................................................................... 153

   Edit Menu ........................................................................................................................ 154

       Copy data to the Windows Clipboard ........................................................................ 154

       Copy selected data directly into Excel ....................................................................... 154

   Export Menu .................................................................................................................... 154

       Export all grid data to file ........................................................................................... 155


xii
                                                                                                            Table Of Contents


       Export selected grid data to file.................................................................................. 155

       Export settings ............................................................................................................. 155

Linking to Excel® Data .................................................................................................... 156

   Enabling the Excel® link ............................................................................................. 156

   Mapping to Excel® ....................................................................................................... 156

       Quick map column ................................................................................................... 157

       Quick map group summaries ................................................................................. 157

       Mapping editor .......................................................................................................... 158

   Editing the Column Map .............................................................................................. 159

   Editing the Group Map................................................................................................. 161

Linking to Excel® Data .................................................................................................... 162

   Enabling the Excel® link ............................................................................................. 163

   Mapping to Excel® ....................................................................................................... 163

       Quick map column ................................................................................................... 164

       Quick map group summaries ................................................................................. 164

       Mapping editor .......................................................................................................... 165

   Editing the Filter Map ................................................................................................... 166

       Single Cell Map ......................................................................................................... 167

       List of Values Map .................................................................................................... 167

       Range Map ................................................................................................................ 169

   Performance considerations ...................................................................................... 170

Publishing Layouts ........................................................................................................... 171

   Advanced ......................................................................................................................... 175


                                                                                                                                    xiii
Data Access Studio Manual


Advanced Operations ...................................................................................................... 175

       Snapshots ..................................................................................................................... 176

Snapshots ........................................................................................................................... 176

Snapshot setup ................................................................................................................. 177

       Step 1 - Select and highlight data to Snapshot ................................................. 177

Maintaining Snapshots .................................................................................................... 180

       Lock/Unlock ............................................................................................................... 180

       Save As (Copy) ......................................................................................................... 180

       Restore ....................................................................................................................... 181

       Rename ...................................................................................................................... 181

       Delete .......................................................................................................................... 181

       Import/Export ............................................................................................................. 181

Maintaining Snapshot Data ............................................................................................ 182

       Add additional rows and tables ............................................................................. 182

       Delete rows ................................................................................................................ 183

       Delete tables.............................................................................................................. 183

Comparing Snapshot data to environment data ........................................................ 184

       Running the comparison ......................................................................................... 184

Working with OLE DB data ............................................................................................. 186

Import and export DAS objects...................................................................................... 188

       Exporting DAS Objects ........................................................................................... 188

       Import .......................................................................................................................... 190

Data Views ......................................................................................................................... 192


xiv
                                                                                                               Table Of Contents


       Summary .................................................................................................................... 192

       Columns ..................................................................................................................... 193

       Indices ......................................................................................................................... 193

Administrator's Guide ......................................................................................................... 195

Administration .................................................................................................................... 195

System Requirements ..................................................................................................... 196

Installation .......................................................................................................................... 197

Preferences for All Users ................................................................................................ 198

Managing User Layouts .................................................................................................. 200

       Copying DAS Layout(s) from one user to another ............................................ 200

       Deleting DAS Layout(s) for a user ........................................................................ 200

User Security ..................................................................................................................... 201

       Accessing User Security ......................................................................................... 201

       Restricting functionality to given users ................................................................ 202

       Restricting access to tables and view to given users....................................... 205

       Removing user security .......................................................................................... 209

User Licenses .................................................................................................................... 210

       Full Users ................................................................................................................... 210

       Limited Users ............................................................................................................ 210

   Resetting Concurrent Licenses ................................................................................. 212

Setting up OneWorld Tasks and Shortcuts................................................................. 213

       Setting up a Solution Explorer Task ..................................................................... 213

       Setting up a Solution Explorer Shortcut .............................................................. 214


                                                                                                                                        xv
Data Access Studio Manual


Web Server ........................................................................................................................ 217

       Minimum Technical Requirements ....................................................................... 217

       Web Architecture ...................................................................................................... 218

       DAS Web Setup........................................................................................................ 218

       Troubleshooting the setup ...................................................................................... 220

       Testing the DAS Web Server within your LAN .................................................. 220

       HTTP Setup ............................................................................................................... 220

   Administration Tasks.................................................................................................... 222

       Updating the DAS Web Server ............................................................................. 222

       Stopping new connections ..................................................................................... 223

Audit Trail............................................................................................................................ 224

       Ways to use the Audit Trail .................................................................................... 224

       Enabling Audit Trail .................................................................................................. 224

       Working with the Audit Trail ................................................................................... 225

       Performance Considerations ................................................................................. 227

Upgrading EnterpriseOne ............................................................................................... 228

       Exporting DAS Instance data ................................................................................ 228

       Importing DAS Instance data ................................................................................. 228

Index ..................................................................................................................................... 229




xvi
User's Guide
Overview
Built from the ground up, Data Access Studio™ (DAS) is a tool that accelerates your business analysis
activities over EnterpriseOne® (OneWorld®) data. DAS cuts through the complexity of your data by
giving you the ability to:


  Integrate tightly with live EnterpriseOne® (OneWorld®) data
  Perform powerful and relevant data analysis
  Filter data with ranges, wildcards, and list of values
  Sort and group multiple columns
  Derive summary information with on-the-fly calculations
  Hot-link data to Excel® to produce on-the-fly Excel® reports
  Export to Excel®, PDF, HTML, XML, TSV, and TIFF
  Print your analyses
  View ODBC and OLE DB data (from legacy systems and non-EnterpriseOne tables)
  Snapshot data and compare to any environment
  Use all the security you setup for your database and EnterpriseOne® (OneWorld®)


All this and more in a completely visual-based environment. No programming is required!


General Capabilities




                                                                                                        1
Data Access Studio Manual




    You can open OneWorld® tables and business views, Published reports, OLE DB/ODBC data, and
    Snapshot data
    You can create business views on-the-fly.
    Progress bar shows percentage of table loaded
    Navigation controls show which row is selected and allows you to navigate through the data
    Filter boxes above each column filter as you type in values - letting you zero-in on the data
    Advanced filtering


      List of values
      Ranges
      Wildcards
      Composite queries
      Distinct values
    You can change the appearance of all grids: colors, fonts, lines, column headings, etc.
    All grids provide export and printing


Grouping and Totaling
Grouping is as easy as clicking a column! Organize and summarize your data in seconds...




2
                                                                      User's Guide




  Create level breaks visually
  Group on any combination of columns
  Adding group and grand totals is a snap


Column Operations
You can perform a wide range of useful operations on each column...




                                                                                3
Data Access Studio Manual




    Sort, group, hide/show columns
    Perform on-the-fly calculations
    Select the column caption as: Alias, SQL Name, Long Name, Two-row description, or your own caption
    Provide custom numeric and date formats
    Auto-size your columns with the Best Fit operations
    Define the print area for the grid


Calculations
DAS is more that a data browser. You can create your own summaries and derived information on-the-fly
with the following types of calculations:




    Database lookup
    Date
    EnterpriseOne Interop
    Math


4
                                                                                           User's Guide


  System
  Text


Adding calculation to your grid is completely visual. You need only select the input columns and the
appropriate calculation and you are done. The results of each calculation display immediately giving you
instant feedback. You do not have to wait for an OMW checkin, transfer, or package build with any of the
DAS calculations.


Reports and Printing




  Publish reports to share with other users
  Publish to all, to a group, or to specific users
  Print the contents of all grids
  Print preview, zoom in/out, header/footers


Embed Excel


  Embed Excel in your reports - do everything Excel can do in DAS!



                                                                                                           5
Data Access Studio Manual


    Quick link live column and summary values into embedded Excel
    DAS saves everything as one central unit. You do not have to worry about the hotlink getting out of sync
    with your report




Security
    Uses standard OneWorld security -- column, row, and table-triggers
    Data Access Studio application level security..


List of Features
Browse tables and business views
         Open multiple tables and views
         Open most recently used tables
Visual Summarization
         Summarize data
         Add column totals
         Group by any set of columns
         Perform calculations on any set of columns
Full Data Security
         Uses OneWorld data security model -- no additional setup required
         Provides application-level security
Print grid data


6
                                                                                        User's Guide


        Landscape/Portrait
        Multiple paper sizes
        Print preview
        Zoom/View multiple pages at a time
Export rows to Excel® and third party applications
        Copy rows directly to Excel ®, PDF, HTML, TIFF
        Copy rows to file
        Copy rows to the clipboard
        Drag-and-drop rows from DAS to third-party application
Feature-rich column layout
        Columns displayed by Description, Alias, SQL Name, or Long Data Item Name
        Hide and show columns
        Re-size, re-order
        Change fonts, colors, lines
        Save, restore, delete layouts
Powerful data filter
        Filters as you type
        Filter with wildcards, ranges, blanks, list of values, distinct values
Sort any combination of columns
Data Navigation
        Number of records shown for every query
        Go to buttons: top, previous page, next page, bottom
        Progress bar shows percentage of records read
        Load data without tying up the user interface
        Cancel button cancels load at any time
Table and View Information
        Summary, column, index, and join information
Access non-OneWorld Data
        Open OLE DB and ODBC compliant tables
        Use Data Access Studio™ rich formatting, printing, and exporting to manage the data
Snapshot any set of rows, columns, and tables
        Compare snapshot to any environment
        Comparison shows all differences between snapshot and environment data
        Useful for verifying table conversions and test cases
Index Assistant
Full Help System

Dominion Software produces Data Access Studio. Data Access Studio is not a JD Edwards®
(PeopleSoft®) Product.




                                                                                                  7
Data Access Studio Manual



Getting Started

Running Data Access Studio
This section guides you in a sequential and visual fashion. The sequence of topics is
how a typical user might use Data Access Studio.

After you install Data Access Studio to your client machine, you will have the
following icon on your desktop:




Click this shortcut to run Data Access Studio. You will be prompted by the
OneWorld™ signon screen. Once you have signed in, you are ready to start using Data
Access Studio.

If you are running Data Access Studio in Web Mode, please ask your system
administrator for the web address to type in your browser to run Data Access Studio.




8
                                                                            User's Guide




Main Window
After you sign on, you will be presented with the main window:




From here, you may do the following:



    - Create a new layout
    - Open a published report
    - Open a layout
    - Open a OneWorld table or business view
    - Open OLE DB data
    - Open Snapshot data

Note, depending on the application security setup, all the options above may not be
available for your user signon.




                                                                                      9
Data Access Studio Manual




Open Published Reports
Published reports are formatted data queries that can be shared between Data
Access Studio users. To open a published report:



 Click on the     icon on the main tool bar
 Or, select File | Open | Report from the main menu

Data Access Studio will present you with the following form (press Find to show the
reports in the grid):




The grid shows:

 Report - The report's name.
 Based on - What Table or View this reports is based on.
 Published by - Who published the report.
 Date Published - When the report was published.

From here, you may do the following:



     - Open reports
     - Close the report selection form
     - Find desired reports
     - Run reports

10
                                                                              User's Guide


 Delete reports - depending on the application security setup, this option may
 not be available to you

Open reports

Opening a report means bringing up the report in a window without running the
report. You may want to do this if you wanted to see what the report offers before
running it.

To open one or more reports:

   1. Find the reports that you need
   2. Select one or more lines in the grid
   3. Click "Open"

Close the report selection form

To close the report selection form:

   1. Click the "Close" button

Find desired reports

Finding a report means querying the list of available reports. To find desired reports:

   1. Use the Filter boxes above any column to narrow your search criteria
      (for more information about Filter boxes, see the Filtering Data section)
   2. Click the "Find" button

Run reports

Running a report means opening and executing the report query. As the report runs,
the progress bar will show the report's percent completion.

To run one or more reports:

   1. Find the reports that you need
   2. Select one or more lines in the grid
   3. Click "Run"

Delete (Un-Publish) reports

If you are authorized to do so, you may delete reports from this form. To delete one or
more reports:


                                                                                          11
Data Access Studio Manual


     1. Find the reports that you want to delete
     2. Select one or more lines in the grid
     3. Click the <Ctrl-Delete> button on your keyboard (or select Edit | Delete
        Row on the main menu.)

When the report is deleted, it is un-published.

Save a report to a Layout

If you are authorized to do so, you may save a report to a layout. Once you save a
report to a layout, you may edit the layout as you see fit (while the published report
itself remains unchanged.) Then if you want to publish your changes, simply publish
your layout (See "Publishing Layouts"). To save a report to a layout:

     1. Find the reports that you want to save.
     2. Select one or more lines in the grid.
     3. Press "Save To Layout" on the toolbar.

For each selected report, DAS will copy the report to your private layout and then
display the layout for your review.

Note

Layouts are stored centrally on the server. Layouts are "local" in the sense you are the
only one that may modify and view your layouts. When you publish a layout to a
report, then you are allowing others to see a copy of your layout as a published report.

Example

Referring to the Select Reports screenshot above, suppose that you wanted to save the
report "partial columns" to your Layouts:



     1.   Select the row for "partial columns" in the Select Reports grid.
     2.   Click "Save to Layout"
     3.   Click OK to close the save to layout message box.
     4.   You may edit your "partial columns" layout as you wish -- it is your local
          copy. When you are done close the layout for "partial columns".




12
                                                                           User's Guide




Open Layouts
Layouts are queries and presentations that you design and use in Data Access Studio.
You can create a layout from scratch or you can base a layout on an existing
EnterpriseOne table or business view. When you create a layout from scratch, you define
the tables and joins you want for your query--this is the Dynamic Join feature.

To open layouts:



 Click on the     icon on the main tool bar
 Or, select File | Open | Layouts from the main menu

Data Access Studio will present you with the following form (press Find to show the data
items in the grid):




From here, you may do the following:



    - Open selected layouts
    - Close the selection form
    - Find desired tables and views
    - Create a new layout

                                                                                      13
Data Access Studio Manual


     - Delete selected layouts

Opening layouts

The selection form above allows you to open layouts in the following ways:

 Select a layout from the "Recently Opened" list
 Find and select multiple layouts

Select a table from the "Recently Opened" list

To open a layout this way:

     1. Click the "Recently Opened" dropdown box
     2. Click on desired line

Find and select multiple layouts

You will use this option to query for one or more tables. To open a table this way:

     1. Use the Filter boxes above any column to narrow your search criteria (for
        more information about Filter boxes, see the Filtering Data section)
     2. Click the "Find" button
     3. Select one or more grid lines
     4. Click the "Open" button or press the <Enter> button on your keyboard

Close the selection form

To close the table and views selection form:

     1. Click the "Close" button

Find desired tables and views

Finding a table or view means querying the list of available objects. To find a table or
view:

     1. Use the Filter boxes above any column to narrow your search criteria (for
        more information about Filter boxes, see the Filtering Data section)
     2. Click the "Find" button

Create a new Layout

Pressing "New" allows you to create a new layout from scratch. You can define and
modify the underlying tables and joins as you wish. Please see "Creating a New Layout"

14
                                                                            User's Guide


for more information.

You may also create a new layout from the main menu: File | New | Layout.

Deleting Layouts

This form provides you with a convenient way to cleanup layouts you do not need.

   1. Find and highlight the layouts you want to delete
   2. Press "Delete" to delete the layout from the system




                                                                                     15
Data Access Studio Manual




Open OneWorld Data
OneWorld data are the tables and business views in the OneWorld System. Data
Access Studio allows you to open any table or business view that you are
permitted to open.

To open OneWorld data:



 Click on the     icon on the main tool bar
 Or, select File | Open | OneWorld Data from the main menu

Data Access Studio will present you with the following form (press Find to show the data
items in the grid):




From here, you may do the following:



     - Open tables and views
     - Close the selection form
     - Find desired tables and views

Open tables and views


16
                                                                              User's Guide


For notational brevity, the following text uses the terms "table" and "business view"
interchangeably. Opening a table allows you to query information the in that table.

The selection form above allows you to open tables in the following ways:

 Specify complete name
 Select a table from the "Recently Opened" list
 Find and select multiple tables

Specify complete name

To open a table this way:

   1. Type the table name in the "Open" edit box
   2. Optionally select a Datasource. If you leave the datasource as "OCM"
      (default), then Data Access Studio will open the table in the datasource
      specified by the Object Configuration Manager
   3. Click the "Open" button or press the <Enter> button on your keyboard

Select a table from the "Recently Opened" list

To open a table this way:

   1. Click the "Recently Opened" dropdown box
   2. Click on desired line

Find and select multiple tables

You will use this option to query for one or more tables. To open a table this way:

   1. Use the Filter boxes above any column to narrow your search criteria (for
      more information about Filter boxes, see the Filtering Data section)
   2. Click the "Find" button
   3. Select one or more grid lines
   4. Click the "Open" button or press the <Enter> button on your keyboard

Close the selection form

To close the table and views selection form:

   1. Click the "Close" button

Find desired tables and views

Finding a table or view means querying the list of available objects. To find a table or


                                                                                           17
Data Access Studio Manual


view:

     1. Use the Filter boxes above any column to narrow your search criteria (for
        more information about Filter boxes, see the Filtering Data section)
     2. Click the "Find" button




18
                                                                              User's Guide




Open Snapshot Data
Snapshot data is a data that a user previously saved off. This snapshot data is generally
useful for comparing how data changes with time. See the Snapshots section for more
information.

To open Snapshot data:



 Click on the     icon on the main tool bar
 Or, select File | Open | Snapshot Data from the main menu

Data Access Studio will present you with the following form:




From here, you may do the following:



  - Open one or more snapshots
  - Close the selection form
 Open a recent snapshot
 Delete one or more snapshots

Open one or more snapshots

To open one or more snapshots:



                                                                                            19
Data Access Studio Manual


     1. Use the Filter boxes above any column to narrow your search criteria (for
        more information about Filter boxes, see the Filtering Data section)
     2. Click the "Find" button
     3. Select one or more grid lines
     4. Click the "Open" button or press the <Enter> button on your keyboard
     5. See Snapshots for information about working with snapshots

Close the selection form

To close the selection form:

     1. Click the "Close" button

Open a recent snapshot

To open a snapshot this way:

     1. Click the "Recently Opened" dropdown box
     2. Click on desired line
     3. See Snapshots for information about working with snapshots

Delete snapshots

To delete one or more snapshots:

     1. Find the reports that you want to delete
     2. Select one or more lines in the grid
     3. Click the <Ctrl-Delete> button on your keyboard (or select Edit | Delete
        row on the main menu.)




20
                                                                          User's Guide




Open OLE DB Data
OLE DB data is any data that conforms to the OLE DB (and ODBC) standards. These
open database standards allow Data Access Studio to present data other that OneWorld
data.

To open OLE DB data:



 Click on the     icon on the main tool bar
 Or, select File | Open | OLE DB Data from the main menu

Data Access Studio will present you with the following form:




From here, you may do the following:



  - Open a new connection
  - Close the selection form
 Open a recent connection

Open a new connection

To open a new connection to an OLE DB/ODBC database:

   1. Click "New Connection"
   2. Data Access Studio will present the following form:




                                                                                       21
Data Access Studio Manual




        By default, this form opens with the ODBC provider. You may select
        an existing ODBC data source from the "Use data source name"
        dropdown list. As an advanced option, you may instead open the data
        source by providing a connection string. You then provide the user
        name and password required for the connection. Normally, you should
        check the "Allow saving password". If this box is not checked, Data
        Access Studio will prompt you for the password again.

     3. Optional - Select different OLE DB provider:

        Select a different OLE DB provider if ODBC is not sufficient for your
        needs




22
                                         User's Guide




4. Optional - Select Advanced options:




                                                  23
Data Access Studio Manual




        You may select the "Advanced" tab to configure advanced access
        options to the database. You will generally not change the options on
        this tab. Options on this tab will vary based on the capabilities of the
        selected Provider:

     5. Press OK to open the connection. See Working with OLE DB data

Close the selection form

To close the selection form:

     1. Click the "Close" button

Open a recent connection

To open a connection this way:

     1. Click the "Recent Connections" dropdown box
     2. Click on desired line

24
                                  User's Guide


3. See Working with OLE DB data




                                           25
Data Access Studio Manual



Creating Reports

Creating Reports
To create a report in Data Access Studio, you start with a layout. A layout defines
what data you want to see and how you want to see it. All layouts are private to your
user id. However, all layout information is stored centrally -- so you can access your
layout from anywhere as long as you sign in with your user ID.

Data Access Studio lets you create a vast array of layouts that can be practical for the
work that you need to get done. For instance, you can create tabular, group, and even
Excel reports. Once you have designed your layout to your satisfaction, you can
publish the layout to any group of OneWorld users who have access to DAS. Once
published, you may modify your layout as you want -- no subscribers will be affected
by your changes until you publish your layout again.

Layouts
The first step, therefore, in understanding how to create reports is how to create,
modify, and manage layouts.

The following sections show you how to:

 Create a layouts
 Modify layouts
 Maintain layouts
 Publish layouts




26
                                                                            User's Guide



Creating Layouts


Creating New Layouts
You can create a layout in two ways:

 Open an existing table or business view and save a layout (see "Open
 OneWorld Data".)
 Define a custom business view on-the-fly (see "Creating a New Layout".)

When you select an existing table or business view, Data Access Studio will present
the default layout for that table. You may then modify the layout as you need and then
save the layout.

When you create a layout with a custom view, then you may modify both the layout
and the table and join conditions at will. This option gives you the maximum
flexibility in designing your report.




                                                                                         27
Data Access Studio Manual




Creating a Table Join Layout
Data Access Studio allows you to create layout based on a table join definition. When
you create a layout this way, you define what tables and joins you want up front. Also,
you may modify the join down-the-road as you see fit.

To create a layout this way:

 Select: File | New | Layout from the main menu.

Data Access Studio will show the "Create Table Join for Layout" form. The purpose
of this form is to allow you to define the tables, joins, and columns that you will use in
your report:




This form has the following areas:

 Layout Name
 Tables
 Suggested Joins
 Actual Joins
 Columns
 Toolbar Buttons




28
                                                                                 User's Guide



Layout Name

This is where you provide a name for your new layout. All layouts must have a non-
blank name.

Tables

This grid is where you list the tables for your report. To add tables:

 Click on the empty edit box under the "Table" column
 Type in a table name or use the visual assist to pick (or multi-select) tables.

You may enter the same table name more than once. In this case, the grid shows you
and "instance" column that distinguishes between the two or more instances of the
same table.

Note, that EnterpriseOne has the following table limits:

 Simple join table-limit is 5 tables.
 Complex join table-limit is 3 tables. A complex join is any join that any non-
 simple join in it.

Suggested Joins

If you define two or more tables, Data Access Studio will attempt to find possible joins
between all the tables you listed. This grid displays some of the common joins you can
do. To apply a join:

 Double-click the row for the join
 or, highlight the row for the join and click "Apply Suggested Joins"

When you apply a join, Data Access Studio transfers the suggested join information to
the "Actual Join" grid (See below.) Note that if a suggested join has multiple fields
within it, all fields will be selected and applied as a group.

If you apply a join that already has a definition in the "Actual joins", then the editor
will warn you if you want to overwrite the existing definition.

Actual Joins

If you have two or more tables in your report, this is where you define join conditions.
Note that if you have more than one table, you must define at least one join condition
for each table.


                                                                                           29
Data Access Studio Manual


You can fill grid in with the Suggested Joins information or you can manually type in
join information.

To manually type in the join information, for each row you must provide the:

  Join type
  Source table
  Source field
  Join Relation
  Target table
  Target field

Each field has a visual assist that shows valid values. If you enter an invalid value, the
grid will show you an error message when you exit the field.

There are three join types available: SIMPLE, LEFT OUTER, and RIGHT OUTER.

A SIMPLE join means that the defined fields MUST have a matching value in both the
source and target tables. 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 now row is
returned to the grid.

A LEFT OUTER join means that if the target table does match the field to the source
table, then the target table values will be returned. If the target table does not match
the field to the source table, then a row will be reutrned to the grid, but the target table
fields will be filled with null values.

LEFT OUTER joins have two very practical uses:

     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 a LEFT OUTER join except the source
and target tables are swapped. Use a RIGHT OUTER join, for instance to identify
detail rows that do not have a header row.

Based on the tables you select, Data Access Studio will attempt to suggest joins that
will do these various reports in the "Suggested Joins" grid.

Columns

This is where you select the columns that you want for your report. To select a


30
                                                                                User's Guide


column:

 Check the box for that column

You must select at least on column from each table in your join.

Note that the fewer columns that you select for your report, the better the report will
perform.

Also, if you select too many columns for your join, your join may not return rows. If
this happens, please see the jde.log for possible errors. To correct the problem, try
removing unnecessary columns from your join.

Toolbar Buttons

Save and Close

Once you have designed your layout view to your satisfaction, press save and close to
save it. Data Access Studio will validate your view to make sure that it can be used. If
any problems are found, Data Access Studio will notify you with the problem and
resolution. You may not save a view that does not validate correctly.

Cancel

Press this to exit the form without saving the view.

Delete

Press this to delete currently highlighted rows in the grid. Note that if you are not
allowed to delete a certain row, this button will have no effect.

Apply Suggested Joins

When you are on the "Suggested Joins" Tab, pressing this button will transfer the
selected join definitions to the "Actual Joins" grid.

Help

Shows this help topic information




                                                                                           31
Data Access Studio Manual




Example of Creating a Table Join
Layout
The following example shows you create and use custom Table Joins. In this example
we will create a join over the Sales Order Header and Sales Order Detail files.

     1. From the main menu, click File | New | Layout...




     2. In Layout Name, type: "Sales Order Join"
     3. In the grid under the Tables tab, type:

          F4201
          F4211

     4. Note that as you tab out of the Table filed, the description will automatically
        populate. Note that you may also use the visual assist in the cell editor to select
        these tables as well:




32
                                                                               User's Guide




   4. Click the "Suggested Joins" tab:




Note that the rows for Suggestion 1 are already highlighted. In general, you can click
on a suggestion row and the editor will highlight the joins for that suggestion. Data
Access Studio determines suggested joins by searching the table definition information.

Double-click the row for suggestion 1 (or alternately click "Apply Suggested Joins"
button on the toolbar). This transfers the selected joins to the "Actual Joins" tab. This

                                                                                            33
Data Access Studio Manual


saves you from type in the information manual. It also allows you to browse possible
joins between the table in your query.

Once you apply the suggested joins, the editor will report: "Applied 3 joins to Actual
Joins".

Note that the join we selected will product a header detail join between the F4201 and
F4211. This will allow us to create reports that let us drill down from the order number
into the order details.

     5. Click the "Columns" tab.
     6. Select the following columns in F4201:

         Order Co (KCOO)
         Order Number (DOCO)
         Or Ty (DCTO)

     7. Select the following columns in F4211:

         Order Number (DOCO)
         Line Number (LNID)
         Short Item No (ITM)

     8. Click "Save and Close"

Data Access Studio will validate your join. If there are no mistakes, it will open a new
window that show the columns for your join. Click the Find button to fetch data into
your join. Try the following:

 Group by order number and sort by Line Number. Click Find.

 When you expand each order number you will see the line numbers and item
 numbers that make up that order


 Type in a search criteria for Short Item No (using the filter box).

        The result will show all orders for that Short Item No.


 From the main menu, click on Layout | Grid Styles.

        On the Grid Styles control, change the "Column Header" to
        "Description and Table"



34
                                                                             User's Guide


    Now you will see to which table each column belongs.

    This will let you distinguish between F4201 Order Number and F4211
    Order Number

In the F4211 Order Number filter box type <BLANK> and click Find.

    This query will show you all Orders that have a header record but no
    detail records. This is an excellent way to find referential integrity
    problems between a header and detail table.

    In general, create a LEFT OUTER join between the header and detail
    table. Search for <BLANK> in on of the join columns for the detail
    table (in this example we chose F4211 Order Number)

From the main menu, click Layout | Customize View...

    When prompted to save your layout, do so.

    You will see the "Create Table Join for Layout" for again. You may
    now alter your business view as you need. On the "Columns" Tab, lets
    select the column F4211 Ship To Number (SHAN). Click "Save and
    Close".

    Now the "Ship To Number" appears in your view.

    Do this as often as you need to alter your table joins. You may
    add/remove columns, join conditions and even tables!




                                                                                      35
Data Access Studio Manual




Maintaining layouts
Once you have created a layout, you may maintain it with the following File operations:

 Open
 Save
 Save As
 Customize View
 Restore
 Delete

Open a Layout

Once created, you may access your layout simply by selecting it--see "Open Layouts".

If your layout is based on a table or view, you may also:

     1. Open the table or view--see "Open OneWorld Data"
     2. Select the desired layout from the "Layout" box in the toolbar

Save a Layout

You may modify layouts as you wish. With your layout open, click the "Save" on the
"File" menu.

If this is a new layout, Data Access Studio will prompt you with the "Save As" form (as
shown in the next section).

Save As Layout

To save a layout as a different name, click the "Save As" link on the "File" menu. This
allows you to make copies of layouts.

Data Access Studio will prompt you with the "Save As" form:




36
                                                                                User's Guide




Enter a layout name in the edit.

To save the filter values in the grid with the layout, click the "Save Filter Values with
Layout" checkbox.

Click "OK" to accept the change and "Cancel" to cancel the change.

Customize View

If you created a Layout from scratch (see "Creating a New Layout".), this option will be
enabled for that layout. When you click customize view, Data Access Studio will display
the form that will let you modify your data view. You can change all aspects of your
view definition (tables, joins, columns on-the-fly.) and apply these changes back to your
layout.

Restore a Layout

To restore a layout to its previous state, click the "Restore" link on the "File" menu.

Delete a Layout

To delete a user layout, click the "Delete" link on the "File" menu.

You may also delete one or more layouts from the Select Layouts form--see "Open
Layouts".




                                                                                            37
Data Access Studio Manual



Layout Styles


Layout Styles
Data Access Studio allows you to customize colors, fonts, and behaviors of your layout:




The Grid Style control appears in two places:

 File | Default Preferences
 Layout | Grid Styles

The Default Preferences Grid Style controls how the grid will look for all un-customized
grids. In other words, each time a user opens a table or business view that does not have
a custom layout, Data Access Studio will apply the Default Preferences Grid Style. For
more information see "Default Layout".

The Layout Menu Grid Style applies only to the currently active grid. This control
allows you to make presentation changes to just that grid. Once you make your changes,


38
                                                                              User's Guide


you may save the layout to that grid.

The following sections discuss how each item in the Grid Style control alters the
appearance of the grid:

Restore Defaults

The "Restore Defaults" button at the top of the control restores each presentation settings
to its default value.

Presentation Property Table

The next item down in the control is the Presentation Property Table. The following
diagram illustrates each customizable property in this grid:




For each row in the Presentation Property Table, you may customize the following:

 Font
 Font Color
 Background Color

Font

To change the Font, click on the desired Font cell:




                                                                                         39
Data Access Studio Manual




Click the "..." editor button:




Select the desired Font and click OK.

Font Color

To change the Font Color, click the desired Font Color cell:




40
                                                                              User's Guide




Select a color from the drop down box. Note that you may also click the "Custom" tab
and "System" tab to select colors.

Background Color

To change the Background Color, you apply the same procedure as the Font Color except
you select a Background cell.

Grid Lines

The next two check boxes on the Grid Style control specify whether or not Data Access
Studio will draw grid lines.

To enable vertical grid lines, check "Show Vertical Lines". To disable vertical grid lines,
un-check "Show Vertical Lines".

To enable horizontal grid lines, check "Show Horizontal Lines". To disable horizontal
grid lines, un-check "Show Horizontal Lines".

Sort at Database


                                                                                         41
Data Access Studio Manual


When unchecked (default), DAS will perform all sorting at the client end. This typically
results in faster selects and counts. This degree of sorting is adequate for the majority of
ad hoc queries and reports.

When checked, DAS will request the database to do any sorting. This typically results in
slower selects and counts when you press the find button. The data that is returned in this
mode will be sorted in relation to the rest of the data in the database itself.

Auto-Width On Find

When checked, Data Access Studio will best fit the grid after each query. When
unchecked, Data Access Studio will leave the columns alone after a query.

Word Wrap Cells

When checked, Data Access Studio will word wrap the contents of each cell. This
feature is useful if you have a long text field that is better displayed on more than one
row. Check this box and then increase the row size by clicking on the row header
boundary and dragging it to the desired height.

When unchecked, Data Access Studio will fit strings in a single row. If the string
exceeds the size of the cell, then Data Access Studio will append "..." to the end of the
string.

Auto-Uppercase Filters

When checked, Data Access Studio force the filter for each column to uppercase if the
Data Dictionary specifies that the column is upper case. This is a great convenience for
when you have to type filters in an upper case column.

When unchecked, Data Access Studio will make all filters mixed case. You will have to
manually manage the correct casing when typing in values for your filters.

Column Header

Data Access Studio allows you to display one of four column header schemes:

 Alias - Show the Data Dictionary item alias
 SQL Name - Show the SQL name of the column
 Long Name - Show the Data Item long name
 Description - Show the two-line grid description
 Description and Table - Show the table name and description. This can be
 very useful for viewing business views that have the same column descriptions
 in different tables.


42
                                                                             User's Guide



Set the desired Column Header text by clicking the dropdown list and selecting one of the
five options:

Multi-Sort Font Size

The Multi-Sort Font size comes into action whenever a user sorts two or more columns
simultaneously. In this case, Data Access Studio will display the sort order of each
column. This is the Multi-Sort Font is the size of the superscript as shown below:




You may change the size of this superscript by selecting one of the values in the Multi-
Sort Font Size drop down list.




                                                                                           43
Data Access Studio Manual




Default Layout
Each user of Data Access Studio has a Default layout. The default layout is how Data
Access Studio will display un-customized grid data. To view your default layout do
the following:

     1. Open Data Access Studio
     2. Click File | Default Preferences:

Data Access Studio will display the following form:




Each of the properties on the "Grid Style" tab is a presentation item that you may
customize. Once customized, Data Access Studio will present each new grid with
these presentation properties.



44
                                                                              User's Guide


To change a property, click the property in the grid and select a new value for that
property. If you have a grid open in the background, you will see a preview of your
change. See the "Grid Styles" section for a detailed discussion of each item on this tab.

You may restore the system default style by pressing "Restore Defaults".

Pressing "OK" commits the change. Pressing "Cancel" reverts to the previous style.




                                                                                            45
Data Access Studio Manual



Navigating Data


Navigating Data
Once you have opened a table or business view layout, you are ready to browse the
data. Data Access Studio provides the following ways to navigate your data:

 Find
 Navigation Buttons
 Filter
 Sort
 Index Assistance

The subsequent sections explain each of these capabilities in detail.




46
                                                                            User's Guide




Finding Data
Finding data is perhaps the most basic data navigation operation. Data Access Studio
provides find functionality with two Find buttons:    (find with no progress update) and
   (find with progress update).

   loads records immediately, but cannot provide a percentage of rows loaded.   loads
slower but always shows the percentage of rows loaded to the grid. Which find operation
you choose depends on your query requirements.

These two buttons appear on the "Load Progress" tool bar. When you press either Find
button, Data Access Studio will select rows from the database and populate the grid with
the resultant records. The figure below shows an example of pressing    Find on the
"Accounts Payable Ledger - F0411" table:




When the Find operation completes, Data Access Studio does the following:

 Loads grid rows with retrieved records
 Disables the Cancel Button
 Updates the Record Selection information ("e.g. Record: 1")
 Highlights first row in the grid




                                                                                       47
Data Access Studio Manual



When the Find operation completes, Data Access Studio does the following:

 Loads grid rows with retrieved records
 Updates the Progress Bar to indicate the percentage of the rows loaded into
 the grid.
 Disables the Cancel Button
 Updates the Record Selection information (e.g. Record: 1 of 365")
 Highlights first row in the grid

What happens during a Find operation

When Data Access Studio loads records, it loads the records on a background thread.
This means that the User Interface will not be locked up while the load occurs. This
feature frees you to do other work in Data Access Studio as the data is loading.

As data loads in the background, Data Access Studio does two things:

 Updates the Progress Bar
 Enables the Cancel Button

The Progress Bar update shows you how fast the load is going and how much data is left.
The Cancel button allows you to the Cancel the load at any time.

When the navigation operation completes, Data Access Studio does the following:

 Loads grid rows with retrieved records
 Updates the Progress Bar
 Disables the Cancel Button
 Updates the Record Selection information
 Highlights grid row depending on Navigation button pressed




48
                                                                               User's Guide




Record Navigation Buttons
Once data is loaded into the table, you may use the record navigation buttons to step
through your data.




  - Scrolls grid to the top record.

  - Scrolls grid to the next page of records. Records are fetched from the database as
needed.

  - Scrolls grid to the previous page of records.

   - Scrolls grid to the bottom record of the selection. Records are fetched from the
database as needed. Note that this operation may take some time if the number of
records is high.




                                                                                         49
Data Access Studio Manual



Filtering Data


Filtering Data
Data Access Studio has a powerful system to filter data.

Data Access Studio provides filtering capabilities via filter boxes. Each grid column
has a filter box between the Column Caption and the Grid Rows:




Filter Box Properties

The filter box provides the following capabilities:

  Filter-as-you-type
  Filter modifiers: Wildcards, ranges, list of values, blanks, equality operators
  List of distinct values

A user may provide filter criteria for each column in a grid. To clear all filter criteria,
press the Clear Filter button: on the main toolbar.

For numeric columns that show a decimal or thousandths separator (e.g. 100,000.00),
the separator characters are optional in the filter box. This means that if you type
100000 or 100,000 or 100000.00 you will still get the same filter result.

The following sections describe each of these features in detail.




50
                                                                                User's Guide




Filter-as-you-type
One way to enter data into a filter box is to click the box and type into it.




Once you begin to type into the box, Data Access Studio immediately filters the data to
match what you type. The following example shows the grid after each character as
you type: 500.




As you type 500, the grid shows just the rows that begin with the characters in the
filter box. This feature allows you to "zero-in" on the data.

The following grid shows what happens after you finish typing:

 If User
                     Then...
 presses...

 Enter               Then the filter box is closed. Data Access Studio performs


                                                                                          51
Data Access Studio Manual



                   a Find on the database and returns rows that match the
                   filter box criteria.

 Right arrow
                   Data Access Studio enables the filter box for the column
                   immediately right of the present one.
 or Tab

                   Data Access Studio enables the filter box for the column
 Left arrow
                   immediately left of the present one.

 Down arrow or
 any mouse         Filter box closes. Data Access Studio applies resulting
 click outside     filter to grid data. No additional fetch from the database is
 the column        performed
 header area

Tips

 The live filter is most effective once all the rows that you require have been
 loaded into the grid. If you have not loaded all the records, then when you are
 finished typing your filter, make sure to press the "Find" button.
 The live filter works by auto-appending a wildcard character. When you finish
 typing and tab out of the filter, the wildcard character is removed. In the
 cases where you want to keep the wildcard at the end of your filter, type in
 the wildcard character ('*') explicitly at the end of your filter.
 If you click out of a live filter, DAS will append the wildcard character to your
 filter. If you TAB out, DAS will keep your final filter result as a literal result.




52
                                                                                 User's Guide




Filter Modifiers
The filter box on each column provides you with the following capabilities:

 Wildcards
 Equality operators: Greater than, Less than, not equal, etc.
 Filter for Blanks
 List of values
 Range of values

Note that you may enter filter values into multiple columns. The grid will show only
the rows that meet all filter criteria.

Wildcards
To specify a wildcard, use the '*' character at the beginning or end of a filter string.

Example 1: When you type "0*" in the Product Code field of the F0004 below:




The grid matches all rows that begin with "0" in the Product Code column.

Notes:

   1. Data Access Studio handles a wild card in the middle of a string such as
      "0*0" by treating the string as "0*"
   2. As you type into the filter box, Data Access Studio filters the grid rows
      after each typed character. Data Access Studio accomplishes this by
      implicitly adding a wildcard to the end of the filter string. When you are

                                                                                           53
Data Access Studio Manual


       done typing, the wildcard is removed. Try typing in some values into the
       filter box and the press the "Enter" key to see this effect.

Equality Operators
Equality operators give you the ability to specify range criteria. Data Access Studio
supports the following filter criteria and syntax (in each case below the 'value' directly
follows the symbol in the syntax column):

 Syntax     Criteria

 >          Filter all rows that are greater than the value

 >=         Filter all rows that are greater than or equal to the value

 <          Filter all rows that are less than the value

 <=         Filter all rows that are less than or equal to the value

 <> or !    Filter all rows that are not equal to the value

Example:

If you type: ">0053608" in the "Account ID" field in the F0902 table, then the grid
shows only those rows whose Account ID is greater than 0053608




Blanks

54
                                                                          User's Guide


Sometimes you will need to search a column for blank or null values. Data Access
supports this by providing the syntax:

<blank>, <BLANK>

When you type "<blank>" or "<BLANK>" into a filter box, the grid will show only
those rows that have a blank or null value in them;

Example:

If you type "<BLANK>" into the PC (Posted Code) field of the F0911 table, then the
grid shows all rows where Posted Code is blank:




Example:

This example shows how to select all rows that are not blank. If you type
"!<BLANK>" into the PC (Posted Code) field of the F0911 table, then the grid shows
all rows where Posted Code is not blank:




                                                                                     55
Data Access Studio Manual




List of Values
Sometimes a user needs to view rows that match several values for a column. For
instance, a user might want to see all rows where Posted Code is P or X. Data Access
Studio provides this ability with the semicolon operator: ;. The semicolon operator
means a logical 'OR'. When typing values into a filter box, simply separate each value
with a ;. The grid will show all rows that match each item in the semicolon-separated
list.

Example:

If you type "FL;WL" in the Business Unit Type column of the F0006, then the grid
will show all rows where the Business Unit Type is either 'FL' or 'WL'.




56
                                                                            User's Guide




Range of Values
Sometimes a user needs to view rows that match a range of values. For instance, a use
might want to see all rows where Batch Number is greater than 8,100 and less than
8,500. Data Access Studio provides this ability with the colon operator: :. Then colon
operator means a logical 'AND'.

Example:

To select the range 8,100 through 8,500 of Batch Numbers in the F0011 table, do the
following:

 Open the table.
 In the "Batch Number" filter box, type ">8100:<8500"
 Press the Find button
 Data Access Studio will display all rows where Batch number is greater than
 8100 and less than 8500.




                                                                                         57
Data Access Studio Manual




Today's date
To filter a date column by today's date, type <TODAY> in the filter box of the date
column. Note that you may combine the <TODAY> tag with equality operations. For
example, if you wanted all dates less than or equal today, you would type:
<=<TODAY>




58
                                                                                 User's Guide




Filter Distinct Values
Data Access Studio provides the ability to select filter values by the distinct values
already loaded into the grid. To get the list of distinct values loaded into a grid, right-
click on the filter box:




Data Access Studio will popup a list of distinct values. Select one of the values in the
popup by clicking it:




                                                                                              59
Data Access Studio Manual




Sorting
Data Access Studio provides the ability to sort any column in ascending or descending
order. Furthermore, you may sort any combination of columns at a time -- an operation
referred to as multi-sorting.

To sort by a column:

     1. Right-click on the column's caption. In this example, right-click the
        "Address Number" caption on the F0101 table.
     2. Select either Sort Ascending or Sort Descending. In this example, we
        chose "Sort Ascending":




Data Access Studio sorts the grid by ascending Address Number. An up arrow
indicates that the column is sorted in ascending order. A down arrow indicates that the
column is sorted in descending order.

Multi-Sort

Data Access Studio provides the ability to sort more than one column at a time. To
sort multiple columns, hold the "Ctrl" key down as each column sort is selected.

In this example, we sort by ascending Business Unit Type and Business Unit in the


60
                                                                               User's Guide


V0006B Business View. To do this:

   1. Open the business view and right-click the Business Unit Type Column.
   2. Holding the Ctrl key down the entire time, click Sort Ascending. Then
      right-click the Business Unit column (still holding down the Ctrl key)

Again click Sort Ascending.




Now release the Ctrl Key. Notice that the grid is sorted ascending by Business Unit
Type first and Business Unit second. Notice each column indicates this multi-sort by
showing an arrow which indicates the direction of sort and a superscript which
indicates the sequence of the sort (i.e. 1 = first, 2 = second, etc.) You may multi-sort
any number of columns.

To clear all sorting, click the Clear Sorting button:   on the main toolbar.

To clear sorting on a specific column, right-click the column header and select
"Remove Sort".




                                                                                           61
Data Access Studio Manual




Index Assistance
To take better advantage of the indices in the OneWorld system, Data Access Studio
implements an "Index Assistant". This Index Assistant is a combo box on the main
toolbar labeled "Index". The Index Assistant combo box shows a list of all valid indices
for the currently active object. Upon selecting an index from this list, Data Access Studio
does the following:

 Moves the columns of the selected index to the front of the column order
 Places a key icon   in the column caption.
 Prevents the column from being moved

Before




After - The Result of applying index to F0911.




Notice the index columns were moved to the front of the column order.

The result of these steps makes each index available to you at the front of the table. To

62
                                                                            User's Guide


take advantage of an index do the following:

 Select an index that most closely matches the query you require.
 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",
 "Explanation Alpha Name" and "Document Number", the index will not be used
 if you only specify a value for only "Explanation Alpha Name" and "Document
 Number". All left-most index columns must be specified first for the index to
 take effect.
 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 provide better performance. If you notice that a fully
 specified index does not result in a fast query, please notify your OneWorld
 database administrator. Provide your DBA with the index columns you used
 and the time the query took to run.

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.




                                                                                          63
Data Access Studio Manual



Working with Columns


Working with Columns
Columns are an essential layout ingredient. Columns represent the data for a field in a
table or business view. Data Access Studio lets you control various aspect of each
column such as width, name, position, and visibility. The following topics discuss
how you can control the columns in a layout.




64
                                                                            User's Guide




Selecting Columns
Many operations in Data Access Studio work on one or more selected columns such
as:

 Grouping
 Sorting
 Group summaries
 Calculations
 Print Areas
 Hiding and Showing columns
 Exporting

In each of the above operations, you must select one or more columns and then
perform the operation. This multi-column capability gives you a shortcut to
performing otherwise tedious tasks.

Selecting a single column

To select a single column:

   1. Left-click the column header

DAS will highlight the column header and data to indicate that the column is now
selected

Selecting a range of columns

To select a continuous range of columns:

   1. Left-click the first column header
   2. Shift-Left-click the last column header

DAS will highlight all selected column headers and data to indicate that the columns
are now selected.

Selecting a set of columns

To select a non-continuous set of columns:

   1. Left-click the first column header

                                                                                       65
Data Access Studio Manual


     2. Ctrl-Left-click the each subsequent column header

DAS will highlight each selected column header and data to indicate that the columns
are now selected.




66
                                                                        User's Guide




Column Renaming
You may change the caption of any column.

Custom Caption

To type in a new column caption:

   1. Right-click the desired column.
   2. Select "Rename"
   3. On the form that appears, type in the new column caption and press OK
      to accept the change.

Alias Caption

The alias caption is the column's unique underlying name.

To set the caption to the alias caption:

   1. Right-click the desired column.
   2. Select "Column Caption -> Alias Header"

Description Caption

The description caption is the column's descriptive title.

To set the caption to the descriptive caption:

   1. Right-click the desired column.
   2. Select "Column Caption -> Description Header"

Additional EnterpriseOne Captions

In addition to the captions above, EnterpriseOne provides a SQL and Long Name
header. The SQL caption is the way the column appears in SQL. The Long Name
Header is the Data Dictionary long name for the given column. Note calculation
columns do not have these two caption types available.

To apply the SQL caption:

   1. Right-click the desired column.

                                                                                 67
Data Access Studio Manual


     2. Select "Column Caption -> SQL Name Header"

To apply the Long Name caption:

     1. Right-click the desired column.
     2. Select "Column Caption -> Long Name Header"




68
                                                                              User's Guide




Column Sizing
You may change the width of any grid column.

Manual Resizing

To change the width of a grid column manually, click the boundary between two
columns:




The grid will show a vertical line. While holding the right mouse button down, drag
the boundary left or right to the desired width. Release the mouse button to make the
change.

Best Fit Single Column

Data Access Studio can automatically size any column to best fit the size of its Caption
and rows contents. To best fit a single column, right-click the column caption and
select "Best Fit Column":

Best Fit All Columns

If you want to best fit all columns at once, click any column caption and select "Best
Fit All Columns":




                                                                                           69
Data Access Studio Manual




Column Hiding
You may hide or show any available grid column.

Hiding a Single Column

To hide a column, right-click the column header and select "Hide Column."

The column will then be hidden from view.

Hiding and showing multiple columns - method 1

You may highlight multiple columns with the standard SHIFT+, and CTRL+
mechanisms (See "Selecting Columns"). SHIFT+click will select all columns between
clicks. CTRL+click will add each column to the selection. Once you have selected the
desired columns, right-click the column header and select "Hide Columns".

The selected columns will be hidden from view.

Hiding and showing multiple columns - method 2

The previous methods work for hiding columns only. The following method shows
how to hide, show, and search for columns. To hide or show multiple columns, right-
click any column caption and select "Hide/Show Columns".

Data Access Studio will pop up the Hide Show Column Control:




70
                                                                           User's Guide




The rows in this control will match the column captions in your grid. A check mark
next to a column means the column is visible. A blank check mark means the column
is invisible. To hide and show columns, simply check and uncheck the Visible check
box.

If you need to hide or show many columns at once, you may right-click on this form to
get the following popup menu:




                                                                                        71
Data Access Studio Manual




The following table describes each available action:

 Menu Item                Action

 Show All                 Shows all columns by checking each box

 Hide All                 Hides all columns by un-checking each box

                          Highlight one to many rows in the control. When you
 Show Selected            select this menu option, DAS will show each
                          highlighted column

                          Highlight one to many rows in the control. When you
 Hide Selected            select this option, DAS will hide each highlighted
                          column



Partial Columns

If you save your layout after you hide columns, then from that point on, the Find


72
                                                                           User's Guide


operation will only return those columns. This provides a performance advantage as
only the columns you request will be brought back to the grid. If you need to show
additional columns from this point, you have to perform another Find before you see
the data in these newly shown columns.

For instance, suppose that you have a layout that only shows the following columns of
the F0902:




Now suppose that you wanted to add the Company column. When you add the
Company column, you will notice that the grid lines appear grayed-out:




The row values are grayed-out because there was not data retrieved for this column on
the last Find. To get data into this column now, just press Find again:




                                                                                        73
Data Access Studio Manual




Note that this behavior only occurs when the layout is saved to a name. If you are
working with the default layout (blank layout name) then all columns are fetched back
to Data Access Studio.




74
                                                                            User's Guide




Column Re-arranging
You may re-arrange grid columns in any sequence. To re-arrange a single column,
click on the column caption and drag to desired location. Release mouse button to
drop column into a desired location.

In this example, we click the "Obj Type" and start dragging to the left:




The grid will highlight the drop location. In this case, "Obj Type" will be dropped to
the left of the "Product Code" column. When we release the mouse button, we see that
this indeed happens:




                                                                                         75
Data Access Studio Manual



Grouping


Grouping Data
Grouping data is one of the unique and powerful features of Data Access Studio.
When you group a column, it means that all rows that have the same column value are
grouped together. You may group as many columns as you require.

Data Access Studio makes it very easy to group columns:

 Group a column by right-clicking the column caption
 Group a column by dragging it onto the Grouping panel

Grouping Data becomes even more useful when you add Summaries. As you will see
in the next section, Summaries allow you to summarize your grouped data. Grouped
data and Summaries are critical components of many data analysis problems.

Group a column by right-clicking the column caption

To group a column this way:

     1. To group by a column, right-click a column and select "     Group By This
        Column".

Example:

     1. Open F0411 in Data Access Studio
     2. Right-Click the Document Number Column (DOC)
     3. Select "Group By This Column":

     4. Result:




76
                                                                       User's Guide




When you grouped by Document Number, Data Access Studio did the
following:

   1. Sorted the Document Number column
   2. Collected all rows that have the same value for that
      column and placed these rows in a group
   3. Displayed each group in the grid with a "+" symbol next to
      it.

When you press the "+" symbol, you will see all rows associated with
that group (i.e. all rows that have the same Document Number):




                                                                                77
Data Access Studio Manual




Group a column by dragging it onto the Grouping panel

To group a column by this method:



     1. To group by the grouping bar, press the        (Toggle Group Panel) button
        on the main toolbar:




        This opens up the Grouping panel on the current grid:




78
                                                                         User's Guide




   2. Drag-and-drop the "Document Number" column onto the Grouping
      panel:




       Now the data is grouped by Document Number

Grouping multiple columns

Grouping multiple columns means grouping by 1 column, then grouping that result by
yet another column. You may group as many columns as you wish. This feature can
be very handy in data analysis.

Example:



   1. Open the F0411 in Data Access Studio


                                                                                     79
Data Access Studio Manual


     2.   Open the Grouping panel
     3.   Drag Address Number onto the Grouping panel
     4.   Drag Document Number onto the Grouping panel
     5.   Click Find




          When you performed these steps, Data Access Studio:

             1. Grouped the rows by Address Number
             2. Grouped the rows by Document Number

     6. Right-click on the first grouped Document Number




                                                             This is the
          group row popup menu. See the Group Row Popup Menu below for a
          full description of each of the menu items.

     7. Click the "Group all at this level" item:




80
                                                                              User's Guide




       This display shows how the rows are grouped first by Address Number,
       then by Document Number (with all the details hidden.)

Group Row Popup Menu

When you right-click on any group row, Data Access Studio will show you a popup
menu with the following items:



 Full expand
 Full collapse
 Group all at this level
 Set grouping level
 Clear grouping
 Add count

Full expand

When you select "Full Expand", Data Access studio opens all the group nodes.

Full collapse

When you select "Full Collapse", Data Access studio closes all the group nodes. After
this operation, you will only be able to see the group rows for the first grouped column.

Group all to this level

This option groups all rows to the given level only.


                                                                                            81
Data Access Studio Manual


Set grouping level

This option will prompt you with an input form to specify a grouping level. Specify a
grouping level and press OK. Once you specify a grouping level, on each subsequent
find, DAS will collapse all groups to this level.

Enter the grouping level as follows:

0 = Expand all rows

1 = Expand to first row

2 = Expand to second row

etc.

This option is useful if you are interested in seeing a particular summary view of the
data versus seeing all the details.

Clear grouping

This option removes all grouping from the grid and returns the grid back to the rows-
only view.

Add count

This option appends the row count to the select group level. This is useful for seeing
how many rows constitute any given group.




82
                                                                            User's Guide




Summaries
One of the powerful features of Data Access Studio is that it allows you to summarize
your column and grouped data. You may summarize your data with the following
operations:

 Sum
 Minimum
 Maximum
 Average
 Count

Data Access Studio provides summarization capability for all columns and all groups.
This allows you to create hierarchical summaries with ease. The following examples
show how you apply a summary operation to a column:

 Example 1: Creating an column amount total for the F0911 file
 Example 2: Summaries and groups
 Example 3: Summaries and collapsed groups
 Example 4: Adding group counts

The last section shows how to change the summary prefix. This enables you to
customize how the summaries appear on your layout.

Example 1: Creating an column amount total for the F0911 file

To create a column total on the F0911, do the following:

 Open the F0911
 Turn on summaries in the grid style control. Summaries are turned on by
 default. See Grid Styles for more information on how to turn on summaries.




                                                                                        83
Data Access Studio Manual




 Right click the Summary Panel in the "Amount" column and select "Sum":




 The sum of the amount value now appears in the footer panel

Example 2: Summaries and groups

You may also apply summaries at the group level.


84
                                                                   User's Guide


Suppose we open the F0911 and group by GL date:




By right-clicking under amount, we select sum. In a similar fashion, we can
right-click under "Doc Co" and select "count". The result is the following:




                                                                              85
Data Access Studio Manual




Example 3: Summaries and collapsed groups

Sometimes, all you want to see is the summary information -- not the row information.
This can be the case when there are many detail rows that would confuse the end result
if they were shown. The following shows how to show the summaries and hide the
details for grouped rows:

 Open the F0902
 Hide all column except for Account ID, Ledger Type, and Week To Date Net
 Postings
 Group by Account ID and Ledger Type
 Right-click any Group Row and click "Full Collapse"
 Make sure your grid style has "Summaries" checked (see Grid Styles)
 Right-click any Group Row and click "Show Collapsed Summaries":




86
                                                                User's Guide




Data Access Studio displays the summary line between each group row (as
shown below).
Right-click the "Week To Date Net Postings" column summary area and
select "Sum".




                                                                          87
Data Access Studio Manual




 Now the grid shows only the summaries for each group. You may repeat this
 procedure for any number of sub-groups.

Example 4: Adding group counts

Data Access Studio also allows you to add a count on the group level. This feature is
especially helpful if you want to see group counts at a glance.

 Continuing the example above, right click on any Account ID group row and
 click "Add Count":




88
                                                                             User's Guide




Data Access Studio places the record count (in parenthesis) next to each group.

Customizing summary text

When you add a summary, Data Access Studio shows the summary with the default
prefix. For example, if you add a summary that is a "Minimum" summary, Data
Access Studio displays the summary as "MIN="<value>.

If you want to specify your own prefix do the following:



   1. Right-click on the group footer to show the summary popup menu.
   2. Select "Customize"
   3. On the form that appears, type in the text you want to display for any of
      the available summary types. If you want to revert the text back to the
      default values, press the "Restore Defaults" button.
   4. Click "OK" to accept.

For this point onward in your layout, added summaries will use the custom prefix that
you specified above. The customized summary prefixes are stored with your layout.

                                                                                        89
Data Access Studio Manual




90
                                                                                User's Guide



Calculating Data


Calculating Data
Data Access Studio gives you the ability to create your own derived information on the
grid. This calculated information allows you to summarize and organize your data into a
meaningful report. All calculations are stored with your layout -- so that you can recall
layouts with different calculation setups. DAS indicates that a column is a calculation
column by displaying a calculator indicator ( ) in the column caption.

DAS supports the following calculation categories:

 Database lookup
 Date
 EnterpriseOne® Interoperability
 Math
 System
 Text

This topic discusses the following:

 Creating calculations
 Editing calculations
 Resolving calculation errors
 Performance considerations


Creating calculations
DAS provides two ways to create a calculation:

 Quick calculation
 Add new calculation with editor

Quick calculation

Not all calculations support this method. For the calculations that do, this is the easiest



                                                                                              91
Data Access Studio Manual


way to add calculations to your grid.

In this method, you do the following:

     1.   Select one or more columns in the grid
     2.   Right click a column.
     3.   From the column menu highlight: Calculations -> Quick ->
     4.   Select a calculation from the popup menu.

Example - Create a calculation column shows the difference in amounts
between the Gross Amount and Open Amount in the Account Payable
Ledger (F0411)

     1. Open the F0411
     2. Select the Gross Amount and Open Amount columns (see Selecting
        Columns)
     3. Right-click on the Gross Amount column header.
     4. Select Calculations->Quick->Math->Difference




DAS will create a new column named "Difference" and place it to the right of the Gross
Amount and Open Amount columns. The Difference column has the result of taking
each Gross Amount row and subtracting the corresponding Open Amount. In general,
the Quick Calculation will process from left-to-right. So in this example, DAS subtracts
the left-most column from the right-most column.

Add new calculation with editor

You can enter all calculations using the calculation editor. The calculation editor lets you
add, edit, and delete any supported calculation.

In this method, you do the following:

     1. Right-click on any column header
     2. Select Calculations->Editor...

92
                                                                       User's Guide


   3. Select a calculation
   4. Fill in the details of the calculation
   5. Save

Example - Create a calculation column shows the difference in amounts
between the Gross Amount and Open Amount in the Account Payable
Ledger (F0411)

   1. Open the F0411
   2. Right-click the Gross Amount column header (any column will do)
   3. Select Calculations->Editor




   4. In the highlighted cell under "Type of Calculation", select "Difference" and
      tab out of the field.




   5. Notice that DAS shows the Settings tab on the right-hand side. You
      specify all the calculations setting in this "Parameters" grid.
   6. In the "Parameters" grid, left-click the "Value" column next to the "Grid
      Column" row.
   7. Click the "..." visual assist.



                                                                                  93
Data Access Studio Manual




     8. Note that for calculations that can take multiple columns, you may choose
         multiple columns from this visual assist. This feature saves you a lot of
         time and tedium in defining multi-column calculations. For this example
         multi-select Open Amount and Gross Amount (to multi-select hold down
         the Ctrl key as you click your selections). Click "Select"
     9. Notice the "Parameters" grid now has two "Grid Column" rows for you two
         selections and one "Grid Column" row for new values.
     10. On the "Grid Column" row for "AAP", click the "+" button. This will change
         the "+" to a "-". This is necessary to indicate which field you want to
         subtract.
     11. Click Save.
     12. Click Close to exit the editor.

As in the "Quick Calculation" method above, you now have a new Difference calculation
that represents the difference between the Gross Amount and Open Amount columns.

Editing calculations
DAS provides two ways to edit calculations:

 Edit single calculation
 Edit all calculations

Edit single calculation



94
                                                                               User's Guide


To edit a single calculation:

    1. Right click on a calculation column header (a column with a            indicator)
    2. Select Calculations -> Edit...
    3. See "Using the calculation editor" below

Edit all calculations

To edit all the calculations in the grid:

    1. Right click on any column header
    2. Select Calculations -> Editor...
    3. See "Using the calculation editor" below

Using the calculation editor

Once you bring up the calculations editor, you may do the following:

  Change the settings of an existing calculation
  Change an existing calculation
   change to another type of calculation
   change caption
   change output type
  Delete existing calculations
  Add new calculations

Any change in the editor will not take effect until the "Save" button is pressed. If you
close the editor and opt not to save your changes, then no calculation will be altered.

Change the settings of an existing calculation

The calculation editor is organized by calculation headers on the left and calculation
details on the right. For each header row that you select on the left, DAS shows the
calculations settings on the right.

To modify a calculation's settings:

    1. Select the calculation on the left
    2. Edit the setting values of the calculation on the right (Non-editable fields
       are grayed out)
    3. For specific setting information for each calculation, refer to the help topic
       for that calculation.

Change an existing calculation



                                                                                           95
Data Access Studio Manual


 You may change an existing calculation to another type of calculation. For
 instance, if you have a calculation that performs a sum and you want to replace
 that calculation with multiplication, simply select "Multiplication" for the
 "Calculation Name" on the left hand side. When you change a calculation to a
 different calculation, DAS clears the detail settings.
 You may change the calculation caption by typing in a new caption. All other
 settings for the calculation will remain intact.
 You may change the output data type of the calculation by selecting a new data
 type (note this option only applies to single output calculations.) Be careful
 when you change the output type as an invalid output type will cause the
 calculation to fail. All other settings for the calculation will remain intact.

Delete existing calculations

To delete one or more calculations:

     1. Highlight one or more calculations on the left.
     2. Press Ctrl-Del (or right-click the grid and select "Delete")

Add new calculations

See "Add new calculation with editor" above.

Typing tips

You can enter all calculations in the editor without to using a mouse. Here are some tips
to help you:

 The TAB key will tab to the next editable field. TAB from the last editable field
 of the header row will activate the first editable field in the "Parameters" grid.
 SHIFT+TAB will tab to the previous editable field. SHIFT+TAB from the first
 editable field in the "Parameters" grid will activate the editor in the calculation
 header row.
 The F4 key will open any active visual assist. If the visual assist is not active,
 you can press ENTER and then F4 to open the visual assist.
 Some visual assist fields will let you type in the value and others will require
 you to select from the list of valid values. When selecting from the list of valid
 values, you may type in the value you need in the filter box, and then press
 Alt+S to select the value.




Resolving calculation errors
96
                                                                               User's Guide


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:

   1.   Run your calculation.
   2.   Right-click on calculation cells that are red.
   3.   Read the message box instructions and optional resolution.
   4.   Press "Edit Calculation" to correct the error.

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:

 Only re-calculating necessary columns.
 Caching results when appropriate (associated descriptions and table lookups).
 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.
 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.
 If you are querying against a large table (>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.




                                                                                          97
Data Access Studio Manual



Database Lookup


Database Lookup
This category has the following calculations:

 Table Lookup




98
                                                                              User's Guide




Table Lookup
Overview

This calculation allows you to fetch data from other EnterpriseOne tables into your
report. The table lookup will automatically cache fetch results -- resulting in fast
execution times if the fetched data is relatively redundant.

Property         Value          Notes
Quick            No
Calculation

Options          Data Name Name of the table or business view to query.
                 Index Name Name of the index to use for query
                 Datasource (Optional). Name of the datasource to use when
                            retrieving data
                 Sort Order (Optional). Select how you want to sort the target
                            dataset. Use this option when the target data set
                            has many values for what you want to query and
                            you need a specific value from based on the sort.

Inputs           Index values Column and literal values that define what you want
                              to fetch from the target table. Table lookup will use
                              these values to query the database.

                                When you select an Index Name (from the Options
                                above), DAS will populate this list of inputs with the
                                fields that match the input.

                                You may add or delete field names as you wish.

                                You may use filter value syntax (See Filtering Data)
                                when specifying literal values to fetch.

Outputs          Multi-Column One to many grid columns to populate with the
                              results of the query.

Use Cases

 Lookup related information that is stored in another table such as descriptions.

Example



                                                                                         99
Data Access Studio Manual


Suppose you wanted to look up the Account Description from the Account Balances table
(F0902). With the F0902 open, from the main menu select, Layout | Edit Calculations.




In the "Type of Calculation" column, select an empty line, click the visual assist and
select "Table Lookup". Once selected, DAS will show the parameters for the "Table
Lookup" on the right-hand side:




100
                                                                      User's Guide




Populate the "Parameters" as follows:

   1. Table/View name : type F0901 (this is the Chart of Accounts where the
      account description is)
   2. Index : Click the visual assist. Select "Account Id". When you click this
      visual assist, in general, you will pick the primary and unique key. DAS
      does, however, does let you pick other indexes as well
   3. Datasource : Normally you leave this field blank. When blank, DAS will
      use the default datasource to do the table lookup. If you want to explicitly
      set a datasource, you may enter the datasource name here.
   4. Define fetch : When you selected the "Account Id" index in step 2 above,
      DAS populates the left part of the field with AID. This refers to the AID
      field of the F0901 (i.e. the lookup table column.) We need to specify what
      value in our gird (i.e. the F0902) we want to use to fetch from the F0901.
      Since we have an AID column in our F0902, we type AID in the "value to
      fetch" field.
   5. Define result : Here we specify what we want to return to our grid. In the
      empty field next to "CALC_0", click the visual assist. Select "Description"
      (DL01)

Your screen should now resemble the parameters below:




                                                                               101
Data Access Studio Manual




When you click "Save" and "Close", DAS will create a new column named "F0901
DL01". When you press find on your grid you will see the description of the account in
your gird. By this method, you can link to any table in the OneWorld system to bring
back relate data to your grid.




102
                                                User's Guide



Date Calculations


Date Calculations
This category has the following calculations:

 Date Difference
 Day of Month
 Day of Week
 Day of Year
 Julian Date
 Month
 Month Description
 Today's Date
 Year




                                                        103
Data Access Studio Manual




Date Difference
Overview

This calculation returns the difference between two Date/Time columns. You may
display the difference in years, months, days, hours, minutes, or seconds.

Property       Value         Notes
Quick          Yes           Column selection must be two date columns.
Calculation                  DAS shows the date difference in days.

Option         Time span     Select how you want to measure the date
               units         difference: years, months, days, hours, minutes,
                             seconds. The default time span is in days. Years
                             are the number of complete years between two
                             dates. Months are the number of complete
                             months between two dates.

Input          Date1         A literal date string or a date column.
               Date2         A literal date string or a date column.

Output         One Column The Date1 minus Date2 represented in the
                          selected time span units.


 Use Cases

 Use when you need to see the number of days between two Date columns.
 Use when you need to see the number of seconds between two DateTime
 composite columns. (See E1 Time to DateTime).




104
                                                                                User's Guide




Day Of Month
Overview

This calculation returns the day of the month value for the given input date.

Property         Value          Notes
Quick            Yes            Column selection must include a date column
Calculation

Input            Date           A literal date string or a date column.

Output           One Column The day portion of the input date represented as
                            an integer.

 Use Cases

 Use when you need the day portion of a date value. Then filter on this field
 to narrow your selection based on the day of the month.




                                                                                        105
Data Access Studio Manual




Day Of Week
Overview

This calculation returns the day of the week value for the given input date.

Property         Value          Notes
Quick            Yes            Column selection must include a date column
Calculation

Input            Date           A literal date string or a date column.

Output           One Column The day of week of the input date as a text string -
                            - e.g. Monday, Tuesday, etc.

Use Cases

 Use when you need the day of the week of a date value. Then filter on this
 field to narrow your selection based on the day of week.




106
                                                                               User's Guide




Day Of Year
Overview

This calculation returns the day of the year value for the given input date.

Property         Value          Notes
Quick            Yes            Column selection must include a date column
Calculation

Input            Date           A literal date string or a date column.

Output           One Column The day of the year of the input date between 1
                            and 366.

Use Cases

 Use when you need the day of the year of a date value. Then filter on this
 field to narrow your selection based on the day of year.




                                                                                       107
Data Access Studio Manual




Julian Date
Overview

This calculation returns the Julian date representation for the given input date.

Property         Value          Notes
Quick            Yes            Column selection must include a date column
Calculation

Input            Date           A literal date string or a date column.

Output           One Column The Julian date representation of the input date.
                            The Julian date is the four-digit year minus 1900
                            followed by the three-digit day of the year. E.g.
                            January 1, 2004 = 104001.

Use Cases

 Use when you need the Julian date representation of a date value. Then
 filter on this field to narrow your selection based on Julian date.




108
                                                                          User's Guide




Month
Overview

This calculation returns the month portion of the given input date.

Property        Value           Notes
Quick
                Yes             Column selection must include a date column
Calculation

Input           Date            A literal date string or a date column.

Output          One Column The month portion of the input date.

Use Cases

 Use when you need the month portion of a date value. Then filter on this
 field to narrow your selection based on the month.




                                                                                  109
Data Access Studio Manual




Month Description
Overview

This calculation returns the month description of the month of the given input date.

Property        Value          Notes
Quick
                Yes            Column selection must include a date column
Calculation

Input           Date           A literal date string or a date column.

Output          One Column The month' description of the input date.

Use Cases

 Use when you need the month description instead of the month number.
 Then filter on this field to narrow your selection based on the month.




110
                                                                   User's Guide




Today's Date
Overview

This calculation returns today's date.

Property         Value          Notes
Quick            Yes
Calculation

Input            Date           None.

Output           One Column Today's date value.

Use Cases

 Use when you need to compare other dates in your grid against today's date.




                                                                           111
Data Access Studio Manual




Year
Overview

This calculation returns the four-digit year of the given input date.

Property         Value          Notes
Quick            Yes            Column selection must include a date column
Calculation

Input            Date           A literal date string or a date column.

Output           One Column The four-digit year of the given input date.

Use Cases

 Use when you need the year portion of a date value. Then filter on this field
 to narrow your selection based on the year.




112
                                                User's Guide



EnterpriseOne


EnterpriseOne Interoperability
This category has the following calculations:

 Account Category
 Associated Description
 E1 Time to DateTime
 Group Accounts




                                                        113
Data Access Studio Manual




Account Category
Overview

This calculation returns EnterpriseOne account category by querying the Automatic
Accounting Instructions table.

Property        Value         Notes
Quick           Yes           The Grid must have an Object (OBJ) column and
Calculation                   a Subsidiary (SUB) column. If the selected quick
                              calculation is on a company column, then DAS will
                              use the company values to query the AAI table.
                              Otherwise, DAS will use the default company:
                              00000

Input           Grid Column Any valid grid column.

Output          One Column The account category of the OBJ and SUB.

Use Cases

 Use to group account categories into Financial reports such as: Balance
 sheets and Profit and Loss.




114
                                                                            User's Guide




Associated Description
Overview

This calculation returns EnterpriseOne description for the given input column.

Property        Value          Notes
Quick           Yes            Column selection must include a column that can
Calculation                    have an associated description.

Input           Grid Column A grid column that can have an associated
                            description.

Output          One Column The associated description of the input column.

Use Cases

 Use when you need the EnterpriseOne description value for a grid column.
 EnterpriseOne stores much of its data as coded values. Use this feature to
 display the language-translated description of these code values on your
 grid.




                                                                                    115
Data Access Studio Manual




E1 Time to DateTime
Overview

This calculation converts an EnterpriseOne date and time column into a composite
DateTime column. Once calculated, you may do DateTime differences between
composite DateTime columns to see the time difference in days, hours, minutes, or
seconds. (See Date Difference.)

Property        Value         Notes
Quick           Yes           Column selection must be a date column and a
Calculation                   numeric time column.

Input           Date Column A literal date string or a date column.
                Time ColumnA literal time string or a time column of the format:
                            hmmss (e.g. 9:15.45 = 91545)

Output          One Column The composite DateTime that represents the input
                           date and time values.


 Use Cases

 Use when you need to summarize a date and time column into one absolute
 DateTime column.




116
                                                                           User's Guide




Group Accounts
Overview

This calculation returns format the Chart of Accounts (F0901) information into a
hierarchical grouping of accounts. This calculation works on any business view that
has the F0901 table with columns Business Unit (MCU), Object (OBJ), Subsidiary
(SUB), and Level of Detail (LDA) fields. You may select the level of detail to
summarize to and summarize amount values such as the week to date field in the
Account Balances table F0902.

Property        Value         Notes
Quick           Yes           The Grid must have the F0901 table Business
Calculation                   Unit (MCU), Object (OBJ), Subsidiary (SUB), and
                              Level of Detail (LDA) fields. If selected on a
                              column or set of columns, DAS will concatenate
                              the columns to form the group description. For
                              instance, you can right-click the description (DL01)
                              column to set the group label to the account
                              description. The calculation defaults to level of
                              detail 9(most detailed). You can change the level
                              of detail later by changing the in-grid Level of
                              Detail control value.

Input           Grid Column Any set of valid grid column.

Output          Seven       This calculation creates seven hidden calculation
                Hidden      columns which it groups to form a chart of
                Columns and accounts. When the calculation is complete, you
                Dynamic     will simply see the grid in the form of a chart of
                grouping    accounts. DAS will automatically close the groups
                            to the correct level up to the level of detail
                            requested. Summaries can be placed on any
                            column such as the Week-To-Date F0902 value to
                            get balances at each level of detail.

Use Cases

 Join the F0901 and F0902 and use to get detail Financial reports such as
 Balance Sheets, Profit and Loss reports, etc. DAS transforms the flat Chart
 of Accounts data into a rollup group of accounts.
 Because the report is in DAS, the report will be very flexible. You may add
 different category codes, filters, etc. to get the data you need to see.


                                                                                      117
Data Access Studio Manual


 Allows flexible drill down by changing the in-line level of detail control.

Example

   1. Create a new join between F0901 and F0902:
          1. Click File -> New -> Layout
          2. In the "Create Table Join for Layout", type a Layout name.
          3. On the "Tables" tab grid, type in F0901 and F0902.
          4. Click on the "Suggested Joins" tab. Double-click the first
             suggested join which is a LEFT OUTER join between the F0901
             and F0902. You should see a message box that says "Applied 1
             join to Actual Joins." The reason we chose a LEFT OUTER join is
             because we want to show the F0901 (Chart of Accounts) record
             whether there is a corresponding F0902 or not.
          5. Click on the "Actual Joins" tab. Verify that there is one record
             there.
          6. Click on the "Columns" tab. Click on the following columns:
             F0901.MCU, F0901.OBJ, F0901.SUB, F0901.LDA, F0901.DL01,
             F0901.PEC, F0902.APYC, F0902.AWTD.
          7. Click Save and Close.
   2. You now will have a join between the Chart of Accounts table and the
      Account Balances table. Right-click on the "Description" column to get
      the column popup menu. On the menu, select: Quick Calculation ->
      EnterpriseOne -> Group Accounts. On the level of Detail popup, click
      OK to accept 9 as the Level of Detail.
   3. Type in a valid Business Unit in the Business unit filter field. Enter !N in
      the PE field so that we filter out non-posting accounts from the report.
      Press Find. You should see a Chart of accounts that resembles the
      following:



   4. Hold the Ctrl key down and left-click the Beg Balance/PYE Forward and
      Week To Date Net Postings columns. Under those columns in the
      blank area under "50.1105. - Petty Cash", right-click in the grid. You will
      see the summaries popup. Select "Sum". This will add sum totals to
      your report:



This shows you account balances per account level.

To change the level of detail, change the value of the "Detail Level" control in the top-
left corner of the grid. You can drill up or down as you like to get more summarization
or less.


118
                                                                                 User's Guide


You can click the "Refresh Calculation" button on the toolbar to re-group the Account
Groups.

Special Notes

Once you add the Group Account calculation to your grid, DAS will disable database
filtering for the F0901 fields except for MCU. The filters will still work on the grid.
The reason DAS does this, is because the Chart of Accounts needs all the information
from the F0901 unfiltered first. After it gets the data, it will filter it and present the
results. This behavior will be transparent to you the user. Just know that it is
happening under the covers. You may notice it if you run into performance problems
with a very large F0902. The best thing to do is to filter by Business Unit or some
other field in the join.




                                                                                             119
Data Access Studio Manual


Math


Math
This category has the following calculations:

 Absolute Value
 Average
 Difference
 Division
 Margin
 Max
 Min
 Multiplication
 Standard Deviation
 Sum
 Uniform Buckets




120
                                                                          User's Guide




Absolute Value
Overview

This calculation returns positive portion of the input value.

Property         Value          Notes
Quick            Yes            Column selection must include a numeric column.
Calculation

Input            Numeric        A numeric literal or a numeric grid column.
                 Value

Output           One Column The positive portion of the input value.

Use Cases

 Use when you need convert numeric quantities to a positive value.




                                                                                  121
Data Access Studio Manual




Average
Overview

This calculation returns numeric average of multiple input values.

Property        Value          Notes
Quick           Yes            Column selection must include a numeric column.
Calculation

Input           Multiple       Numeric literals and/or numeric grid columns.
                Numeric
                Values

Output          One Column The numeric average of all input values.

 Use Cases

 Use when you need calculate the average over a set of numeric values.




122
                                                                        User's Guide




Difference
Overview

This calculation returns the numeric difference between input values.

Property        Value          Notes
Quick           Yes            Column selection must include a numeric column.
Calculation

Input           Multiple       Numeric literals and/or numeric grid columns.
                Numeric
                Values

Output          One Column Subtracts last input value from the sum of all
                           preceding input values.

 Use Cases

 Use when you need to get the difference between numeric quantities. You
 can then filter on this column to narrow your data by difference criteria.




                                                                                123
Data Access Studio Manual




Division
Overview

This calculation returns result of dividing two input values.

Property         Value          Notes
Quick            Yes            Only if two columns are selected and both
Calculation                     columns are numeric columns.

Input            Numeric        A numeric literal or a numeric grid column.
                 Value1
                 Numeric        A numeric literal or a numeric grid column.
                 Value2

Output           One Column The result of dividing Numeric Value1 by Numeric
                            Value 2.

Use Cases

 Use when you need to get the ratio between two numeric quantities.
 Use when you need to get the value of a percentage quantity (i.e. divide by
 100)
 Use when you need to factor a quantity by a constant value (i.e. divide by x)




124
                                                                            User's Guide




Max
Overview

This calculation returns maximum value from the set of multiple input values.

Property        Value          Notes
Quick           Yes            Column selection must include a numeric column.
Calculation

Input           Multiple       Numeric literals and/or numeric grid columns.
                Numeric
                Values

Output          One Column The numeric maximum of all input values.

Use Cases

 Use when you need to get the maximum value from a set of numeric
 quantities.




                                                                                    125
Data Access Studio Manual




Min
Overview

This calculation returns minimum value from the set of multiple input values.

Property        Value          Notes
Quick           Yes            Column selection must include a numeric column.
Calculation

Input           Multiple       Numeric literals and/or numeric grid columns.
                Numeric
                Values

Output          One Column The numeric minimum of all input values.

 Use Cases

 Use when you need to get the minimum value from a set of numeric
 quantities.




126
                                                                              User's Guide




Multiplication
Overview

This calculation returns the result of multiplying all of the input values.

Property         Value           Notes
Quick            Yes             Column selection must include a numeric column.
Calculation

Input            Multiple        Numeric literals and/or numeric grid columns.
                 Numeric
                 Values

Output           One Column The result of multiplying all of the input values.

Use Cases

 Use when you need multiply multiple numeric quantities.
 Use when you need to scale a quantity by a constant value (i.e. multiply by x)




                                                                                      127
Data Access Studio Manual




Standard Deviation
Overview

This calculation returns the standard deviation of the input values.

Property         Value          Notes
Quick            Yes            Column selection must include a numeric column.
Calculation

Input            Multiple       Numeric literals and/or numeric grid columns.
                 Numeric
                 Values

Output           One Column The standard deviation of the input values.

Use Cases

 Use when you need to calculate the standard deviation over a set of numeric
 quantities.




128
                                                                         User's Guide




Sum
Overview

This calculation returns the sum of the input values.

Property         Value          Notes
Quick            Yes            Column selection must include a numeric column.
Calculation

Input            Multiple       Numeric literals and/or numeric grid columns.
                 Numeric
                 Values

Output           One Column The sum of the input values.

Use Cases

 Use when you need to get sum of a set of numeric quantities.
 Use when you need to offset a quantity by a constant value (i.e. add x)




                                                                                 129
Data Access Studio Manual




Uniform Buckets
Overview

This calculation allows you to organize your numeric data into "buckets". A bucket is
a group of numbers that fall into a specified range of values. The result is a histogram
of values that fall into each specified bucket.

Property        Value             Notes
Quick           No
Calculation

Options         Bucket size       The size of the bucket interval. E.g., if the size
                                  is 30, then each bucket represents a range of
                                  30 numbers.
                Lower limit       The lower limit of the bucket range. If specified,
                (optional)        then all values less than or equal to this limit will
                                  be placed in the same "lower limit" bucket.
                Upper limit       The upper limit of the bucket range. If specified,
                (optional)        then all values greater than or equal to this limit
                                  will be placed in the same "upper limit" bucket.

Inputs          Value             Literal value or grid column. In general, you will
                                  supply a grid column with multiple numeric
                                  values that you want to organize.

Outputs         One Column        The result of organizing the input numeric
                                  values into bucket ranges.

Use Cases

 Use to create an aging report: i.e. organize all date differences by <30 days,
 31-60 days, 60-90 days, etc. You can group the Uniform Bucket column to
 further organize the numeric data.
 Use to create a histogram of data.

Example

Suppose you had a column that was the date difference in days. Suppose you need to
organize the date difference as follows:



130
                                                                            User's Guide


0-30 -> bucket 1

31-60 -> bucket 2

61-90 -> bucket 3

>90 -> bucket 4

Then set up a uniform bucket with the bucket size = 30, lower limit = 0, and upper
limit = 90.

Group by the Uniform bucket column.

Add a count to the Uniform bucket group to see how many items fell into each bucket.




                                                                                     131
Data Access Studio Manual



System


System
This category has the following calculations:

 Group Row Index
 Group Row Summary
 Row Index
 Type Converter




132
                                                                                User's Guide




Group Row Index
Overview

This calculation returns index of the row relative to its grouping. E.g. the first row in a
group has a group row index of 1, the second row has a group row index of 2, etc.

Property         Value          Notes
Quick            Yes            Grid should have at least one grouped column.
Calculation

Output           One Column The index of the row relative to its grouping.

Use Cases

 Use when you need to get the first element of each group. Sort ascending
 on the last group and filter this column with a '1'.
 Use when you need to get the last element of each group. Sort descending
 on the last group and filter this column with a '1'.




                                                                                          133
Data Access Studio Manual




Group Row Summary
Overview

This calculation returns summary value of the input column.

Property        Value         Notes
Quick           Yes           Grid must be grouped. Input column must have a
Calculation                   defined summary.

Input           Grid Column A grid column with a summary (See "Summaries")

Output          One Column The value of the input column's summary.

Use Cases

 Use when you need to filter by the summary value of grid column.
 Use when you need to filter by groups that have a certain number of
 elements. Create a count group summary on a column. Create this
 calculation over that column. Filter this calculation column with a number.
 You will see only the groups that have that number.
 Use when you need to filter by groups that have a certain sum. Create a
 count group summary on a column. Create this calculation over that
 column. Filter this calculation column with a number. You will see only the
 groups that have sum that match that number.




134
                                                                                User's Guide




Row Index
Overview

This calculation returns index of the row in the grid. E.g. the first row in the grid has a
row index of 1, the second row has a row index of 2, etc.

Property         Value           Notes
Quick            Yes
Calculation

Output           One Column The index of the row in the grid.

Use Cases

 Use when you need to filter by rows in the grid. If you want to filter your grid
 by rows 100 thru 200, create a Row Index calculation column. Filter this
 column with: >=100:<=200.




                                                                                          135
Data Access Studio Manual




Type Converter
Overview

This calculation returns the result of converting the input value to the output data type.
The output data type is this calculations data type. See "Change an existing
calculation".

Property         Value          Notes
Quick            Yes
Calculation

Input            Value          A literal or a grid column.

Output           One Column The result of converting the input value to the
                            output data type.

Use Cases

 Use when you need to convert the type of one column to another data type.
 For instance, you can convert a numeric column to a string column.




136
                                                User's Guide



Text


Text
This category has the following calculations:

 Concatenation
 Pad
 Split
 String Length
 Substring
 Trim




                                                        137
Data Access Studio Manual




Concatenation
Overview

This calculation returns the concatenation of the input values.

Property         Value          Notes
Quick            Yes
Calculation

Input            Multiple       Literals and/or Grid columns.
                 Values

Output           One Column The string result of concatenating all input values.

Use Cases

 Use when you need to create a column that is a concatenation of other
 columns. You can then group by and filter over this calculation column.




138
                                                                               User's Guide




Pad
Overview

This calculation allows you to pad an input value on the left or right with given
characters.

Property         Value           Notes
Quick            Yes
Calculation

Options          Left or right   Side of the string to pad - left or right.
                 side
                 Strip blanks    Option to strip blanks before doing the pad.
                 Padding         Character to pad.
                 character
                 Total width     Total width of resulting string.

Inputs           Value           Literal value or grid column.

Outputs          One Column The result of padding the input value per the given
                            option.

Use Cases

 Use when you need to pad a column value with a specific character.
 Use to create correctly right-padded strings for fields such as
 EnterpriseOne's MCU field.




                                                                                       139
Data Access Studio Manual




Split
Overview

This calculation allows you to split an input value into multiple output columns.

Property        Value          Notes
Quick           No
Calculation

Options         Delimiters     One or more delimiting characters. Each
                               character in this list will result in a splitting of the
                               string by that character.

Inputs          Value          Literal value or grid column.

Outputs         Multiple       The result of splitting the input value by the given
                Columns        delimiting characters. Each output column is
                               specified by the 1-based index of the split. For
                               instance given the string A_B_C, if the delimiting
                               character is '_', then the 1 index of the split
                               represents string "A", the 2 index represents string
                               "B", etc.

Use Cases

 Use when you need to split a value into component parts. E.g. splitting a
 date string into day, month, and year parts.
 Split MCU.OBJ.SUB account string into MCU, OBJ, and SUB components.




140
                                                                             User's Guide




String Compare
Overview

This calculation compares two string values.

Property        Value          Notes
Quick           Yes            Must have two grid columns selected
Calculation

Options         Strip blanks Option to strip blanks before doing length
                             calculation.

Inputs          First string   Literal value or grid column.
                Second         Literal value or grid column.
                string

Outputs         0, 1, or -1    0 if the two strings are equal. 1 if the first string is
                               greater than the second string. -1 if the second
                               string is greater than the first string.

Use Cases

 Use when you need to see if two grid columns are different from each other.
 Use when you need to see if a grid column is different from a literal value.




                                                                                      141
Data Access Studio Manual




String Length
Overview

This calculation returns the string length of the input value.

Property         Value           Notes
Quick            Yes
Calculation

Options          Strip blanks Option to strip blanks before doing length
                              calculation.

Inputs           Value           Literal value or grid column.

Outputs          One Column The string length of the input value.

Use Cases

 Use when you need to get the string length of the input value. Then filter on
 this field to filter by string length.




142
                                                                          User's Guide




Substring
Overview

This calculation allows extract a portion of an input value string.

Property         Value          Notes
Quick            No
Calculation

Options          Start index    The 1-based index of the start of the substring. 1
                                = the first character of the string. 2 = the second
                                character of the string, etc.
                 Length         The number of characters in the substring.

Inputs           Value          Literal value or grid column.

Outputs          One Column The result of extracting the specified portion of the
                            input value string.




                                                                                      143
Data Access Studio Manual




Trim
Overview

This calculation allows you to trim given characters from an input value.

Property        Value          Notes
Quick           Yes            By default, trims input columns of white space
Calculation

Options         Trim           The set of characters to remove from the input
                delimiters     value.
                Side           Side of the string to trim - left, right, or both

Inputs          Value          Literal value or grid column.

Outputs         One Column The result of removing the given characters from
                           the input value.

Use Cases

 Use when you need to trim a value of certain characters. E.g. trim a value of
 white space.




144
                                                                                User's Guide



Printing and Exporting


Header and Footer
Data Access Studio provides the ability to specify a report header and footer to any
grid. Once defined, the header information such as page numbers, 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 Layout | Header/Footer...




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, there are six special tags (which are enclosed
by brackets), that allow you to add dynamic information such as:

 Page number
 Page number of Pages
 Date
 Time

                                                                                          145
Data Access Studio Manual


 User
 Layout name.

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.




146
                                                                              User's Guide




Printing Preferences
Data Access Studio provides the ability to specify printing preferences for each layout.
Printing preferences include:

 Margins
 Portrait/Landscape
 Print filter values

When you press save on the layout, the printing preferences will be saved with your
layout.

To define printing preferences:

   1. Click on Layout | Printing preferences...
   2. Edit the Margins, landscape, and show filter options
   3. Click OK to apply your settings.

When you print or export, DAS will use these printing preferences to format your
output.




                                                                                       147
Data Access Studio Manual




Print Areas
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.

Data Access Studio allows you to do the following operations with Print Areas:

 Define Print Area as the currently selected set of columns
 Clear the Print Area
 Show the Print Area

Define a Print Area

To define a Print Area:

   1. 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.
   2. From the main menu, select: Layout -> Print Area -> Set To Selected
      Columns

When you print this layout, Data Access Studio will print only the columns defined in
the Print Area. Note that the Data Access Studio saves the Print Area with layout.

Clear the Print Area

To clear the Print Area:

   1. From the main menu, select: Layout -> 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, do the following:

   1. From the main menu, select: Layout -> Print Area -> Show




148
                                                                               User's Guide




Printing
Data Access Studio provides the ability to print any grid. To print a grid, load data
into the grid and click the Print button   on the toolbar:




Column Print Options

Data Access Studio allows you to select which columns you want to print:

 Print all visible columns
 Print only selected columns
 Print only columns defined as the Print Area

Print all visible columns is the default behavior. If you do not have any columns
selected, or if your layout does not have a Print Area defined (see Print Areas), then the
    Print command will simply print what the grid shows. If you have columns selected
or if you have a print area defined for your layout, when you select the    Print
command, Data Access Studio will prompt you to select which of the above three
options you want.

Print Preview

Once you press    Print button (and optionally what columns to print), Data Access
Studio prompts you with a Print Preview screen:




                                                                                        149
Data Access Studio Manual




The print preview screen provides the following options:




 Icon             Action          Description

                                  Prompts you with a standard print dialog. You
                  Print           may select a different printer, all pages or
                                  page range, etc.

                                  Prints directly to default printer without
                  Print Direct
                                  providing the intermediate print dialog box.

                                  Prompts you with a standard page setup
                                  dialog. You may change margins,
                  Page Setup
                                  portrait/landscape, paper size, and paper
                                  source.

                                  Prompts user with a header/footer dialog box.
                  Header and
                                  You may add page numbers, dates, time, and
                  Footer
                                  text to the header and footer of the printout.



150
                                                     User's Guide



Magnifier     When toggled on, this button allows you to
Toggle        zoom in and out by repeated clicking.

              Each time you click this button, the preview
Zoom In
              zooms into (magnifies) the current region.

              Each time you click this button, the preview
Zoom Out
              zooms out of the current region.




Select        Allows user to select the zoom factor from the
Zoom          dropdown list




Go to First
              Positions Preview to first page.
Page

Go to
Previous      Positions Preview to the previous page.
Page

Go to Next
              Positions Preview to the previous next page.
Page

Go to Last
              Positions Preview to last page.
Page



Multiple      Allows user to select how pages to preview at
Pages         a time.



              Allows you to select a custom background
Background
              color.

Close         Closes the preview window and returns to
Preview       Data Access Studio.




                                                               151
Data Access Studio Manual




152
                                                                               User's Guide




Exporting Data
Data Access Studio gives you one-click power to transfer grid data to the following
outputs:

       Windows Clipboard
       Tab-Separated-Value file
       Excel
       PDF (Adobe Portable Document Format)
       HTML
       XML
       TIFF (multi-page picture scan format)

The exporting functions are on the Edit menu and the Export menus. The comparison
grid shows advantages of each different type of export:

Export type Advantages

PDF                   Standards-based output.
                      Fits report to page automatically--no resizing or fitting
                       necessary.
                      Output is fast, small
                      Email or post on web
                      Output is text-searchable

HTML                  Standards-based output
                      Output is fast, small
                      Email or post on web
                      Output is text-searchable

Excel                 Standards-based output
                      Output is fast, small
                      Email or post on web
                      Output is text-searchable
                      Can do additional spreadsheet operations to format or calculate
                       data

Tab-                  Standards-based output.
Separated-            Output is fast, small
Value                 Email or post on web
                      Output is text-searchable


                                                                                       153
Data Access Studio Manual


                      Can import into external databases

XML                   Standards-based output.
                      Output is fast, small
                      Email or post on web
                      Output is text-searchable
                      Can import into external databases
                      Can import into external report writers such as XMLp

TIFF                  Standards-based output.
                      Document is an image that is hard to tamper.
                      Email or post on web



Edit Menu
Copy data to the Windows Clipboard

To copy data from the grid to the Windows clipboard:

   1. Select the rows that you want with the mouse. The grid allows multiple
      selection of rows using the Windows standard CTRL and SHIFT key selection
   2. Press Ctrl+C or select Edit | Copy from the main menu
   3. You may now paste the data from the Windows clipboard to a third party
      application by pressing Ctrl+V (Paste) into the target application

Copy selected data directly into Excel

To copy data from the grid to Excel file:

   1. Select the rows that you want with the mouse. The grid allows multiple
      selection of rows using the Windows standard CTRL and SHIFT key selection.
   2. Press Ctrl+E (or from the file menu Edit | Copy to Excel)

       Data Access Studio will open a new instance of Excel and copy the
       selected rows into a new worksheet.

Export Menu
The export menu on the main menu bar is the central menu for export operations.
From here you have three options:

      Export all grid data
      Export selected grid data


154
                                                                                 User's Guide


      Set Export options

Export all grid data to file

Export all grid data saves you the step of having to click Ctrl-A first to select all the
data before exporting it. From here you may export to

      Tab-Separated-Value file
      Excel
      PDF (Adobe Portable Document Format)
      HTML
      TIFF (multi-page picture scan format)
      XML

Simply select the option and DAS will export to the default path and open the exported
file when finished. For more information on how to set the default path and other
export options, see "Export settings" below.

Export selected grid data to file

Export selected grid data gives you the flexibility to select specific columns or rows
that you want to export. From here you may export to

      Tab-Separated-Value file
      Excel
      PDF (Adobe Portable Document Format)
      HTML
      XML

Simply select the option and DAS will export to the default path and open the exported
file when finished. For more information on how to set the default path and other
export options, see "Export settings" below.

Export settings

This menu item lets you set the following export options:

      Export Directory - Defines the root directory where to export files. The default
       value is the user's temporary directory.
      Open file after export - If checked, DAS will open the exported file after the
       export. The default value is checked.
      Automatically name and export to directory - If checked will automatically
       name the export to <default directory>\<layout name>. This saves you the
       tedium opening the Save To File Dialog and specifying a file name each time.
       The default value is checked.


                                                                                            155
Data Access Studio Manual




Linking to Excel® Data
Data Access Studio gives you the ability to link your data to an embedded Excel®
spreadsheet. The embedded spreadsheet is stored with your layout -- so you never have
to worry about your data and spreadsheet getting "out of sync". Once enabled, you may
use all of Excel's features to format and calculate for your report. All your work gets
saved as one unit in the Data Access Studio layout. Furthermore, you may publish your
layout, just as you would normally, so that others may run your Excel®-related report.

Enabling the Excel® link
To enable the Excel® link:

   1. Open a OneWorld Table or Business View
   2. On the Layout menu, click: Layout | Embed Excel® | Enable

Once enabled, you will see a new "Excel®" tab on your layout grid. In this tab is a fully
operational instance of Excel®. You may add sheets, formulas, charts, pivot tables, etc.
In short you may do anything in this tab that Excel® lets you do.

Mapping to Excel®
There are two type of maps:

   1. From Excel® to DAS filter
   2. From DAS to Excel®

The first type of map lets you use Excel® cell values as filters for your query. This way
you can create a pleasing presentation of filters on your Excel® spreadsheet. The user
enters the filter values on the excel spreadsheet (e.g. Fiscal Year 1) and DAS maps this
entry to the data filter. The result is a polished and controlled way to query for data.

The second type of map takes data from the query and maps it back to Excel®. Data
Access Studio lets you map column values and group summary values to Excel®
columns. A column map simply maps all column values from Data Access Studio to
Excel®. The group map maps the group summaries from Data Access Studio to Excel®.
The advantage of the group map is that it lets Data Access Studio summarize the data
first, then Data Access Studio has less to map to Excel®. This can be a vital feature
because Excel® has a 65536 row limit.

By default, Data Access Studio will map data to a worksheet named DASLink. If the
DASLink worksheet does not exist, Data Access Studio will automatically create it. The


156
                                                                           User's Guide


DASLink worksheet will always get cleared and populated as Data Access Studio
refreshes data. You should not place any formula or chart on the DASLink worksheet.
Place all formulas, charts, and persistent objects on a worksheet other than DASLink.

Data Access Studio provides three ways to define the Excel® mapping:

   1. Quick map column
   2. Quick map group summaries
   3. Mapping editor

Quick map column

This operation lets you create a link between Data Access Studio columns and Excel®
columns. To map column values:

   1. Select one or more columns
   2. Right-click the column header of one of the selected columns and select:
      Embed Excel® | Quick Map Column

Data Access Studio then does the following:

   1. Creates a new worksheet in Excel® called DASLink (if it does not already
      exist)
   2. Creates a mapping between the columns you selected and the next
      available columns in DASLink.

Example:

Suppose you opened the F0902 and select the Account ID and Century columns. When
you perform the Quick Column Map, Data Access Studio creates a new worksheet called
DASLink. Data Access Studio then maps Account ID to column A and Century to
column B (both with the captions). Once the map is established, every time you load
records into the Data Access Studio grid, Data Access Studio will synchronize that data
with the corresponding Excel® columns.

Note: The Excel mapping is refreshed anytime you press the Find button or
when you select Layout | Embed Excel | Refresh. The Refresh operation clears
the contents of DASLink and re-maps the grid information into the excel
worksheet. Changing a filter value in the Data Access Studio grid will not refresh
the mapping.

Quick map group summaries

This operation lets you create a link Data Access Studio summaries and Excel®



                                                                                    157
Data Access Studio Manual


columns. To map summaries:

   1. Group one or more columns
   2. Select one or more columns that you want to summarize into Excel®.
   3. Right-click the column header of one of the selected columns and select:
      Embed Excel® | Quick Map Group

Data Access Studio then does the following:

   1. Creates a new worksheet in Excel® called DASLink (if it does not already
      exist)
   2. Automatically adds default summaries to the selected columns (if the
      summaries do not already exist)
   3. Creates a map between the group values of the highest expanded group.
   4. Creates map between the columns summaries of the highest expanded
      group and the next available columns in Data Access Studio Link.

Example:

Suppose you opened the F0902 and group by the Account ID and Century columns.
Suppose you then select the "Net Posting 1" column. Right-click and select: Embed
Excel® | Quick Map Group. Data Access Studio does the following:

   1.   Creates a new worksheet called DASLink.
   2.   Adds a sum summary to Net Posting 1 on the "Data" tab.
   3.   Maps the group values for Account ID and Century to column A.
   4.   Maps the sum summary of NetPosting 1 to column B.

Mapping editor

The mapping editor gives you the fine-grained way to define column and group
mappings. In the editor you may add, edit, and delete mappings in your layout. To view
the mapping editor:

   1. Select Layout | Embed Excel® | Edit Mapping...

The editor is organized as follows:

 The left-hand pane shows the map headers
 The right-hand pane shows the map details

As you select map headers on the left, the right-hand pane will show the parameters that
need to be completed for that map (for parameter details please see “Editing the
Column Map”.



158
                                                                           User's Guide


Adding a map

To add a new map:

   1. Click on the blank row under "Type of Mapping"
   2. Use the visual assist to select a valid map type (e.g. Map column to
      Excel®)

Once you add the map, the right-hand pane will show the details for the map you added.

Editing a map

To Edit a map:

   1. Click on the row for the map in the left-hand pane
   2. Modify the parameters for the map in the right-hand pane

Deleting maps

To delete maps:

   1. Select one to many rows on the left-hand pane
   2. Click "Delete" on the toolbar

Editing the Column Map
This section covers all the parameters for defining a column map. When you add or edit
a column map, the mapping editor will resemble the following:




                                                                                    159
Data Access Studio Manual




Here is the description of each parameter in the right-hand pane:

Parameter                                    Description
                                             If checked, the mapping will map the
                                             column caption and then the column
Map column caption
                                             data. If unchecked, the mapping will
                                             not map the caption to Excel®.
                                             If checked, the mapping will map the
                                             column's summary value to Excel®. If
Map column summary
                                             unchecked, the mapping will not map
                                             the column summary value.
                                             If checked, the mapping will auto fit the
                                             column in Excel®. If unchecked, the
Auto fit column
                                             mapping will not auto fit the column in
                                             Excel®.
                                             The Grid Column name to be mapped
Input column                                 to Excel®. Must be a valid grid column
                                             name.
                                             The Excel® sheet to output the column
Excel® sheet
                                             values. DASLink by default.
                                             Excel® column (e.g. A) to map the
Excel® column
                                             column values.
                                             Excel® row to map the column values.
Excel® row
                                             1 by default.


160
                                                                           User's Guide




Editing the Group Map
This section covers all the parameters for defining a group map. When you add or edit a
group map, the mapping editor will resemble the following:




Here is the description of each parameter in the right-hand pane:

Parameter                                    Description
                                             If checked, the mapping will map the
                                             column caption and then the column
Map column caption
                                             data. If unchecked, the mapping will
                                             not map the caption to Excel®.
                                             If checked, the mapping will map the
                                             column's summary value to Excel®. If
Map column summary
                                             unchecked, the mapping will not map
                                             the column summary value.
Auto fit column                              If checked, the mapping will auto fit the


                                                                                    161
Data Access Studio Manual


                                           column in Excel®. If unchecked, the
                                           mapping will not auto fit the column in
                                           Excel®.
                                           Map at or up to this grouping level.
Group level
                                           (See Up to level option)
                                           Either:
                                             Equal to group level
                                             Up to and including group level

                                           Equal to group level means that only
Up to level option                         summaries at the group level will be
                                           mapped across.

                                           Up to and including group level means that
                                           all summaries up to and including the group
                                           level will be mapped across.
                                           Either:
                                             Category and value
                                             Group value only

                                           Category and value is the way the group
                                           text is shown in the Data Access Studio
Group text option
                                           grid. The group text is shown by showing
                                           the field name first and then the group
                                           value.

                                           Group value only just maps the group
                                           value.
                                           The grid columns whose summaries will
Multiple Input                             be mapped to excel. You may specify
                                           one to many columns.
                                           The Excel® sheet to output the column
Excel® sheet
                                           values. DASLink by default.
                                           Excel® column (e.g. A) to map the
Excel® column
                                           column values.
                                           Excel® row to map the column values.
Excel® row
                                           1 by default.


Linking to Excel® Data
Data Access Studio gives you the ability to link your data to an embedded Excel®
spreadsheet. The embedded spreadsheet is stored with your layout -- so you never have
to worry about your data and spreadsheet getting "out of sync". Once enabled, you may

162
                                                                             User's Guide


use all of Excel's features to format and calculate for your report. All your work gets
saved as one unit in the Data Access Studio layout. Furthermore, you may publish your
layout, just as you would normally, so that others may run your Excel®-related report.

Enabling the Excel® link
To enable the Excel® link:

   1. Open a OneWorld Table or Business View
   2. On the Layout menu, click: Layout | Embed Excel® | Enable

Once enabled, you will see a new "Excel®" tab on your layout grid. In this tab is a fully
operational instance of Excel®. You may add sheets, formulas, charts, pivot tables, etc.
In short you may do anything in this tab that Excel® lets you do.

Mapping to Excel®
There are two type of maps:

   1. From Excel® to DAS filter
   2. From DAS to Excel®

The first type of map lets you use Excel® cell values as filters for your query. This way
you can create a pleasing presentation of filters on your Excel® spreadsheet. The user
enters the filter values on the excel spreadsheet (e.g. Fiscal Year 1) and DAS maps this
entry to the data filter. The result is a polished and controlled way to query for data.

The second type of map takes data from the query and maps it back to Excel®. Data
Access Studio lets you map column values and group summary values to Excel®
columns. A column map simply maps all column values from Data Access Studio to
Excel®. The group map maps the group summaries from Data Access Studio to Excel®.
The advantage of the group map is that it lets Data Access Studio summarize the data
first, then Data Access Studio has less to map to Excel®. This can be a vital feature
because Excel® has a 65536 row limit.

By default, Data Access Studio will map data to a worksheet named DASLink. If the
DASLink worksheet does not exist, Data Access Studio will automatically create it. The
DASLink worksheet will always get cleared and populated as Data Access Studio
refreshes data. You should not place any formula or chart on the DASLink worksheet.
Place all formulas, charts, and persistent objects on a worksheet other than DASLink.

Data Access Studio provides three ways to define the Excel® mapping:

   1. Quick map column


                                                                                      163
Data Access Studio Manual


   2. Quick map group summaries
   3. Mapping editor

Quick map column

This operation lets you create a link between Data Access Studio columns and Excel®
columns. To map column values:

   1. Select one or more columns
   2. Right-click the column header of one of the selected columns and select:
      Embed Excel® | Quick Map Column

Data Access Studio then does the following:

   1. Creates a new worksheet in Excel® called DASLink (if it does not already
      exist)
   2. Creates a mapping between the columns you selected and the next
      available columns in DASLink.

Example:

Suppose you opened the F0902 and select the Account ID and Century columns. When
you perform the Quick Column Map, Data Access Studio creates a new worksheet called
DASLink. Data Access Studio then maps Account ID to column A and Century to
column B (both with the captions). Once the map is established, every time you load
records into the Data Access Studio grid, Data Access Studio will synchronize that data
with the corresponding Excel® columns.

Note: The Excel mapping is refreshed anytime you press the Find button or
when you select Layout | Embed Excel | Refresh. The Refresh operation clears
the contents of DASLink and re-maps the grid information into the excel
worksheet. Changing a filter value in the Data Access Studio grid will not refresh
the mapping.

Quick map group summaries

This operation lets you create a link Data Access Studio summaries and Excel®
columns. To map summaries:

   1. Group one or more columns
   2. Select one or more columns that you want to summarize into Excel®.
   3. Right-click the column header of one of the selected columns and select:
      "Embed Excel® | Quick Map Group and Summaries" or "Embed Excel® |
      Quick Map Summaries"



164
                                                                            User's Guide


Data Access Studio then does the following:

   1. Creates a new worksheet in Excel® called DASLink (if it does not already
      exist)
   2. Automatically adds default summaries to the selected columns (if the
      summaries do not already exist)
   3. Creates a map between the group values of the highest expanded group.
   4. Creates map between the columns summaries of the highest expanded
      group and the next available columns in Data Access Studio Link.

Example:

Suppose you opened the F0902 and group by the Account ID and Century columns.
Suppose you then select the "Net Posting 1" column. Right-click and select: Embed
Excel® | Quick Map Group. Data Access Studio does the following:

   1.   Creates a new worksheet called DASLink.
   2.   Adds a sum summary to Net Posting 1 on the "Data" tab.
   3.   Maps the group values for Account ID and Century to column A.
   4.   Maps the sum summary of NetPosting 1 to column B.

Mapping editor

The mapping editor gives you the fine-grained way to define column and group
mappings. In the editor you may add, edit, and delete mappings in your layout. To view
the mapping editor:

   1. Select Layout | Embed Excel® | Edit Mapping...

The editor is organized as follows:

 The left-hand pane shows the map headers
 The right-hand pane shows the map details

As you select map headers on the left, the right-hand pane will show the parameters that
need to be completed for that map (for parameter details please see “Editing the
Column Map”.

Adding a map

To add a new map:

   1. Click on the blank row under "Type of Mapping"
   2. Use the visual assist to select a valid map type (e.g. Map column to
      Excel®)


                                                                                     165
Data Access Studio Manual


Once you add the map, the right-hand pane will show the details for the map you added.

Editing a map

To Edit a map:

   1. Click on the row for the map in the left-hand pane
   2. Modify the parameters for the map in the right-hand pane

Deleting maps

To delete maps:

   1. Select one to many rows on the left-hand pane
   2. Click "Delete" on the toolbar

Editing the Filter Map
This section covers all the parameters for defining a filter map. The filter map will take
static Excel values and use them in the data filter when the user presses the Find button
   . When you add or edit a filter map, the mapping editor will resemble the following:




The "Filter Map Option" allows you to specify which of the following filters you want to
define:

166
                                                                               User's Guide


 Single Cell
 List of Values
 Range

Single Cell is simply one excel value that you want to use as your filter (e.g. LT = 'AA'.)
List of values is a set of excel cells that you want to use for your filter (e.g. a list of
accounts.) Range is a begin and end value (e.g. a beginning and end date.)

The editor will show you appropriate inputs for each type of map you specify.

Single Cell Map

For the single cell map, all you need to do is specify the sheet, column and row. Then
specify the Grid Column that you want to filter against. Here is the description of each
parameter for the single cell map.

Parameter                                     Description
                                              The Excel® sheet that has the filter
Source Excel® sheet
                                              value. "Sheet1" by default.
                                              The Excel® column (e.g. A) that has the
Source Excel® column
                                              filter value.
                                              The Excel® row that has the filter
Source Excel® row
                                              value. 1 by default.
                                              The Grid Column to filter against. Must
Output column
                                              be a valid grid column name.

List of Values Map

For the list of values map, you need to specify the sheet, column and row range for the
list of values. Then specify the Grid Column that you want to filter against. Here is the
description of each parameter for the single cell map.




                                                                                        167
Data Access Studio Manual




Parameter                                   Description
                                            The Excel® sheet that has the filter
Source Excel® sheet
                                            value. "Sheet1" by default.
                                            The Excel® column (e.g. A) that has the
Source Excel® column
                                            filter values.
                                            The row that begins the list of values. 1
Beginning row
                                            by default.
Ending row                                  The row that ends the list of values.
                                            The Grid Column to filter against. Must
Output column
                                            be a valid grid column name.

Example

Suppose you had 10 accounts that you want to query against the F0902. Enter the 10
accounts in column A in sheets 1 so that A1 has account 1, A2 has account 2 and so on.
In the mapping editor specify Sheet = Sheet1, Column = A, Beginning Row = 1, Ending
Row = 10, and Output = AID. Now when you click the find button, DAS will populate
the AID filter with the accounts that you list in A1 through A10. You can change the
accounts as you wish or save them with your layout.



168
                                                                              User's Guide


Range Map

For the range map, you need to specify two cells: the cell that holds the begin value and
the cell that holds the end value. Then specify the Grid Column that you want to filter
against. Here is the description of each parameter for the single cell map.




Parameter                                     Description
                                              Select from:
                                                Between start and end. Also include
                                                values that match the start and end.
                                                Between start and end excluding
                                                both. Exclude values that match start
Range Type
                                                and end.
                                                Between start and end excluding
                                                end. Include start; exclude end.
                                                Between start and end excluding
                                                start.
                                              The sheet that has the filter values.
Source Excel® sheet
                                              "Sheet1" by default.
                                              The column (e.g. A) that has the
Beginning column
                                              beginning range value.


                                                                                       169
Data Access Studio Manual


                                         The row that has the beginning range
Beginning row
                                         value.
                                         The column (e.g. A) that has the ending
Ending column
                                         range value.
                                         The row that has the ending range
Ending row
                                         value.
                                         The Grid Column to filter against. Must
Output column
                                         be a valid grid column name.

Performance considerations
When mapping columns to Excel®, you can increase performance by:

 Mapping summaries instead of column values for large tables. Excel® can only
 handle 65536 column values. If you have more than this, try to summarize the
 columns first and then map the column summary.




170
                                                                               User's Guide




Publishing Layouts
You may share your layout with other users by publishing it. Once a layout is published, it is
called a report. Publishing consists of :

   1.   Specifying a name for your published report
   2.   Specifying which users or groups of user may see the layout
   3.   Specifying what permissions users will have with your report
   4.   Publishing the report

Once a layout is published as a report, all subscribed users will be able to run the report. A
published report is available to users in all available EnterpriseOne environments.

To publish a layout:

   1. Select a layout that you would like to publish.
   2. Select File | Publish.

        Data Access Studio will present to publish layout form:




        From this form, you will specify how you want your layout to be published to
        other users.

   3. In the "To report:" edit box, enter the name for your published report. This field

                                                                                        171
Data Access Studio Manual


      defaults to the name of your layout. In general, you will usually use the layout
      name as your report name.

      You may press the visual assist button on this edit box to view other layouts that
      were published for the current table or business view.

      As you type in a name in the edit box, Data Access Studio will query the
      published records to see if your report name has already been published. If it has
      been published before:

        "Previously published:" indicator on this form will display "Yes".
        The grid showing the subscribed users is populated with the user-publishing
        information

      Just like layouts, the published name is unique for its "Based on" table or view.

   4. The next step is specifying which users will see your published report. To make
      this process less redundant, Data Access studio gives you three options for
      specifying a set of users (in order of decreasing granularity):

          1. OneWorld user name
          2. OneWorld group name
          3. All Data Access Studio users (DASUSERS)

      If your subscriber definitions overlap, the specification with the highest
      granularity wins. For instance if you published to DASUSERS and to a specific
      User Name, the permissions of the specific User Name win out because it is more
      granular than all users.

      To select the subscribers, click on a cell in the "User ID" columns, and click the
      visual assist:




172
                                                                    User's Guide




   Select one of the available lines. In this example we will choose DASUSERS.
   DASUSERS is a special group which means publisher to all Data Access Studio
   Users.

5. For each subscriber, you may optionally define the permissions the user has with
   your report. To specify a permission for a subscriber, highlight the user name in
   the grid and select which permissions to enable or disable. In this example, we
   will disable the DASUSERS subscriber from altering the report's data selection:




                                                                            173
Data Access Studio Manual




   6. Click "Publish" to publish your layout as a report.
   7. To delete a subscriber (User ID), highlight the row(s) of User IDs on the left-hand
      grid and press <Ctrl-Delete> on your keyboard (or select Edit | Delete Row from
      the main menu.)
   8. Refer to Open Published Reports to see how to un-publish a report.




174
                                                   User's Guide



Advanced

Advanced Operations
This topic includes advanced operations such as:

 Snapshots
 OLE DB Data
 DAS Objects
 Data Views




                                                           175
Data Access Studio Manual



Snapshots


Snapshots
The Snapshot feature is a powerful means of storing and comparing data. A Snapshot
is data that you select to save for future use. The Snapshot data can be any set of rows,
columns, and tables. No programming is required to set up a Snapshot. All the setup
is visual: drag-and-drop and cut-and-paste. You may define as many Snapshots as
necessary for a particular application. Finally, the Snapshot system provides a means
of showing the difference between the Snapshot and any OneWorld® Environment.

The list of Snapshot applications includes the ability to:

 Validate environment data such as Data Dictionary, UDCs and Menus
 Save data prior to a data conversion
 Check the results of a data conversion
 Compare data in different environments
 Setup automated tests (such as Autopilot and WinRunner®)
 Check the results of automated tests

The following sections describe how to setup, use and maintain Snapshots in Data
Access Studio.




176
                                                                                User's Guide




Snapshot setup
Setting up the data in a Snapshot is done visually. The following example will step
through the required activities to setup a Snapshot:

Step 1 - Select and highlight data to Snapshot

In general, this step is where you open any table, show any columns in that table and
select any set of rows. In this example:

 We open the Business Unit Master Table (F0006).
 We hide all columns except the first five columns




(Please see previous sections which cover how to open a table, hide/show columns, and
filter rows in Data Access Studio)

Once you have fetched the rows into the grid, highlight the rows you want to snapshot.
In this example, we will highlight the first five rows and right-click the focused selection:




                                                                                         177
Data Access Studio Manual




Data Access Studio creates a new snapshot window and transfers the selected rows to the
snapshot:




You can continue to add additional rows from the Business Unit Master as you wish.
Note that the Snapshot will not allow duplicate rows to be copied. You may also add
rows from any number of tables to this same Snapshot. Please see "Maintaining


178
                                                                           User's Guide


Snapshot Data" for more information.

To save the Snapshot, press File | Save or press the Save icon  on the toolbar. Because
this is a new Snapshot, Data Access Studio will prompt you for the Snapshot name:




The maximum length of the Snapshot name is 300 characters.

This completes the example on how to setup a Snapshot.




                                                                                    179
Data Access Studio Manual




Maintaining Snapshots
Once you have created a snapshot, you may modify and maintain the Snapshot with the
following functions:

 Lock/Unlock
 Save As (copy)
 Restore
 Rename
 Delete Snapshot
 Add additional rows
 Delete rows
 Import/Export

Each of these functions is available on the main menu of the Snapshot. This section
describes each of these features in detail.

Lock/Unlock

Locking a Snapshot means marking the Snapshot so that it cannot be modified.
Unlocking a Snapshot allows the Snapshot to be modified again. You "Lock" a Snapshot
when you need the Snapshot to remain unchanged. For example, if you set up a snapshot
to be the initial conditions for a test case, you would lock the Snapshot so that it will not
be inadvertently modified later -- as modifying the Snapshot could break a test case that
depends on the Snapshot remaining unmodified.

To lock a Snapshot, selects: File | Lock on the main menu:

Once locked, the Snapshot cannot be modified. You may, however, unlock the Snapshot
to allow modifications.

To Unlock the Snapshot, select: File | Unlock:

Save As (Copy)

You may want to copy your Snapshot to a Snapshot of a different name. To do this,
select File | Save As... on the main menu.

Data Access Studio prompts you with the "Save As Snapshot" form:




180
                                                                               User's Guide




You enter the name of the Snapshot to "Save As" and click "OK" to accept. If you click
"Cancel" then the Snapshot will not get copied.

Restore

To restore the snapshot to its last saved state, click File | Restore on the main menu. All
non-saved changes will be lost. The Snapshot will re-open itself to its last saved state.

Rename

To rename a Snapshot, click File | Rename of the main menu.

You will be prompted with the following form:




To rename your snapshot, type in the new name of your Snapshot and press "OK". If you
press "Cancel", your Snapshot will not be renamed.

Delete

To delete your snapshot do the following. Select File | Delete on the main menu.

Data Access Studio prompts you with a delete confirmation dialog box prior to deleting
the Snapshot. Once you confirm the delete, Data Access Studio closes the Snapshot form
and deletes the Snapshot.

Import/Export

You may export your snapshot to an external file. You may also import an external
snapshot file into Data Access Studio. For a discussion of how to Import and Export, see
the section titled Import and Export DAS Objects.




                                                                                         181
Data Access Studio Manual




Maintaining Snapshot Data
You may modify the data in an unlocked Snapshot. Snapshots support the following
types of data modification:

 Add additional rows and tables
 Delete rows
 Delete tables

The following sections describe each of these operations in detail.

Add additional rows and tables

You may add any rows from any table to a snapshot. If the table exists in the snapshot,
the new rows will be added to the Snapshot table. If the table does not exist in the
Snapshot, Data Access Studio will create the table and then add the rows. You may
add rows to your Snapshot using the following methods:

Cut-and-Paste

In the Cut-and-Paste method, do the following steps:

   1. Select and highlight the desired rows from a table.
   2. Press Ctrl+C (or from the main menu: Edit | Copy) to copy the
      highlighted rows.
   3. Click on the snapshot window to activate it and press Ctrl+V (or Edit |
      Paste) to paste the rows into the snapshot.

Drag-and-Drop

To copy rows with the Drag-and-Drop method, do the following:

   1. Select and highlight the desired rows from a table.
   2. Left-click and hold the focused cell (the cell with the dots that surround
      it)
   3. Drag the focused cell to the snapshot region (you will see the cursor
      change to the copy drop cursor when you hover over the Snapshot
      window)
   4. Drop the rows into the Snapshot window by releasing the mouse button.

Using either method above, if the added rows conflict with the primary key of existing
rows, Data Access Studio will prompt you with the following dialog box that



182
                                                                             User's Guide


resembles the following:




This dialog box shows which rows are in conflict and asks you if you want to suppress
all error messages. If you press "Yes", then this dialog will not show subsequent
errors. If you press "No" then Data Access Studio will popup this dialog for each
unique key violation. If you press "Cancel" then the copy will be aborted. In no event
will the copy process overwrite existing data with duplicate incoming data.

Delete rows

You may delete any row from any table in your Snapshot. You may delete rows using
the following methods:

Cut

To cut rows from a Snapshot table, highlight the desired rows and press Ctrl-X (or
select Edit | Cut on the main menu.) This deletes the table rows and copies them into
the clipboard. You may re-paste these rows back into your table.

Delete

To delete rows from a Snapshot table, highlight the desired rows and press the Delete
key (or select Edit | Delete on the main menu.) The Snapshot application will prompt
you with a delete confirmation prior to the delete.

Delete tables

You may delete tables from your snapshot by closing the table. You may close the
table using the following methods:

 Click on the table and select File | Close from the main menu.
 Click on the "X" icon in the upper right-hand corner of the table.

Note that all of the above ways to modify the snapshot will not be saved until you
press "File | Save" on the main menu.




                                                                                        183
Data Access Studio Manual




Comparing Snapshot data to
environment data
One of the more powerful features of Snapshots is the data comparison tool. The data
comparison tool produces a report of all differences between the Snapshot data and the
data in a given Environment. This automatic differencing capability gives you the
power to find data inconsistencies quickly. No programming is required to use this
feature--all comparison setup is visual.

Running the comparison

To compare your snapshot to environment data, select File | Compare To .

You will be prompted with the Select Environment form:




Highlight an environment against which to compare the Snapshot. Press "OK" to start
the comparison.

Note if you have a lot of data in your Snapshot, the comparison may take some time.

Once the comparison is complete, Data Access Studio will present the differences
results:


184
                                                                               User's Guide




The results are organized as follows:

   1. The primary key of the table is marked and moved to the front of the
      column order. This lets you know how to identify the record in question
   2. A column named "Summary of Difference" is added after the primary
      key. This column states what the difference is between the snapshot
      and the environment data:

         "No matching record" means that this record exists in the Snapshot but does
         not exist in the Environment data.
         "Changed" means that the snapshot record exists in the Environment data,
         but one or more columns have different values between the two locations.
         The difference report only shows a column if at least one change was
         detected in that column. For each change, the column shows the Snapshot
         value on the left, the "=>" symbol, and the Environment data value on the
         right.

   3. The remaining data columns.

As with all Data Access Studio grids, you may export or prints the results of the grid.

If your snapshot does not have all primary keys of a table, then the compare utility will
not be able to compare the snapshot data to environment data. In this case, the
compare will pop up a message box that indicates it cannot compare the data because
the primary key columns are missing.




                                                                                          185
Data Access Studio Manual




Working with OLE DB data
Once you have opened either an ODBC connection or an OLE DB connection, Data
Access Studio presents you with a list of tables in that data source. The following form is
an example of a list of tables in a data source.




Depending on the data source provider, the Schema column may or may not have values
in it.

You may apply the filtering and grouping capabilities of Data Access Studio to organize
the table list information. You may save these layouts just like any other layout in Data
Access Studio.

You may open a table by highlighting the table name and clicking Open (pressing Alt-O
or double-clicking a row will also open the table). You may select multiple rows at a
time.

Working with an open table

Once you open a table, Data Access Studio presents the table much like a OneWorld
table with the following exceptions:


186
                                         User's Guide


No visual assist filters are available
Data summary views are not available
Index assistant is not available




                                                 187
Data Access Studio Manual




Import and export DAS objects
Data Access Studio allows you to import and export objects from your session. This
allows you to share your DAS objects with other DAS users. You may share the
following objects:

 Layouts
 Default settings
 Snapshots

To share an object:

   1.   Sign on to Data Access Studio
   2.   Export one or more objects to a DAS export file
   3.   Send file to another DAS user
   4.   Have DAS use import your DAS export file

Exporting DAS Objects

To export your user settings, select File | Export DAS Objects from the main menu.

You will be prompted with the "Export DAS Objects" form:




188
                                                                              User's Guide




This form shows all objects in Data Access Studio that can be exported. Each object is
group under a category -- e.g. Snapshots is a category that groups all the saved
snapshots for the signed on user.

Each node in the tree has a check box. If you check the box, then that node and
everything underneath is selected for export. This system allows you to export many
things at once or just one item it a time -- depending on your needs.

If you press the "Cancel" button, then this form will close and nothing will be exported.

Once you have checked the items that you want to export, press the "Export" button.
You will be prompted with the following dialog:




                                                                                       189
Data Access Studio Manual




Type in a suitable name for your export in the "File name" edit box and press "Save" to
export your items.

Import

Once you have exported items to a .das file, any Data Access Studio user can import
that file into their session. To import a .das file, select File | Import DAS Objects on
the main menu. You will be prompted by a file selection dialog:




190
                                                                             User's Guide




Search for the .das file that you want to import and press "Open".

Data Access Studio will import the items in the .das file into your Data Access Studio
session.




                                                                                         191
Data Access Studio Manual




Data Views
Data Access Studio provides additional information about Tables and Views, including:

 Table summary information
 Column information
 Index information
 Join information

To organize this information, Data Access Studio employs the concept of a view. A
"View" is simply a label that represents information about a given table or view. For
example, if you open the Accounts Payable Ledger - F0411 table in Data Access Studio
and presses Find, you will see something resembling the following:




The information that shows in the grid is the "Data" view. I.e. this information is the data
in the F0411. Data Access Studio indicates that this is the "Data" view by displaying the
word "Data" in the View list box:

If you click on the Views list box, you will see a list of different information that you may
view for the F0411:

The following screens show the view information when you select each of these items:

Summary


192
                                                                           User's Guide


The Summary view shows summary information about the object. In our example, the
summary information of the F0411 includes how many columns the tables has, how many
indices, etc...

Columns

The Columns view show the column information about the object:




Indices

The Indices view shows information about the objects indices. You may click the '+' next
to the index name to see the columns that comprise a particular index:




                                                                                    193
Data Access Studio Manual




194
Administrator's Guide
Administration
This section is shows how to maintain DAS from an administrative standpoint.




                                                                               195
Data Access Studio Manual




System Requirements
Data Access Studio requires JD Edwards™ OneWorld Xe™ or ERP 8™ to be
installed first. To obtain JD Edwards™ software visit http://www.jdedwards.com/

Data Access Studio is a client-side tool. It will work on any Windows® workstation
meeting the following requirements:

 OneWorld Xe™ or ERP 8™ installed on workstation
 Pentium III or better processor
 256 MB RAM (512 recommended)
 Windows 2000 or Windows XP (XP is recommended)




196
                                                                   Administrator's Guide




Installation
Installing Data Access Studio involves three steps:

   1. Installing the software to the Deployment Server
   2. Installing the software to a client machine
   3. Setting up the software license

For complete up-to-date install instructions for Data Access Studio on EnterpriseOne,
visit: http://www.dominionsoft.com/Install_instructions.htm




                                                                                        197
Data Access Studio Manual




Preferences for All Users
Data Access Studio lets you set certain options for all users:

   1. Sign in to Data Access Studio as user: DASADMIN
   2. From the File menu, select "Preferences for all users..."

This will show the preferences dialog. The following is a list of preferences you can
set:



Preference         Description
Case-sensitive Check this option if your EnterpriseOne database is set to be
queries        case-sensitive for queries. Uncheck this option if your
               EnterpriseOne database is set for case-insensitive queries.
Enable        Check this option if you want DAS to record all DB access
EnterpriseOne through DAS. Check the Auto-Purge if you want DAS to
Logging       automatically delete records older than the given number of
              days. If you do not check the Auto-Purge, then DAS will
              persist all audit records. For more information, see Audit
              Trial.
Default date       Set this to a .NET compatible date format string. This will be
format             the default date format for all DASUSERS. If a DAS user
                   has a JDE date format, then the JDE Date format will
                   override this global setting. Also, if the DAS user designs a
                   report with a custom date format, this will override this global
                   setting.

                   If this setting is blank, then DAS will first check the JDE date
                   format for the user. If this is blank, DAS will user the date format
                   for the client workstation as the default.

                   WARNING: If you set this value to a valid date format, you may
                   possibly break pre=existing queries that query for date in a
                   different format.

                   Example date formats:

                   yyyy-MM-dd = 2007-01-01




198
                                             Administrator's Guide


d/M/yy = 1/1/07

Google DateTimeFormatInfo for more date formatting options.




                                                              199
Data Access Studio Manual




Managing User Layouts
When you sign in as DASADMIN, you will be able to see and manage all the Layouts
and Reports in DAS. You will not necessarily be able to run these layouts and reports.
The purpose of this feature is to let the DASADMIN copy and delete layouts for any
user. This can be useful if a user leaves the company or if a user changes his/her E1
user name.

Copying DAS Layout(s) from one user to another

   1. Run Data Access Studio and sign in as user DASADMIN.
   2. Click File | Open | Layouts...
   3. Type the name of the user to copy from in the "Owner" filter.
   4. Click "Find"
   5. Multi-select the layouts for that user by clicking the rows in the grid and
      holding down the Ctrl key or Shift key.
   6. Click "Copy from user to user".
   7. In the resulting message box, enter the destination user and click OK.
   8. Click OK on the confirmation Message Box.

Deleting DAS Layout(s) for a user

   1. Run Data Access Studio and sign in as user DASADMIN.
   2. Click File | Open | Layouts...
   3. Type the name of the user to copy from in the "Owner" filter.
   4. Click "Find"
   5. Multi-select the layouts for that user by clicking the rows in the grid and
      holding down the Ctrl key or Shift key.
   6. Click "Delete".
   7. Click OK on the confirmation Message Box.




200
                                                                       Administrator's Guide




User Security
Data Access Studio extends the OneWorld security model by providing application-level
security. This section covers:

 How to restrict access to Data Access Studio functionality to given users
 How to restrict access to tables and view to given users

As a default, all Data Access Studio functionality is available to all users.

Accessing User Security

To access the User Security setup, you must sign on to Data Access Studio with the user named
"DASADMIN".

   1. Add a user named DASADMIN to OneWorld
   2. Do not share the DASADMIN password with anyone
   3. Launch Data Access Studio and sign in as DASADMIN

If you licensed Data Access Studio by number of users, then the DASADMIN user will not
consume an extra license. NOTE: The DASADMIN user also has access to all layouts and
reports. You can use the DASADMIN account to manage layouts and reports of E1 users such
as contractors who are no longer with your company.

Once you have signed in as DASADMIN, select File | Manage Security. Data Access Studio
will display the Work With Security form:




                                                                                        201
Data Access Studio Manual


This form is a header with two details. The header portion (users/groups) is the grid on the left.
The two details are the tabs on the right-hand side. Whenever you selected a row on the left, the
corresponding details for that row display on the right.

For maximum flexibility and minimum redundancy, Data Access studio gives you three options
for specifying a set of users (in order of decreasing granularity):

           1. OneWorld user name
           2. OneWorld group name
           3. All Data Access Studio users (DASUSERS)

If your security definitions overlap for a given user, the definition with the highest granularity
wins. For instance if you defined security to DASUSERS and to a specific User Name, the
permissions of the specific User Name win out because it is more granular than all users. The
following sections will exemplify this concept.

Restricting functionality to given users

Data Access Studio allows the DASADMIN to restrict the following items from users:

 Export DAS Objects
 Import DAS Objects
 Create Layouts
 Open Layouts
 Enable Sort at Database
 Use OLE DB
 Open OneWorld tables
 Open OneWorld table in non-OCM datasource
 Open Published Reports
 Publish Reports
 Delete Published Reports
 Copy Published Reports to Layout
 Use Snapshots
 Embed Excel

Each of these items appears on the "Permissions" tab for a given user.

Example:

Secure all Data Access Studio users from using Snapshots. Allow the user MARY to use
snapshots.

   1. With the Work With Security application open, click on the open cell under the
      "User ID" column. Click the visual assist:



202
                                                          Administrator's Guide




2. Select MARY and tab out of the "User ID" field. The detail portion (right-hand
   side) will show the details for MARY:




                                                                           203
Data Access Studio Manual




   3. Click on the cell beneath "MARY" in the "User ID" column. Select the visual
      assist and pick "DASUSERS". Tab out of the "User ID" column:




204
                                                                    Administrator's Guide




   4. Uncheck "Can use Snapshot"
   5. From the menu, select File | Save or press the           Save button on the toolbar.

       At this point, all users of Data Access Studio (DASUSERS group) will not see the
       Open Snapshot option on their toolbar. MARY, however, will be able to see this
       Open Snapshot option on her toolbar.

In this manner you can permit and restrict the above permissions for any set of users.

Restricting access to tables and view to given users

The second tab on the detail side of the form ("Filter Tables and Views") allows the
DASADMIN to secure certain tables and views from a set of users. Each displayed filter applies
only to the currently selected user (on the left-hand side of the form). The filter mechanism
works with the filter syntax of Data Access Studio (see Filtering Data).

"Filter Tables and Views" supports the concept of inheritance. Consider the relationship in this
diagram:




                                                                                         205
Data Access Studio Manual




What the diagram represents is:

 a User is a subset of a User Group
 a User Group is a subset of DASUSERS (all users).

Given this relationship, when you define filters for DASUSERS, then User Groups and Users
"inherit" these filters. This means that as you change the DASUSERS filters, you will not have
to go back an update each User Group and User -- as they will automatically be updated.
Likewise, if you define filters for a User Group, then all Users that belong to that group inherit
the filters.

You may enable or disable any inherited filter. This can allow you to provide a default set of
filters for a group, while giving you the flexibility to provide special access to a particular user.

Example:

In this example we will setup the following security:

 Disable access to V0101JB from all users
 Allow the ACCOUNTING group to only open product code 04
 Allow MARY (who is part of the accounting group) to open any table except product
 code 98


   1. Continuing our example from above, select DASUSERS on the header grid.
      Select the "Filter Tables and Views" tab. Select "OBNM" (Object Name) in the
      "Alias" column and "!V0101JB" in the "Filter Criteria" column:




206
                                                                 Administrator's Guide




   Recall that the ! operator means "not". (See Filtering Data.) All entries in the
   DASUSERS will always have the Inherited value = "No". This is because
   DASUSERS is the most generic group.

2. Add the ACCOUNTING group to the "User Id" column on the left by click the first
   cell in the bottom row (*):




   Notice that the ACCOUNTING group inherited the OBNM filter from
   DASUSERS.

3. For the ACCOUNTING group add a new filter:

    Click on the new row (bottom row) in the "Filter Tables and Views" grid.
    Select "SY" (Product Code) in the "Alias" column and "04" in the "Filter Criteria"
    column:




                                                                                      207
Data Access Studio Manual




       Notice that the new row shows that it is not inherited. This means that this row
       directly applies to this group.

   4. Click on MARY's row:




       Notice that MARY inherited the filters from DASUSERS and ACCOUNTING.
       This is because MARY belongs to the ACCOUNTING group and all users belong
       to the DASUSERS group.

   5. Uncheck the both Enable boxes in MARY's filter. This overrides the inherited
      filter and allows MARY to view all objects.
   6. To save the security settings press the Save button  on the toolbar.

At this point, every user except MARY will not be able to open V0101JB. All ACCOUNTING
users will only be able to see tables and business views whose Product Code begin with 04.
Even though MARY is a member of the ACCOUNTING group, we disabled the inherited
filters. Therefore, MARY's security settings override the group's security settings.



208
                                                                         Administrator's Guide


Removing user security

To remove user security for a set of users:

    1. Open the Work With Security form
    2. Select one or more users
    3. Press <Ctrl-Delete> on your keyboard (or select Edit | Delete Row from the main
       menu)

This will remove all security settings for the selected users.

You may not delete inherited rows. You may however, disable an inherited filter by unckecking
the "Enable" box.

If you delete a filter with an unchecked inherited filter, the filter will revert to its enabled state.




                                                                                              209
Data Access Studio Manual




User Licenses
Data Access Studio supports two types of users:

 Full users
 Limited users

A Full user typically has access to all of the DAS functionality (less any secured functionality).
A limited user, on the other hand, may only use published reports.

When DAS is licensed by user, Dominion Software issues licenses based on the number of full
users and/or the number of limited users. In order to run DAS you must have at least one full
user. Under the user-license model, an organization must decide which users require full access
to DAS and which users require the limited access. Typically organizations make this split
based on those users who will design Reports and those users who will run the reports.

Each user license is a concurrent license. As long as a user is signed in, the user's license is in
use. When the user signs off, the license is freed for the next user. When the total number of
users exceeds the licenses purchased, the user who exceeds the limit will be prompted with a
license exceeded message box. DAS denies this user access until a license becomes available.

Full Users

All users in DAS, by default, are Full Users. If you purchased full user licenses for everyone
then no additional setup is necessary.

Limited Users

If you have purchased limited user licenses, you must follow the steps in this section.

This section describes how to designate OneWorld users/groups as limited users. If you do not
perform the steps in this section, DAS will not use your limited license -- DAS will default all
users as full users and consume full user licenses.

To designate an individual or group as limited:

   1. Sign on to DAS as "DASADMIN" (See "User Security").
   2. Once you have signed in as DASADMIN, select File | Manage Security. Data
      Access Studio will display the Work With Security form.
   3. With the Work With Security application open, click on the open cell under the
      "User ID" column. Click the visual assist and select the user that will be a
      Limited User. You may select either a user or a group.
   4. Tab out of the "User ID" field. The detail portion (right-hand side) will show the


210
                                                             Administrator's Guide


      details for this user.
   5. In order to mark the user as limited, you must uncheck all "Permissions" except
      "Can use published reports". Only when this is the sole "Permission" will the
      user or group be designated "Limited".




   6. Repeat step 3 through 5 for every user that should be a Limited User.



Limited users are allowed to:

   1. Open published reports
   2. Depending on the authorization settings; change selection criteria
   3. Delete/Change/Add Groupings
   4. Add/Delete Sort sequences
   5. Add/Delete/Change Filter Criteria
   6. Execute Calculations
   7. Change Column Captions
   8. Set/define Print Area
   9. Export to Excel
   10. Modify Grid Style

Limited users are NOT allowed to:

   1. Open/use tables and business views
   2. Open/use OLE DB data
   3. Open/use Snapshot data

                                                                              211
Data Access Studio Manual


   4.   Create new reports
   5.   Delete reports
   6.   Save their opened and modified reports in DAS.
   7.   Import/Export DAS Objects
   8.   Open any data source other than the OCM datasource




Resetting Concurrent Licenses
Data Access Studio keeps track of the users that are on the system. If Data Access Studio has an
incorrect user count, then you can reset the concurrent licenses as follows:



   1. Sign in to EnterpriseOne as user: DASADMIN
   2. From the File menu, select "Reset Concurrent Licenses"

This will free up all unused limited and full user concurrent licenses.




212
                                                                   Administrator's Guide




Setting up OneWorld Tasks and
Shortcuts
Setting up a Solution Explorer Task

This topic explains how to add a Solution Explorer Task for DAS.

   1. Sign in to OneWorld Solution Explorer
   2. Select a menu to add a task
   3. Right-click on the menu tree and select: Insert New Task...:




                                                                                    213
Data Access Studio Manual




   4. Enter the information as shown above and click OK.

When you install Data Access Studio to your workstation, the installer automatically
creates a shortcut (LaunchDAS.exe) in your Windows directory. Because the reference
to this shortcut requires no absolute path, it will work on all workstations.

Setting up a Solution Explorer Shortcut

Fastpaths are stored in User Defined Code H90|FP. To add a fastpath to Data Access
Studio:

   1. Type "UDC" in the Solution Explorer fastpath to bring up the "Work With
      User Defined Codes"
   2. Enter H90 for "Product Code" and FP for "User Defined Codes" and click



214
                                    Administrator's Guide


   find:




3. Click "Add" to add a new code:




                                                     215
Data Access Studio Manual




   4. Insert the information as shown and press "OK".




216
                                                                     Administrator's Guide




Web Server
Data Access Studio has the capability to host client sessions from a central server--the
DAS Web Server. In this mode of operation, client machine does not need OneWorld
installed or DAS installed.

This mode of operation has the following benefits to an organization:

 Client machines do not require a Fat Client Install
 Client machines get to use full capabilities of Data Access Studio
 DAS Server is the only place where you need to update software
 Network protocols TCP (fast) and HTTP (secure) are both supported

DAS has two ways to operate in distributed mode: LAN Server and WAN Server. In
the LAN server configuration, all you need is a network server and a fileshare. IIS is
not necessary for the LAN Server configuration. In the WAN Server configuration you
need a Windows Server and IIS 5.0+. In this mode, IIS has a virtual directory that hosts
the Data Access Studio application. Users can run DAS over https and securely transfer
information over a WAN.

For each client request, the DAS Web Server creates a connection to the OneWorld
system based on the client’s login credentials. Once connected to OneWorld, the DAS
Web Server launches Data Access Studio in a secured environment on the client
workstation. Once Data Access Studio runs on the client workstation, all of its user-
interface capabilities are available to the user.

Minimum Technical Requirements

When Data Access Studio is run in this configuration, the server and client workstation
need to meet the following minimum technical requirements:

Server

 Windows Server 2000 or greater
 1 processor (3.0 GHz ) per 40 clients
 1 GB RAM per 40 clients
 20 GB HDD
 TCP network
 OneWorld client install (OneWorld Client minimum technical requirements)
 Data Access Studio client install
 .NET Framework v1.1
 IIS 5.0+ (only necessary if you run DAS in a WAN configuration)



                                                                                           217
Data Access Studio Manual



Client

 Windows 2000 or greater
 .NET Framework v1.1
 DAS Security MSI (see below)

Web Architecture




In the above architecture, one to many thin clients connect to the DAS Web Server
using a URL. Once connected, the DAS Server service creates a DASWorker to host
the client's requests. The session then executes the User Interface portion of DAS on
the client workstation. The result is a scalable solution that compromises none of the
rich user interface capabilities of DAS. In this architecture, you will use a network
share to expose the DAS executable and TCP for the data transfer.

DAS Web Setup

There are only a few steps to setup a DAS Web Server. A DAS Web Server is an
augmented Full Client running on a Server Operating System. That said, here are the
steps to setup the server up:

   1. Install Windows 2000 or Windows 2003 on a machine (you may use
      Windows XP for testing purposes. Dominion Software recommends
      Windows Server editions because of the added security features.)
      Signon as an Administrator.
   2. Install a OneWorld Full Client
   3. Install Data Access Studio. Run "Setup" from the "Data Access Studio"
      directory on your deployment server.


218
                                                                   Administrator's Guide


    4. Open Start->Run and type: "RunDASWebSetup".



Fill in the fields as follows (or accept the defaults to test):

Field            Description
                 If you run DAS entirely within a firewall, select tcp.
Channel
              If you run DAS across a firewall (over an unsecured LAN), then select
              http.
              For fast communication select Binary. To debug network
Formatter
              sessions, select SOAP.
              Check to compress the network traffic. Check this if you are
Compress
              running over a slow network connection or WAN
              Check to encrypt the network traffic. Check this if you are
              running over an unsecured network. Select from the following
Encrypt       encryption algorithms: DES, TripleDES, RC5, and Rijndael. Also
              select the key size you want for your encryption. Higher key
              sizes correspond to higher security.
              The port for the DAS Server service. If you run a firewall, make
Listener Port
              sure this port is open.
              The port that the DAS Server service will start with to assign
Start Port
              client connections
              The maximum number of allowed client connections. Use this
Maximum       setting to limit the number of users per server as you see fit. If
Connections you run a firewall, make sure the range of Start Port to Start Port
              + Maximum Connections is open.
              The number of seconds a DASWorker will stay alive after a client
              session has disconnected. When the client session ends
Lease
              abnormally, this timeout ensures that the corresponding server
Timeout
              executable gets cleaned up. Using the default timeout is
              recommended.
              Once a user signs in, this is the number of seconds the server
Signin        has to create a session for the user. If users have trouble
Timeout       signing in, try increasing this value. Otherwise, the default
              timeout is recommended.
Firewall
              Advanced and optional. The machine name or URL of your
Machine
              firewall. Used to troubleshoot connections through a firewall.
Name
Default
              Leave Blank
Environment
URL           The IP or machine name of the DAS Web Server.
Prompt        When you press "Setup Web" on the menu bar and this field is
before setup checked, the setup program will display a message box that

                                                                                    219
Data Access Studio Manual


                warns you that you are about to setup the Web Server. Any
                DAS client that is connected to the existing Web server will be
                disconnected.



   5. Once you have filled in the above fields, press "Setup Web":

       You will see a report of the setup steps. These steps convert the Data
       Access Studio workstation to a DAS Web Server. When you see the
       "Setup Succeeded" message, that means the DAS Web server is running
       and ready to take client requests.

Troubleshooting the setup

       If any error occurs during the setup process, you will see the word Error
       in red. Each error is accompanied by the description of what went
       wrong and what you can do to resolve the issue manually.

       The most common error is insufficient rights.

Testing the DAS Web Server within your LAN

To test the setup:

   1. Sign in to a client machine that can access the DAS Web Server via the
      network.
   2. Install the DAS security MSI from: "
      \\<ServerName>\DAS\DASWebSecurity.msi". If you are an
      administrator, then you may run this msi on your workstation. If your
      users have limited security access, then you need to deploy this MSI to
      the individual workstations using network management software such as
      ZenWorks or Systems Management Server.
   3. Open Internet Explorer. Type " \\<ServerName>\DAS\DAS.lnk" -- where
      <ServerName> is the name of the DAS Web Server. (Note that if you
      did not perform step 2, you will be prompted with a security message
      that instructs you to apply the DASWebSecurity.msi. )
   4. Sign in and start using Data Access Studio.



HTTP Setup

Once you have run DAS Web setup, DAS is ready to run over your LAN. To enable
DAS to run in IIS over a WAN, do the following additional steps:

220
                                                         Administrator's Guide


1. Setup an IIS server
2. Install a OneWorld Fat Client and Data Access Studio on the IIS Server.
3. In Program Files/Dominion Software/Data Access Studio, right-click the
   Data Access Studio folder. Select "Properties". Select the "Web
   Sharing" tab.




   Select "Share this folder".




                                                                          221
Data Access Studio Manual




       Enter an Alias such as "DASWeb". Click OK. This will create a
       DASWeb virtual directory in IIS.

       In IIS, allow anonymous access to the DASWeb folder.

       Configure IIS to run HTTPS (encrypted HTTP) for your new virtual
       directory. Please have a qualified IIS resource setup IIS security to
       prevent unauthorized access to Data Access Studio.

       To run DAS from a browser, type:
       "https://<ServerName>/DASWeb/DataAccessStudio.exe". If you have a
       slow connection, DAS may take up to a minute to load. Once DAS is
       loaded, the browser will cache the downloaded information so that future
       sign-ons go faster.

Administration Tasks
Updating the DAS Web Server

You will update both the client package and the DAS software over time.

Updating the client package

You will need to update the client package to ensure the table and business view
specifications are up-to-date. When you apply and update package, all users must be
off the system.


222
                                                                 Administrator's Guide


   1. To force users off the system, kill all DASWorker Processes in Task
      Manager.
   2. Install the desired OneWorld package.
   3. Open Start->Run and type: "RunDASWebSetup".

Updating DAS

You will need to update Data Access Studio when new updates are available from
Dominion Software:

   1. To force users off the system, kill all DASWorker Processes in Task
      Manager.
   2. From your Deployment Server share, run "Data Access
      Studio\Setup.exe"
   3. After you run Setup.exe, WebSetup will automatically run. This will stop
      the DAS Web Server, setup, and restart the services.

Stopping new connections

To stop new sessions from connecting, stop the "Data Access Studio Service" in the
Services window. To enable new connections, start the "Data Access Studio Service".
Note that this service by default is configured to start at server boot up.




                                                                                  223
Data Access Studio Manual




Audit Trail
Although many databases support audit trails, database audit tools typically do not
work well with EnterpriseOne. This is because EnterpriseOne maps many
EnterpriseOne users to one database proxy user. Once mapped to the proxy user, the
database audit utilities will not be able to determine which EnterpriseOne user was
responsible for which query.

Data Access Studio solves this problem with its own audit trail utility. When enabled,
the Audit Trail records the EnterpriseOne and OLE DB database activity for all DAS
users. The DASADMIN can then review and purge the audit trail as necessary.

The following section discusses how to enable and use the Audit Trail feature.

Ways to use the Audit Trail

The audit trail can be useful for the following:

   1. Identify who submitted a query that needs to be terminated. Simply
      query for the table names and sort by most recently used to see which
      EnterpriseOne users could have submitted the query in question
   2. Identify inefficient queries. The Audit Log can show if a user is
      accessing the database inefficiently. For instance, if the user is filtering
      on non-indexed columns or if the user is doing excessive table lookups
      as opposed to creating a business view to do the same thing.
   3. Identify unauthorized access. The Audit Log can show if a user is
      accessing a table that they should not be authorized to access.
   4. Monitor DAS Performance. The Audit log shows run times for the
      main select, load, and calculation operations. This will help you identify
      poor running queries.
   5. Monitor how DAS is utilized in your organization.

Enabling Audit Trail

By default, the Audit Trail is turned off. To turn on the audit trail:

   1. Sign into Data Access Studio as DASADMIN (See User Security for
      information about how to add the DASADMIN user.)
   2. From the File menu, select "Preferences for all users..."
   3. Check "Enable EnterpriseOne Auditing"
   4. Auto-purge. If you want DAS keep all audit records, uncheck the "Auto-
      Purge" option. If you want DAS to automatically delete audit records
      that are older than a certain number of days, check the Auto-Purge


224
                                                                    Administrator's Guide


       option and enter then thresheld number of days.

Once Auditing is enabled, DAS will record database access for all DAS users. All
records are stored in the FDASPROP table. Access recording will continue
indefinitely until you disable auditing.

Working with the Audit Trail

To work with the Audit Trail:

   1. Sign into Data Access Studio as DASADMIN
   2. From the File menu, select "Audit Trail".

You will see the following Audit Trail form below.




This form will let you query and report on the audit records. Each field is described
below:

Accessed Tables List the tables that the activity is accessing
                Defines the type of activity:

                     10 - Counting records

Activity             20 - Main Select

                     30 - Loading data

                     40 - Calculating data
                     The EnterpriseOne user that submitted the query from Data
User
                     Access Studio
                     The time in seconds that the activity took to complete. All
Elapsed time
                     sub-second activities will be rounded up to 1s when they


                                                                                        225
Data Access Studio Manual


                      finish. If an activity does not finish its elapsed time will be
                      0.
                      0 - Full User
User Type
                      1 - Limited User
Audit Record          The size of the audit record. Useful to determine when to
Size                  purge the audit records.
                      The system-generated audit ID for each distinct DAS
Audit ID
                      activity
Access Date           The date that the audit record was created
Access Time           The time that the audit record was created

Querying audit records

When you click find, you will see the most recent activity at the top of the grid. The
default layout for the audit groups the related activities together and sorts by the
activity type. This shows you what any DAS report or layout does by its activity
components.

To query the audit records, simply enter filter values in the filter boxes (see Filtering
Data) and press "Find".

You can filter for access tables by typing in the table name with * before and after the
name. For example, if you wanted to see accesses to F0101, type: *F0101*.

You can filter by user simply by typing in the EnterprisOne user name in the User filter
box.

You can query for long-running selects. Enter 10 in the Activity Type and enter
something like ">60000" in the Elapsed time filter. This will show those selects that
took longed than one minute to execute.

Manually purging audit records

To delete any set of audit records:

   1. Highlight the audit record
   2. Press Delete

To purge all audit records:

   1. Click "Clear All"

Note that only the DASADMIN user has the authority to do these operations.



226
                                                                      Administrator's Guide


You can use the summary total at the bottom of the form in the "Audit Record Size"
column to determine how large the audit records are in bytes.

Viewing Audit Details

By default, the audit record shows an outline of the query. This shows what tables
were accessed, what layout was run, what filters and sorts were used. This detail
information can be useful in identifying what precisely a user did on any given query.

You may also choose to display this information in XML format. This format may be
useful if you want to export the audit document to a third party tool for further analysis
and reporting.

Reporting on audit information

As with any DAS grid, you can customize the grid as you need and save the grid as a
layout for future use. See Maintaining Layouts for more information.

Performance Considerations

Turning on the audit trail does increase the amount of database and network traffic in
your system. Data Access Studio makes every effort to minimize this overhead as
much as possible. The users should not experience noticeable delays when the Audit
Trail is on.

You may want to purge the Audit Trail records if you begin to notice significant
delays.




                                                                                         227
Data Access Studio Manual




Upgrading EnterpriseOne
When you upgrade EnterpriseOne or install a new instance, you will need a way to get
the Data Access Studio information from your old instance to your new instance. This
topic describes how you can perform this transfer. This transfer will work regardless
of the source and target E1 releases. For example, you can export objects from non-
unicode Xe to Unicode E8.12.

Exporting DAS Instance data

The first step is to export the DAS Instance data from your current instance. This will
export all relevant work including layouts, reports, report publishing, defaults, etc.

   1.   Run Data Access Studio and sign in as user DASADMIN.
   2.   Click File | Work with DAS Instance...
   3.   Click the "Export" button.
   4.   On the Export DAS Instance file dialog, click Save.

Data Access Studio will export the DAS Instance data to the .dasi file you specified.
You will use this .dasi file in the next step to import to the new instance.

Importing DAS Instance data

Once you have exported to a .dasi file, you can import this .dasi file into any new
instance of Data Access Studio. The new instance must have its own license key,
however.

   1.   Run Data Access Studio and sign in as user DASADMIN.
   2.   Click File | Work with DAS Instance...
   3.   Click the Import tab and click the "Import" button.
   4.   On the Import DAS Instance file dialog, select the .dasi file and click
        Open. If you want to overwrite the target with the contents of the .dasi,
        check the "Overwrite target" checkbox (this is usually NOT
        recommended.)

Data Access Studio will proceed to import the contents for the .dasi file into your new
instance.




228
Index
A                                                                Advanced Operations ......................... 176

Absolute Value ................................... 122           Alias Caption .........................................68

Access                                                           Apply Suggested Joins ................... 29, 33

    Data Access Studio......................... 202              Associated Description ...................... 116

    User Security .................................. 202         Audit Trail

Access.................................................. 202         Enabling .......................................... 225

Account Balances table ...................... 118                Audit Trail .......................................... 225

Account Category ............................... 115             Auto-appending

Account Groups .................................. 118                wildcard .............................................52

Accounts                                                         Auto-appending .....................................52

    Chart ................................................ 118   Automatic Accounting Instructions .. 115

Accounts.............................................. 118       Average ............................................... 123

Accounts Payable Ledger..................... 48                  B

Actual Joins........................................... 29       Balance Sheets .................................... 118

Add Count ............................................. 84       Best Fit All Columns.............................70

Adding                                                           Best Fit Column.....................................70

    fastpath ............................................ 214    Best Fit Single Column .........................70

    Solution Explorer Task .................. 214                BLANK ..................................................54

    wildcard............................................. 54     Blanks

Adding ........................................... 54, 214           Filter ...................................................54

Additional EnterpriseOne Captions..... 68                        Blanks ....................................................54

Administration .................................... 197

                                                                                                                           229
Data Access Studio Manual


C                                                                Copy

Calculation Name ................................. 92                Excel ............................................... 154

Calculations -> Quick .......................... 92              Copy .................................................... 154

Cancel .................................................... 48   Corresponding

Cancel Button ....................................... 48             Excel® ............................................ 157

Case-insensitive .................................. 200          Create Table Join ...................................33

Clear Filter button................................. 51          Creating ..................................................28

Clear Sorting button ............................. 61            Custom Caption .....................................68

Column Re-arranging ........................... 76               D

Column Renaming ................................ 68              DAS ..................................................... 197

Column Sizing ...................................... 70          Das file ................................................ 189

Columns                                                          DAS Instance ...................................... 229

    Selecting ............................................ 66    DAS Objects ....................................... 189

Columns .......................................... 65, 66        DAS Performance .............................. 225

Compare                                                          DASADMIN....................................... 225

    String ............................................... 142   Dasi ..................................................... 229

Compare .............................................. 142       Dasi file ............................................... 229

Concatenation ..................................... 139          DASLink ............................................. 157

Concurrent Licenses                                              DASUSERS ........................................ 172

    Resetting ......................................... 211      Data

Concurrent Licenses ........................... 211                  Calculating .........................................92

Conforms                                                             Exporting ........................................ 154

    OLE DB ............................................ 22           Filtering..............................................51


230
                                                                                                                      Index


   Grouping ........................................... 77     Deployment Server............................. 199

   Navigating ......................................... 47     Description Caption ..............................68

Data ............................ 47, 48, 51, 92, 154          Description Header................................68

Data Access Studio                                             Difference ..................................... 92, 124

   access............................................... 202   Display

   Installing ......................................... 199        Work With Security ....................... 202

Data Access Studio .......... 8, 48, 199, 202                  Display ................................................ 202

Data Access Studio Link .................... 157               Division ............................................... 125

Database Lookup .................................. 99          During

Date Calculations................................ 104              Find ....................................................48

Date Difference................................... 105         Dynamic Join .........................................14

Date Published ...................................... 11       E

DateTime                                                       E1 Time

   E1 Time ........................................... 117         DateTime ........................................ 117

DateTime ............................................. 117     E1 Time............................................... 117

Day Of Month ..................................... 106         Edit .........................................................92

Day Of Week ...................................... 107         Edit Mapping ...................................... 157

Day Of Year ........................................ 108       Editor ......................................................92

Default Layout ...................................... 45       Embed Excel®.................................... 157

Delete                                                         Enabling

   Snapshot .......................................... 181         Excel® ............................................ 157

Delete ............................................ 37, 181    Enabling .............................................. 157

Delete Snapshot .................................. 181         EnterpriseOne Interoperability .......... 114


                                                                                                                         231
Data Access Studio Manual


Excel                                                             Filter-as-you-type ..................................52

    Copy ................................................ 154     Filtering

Excel .................................................... 154        Blanks ................................................54

Excel®                                                                Data ....................................................51

    Mapping .......................................... 157        Filtering ........................................... 51, 54

Excel® ................................................. 157      Financial reports ................................. 115

Excel® Data                                                       Find button .............................................54

    Linking ............................................ 157      Finding

Excel® Data ........................................ 157              Data ....................................................48

Excel®-related .................................... 157           Finding ...................................................48

Export .................................................. 229     Footer .................................................. 146

Export DAS objects ............................ 189               FP ........................................................ 214

Export Selected Rows ........................ 154                 Full Collapse ................................... 77, 84

Exporting                                                         Full Expand............................................77

    Data ................................................. 154    Full Users ............................................ 211

Exporting ..................................... 154, 181          G

F                                                                 Group Accounts .................................. 118

Fastpath                                                          Group Row Index ............................... 134

    add ................................................... 214   Group Row Summary ........................ 135

Fastpath ............................................... 214      Grouping

Fastpaths.............................................. 214           Data ....................................................77

Filter Box Properties ............................ 51             Grouping ................................................77

Filter Distinct Values ........................... 60


232
                                                                                                                     Index


H                                                               Julian ................................................... 109

Header ................................................. 146    Julian Date .......................................... 109

Header/footer ...................................... 146        L

Hide All ................................................. 71   Layout

Hide Column ......................................... 71            Create Table Join...............................33

Hide Selected ........................................ 71           Publishing ....................................... 172

Hide/Show Columns ............................ 71               Layout ................ 14, 27, 28, 33, 172, 201

I                                                               Layout Example ....................................27

Import .......................................... 181, 229      Layout Name .........................................29

Import DAS Objects ........................... 189              Level

Import/Export ..................................... 181             Detail ............................................... 118

Index Assistance ................................... 63         Level.................................................... 118

Index Assistant...................................... 63        Limited Users ..................................... 211

Indices ................................................. 193   Linking

Inherited .............................................. 202        Excel® Data ................................... 157

Installation........................................... 199     Linking ................................................ 157

Installing                                                      Load Progress ........................................48

    Data Access Studio......................... 199             Lock/Unlock ....................................... 181

Installing.............................................. 199    Locking

J                                                                   Snapshot .......................................... 181

JD Edwards™ ..................................... 198           Locking ............................................... 181

JD Edwards™ OneWorld Xe™ ........ 198                           Long Name Header ...............................68

Join Relation ......................................... 29


                                                                                                                        233
Data Access Studio Manual


M                                                                    Snapshot .......................................... 181

Main Window ......................................... 9          Modifying ........................................... 181

Maintaining                                                      Month .......................................... 110, 111

    Snapshot Data ................................. 183          Month Description ............................. 111

    Snapshots ........................................ 181       Multiplication ..................................... 128

Maintaining ......................................... 181        Multi-Sort ..............................................61

Manage Security ................................. 202            Multi-Sort Font ......................................39

Managing                                                         Multi-Sort Font Size..............................39

    User ................................................. 201   N

Managing ............................................ 201        Navigating

Manual Resizing ................................... 70               Data ....................................................47

Mapping                                                          Navigating..............................................47

    Excel®............................................. 157      Navigation button ..................................48

Mapping .............................................. 157       New | Layout .........................................33

Margins ............................................... 148      New Layout ........................ 14, 28, 29, 37

Marking                                                          Non-unicode Xe

    Snapshot .......................................... 181          Unicode E8.12 ................................ 229

Marking ............................................... 181      Non-unicode Xe ................................. 229

Math..................................................... 121    Number

Max ...................................................... 126       Pages ............................................... 146

Min ...................................................... 127   Number ............................................... 146

Minimum ............................................... 84       O

Modifying                                                        ODBC ........................................... 22, 187


234
                                                                                                                       Index


OLE DB ........................................ 22, 187           Popup menu .................................... 84, 92

OLE DB data ................................ 22, 187              Portrait/Landscape.............................. 148

OLE DB/ODBC .................................... 22               Preferences

OneWorld .............................................. 63           Printing............................................ 148

OneWorld Data ..................................... 17            Preferences .......................................... 148

OneWorld Solution Explorer ............. 214                      Press Ctrl+E ........................................ 154

OneWorld System ................................ 17               Pressing

OneWorld users/groups ...................... 211                     Clear Filter button .............................51

Open Layouts ........................................ 14             Find ....................................................48

Open OLE DB Data ............................. 22                 Pressing ..................................................48

Open OneWorld Data ........................... 17                 Print Areas .......................................... 149

Open Published Reports ....................... 11                 Print button ......................................... 150

Open Snapshot Data ............................. 20               Print Direct.......................................... 150

Owner .................................................. 201      Print Preview ...................................... 150

P                                                                 Printing

Pad ....................................................... 140      Preferences...................................... 148

Pages                                                             Printing ................................................ 148

    number ............................................ 146       Profit.................................................... 118

Pages.................................................... 146     Progress Bar ...........................................48

Parameters ............................................. 92       Providing

PDF ...................................................... 146       application-level ............................. 202

Performance Considerations .............. 225                     Publishing

Please see .............................................. 14         Layouts............................................ 172


                                                                                                                          235
Data Access Studio Manual


Publishing ........................................... 172           Snapshot .......................................... 178

Q                                                                 Save ........................................................37

Querying                                                          Save As ..................................................37

    AAI .................................................. 115    Save As Layout .....................................37

    Automatic Accounting Instructions                             Save As Snapshot ............................... 181
      ..................................................... 115
                                                                  Save Filter Values .................................37
Quick Calculation ................................. 92
                                                                  Schema .............................................. 187
Quick Column Map ............................ 157
                                                                  See
Quick Map Column ............................ 157
                                                                     Group Row Popup Menu ..................77
Quick Map Group ............................... 157
                                                                  Select
R
                                                                     Account Groups.............................. 118
Recent Connections .............................. 22
                                                                  Select Calculations ................................92
Recently Opened....................... 14, 17, 20
                                                                  Select Calculations->Editor ..................92
Record Navigation Buttons .................. 50
                                                                  Select Zoom ........................................ 150
Record Selection ................................... 48
                                                                  Selecting
Remove Sort ......................................... 61
                                                                     Columns .............................................66
Report ............................................ 11, 146
                                                                  Selecting.................................................66
Resetting
                                                                  Semicolon-separated list .......................54
    Concurrent Licenses ....................... 211
                                                                  Set
Resetting.............................................. 211
                                                                     case-insensitive............................... 200
Restore Defaults ............................. 39, 45
                                                                  Settings ...................................................92
Row Index ........................................... 136
                                                                  Shortcuts ............................................. 214
S
                                                                  Show
Save

236
                                                                                                                       Index


   Print Area ........................................ 149          Comparing ...................................... 185

Show .................................................... 149    Snapshot Data ............................. 183, 185

Show All ............................................... 71      Snapshot setup .................................... 178

Show Collapsed Summaries ................ 84                     Snapshot window ............................... 183

Show Horizontal Lines ......................... 39               Solution Explorer fastpath ................. 214

Show Selected ....................................... 71         Solution Explorer Shortcut ................ 214

Show Vertical Lines ............................. 39             Solution Explorer Task ...................... 214

Signon...................................................... 9   Sort Ascending ......................................61

Single Column                                                    Sort Descending ....................................61

   Hiding................................................ 71     Split ..................................................... 141

Single Column ...................................... 71          SQL Name ........................................ 1, 39

Snapshot                                                         SQL Name Header ................................68

   compare ........................................... 185       Standard Deviation ............................. 129

   deleting ............................................ 181     String

   Locking ........................................... 181          Compare .......................................... 142

   Maintaining ..................................... 181            Length ............................................. 143

   marking ........................................... 181       String ........................................... 142, 143

   modifying ........................................ 181        Substring ............................................. 144

   need ................................................. 181    Suggested Joins .............................. 29, 33

   Snapshot .......................................... 181       Sum ............................................... 84, 130

   Unlocking........................................ 181         Sum totals ........................................... 118

Snapshot ................. 1, 177, 181, 183, 185                 Summary

Snapshot Data                                                       Difference ....................................... 185


                                                                                                                          237
Data Access Studio Manual


Summary ........................ 77, 84, 185, 193                     non-unicode Xe .............................. 229

Summary Panel ..................................... 84            Unicode E8.12 .................................... 229

System ................................................. 133      Uniform Buckets ................................ 131

System Requirements ......................... 198                 Unlocking

T                                                                     Snapshot .......................................... 181

Table Join Layout ................................. 33            Unlocking ........................................... 181

Table Lookup ...................................... 100           Un-Publish .............................................11

Table/View .......................................... 100         Upgrading ........................................... 229

Tables .................................................. 193     Use

Tab-Separated-Value file ................... 154                      Audit Trail ...................................... 225

Text ...................................................... 138   Use Data Access Studio™ ......................1

The Snapshot application ................... 183                  User Licenses...................................... 211

TODAY ................................................. 54        User Security

Today's Date ....................................... 112              Accessing ........................................ 202

Totaling ................................................... 1    User Security ...................................... 202

Trim ..................................................... 145    Users/groups ....................................... 202

Two-row .................................................. 1      Uses OneWorld .......................................1

Type                                                              V

    Calculation ........................................ 92       Value ......................................................92

Type ....................................................... 92   View Information ....................................1

Type Converter ................................... 137            View list .............................................. 193

U                                                                 View ODBC ............................................1

Unicode E8.12                                                     Viewing Audit Details ....................... 225


238
                                                                                                                     Index


Views ................................................... 193   Windows Server 2000 ........................ 218

Visual Summarization ............................ 1             Windows XP ....................................... 218

W                                                               Word Wrap Cells ...................................39

WAN Server ....................................... 218          Work With Security ................... 202, 211

Web ..................................................... 218   Work With Security application 202, 211

Web Mode ............................................... 8      X

Web Server.......................................... 218        XML .................................................... 225

Web Sharing ....................................... 218         Y

WebSetup ............................................ 218       Year ..................................................... 113

Wide Area Network............................ 218               Z

Wildcard .......................................... 52, 54      ZenWorks ........................................... 218

Wildcards .................................... 1, 51, 54        Zoom In............................................... 150

Windows 2003 .................................... 218           Zoom Out ............................................ 150

Windows Clipboard ............................ 154              Zoom/View ..............................................1

Windows Server ................................. 218




                                                                                                                        239

								
To top