Orbeon Forms community mailing list

How to extract data from Orbeon database for reports?

Classic

List

Threaded

5 messages Options

VBerget

How to extract data from Orbeon database for reports?

Hi,

We are considering using Orbeon for a new project where forms filled in by users are a central part of the solution. We have done som testing and buildt a few forms, but here is a one question that we have:

As I understand it, the results from filling out a form is stored as a single XML-entry in the database (is this correct?).
What we need to do is generate a number of reports that summarizes data from all "instances" of a filled out form. Example: what percentage chose alternative 'A' in a specific form
In a pure SQL world, an example of what we need would be something like "select result_of_question_xx from forms_result where form_name = 'YYY'

I see that the SQL Processor allows SQL-queries against the database. Can this be used to query the forms results, or do we need to use another mechanism to query for data like this?

(The database will probably be Oracle)

bsteuhl

Re: How to extract data from Orbeon database for reports?

If you are using form-runner then yes Forms are stored as single xml file in eXist XML database out of the box. You can have a different persistence layer I believe but you would have to follow the documentation for this. You would have to use xquery to get the reports you want if data is in XML format. I develop outside of form runner and you can have your data stored in SQL database with SQL processor and then build SQL reports. We still store our data in eXist as single xml file and run xquery for different reports in combination with some KPI data being stored in MySQL from within Intalio BPM.

Brian Steuhl
Website: http://BTMSoftwareSolutions.com
Business Email: [hidden email]
Cell: 908-421-0742

Home Office: 732-961-3187

Google Voice: 732-800-1286 (1BTM)
RSS Feed To My Blog:

From: VBerget <[hidden email]>
To: [hidden email]
Sent: Sun, January 9, 2011 4:31:36 PM
Subject: [ops-users] How to extract data from Orbeon database for reports?

Hi,

We are considering using Orbeon for a new project where forms filled in by
users are a central part of the solution. We have done som testing and
buildt a few forms, but here is a one question that we have:

As I understand it, the results from filling out a form is stored as a
single XML-entry in the database (is this correct?).
What we need to do is generate a number of reports that summarizes data from
all "instances" of a filled out form. Example: what percentage chose
alternative 'A' in a specific form
In a pure SQL world, an example of what we need would be something like
"select result_of_question_xx from forms_result where form_name = 'YYY'

I see that the SQL Processor allows SQL-queries against the database. Can
this be used to query the forms results, or do we need to use another
mechanism to query for data like this?

(The database will probably be Oracle)

--
View this message in context: http://orbeon-forms-ops-users.24843.n4.nabble.com/How-to-extract-data-from-Orbeon-database-for-reports-tp3206331p3206331.html
Sent from the Orbeon Forms (ops-users) mailing list archive at Nabble.com.

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Erik Bruchez

Re: How to extract data from Orbeon database for reports?

Administrator

In reply to this post by VBerget

We have an Oracle XML persistence layer:

http://wiki.orbeon.com/forms/doc/developer-guide/form-runner/oracle-persistence-layer

Data is stored as XML into fixed tables, but you could create Oracle
relational views for your reporting tool.

-Erik

On Sun, Jan 9, 2011 at 1:31 PM, VBerget <[hidden email]> wrote:

>
> Hi,
>
> We are considering using Orbeon for a new project where forms filled in by
> users are a central part of the solution. We have done som testing and
> buildt a few forms, but here is a one question that we have:
>
> As I understand it, the results from filling out a form is stored as a
> single XML-entry in the database (is this correct?).
> What we need to do is generate a number of reports that summarizes data from
> all "instances" of a filled out form. Example: what percentage chose
> alternative 'A' in a specific form
> In a pure SQL world, an example of what we need would be something like
> "select result_of_question_xx from forms_result where form_name = 'YYY'
>
> I see that the SQL Processor allows SQL-queries against the database. Can
> this be used to query the forms results, or do we need to use another
> mechanism to query for data like this?
>
> (The database will probably be Oracle)
>
>
> --
> View this message in context: http://orbeon-forms-ops-users.24843.n4.nabble.com/How-to-extract-data-from-Orbeon-database-for-reports-tp3206331p3206331.html
> Sent from the Orbeon Forms (ops-users) mailing list archive at Nabble.com.
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws
>
>

VBerget

Re: How to extract data from Orbeon database for reports?

Erik Bruchez wrote

We have an Oracle XML persistence layer:

http://wiki.orbeon.com/forms/doc/developer-guide/form-runner/oracle-persistence-layer

Data is stored as XML into fixed tables, but you could create Oracle
relational views for your reporting tool.

-Erik

As somenone mentioned, we are indeed using Form Runner (and Oracle).

If I understand you correctly we can use the Oracle XML Persistance Layer and then use something like XMLTable in Oracle to create relational views on the same data?
That sounds like a good strategy to me, seems I need to study XMLTable more in detail

- Vidar

Erik Bruchez

Re: Re: How to extract data from Orbeon database for reports?

Administrator

Vidar,

I am not sure exactly how you create such a view, but I am positive we
have done this in the past! Oracle even has materialized views which
might improve performance: http://goo.gl/Z7Je

-Erik

On Tue, Jan 11, 2011 at 1:18 AM, VBerget <[hidden email]> wrote:

>
>
> Erik Bruchez wrote:
>>
>> We have an Oracle XML persistence layer:
>>
>>
>> http://wiki.orbeon.com/forms/doc/developer-guide/form-runner/oracle-persistence-layer
>>
>> Data is stored as XML into fixed tables, but you could create Oracle
>> relational views for your reporting tool.
>>
>> -Erik
>>
>>
>
> As somenone mentioned, we are indeed using Form Runner (and Oracle).
>
> If I understand you correctly we can use the Oracle XML Persistance Layer
> and then use something like XMLTable in Oracle to create relational views on
> the same data?
> That sounds like a good strategy to me, seems I need to study XMLTable more
> in detail
>
> - Vidar
>
> --
> View this message in context: http://orbeon-forms-ops-users.24843.n4.nabble.com/How-to-extract-data-from-Orbeon-database-for-reports-tp3206331p3208656.html
> Sent from the Orbeon Forms (ops-users) mailing list archive at Nabble.com.
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws
>
>