This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Mar 8, 2012 7:30 AM by 922616 RSS

Websheets?

785233 Newbie
Currently Being Moderated
Hi,

We've been taking a look at the new websheets in Apex 4 but I'm not really clear on the intention of these.

What is the intended audience? What sort of applications is the tool intended to build?

Its great that users have the ability to create data grids and build an interactive report on a table but surely it wont take long before the user will need to understand SQL in order to build any sort of useful reports which seems a bit advanced for more of an end user kind of role? It seems that this would be excellent for building a simple application around a single table but in my opinion this is a fairly unlikely scenario.

Does anybody have any valid business cases that they would use websheets for, or can anybody help me understand the intention of the websheets functionality?

Thanks very miuch.
  • 1. Re: Websheets?
    dpeake Explorer
    Currently Being Moderated
    user11982879 (Please update your profile so we can use your name),

    Websheets are designed for Web-based content sharing.
    They combine hierarchical text pages, editable data grids and interactive reports.
    The text pages allow for Wiki-like syntax including SQL Tags.
    Websheets incorporates "sharing" model capabilities (reader, contributor, administrator) and the ability to add annotations (attachments, tags, notes, and links)

    The key aspect is that for those users who have zero SQL skills they can still define data grids (enter columns or import a spreadsheet) and define interactive reports (specify a table rather than enter SQL).
    For users with minimal SQL skills then they can utilize the SQL tag markup syntax and define interactive reports using SQL queries.

    Websheets are not designed to be formal applications developed by IT but an ad-hoc collaboration tool used directly by users.
    For this reason users can manipulate the application directly from within the application runtime environment rather than going to a separate development environment to change the application.
    The intended audience is business users who want to combine free-form textual pages with local data stores and reports from an existing database.
    Websheets combines Wiki type pages with spreadsheet like capabilities that we believe will become very popular with business users once they get comfortable with the interface.

    What do business users use spreadsheets for?
    Often to collaborate on simple data which is emailed around with textual context within the email (or in a text box on the first sheet).
    These spreadsheets generally don't include complex formulas and lead to significant issues with poor security, multiple versions of truth, and significant effort to maintain the "master" spreadsheet.
    Websheet information and data are all stored in the Oracle Database as a single source of truth alleviating the issues with spreadsheets.

    As for valid business cases - basically anything where an organization currently uses spreadsheets to share data or have Wikis that they want to add reporting to.

    I welcome further posts on how others in the APEX Community intend to introduce Websheets to their organization.

    Regards,
    David Peake
    Product Manager,
    Oracle Application Express
  • 2. Re: Websheets?
    739036 Newbie
    Currently Being Moderated
    Sorry David but I agree with the initial post

    Interactive report and data grids looks good but the actual query build part is very weak.

    SQL or single table (SQL is likely to involve IT – single table will just frustrate people) – I really want to like this product but its lacking a good query builder

    I look forward to seeing some references of websheets working in the real world and not just conceptual examples.
  • 3. Re: Websheets?
    dpeake Explorer
    Currently Being Moderated
    user2293646 (Again please update your profile),

    This is our first release of Websheets and we also want to see how the current offering stands-up.
    We welcome feedback from the community on how it can be improved in the future.

    David
  • 4. Re: Websheets?
    369783 Newbie
    Currently Being Moderated
    Hi David,

    I've been experimenting abit with the websheets, both the included sample application and the tutorial on websheets, and it seems like it could be useful, but still needs some refinement.

    They could definately be more useful if they allowed some backend programming, similar to what the rest of Apex provides. Admittedly, most of that is beyong the "normal business user" and require assistance from IT, but it would require less assistance than developing custom applications for each group of users.

    One huge potential problem I found in my initail experimenting (at least for the types of users I have in my organization), is they know nothing about database, and they know nothing about spreadsheets, other than they can quickly and easily add whatever data they want, anywhere they want (and it is a nightmare to get into a usable form usually). So, these types of users create HUGE spreadsheet, hundreds of columns and (quite often) tens or hundreds of thousands of rows.

    So, in my testing, I did something similar to what my 'types' of users would do (except I just created a table similar to their spreadsheets). For direction on how Oracle intended this functionality should or could be used, I followed the example in that tutorial about "Builing a Websheet Appilication..." and used the "Creating a Workeet SQL Data Report" and added a page section based upon the report. The websheet page takes about 4 hours to load now, crippling my PC while the web browser (IE8) tries to load everything and create the select lists, etc. Then, when it is done, the section only shows part of the report (first few columns), and finally I get a chance to use the navigation to go to the actual report. The report itself loads fairly quickly, considering the amount of data it has to retrieve, and shows all of the columns, but if I create a page section based upon that report, it browser/PC performance plummets, and then only the first few columns get displayed (it is unscrollable left or right).

    These are first few impressions I have after some very BASIC experimentation, so perhaps there are better ways I'm not aware of yet. Anyway, I hope this feedback helps.

    Bill Ferguson
  • 5. Re: Websheets?
    SaraB Newbie
    Currently Being Moderated
    Hi David

    I just stumbled across this thread and thought I'd add my thoughts. I've not worked extensively with 4.0 yet, but I have no great hurry to upgrade to 4.0 as I cannot see any major benefits.

    WebSheets has been the subject of many blog posts over the past few months and I've read them with interest, waiting to read something that explained the point of them to me. When I first started using APEX (many years ago now!) it was marketed as a rapid application development tool that would allow customers that currently used spreadsheets as part of their corporate processes, to quickly and easily convert them into web applications. Admittedly this needed a bit of web and oracle knowledge, but I worked for a software house that used Oracle already so I quickly converted our spreadsheets into applications. And I know of a few companies that grew their business selling their APEX services to large companies to do the same thing. Once the spreadsheets were converted to APEX applications, the customer always wanted more and this could quickly and easily be developed for them using APEX.

    Now we have WebSheets, which are marketed the same way. I have to say I'm confused. APEX is a good development tool, but WebSheets to me appears to be something that should have been a sample application not a major feature of the development environment. WebSheets are being marketed in such a way that it takes work away from developers, it's a feature that allows you to create your own database application without needing to involve developers. It's like releasing a new version of Oracle Forms with an application that allows non-developers to create forms without any database knowledge.

    However, once the non-developer has played with WebSheets they may want something a bit extra. As far as I can see, WebSheets cannot be built into a normal application. They cannot be styled (major problem!!) and the same thing cannot be easily reproduced by an APEX developer in a normal application. So someone can't build their WebSheet application and then contact an APEX developer to add a bit more functionality to it.

    The marketing strategy appears confused. You're adding something to a development tool which is marketed directly at end-users, cutting out the APEX developers. Who supports the WebSheet applications that a customer develops? I don't see how APEX can be an easy tool for end-users AND a development tool used by APEX developers (most of whom have long careers using Oracle). I'm concerned that the APEX team are favouring easy use by end-users, which will compromise APEX as a development tool.

    Styling in general is a problem with APEX. I would have liked to see more improvements to the HTML that is produced by such features as Interactive Reports. Instead the inconsistencies have gotten worse with 4.0 and it is even more difficult to develop a custom theme due to sometimes outdated and uncontrolable HTML/Javascript and inconsistent classes and id's.

    Personally I would have prefered the time spent on WebSheets to be spent on sorting out the HTML/JavaScript/CSS. I understand that this is just the first release of WebSheets and it should get better, but this means that more time will be spent on something that isn't beneficial to everyone.

    This is just my opinion. I love developing with APEX 3.2 and there is nothing I haven't been able to achieve with this version (except perhaps styling). I'm just a little disappointed by the new features of 4.0, especially WebSheets. I'm sure I'm not alone, it's just not popular to speak out against APEX if you're an APEX developer!

    Regards
    Sara
  • 6. Re: Websheets?
    mhichwa Newbie
    Currently Being Moderated
    This is a very interesting thread, here is some additional background.

    Business Requirement: My division uses spreadsheets all the time, and its really inefficient; multiple conflicting copies of the spreadsheet are emailed; data collection frequently requires merging of many spreadsheets. Enforcing consistent data values is hard; some users enter "Y", others "Yes" etc. You say Oracle APEX can allow spreadsheet data to be shared on the web / intranet, but it's not that easy, even a simple application requires a level of programming thats too much for the typical "business user". First simply managing the data objects is hard, for example adding columns to tables, writing a group by SQL query, writing a trigger to populate a key, etc. Developing a form in APEX is also hard, their are so many properties its just way too confusing for the "casual user".

    Websheet Datagrids: An analogy to Adobe photoshop works at some level; Photoshop is an amazing tool, but it's a professional tool with allot of knobs. If you don't want to learn photoshop but want to crop, add contrast, and remove red-eyes from your photos; well photoshop elements is all you need. So a websheet simplifies the task developing (and refinement) of a data tracking application; at least to the level of a simple data collection spreadsheet. The websheet datagrid allows you to share a simple data structure on the web. You can create a websheet in just a few minutes and it provides you with a wealth of feature function:

    - Multi and single row view with edit capabilities in multi and single row
    - Ability to define lists of values to restrict data values; works in both report view and single row view
    - Automatic tracking of changes, so its clear who made what change when
    - Ability to annotate a row with a files, notes, and tags; w/ corresponding pseudo-columns to expose these in report view
    - Full interactive report capabilities, with row highlighting, charting, group by capability, aggregates etc
    - Ability to specify basic data validations
    - Ability to expose help text at the column level to make it clear what the purpose is for columns
    - Basic Access Control, so it can be shared read only, or read write
    - Ability to group attributes / columns into regions in the single row view

    So now I have described Websheets as a fusion of a basic database form and report; and a spreadsheet, with attributes of both. So you get a form and a report on a table, that can be developed without writing SQL, without large complex property sheets, in fact without being exposed to an application development tool. You get the feature function of a data collection spreadsheet, but you miss the spreadsheet formula's and the ability to populate a column of data by cutting one cell and pasting into many cells; so we can't really call it a spreadsheet replacement. You also miss the layout and HTML styling capabilities Oracle APEX forms has; so its a compromise; but you do get a powerful data collection tool.

    Websheet Pages: Sharing a single websheet (aka simple web datagrid) lacks context (e.g. if I was to get a url to a datagrid what should I do, I would prefer to land on a page that tells me what to do); so the next part of the websheet vision was to add basic wiki capability. So websheets have "pages" which are similar to a wiki page in that you can use basic [[mypage]] markup syntax to link one page with another. So the goal of Websheet Pages is to exploit the power of a wiki by providing a workgroup a place to create pages of data (basically a shared word processor on the web with links; e.g. a wiki). These pages are intended to provide the core of a typical websheet application. The unique feature / innovation comes with the ability to integrate the datagrids (data management capabilities) with the websheet pages (adhoc web page authoring / wiki). A logical extension to the websheet page is the SQL tag, e.g. [[SQL: select ename, deptno from emp order by 1]] allowing the SQL savvy user to spice up a web page with SQL with a grid of data (previous example), or a single value, for example: "Currently we have [[SQLVALUE: select count(*) from emp where deptno=10]] employees in department 10.

    Thus the vision of providing a no-programming-skills-required multi user web page authoring tool with data management and database integration capabilities. So a websheet application is very different from a traditional APEX application, here are some of the differences.

    *Oracle APEX Database Application (Classic APEX)*:
    - A developer builds and application and deploys it to users; users request enhancements / bug fixes, and developers redeploy improved applications; e.g. traditional application development / deployment
    - Requires a SQL Savvy developer and some investment in learning basic APEX programming concepts; page layout, regions, templates, branches, processes, session state concepts (e.g. referencing item values in SQL queries).
    - Similar to other application development frameworks (php, jsp, ...) in that applications are developed and deployed; basically a formal application

    *Oracle APEX Websheet*:
    - No (or limited) distinction between end users and developers; end users develop web page content and manage data in datagrids.
    - No exposure to a development tool / environment, just simple small dialogs / forms to add columns to data grids, or add a new page.
    - Similar to a wiki or a multi user spreadsheet; where a community maintains the content / structure; everyone is a developer; its a community place; basically a less formal application

    Perhaps it's best to think of Websheets as a simple wiki like tool that can be augmented with data from the database (e.g. SQL tag) or with locally defined and managed data (e.g. datagrid). This combination of functionality can be used anytime you would use a wiki or simple data collection spreadsheet. The application is not a traditional application; its a community application where all / most users are fully empowered to add pages, datagrids and columns; just like with a wiki. Our vision is that websheets would be used to help manage a project, or anyplace community managed basic page and data content is needed.

    We (Oracle) fully understand that websheets in APEX 4.0 is a "version 1.0" feature; and we are hopeful that through this forum, and other channels our customers / users will help guide the APEX development team in the refinement of websheet vision.

    Edited by: mhichwa on Jul 21, 2010 11:25 AM
  • 7. Re: Websheets?
    VANJ Journeyer
    Currently Being Moderated
    Interesting thread. I have been using Apex for a very long time, since version 1.6 and as such Sara pretty much summed up my thoughts.

    Mike - Thanks for your comments, always helps to get some context from the product team. Like any organization, we have our fair share of spreadsheets but instead of emailing them around and merging changes into the master copy, we store the .xls file on a shared network drive (that is backed up by IT) turn on the sharing features in Excel so multiple users can make concurrent updates. This goes a long way towards alleviating some of the pain usually associated with spreadsheets, doesn't it?

    Your rationale for building websheets as a separate "module" within Apex instead of as a regular, sample database application is primarily for its collaborative, publishing capabilities. Speaking for myself, I think the learning curve and the newness factor for websheets is too high to overcome, shared spreadsheets are simply too attractive to stop using. Besides, for business end-users to create websheets as easily as spreadsheets, wouldn't we need to setup integrated authentication with Windows? Are there plans to add more robust NTLM support to the Support for NTLM? We haven't upgraded to 4.0 yet. Some of the marquee features like dynamic actions and plugins look fantastic, look forward to seeing a community driven plugin repository with all sorts of widgets/apps. But frankly, I don't really see much of a future for the websheet vision, it is similar enough to a spreadsheet so that initial adoption by business users would be painless but as you start to really use it, you find that a real spreadsheet offers more functionality. Besides, if we really wanted more spreadsheet like functionality like formulas, embedded charts, etc, online spreadsheets like Google Docs/Apps, Zoho, etc are much more capable than websheets. Just my 2c.

    Regards
  • 8. Re: Websheets?
    369783 Newbie
    Currently Being Moderated
    Well, before someone on the Apex team chimes in, the 'nifty' thing about Websheets is that they are far much more than just a simple spreadsheet. They also allow pictures, Word documents, pdf's, practically any kind of binary file. They also allow tagging, notes, and other things that spreadsheets are just woefully incapable or otherwise inadequate for.

    This version 1.0 of Websheets is interesting, but at least for my users, I don't don't think they are ready for yet for me to make available for my users. I've started another thread (Websheet questions (suggestions?) with some questions and/or suggestions to make the Websheets more usefull, at least to me anyway, like the ability to let the user uploading a spreadsheet specify a table name, so they will actually become a table within Oracle instead of (at least from what I can see so far), being stored in the database as a BLOB.

    Bill Ferguson

    Edited by: wbfergus on Jul 21, 2010 1:43 PM
  • 9. Re: Websheets?
    mhichwa Newbie
    Currently Being Moderated
    Thanks for the comments and it sounds like you have working solutions for many of the problems websheets is designed to fix. I however stand firm that the vision has merit, perhaps not for everyone, perhaps not in it's version 1 form, but a useful tool.

    -- Using sharing in MS excel on shared network drives gives you full multi user excel

    Not all organizations / users want to do this. For example I use a mac and don't use excel on my mac (my company will not buy it for mac), I also would like to consume this content on my ipad and yet to be developed devices that are likely to not run windows.

    I know the name websheet implies "web spreadsheet"; but contrary to this name our objective was not to build a spreadsheet replacement, our objective was data collection and reporting in the context of a collaborative wiki.

    I am a big fan of having HTML; I understand rich clients like excel, word, photoshop, ultra-edit, SQL Developer etc are all great and all have their place; but for exposing access to database data via information systems, I still prefer the Web. Using HTML works better with firewalls, its a true "universal client". So my mantra is "have web browser with a network connection; and I'm good to go". And, to be clear by web browser I include all popular browsers firefox, IE, Safari, or Chrome.

    Again, I am not advocating everyone do this; just advocating the elegance and rationale for websheets.

    -- wouldn't we need to setup integrated authentication with Windows?

    Well Oracle APEX should support windows authentication better then we do now; and yes the solution is likely to involve the APEX listener. A solution in this space is likely in the future. Once we solve this it will benefit not only websheets but all APEX applications.

    -- building websheets as a separate "module" within Apex instead of ...

    We didn't want to introduce two products; or introduce the concept of a "supported application"; we wanted the elegance of one integrated product. One place to view, manage, and report on all applications (classic database and websheet). Using Oracle APEX 4.0 you can create a websheet in less then 1 minute and distribute the URL via email to others; so most never need to see the APEX builder.

    -- online spreadsheets like Google Docs/Apps, Zoho

    Websheets is not trying to be a spreadsheet; a different name would have been ideal to make this clear, so I understand this association is 100% our fault. We just had a very hard time coming up with a better name.

    Websheets is the unique combination of
    1. community structured data collection and reporting
    2. community managed web pages (aka wiki)
    3. database reporting exposed via community web pages

    If you want an online spreadsheet (formulas etc) APEX websheets is not it, if you want community web pages only, Wiki's are certainly more mature / proven. If you want simplistic database reporting you have other options as well; but if you want the combination of the 3 on the web, accessible using a popular browser, deployable on public and private clouds, etc; websheets is the solution.

    So being realistic I would recommend APEX Websheets for those who are comfortable on the leading edge (e.g. version 1 software); and one-with-the-vision. I think this will be a small number; but I hope overtime the early adopters will help guide the APEX dev team and we will overtime end up with a powerful tool for workgroups.

    Edited by: mhichwa on Jul 21, 2010 3:32 PM
  • 10. Re: Websheets?
    andreml Explorer
    Currently Being Moderated
    Hi there,
    I agree to nearly 100% with Sara and VANJ.
    Just today a colleague and I discussed about the sense and nonsense (sorry) of the worksheet idea.
    Well, as mhichwa stated this is the first version 1.0 of web sheets.
    However, we here agreed what version it ever will live to see it will never be able to replace Excel.
    Having a goal like the illusion to replace Excel in any way only can be fail and will be frustrating not only for Oracle but also for some enthusiastics (still) believing in this illusion.
    From my view the direction should be to make it an easy way for:
    Maintaining simple plain basic data by end users via a web browser, however to be used in „real“ (Apex) applications e.g. Mapping- and Hierarchies-Tables, Include /Exclude lists (as tables)
    What do we need to use it in a useful way?:
    1. A safe and secure and error-free (also for non-American language!) way to copy an past data from any tabular tool (e.g. Excel) into a grid
    2. A opportunity to take over such data (grids) from the BLOB columns into „real“ Oracle tables with less effort in order to (re)use it in any app and any way we want.
    3. Opportunity not only to create such grids but also to remove them (found no way so far)

    From my view the main contradiction is that on one side the user a forced to deal with structured data (in tables) and than on the other hand he is promised to create unstructured contend (like wikis).

    If you manage to make web sheets an easy to use helper(!)-tool for data manipulation by end users this would the be 1.5 version for me. When this idea has been established and is accepted by „normal“ end users the next step could be to make it more excel like in any way one can imagine.
    Make small steps not big promises - That should be the maxim.

    However, Thank for all the great work you have done so far in 4.0!
    Regards
    Andre
  • 11. Re: Websheets?
    mhichwa Newbie
    Currently Being Moderated
    Just to be clear, the goal is not to replace excel, the goal is to create a community managed wiki + structured data management + db reporting tool to facilitate web 2.0 communications between project members (aka the community). The only use case for excel replacement is basic data collection.

    The websheet sample application provides a good use case. You can also view a nicely presented overview on OTN here: http://www.oracle.com/technology/products/database/application_express/html/more_websheets.html you will see an example of how it was envisioned Websheets be used. The sample application shows wiki style pages, organized in hierarchies, fully searchable content via simple search field (upper right), editable by the "community" with inline chart a data pulled from data grids. Full web 2.0 style tagging, annotations etc; all from a browser.

    You also asked some questions.

    1. A safe and secure and error-free (also for non-American language!) way to copy an past data from any tabular tool (e.g. Excel) into a grid

    Are specific characters not working? We have tested with multi byte data; if its not working do you know the character set your database is running in (you can view this by navigating to "Home > Administration > About APEX". A report in the bottom right indicates the character set of your database.

    2. A opportunity to take over such data (grids) from the BLOB columns into „real“ Oracle tables with less effort in order to (re)use it in any app and any way we want.

    The data can be manipulated and converted into "real" tables, although perhaps in a less then perfect way. So if you created a websheet with 3 "string" columns you could run the following SQL in your schema: create table my_table as select c001 a, c002 b, c003 c form APEX$_WS_ROWS where DATA_GRID_ID = <your data grid ID>; You could also laod data using insert statements, and of course update data using updates. An example update: update APEX$_WS_ROWS set c001 = 'Yes' where substr(upper(c001),1,1) = 'Y' where DATA_GRID_ID = <your data grid ID>;

    3. Opportunity not only to create such grids but also to remove them (found no way so far)

    To remove a datagrid (1) run the data grid (2) click manage, (3) click remove data grid

    hope this helps.

    If you don't want web 2.0 style HTML collaboration tools then websheets is not for you, if you do want this and feel websheets is missing functionality let us know. In addition to general refinement, I was thinking:

    1. Drill down from data grid to data grid.
    2. Better control over UI.
    3. Ability to expose a data entry form as part of a workflow: so you could email someone with a link to a data entry page; they would enter a datagrid row; then the UI would simple say "thank you!"; Useful for servey's etc. It would include built in email notification and reminders for users who did not respond.
    4. Email injesting; so you could email a given account like "project_ideas" and it would add the ideas to a data grid and manage the attachments.

    These features would work toward the goal of collecting and managing simple data simply in the context of a community managed web 2.0 web site.

    Mike

    Edited by: mhichwa on Jul 21, 2010 10:18 PM
  • 12. Re: Websheets?
    739036 Newbie
    Currently Being Moderated
    APEX is a brilliant Oracle database development tool, please stick to this route introducing websheets is just a diversion.

    My disappointment is that the time spent of developing websheets could have been better spent.

    Oracle – who’s asked for websheets, can somebody provide a real world example and a real business case?

    Wiki’s, excel etc... people want best of breed – why use websheets ("Jack of all trades, master of none")?

    APEX 4 is very good and all involved deserve great credit, the product is a serious development tool.

    The sooner APEX’s profile gets raised by Oracle the better – its makes jdeveloper look ridiculously complicated.
  • 13. Re: Websheets?
    andreml Explorer
    Currently Being Moderated
    Hello Mike,

    1. Was clarified by chrictina. Thanks again to her.
    3. Thank you! I finaly found it. A little tricky for the first time user, since one expect something different under this menu. However it's Ok now.

    3. Still open questions:
    When I run this query asy SYS
    SELECT * FROM apex_040000.apex$_ws_rows;
    There are no rows.

    When I run this query as Workspace-Owner in APEX SQL Workshop
    select * from APEX$_WS_ROWS;
    I see values.

    When I:
    As SyS grant select on apex_040000.APEX$_WS_ROWS to AAA;
    And as DB-User AAA
    SELECT * FROM apex_040000.apex$_ws_rows
    There are no rows.

    So this seems to work inside APEX only. For far so good.
    But where can I get the ID from for a datagrid name ?
    I would say there has to be another abstraction layer for the kind of usage we are talking about.
    Can you provide such thing in a patch, please.
    From that point onwards it would fullfill my basic requirements.

    Regards
    Andre
  • 14. Re: Websheets?
    429923 Journeyer
    Currently Being Moderated
    Andre,

    With your websheet running, go to Data > Data Grid and click the data grid you are interested in. The end of the url will contain WS_APP_ID,P2_ID,P2_WEBSHEET_ID - those identify the datagrid and can be used as follows:

    select * from APEX$_WS_ROWS
    where ws_app_id = :WS_APP_ID
    and data_grid_id = :P2_WEBSHEET_ID

    Obviously, you need to replace with the values - I was just trying to show you the mapping.

    -- Sharon
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points