11 Replies Latest reply on Apr 6, 2012 3:39 PM by Paavo

    New Application - Is This Possible

    Gus C
      I have been tasked to see if this is possible with Apex.

      At the moment, users have spreadsheets that they manipulate and then load on to the server.
      These speadsheets are then used as master data for a SAP Business Objects report.

      We would like to remove all these spreadsheets and hold the data in oracle tables.

      I need to build an application were the user initially, creates and populates a table from an excel spreadsheet.
      They then have full control over that data, ie, insert, update and delete.
      Later they can insert new data from a spreadsheet or truncate the table.

      Also if possible, they should be able to delete multiple rows with one click, update multiple rows with one click.
      I need to make this application as user friendly as possible, ie, incorporate as much functionality that excel provides as possbile.

      Any ideas appreciated

        • 1. Re: New Application - Is This Possible
          Lajos Sarecz-Oracle

          You should start with this OBE (Oracle By Example): [Creating and Running a Database Application Using Oracle Application Express 4.0|http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r40/apexstart/apexstart_b/apexstart_b_ll.htm] .
          Further OBEs can be found here .

          • 2. Re: New Application - Is This Possible
            Gus C
            I am not new to Apex.
            This requirement is a little different to the normal transactional applications I have been building.
            I have been looking in to websheets.
            I seem to be able to load the data into a data grid, via copy and paste from a speadsheeet.
            Where is this loaded, ie, in a table ?

            • 3. Re: New Application - Is This Possible
              Rod West
              Hi Gus,

              Your options depend on which version of Apex you are using. If you are on Apex 4.0 then certainly have a look at websheets. The data is stored in the APEX$_WS_ROW table in the parsing schema and you can build views etc. on this to format the data how you want it. However, what you can do with a websheet application is quite limited.

              In Apex 4.1 there is a data upload wizard which build pages to upload data into your application from a spreadsheet.

              Interactive reports should also be considered. These are very flexible and can be configured to update and delete data as well, though it would be difficult to upload data directly from a spreadsheet.

              Rod West
              • 4. Re: New Application - Is This Possible
                Gus C
                Hi Rod

                Looking at websheets -
                It is ok for loading the data from a spreadsheet, updating data etc, but this data needs to be held in a table accessable to SAP Business Objects for reporting, not an Apex specific table.

                If I use the new data load function in 4.1, then I need to specify a table, which I need to be built dynamically.
                Also I would need to build the screens for the updateble interactive reports.

                • 5. Re: New Application - Is This Possible
                  Gus C
                  I don't think websheets have enough functionality for my needs.

                  I think I need to look at creating a page were the user defines a table, ie, colums, data types.
                  They click a button, that creates the table.
                  They then load the data via the new data load wizard.

                  Is it possible for the user to create a page with a interactive report on the click of a button.

                  • 6. Re: New Application - Is This Possible
                    Rod West
                    I don't think websheets have enough functionality for my needs.
                    Probably not, but you can report on websheet content in other applications such as Business Objects.
                    I need to look at creating a page were the user defines a table, ie, colums, data types
                    Creating database objects dynamically will cause you lots of problems, so I wouldn't recommend going this way. Better to define a database structure which is static and contains metadata to describe what is in the columns.
                    Is it possible for the user to create a page with a interactive report on the click of a button.
                    No, but you can define a classic report based on dynamic SQL. Users cannot create pages.
                    • 7. Re: New Application - Is This Possible
                      I think that you may have dismissed the data load wizard functionality too quickly.

                      Surely there is a finite number of data items (as you indicate that they are funnelled into a business objects master table). In which case, create one of more tables into which the data can be loaded; then the end-users can choose which column of data corresponds to which table column during the wizard load.

                      You could even play dice with the devil and add some "generic" columns into your data-load tables.

                      • 8. Re: New Application - Is This Possible
                        Gus C
                        I don't think there is a solution to my problem.
                        We don't want to build a full Apex app as it defeats the purpose, ie, everything created automatically or by the user.

                        Websheets looked promising, but the funtionality is limited and the data is held in an internal Apex table. This means that
                        we would need to build a view looking at that table. This also defeats the purpose.

                        Unless anyone has any more ideas, I think I will tell the management that Apex is not the tool for this project.

                        Thanks for your help anyway

                        • 9. Re: New Application - Is This Possible
                          Why not make a solution like:
                          1.) Make excel template with target table columns
                          - add extra column for 'upload comment' and 'upload date' and 'upload app_user' anything to make potential upload distinct from previous ones.
                          2.) Populate the excel template with valid sample data, put some sample data as well to 'upload comment' etc. fields
                          3.) Create small application from spreadsheet by copy-pasting 1.)-2.) created excel which creates the oracle table for you
                          4.) Then make couple of pages:
                          - if you are using apex 4.1 make upload page with the upload page wizard for the new table
                          - make page for deleting old or erronous uploads, use 'upload comment' and 'upload date' etc. to filter the rows which you want to mass-delete
                          - make page for editing the data perhaps good several pages for different purposes: e.g. master detail for navigating the data by the potential key columns and tabular form for updating several cols at once.
                          - make page for making the computations and updating the data like now in excel (can be omitted in first step if the upload excel makes lots of calc)
                          5.) Make couple of reports for checking the quality of the data:
                          - check duplicates of rows
                          - check that your keys are in shape, so you don't have problem with the BO dimensions
                          - something visual
                          6.) Consider a separate reporting view or table for your quality inspected data (make a page to mark good data and copy to reporting)
                          - so you don't aggregate to your BO reports some content someone is uploading and massaging at the same time with the above pages
                          - perhaps you could have similar 'prompt filters' and selects as you have in BO report in APEX reports you make, so the checking is easier

                          After this step (takes typically less than hour) you have application for staging the data to database and you can turn the BO to fetch the data from the table dedicated to reporting.
                          Then you have time to think if the excel template you have is 'handy' for making BO reports. Could perhaps work on the keys (dimensions) and with the variables and their values more.

                          Beware database nulls in calculations, they act a bit differently than you expect from the excel world when calculated, try these in sql workshop:
                          select 1+null from dual
                          select 1*null from dual
                          select 'dimension 1'||null from dual

                          When making BO reports you could perhaps benefit turning some of the excel columns to variables, so you end-up having a table of less columns and more variable - variable value pairs.
                          This I think the previous posters ment that you could then design a relational database for the data you now have in excel. Then you gain several benefits like hiearchies, constraints, named dynamic list of values etc..

                          Also you are not trapped to apex only, because you can make a "download" page for the data you have in database. You just download a filtered set for excel massaging and then uploadit back to database.
                          Again the time spent in fiddling with the excel formulas vlookups, named ranges, links to external sources + protecting the structures from end-users copypaste etc. challenges can be step by step reduced
                          when the forms for modifying the existing data become ready. Typically then you usually pay more attention to the process the excel was designed to support and I recommend that you select the key output deliverables from the excel first for implementation so you can perhaps publish those as BO reports. Then gradual work towards the internals and inputs and if possible seek the input raw data directly from your existing databases or via upload-pages leaving out the excel to solve the heavy computation part for filtered downloads.

                          There are other benefits when you can manage the 'project' in apex team developer and perhaps share tasks for different developers, so they all can track the progress.
                          Remember to enable "feedback" link in your application, that simply rox. Also publish always the production version of the app with Application Alias name so the production version can be changed on the fly.

                          rgrds Paavo
                          • 10. Re: New Application - Is This Possible
                            Gus C
                            The upload of the excel spreadsheets will be executed only once. From then on it will just be maintained.
                            The problem is that there will be several different spreedsheets holding different types of data. These spreadsheets are created by the users and new ones could appear.
                            Each spreadsheet once loaded, should have it's own table, which will later be accessed by SAP BO for reporting.
                            Each table should have it's own screens for update, insert, delete etc.

                            The mangement want all this to be dynamic, ie, when a new spreadsheet appears, the user just loads it in to Apex and the table is created
                            and all the screens required to maintain the data.


                            • 11. Re: New Application - Is This Possible
                              Why not to rethink what you mean with user? Perhaps you could make apex workspace for excel-importers and give them power to develope apex-applications with "create application from spreadsheet" wizard. I think that actually will answer to your need for the import-step and creation of the first 'engineering' views for delete, modify and insert. Perhaps you could make a competition and ask management to support in that: "best excel to web"- implementation of the week?
                              Even persons without no programming background can make www-applications themselves and the feeling of the power can help when you start suggesting improvements to the information flows and improving the quality (e.g. realtime availability) of the BO reports.

                              At least you could give apex a try and see what happens, creation of this kind of application takes less than 5 minutes per worksheet.
                              Of course you then could have a good track of 'converted' excels and their corresponding applications and tables.

                              rgrds Paavo