====== Generating Reports From a Xojo Web Application ======

<note>
[[http://youtu.be/iSdF-FBUGHo | VIDEO OF THIS TUTORIAL]]
</note>

===== Overview =====

In this tutorial you will create a simple Xojo web application that uses Valentina Reports engine to produce pdf reports on the fly, then presents it to the user.

The best way to generate reports from web applications is to use Valentina Server, as it works for both CGI and Standalone development of Xojo applications.

You will go through the following big steps:
  * Download and register an existing database on Valentina Server
  * Create a new server project, datasource, query, and report
  * Create a new Xojo web application
 

===== Requirements =====

The following applications and components should be already installed and configured:

  * Xojo IDE
  * V4RB plugin
  * Valentina Server
  * Valentina Studio

===== Step 1: Prepare a Database =====

==== Download ====

  * Go to "/Library/VServer_64" directory, "projects" folder is empty and there is only one system "master.vdb" database in "databases" folder

{{:valentina:vcomponents:vreport:tutorials:xojo_vserver_initial.png|}}

  * Download [[valentina:products:vstudio:examples:databases:sakila | valentina_sakila.vdb ]] sample database
  * Copy it into "databases" folder

{{:valentina:vcomponents:vreport:tutorials:xojo_vserver_database.png|}}

==== Register Database ====

  * Start Valentina Studio
  * Switch to Bonjour section in the Servers column of the Start Page
  * Click on the first row to open simple, not secured connection

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_bonjour.png|}}

  * Provide login and password, "sa"/"sa" by default

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_login.png|}}

  * Double click the database to register it on the server

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_databases_unregistered.png|}}

  * Double click the database to open it

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_databases_registered.png|}}

Now that you have a sample database, registered and prepared to create a report on it.

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_database_opened.png|}}

===== Step 2: Prepare the Valentina Reports Server Project =====

==== Create a Project ====

  * Right click on the connection and select Create->Project... in the contextual menu

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_project_menu.png|}}

  * Input "xojo_web_reports" and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_project.png|}}

You get the following empty project:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_empty_project.png|}}
  
==== Create a Datasource ====

  * Click Create... button and select Datasource... in the dropdown menu

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_datasource_menu.png|}}

  * Select "valentina_sakila" and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_datasource.png|}}

The result is the new datasource:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_created_datasource.png|}}

==== Create a Query ====

  * Click on Create... button and select Query... in the dropdown menu
  * Input name "Query_Actors" and query, like on the following picture, and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_query.png|}}

A newly created query appears on the project tab:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_created_query.png|}}

==== Create a Report ====

  * Click Create... button and select Report... in the dropdown menu
  * Input name "Report_Actors" and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_name.png|}}

  * Select the following regions to show and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_regions.png|}}

  * Select query "Query_Actors" and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_source_data.png|}}

  * Select fields and set captions, as shown on the picture, and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_fields.png|}}

  * There is no grouping in the report, so click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_grouping.png|}}

  * Define a sort order and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_sorting.png|}}

  * Define a layout and click Next

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_layout.png|}}

  * Select desired style and click Finish

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_create_report_style.png|}}

The result displays the report:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_report_editing.png|}}

Click Preview to make sure that it is working properly

{{:valentina:vcomponents:vreport:tutorials:xojo_web_vstudio_report_preview.png|}}

===== Step 3: Prepare a Xojo Project =====

==== Create a Project ====

  * Run the Xojo IDE application
  * Select Web section
  * Input "Xojo_Web_Reports" as a name of a new project and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_project_name.png|}}

==== Prepare a User Interface ====

The user interface of the web page is simple in this example: one rectangle, one label and one button, which starts report generation.

  * Open the Library panel

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_library.png|}}

  * Drag the Rectangle control onto the web page
  * Place the Label control and input some description:
  
{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_label.png|}}

  * Drag the button from the Library over rectangle and change the label to "PDF", adjust other controls

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_button.png|}}

==== Init Valentina Client ====

Any application must call a special Valentina initialization method that sets the size of cache, and the serial number (except for a client connection, which doesn't need a serial). It can be Valentina.Init, Valentina.InitClient, Valentina.InitReports, depending on the kind of application and used technologies.

In this step, the web application communicates directly with Valentina Server, so only the Valentina.InitClient call is needed at this point.

The best place for this call is in an Open Event Handler of the application. This approach works with all kinds of deployment.

  * Right click on the "App", select Add to "App"->Event Handler...

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_add_app_event_handler.png|}}

  * Select Open handler and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_select_handler_open.png|}}

  * Input the following code into the handler body:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_handler_open.png|}}

==== Shutdown Valentina Client ====

Before an application is closed, it is a good practice to call the Valentina shutdown method. It can be placed into the Close Event Handler of the application.

  * Right click on the "App", select Add to "App"->Event Handler...
  * Select Close handler and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_select_handler_close.png|}}

  * Input the following code into the handler body:

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_handler_close.png|}}

==== Prepare File Download ====

To make generated report available for download it is necessary to declare WebFile property in the WebPage instance.
Thus, a link for file download will be valid during page's lifecycle.

  * Make a right click on the "App", select Add to "App"->Property

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_add_property_menu.png|}}

  * Input "mPDF" as a name, "WebFile" as a type

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_property.png|}}

==== Add a Button Event Handler ====

Report generation is initiated by clicking on the "PDF" button, to do it:

  * Double click the "PDF" button
  * Select Action in the list of handlers and click OK

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_button_handler.png|}}

==== Generation Logic ====

Now implement then main generation logic in the button event handler

  * Open a new connection to a Valentina Server:

<code>
Dim conn As VConnection = New VConnection( "127.0.0.1", "sa", "sa" )
conn.Open
</code>

  * Open a project, we've created in the Step 2:

<code>
Dim projPath As FolderItem = GetFolderItem( "xojo_web_reports.vsp" )
Dim proj As VProject = New VProject( conn, projPath )
proj.Open
</code>

  * Define a datasource string, query and print report as PDF to RAM:

<code>
Dim datasource As String = "vserver://host='localhost' port='15432' dbname='valentina_sakila' user='sa' password='sa'"
Dim report As VReport = proj.MakeNewReport( "Report_Actors", datasource, "SELECT * FROM actor"  )
Dim data As String = report.PrintToBuffer( EVReportPrintType.kToPDF, 1 )
</code>

  * Close project and connection:

<code>
proj.Close
conn.Close
</code>

  * Prepare file download:

<code>
mPDF = New WebFile
mPDF.MimeType = "application/pdf"
mPDF.FileName = "report.pdf"
mPDF.ForceDownload = False
mPDF.Data = data
</code>

  * Show generated report in the browser:

<code>
ShowURL( mPDF.URL )
</code>

The result appears as the following...

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_main.png|}}

===== Step 4: Testing =====
 
Now test your application.

  * Click Run button

{{:valentina:vcomponents:vreport:tutorials:xojo_web_xojo_run.png|}}

  * Click PDF button to generate a report

{{:valentina:vcomponents:vreport:tutorials:xojo_web_browser.png|}}

  * A generated PDF file appears

{{:valentina:vcomponents:vreport:tutorials:xojo_web_browser_pdf.png|}}

Download resulting projects:
{{:valentina:vcomponents:vreport:tutorials:xojo_web_reports.zip|xojo_web_reports.zip}}.