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.