Article Details

Id:	11104
Product:	finPOWER Connect
Type:	NEW
Version:	1.06.08
Opened:	24/04/2013
Closed:	24/04/2013
Released:	28/05/2013
Job:	J011507

Documents; Script can now create and use finReports library

Documents Scripts can now create and use finReports library, e.g., to utilise the Account List query.

A new option 'Allow Script to create and use Scripts and Queries' must be checked on the Document page to allow the Script access to the finReports library.

The following sample Script can be used by a Document configured as follows:

Type: Account
Allow Script to create and use Scripts and Queries: True
Show in Report Explorer: True
Parameters: Custom

Option Explicit On
Option Strict On

Private mQuery As ISQueryBase
Private mReports As ISfinReports

Public Function Main(ByVal source As Object, _
                     ByVal eventId As String, _
                     ByVal eventArgs As ISKeyValueList, _
                     ByRef handled As Boolean, _
                     ByVal parameters As Object, _
                     ByRef returnValues As ISKeyValueList, _
                     ByRef text As String) As Boolean

  ' Assume Success
  Main = True

  ' Create Reporting Layer
  mReports = New ISfinReports(finBL)
  mReports.CreateQuery(isefinQueryType.AccountList, mQuery)

  ' Handle Events
  Select Case eventId
    Case "AfterParametersInitialise"
      ' Create Document Parameters from Query Parameters
      With DirectCast(parameters, ISParameters)
        .AddParameters(mQuery.Parameters, True)
      End With

    Case "Publish"
      ' HTML Document is being published
      Main = Publish(DirectCast(parameters, ISKeyValueList), text)
  End Select

End Function

Private Function Publish(ByVal parameters As ISKeyValueList, _
                         ByRef html As String) As Boolean

  Dim drv As DataRowView 
  Dim sb As StringBuilder 

  ' Assume Success
  Publish = True

  ' Initialise
  sb = New StringBuilder()
  
  ' Update Query Parameters from Document Parameters
  mQuery.Parameters.UpdateFromKeyValueList(parameters)
  
  ' Update Query Columns to include (these names can be seen when running the
  ' Account List query Or report And looking at the exportable columns list
  mQuery.Columns.SetIncluded("AccountId,AccountName,Balance")
  
  ' Execute Query
  Publish = mQuery.Execute()
  
  ' Output HTML
  If Publish Then
    sb.AppendLine("<html>")
    sb.AppendLine("<body>")
    
    sb.AppendLine("<table>")
    sb.AppendLine("<thead>")
    sb.AppendLine("<th align='left'>Account</th>")
    sb.AppendLine("<th align='left'>Name</th>")
    sb.AppendLine("<th align='right'>Balance</th>")
    sb.AppendLine("</thead>")
    sb.AppendLine("<tbody>")
    
    For Each drv In mQuery.DataSet.Tables(0).DefaultView
      sb.AppendLine("<tr>")
      
      sb.AppendFormat("<td align='left'>{0}</td>", finBL.HtmlEncode(finBL.Database.GetFieldString(drv!AccountId)))
      sb.AppendFormat("<td align='left'>{0}</td>", finBL.HtmlEncode(finBL.Database.GetFieldString(drv!AccountName)))
      sb.AppendFormat("<td align='right'>{0}</td>", finBL.HtmlEncode(finBL.FormatCurrency(finBL.Database.GetFieldDecimal(drv!Balance))))
      
      sb.AppendLine("</tr>")
    Next
  
    sb.AppendLine("</tbody>")
    sb.AppendLine("</table>")
    
    sb.AppendLine("</body>")
    sb.AppendLine("</html>")
    
    html = sb.ToString()
  End If

End Function

NOTE: This sample presents the User will the parameters defined on the Account List query but there is no reason that the Document could not define it's own sub-set of the parameters and update the query's parameters.