|
1. Introduction
In this tutorial the creation of a simple Exact Synergy Enterprise report will be described.
2. Getting started
The application that we are writing in this tutorial will show you a simple report on the cost centers. The report needs to show a list of cost centers that can be filtered on Division and will enable the user to change the sort order.
This tutorial will contain the following steps:
1. Create the initial aspx
2. Setting up the basic selection values and filter options
3. Build the SQL statement, based on the filter options
4. Show the result in the list
5. The finishing touch
3. Writing the application
3.1 Create the initial aspx
The initial aspx page is shown below. It can serve as a template for all reports to be created.
|
<%@ Page Language="VB" %>
<%@ Register TagPrefix="ex" Namespace="Exact.Web.UI.Controls" Assembly="Exact.Web.UI.Controls" %> (1)
<%@ import Namespace="Exact.Data" %>
<%@ import Namespace="Exact.Common" %> (2)
<%@ import Namespace="Exact.Core" %>
<script runat="server" >
(3)
</script>
<html>
<head>
<meta name="save" content="history" > (4)
<link rel="stylesheet" type="text/css" href="Exact.css" > (5)
</head>
<body>
<form runat="server" id="frm" > (6)
</form>
</body>
</html>
|
1. <@ Register ... @> tag, this is required if you want to use any of the Exact Server controls
2. <@ import ... @> tag, this is required if you want to make use the namespaces directly in your document
3. <script runat="server"> ... </script> block. In this script block the VB code to be execute at the Server will be added. This script block must be the first tag or first tag after the html tag in you aspx file to make sure there is a clear separation between code and the user interface
4. <meta name="save" content="history">, necessary to make sure the Exact Server controls keep their state when the browser back and forward buttons
5. <link rel="stylesheet" .... >, make sure the correct "Exact.css" is used. The Exact Server controls rely on this style sheet
6. <form runat="server"> ... </form> block. An aspx file must have this tag even if you don't use any Exact Server controls
Running this page results in a blank screen in the browser. It's time to add functionality.
3.2 Setting up the basic selection values and filter options
In the <form runat="server" ... > now add controls for the header and selection values
|
<form runat="server" id="frm">
<ex:header runat="server" id="hdr" caption="Cost Centers" captionid="3384">
<ex:headerbutton id="hbFavorite" buttonstyle="Favorite" /> (1)
<ex:headerbutton id="hbHelp" buttonstyle="Help" />
</ex:header>
<ex:buttonbar runat="server">
<ex:button runat="server" id="btnRefresh" buttonstyle="Refresh" buttontype="Submit" /> (2)
<ex:button runat="server" id="btnReset" onclick="Search_Reset();SysSubmit();"
buttonstyle="Reset" /> (3)
</ex:buttonbar>
<ex:search runat="server" id="Search" action="Report" layoutcolumns="3">
<ex:searchfields>
<ex:searchfield caption="Division" captionid="64">
<ex:browsefield runat="server" id="Division" autopostback="True"
standardbrowser="Division" enablesetting="True" /> (4)
</ex:searchfield>
<ex:searchfield caption="Show" captionid="8804">
<ex:combobox runat="server" id="Status" autopostback="True" validationas="Int"
validationlowerrange="0" validationupperrange="2" validationdefault="1"
allowempty="False">
<ex:listitem caption="All" captionid="5614" value="2" />
<ex:listitem caption="Active" captionid="2212" value="1" />
<ex:listitem caption="Inactive" captionid="29544" value="0" />
</ex:combobox>
</ex:searchfield>
</ex:searchfields>
</ex:search>
|
In the HTML code you see:
1. Header control, completely translated and with the upper right buttons. You can add extra buttons without any programming required
2. Refresh button, the 'buttonstyle' property automatically defaults the image and translated text shown on the button.
3. Reset button, this will reset the search fields automatically
4. Browse control, you can provide a SQL statement for the reference value, or make use of the predefined browsers. No additional programming is required
It's best to close the Exact Server controls with the shortcut "/>" instead of the full tag.
When you run the aspx file you notice that the values of the controls are automatically preserved during post back to the server. You don't need to do anything to make sure the values are preserved.
The controls are automatically initialized with the parameters from the URL, you can try this:
· http://server/site/HRMCostcenters.aspx (no parameters)
· http://server/site/HRMCostcenters.aspx?Division=001, default values for the browser and check box.
Now the selection values and filter are working, it's time to build a SQL statement for display of the list.
3.2 Building the SQL statement, based on the filter options
Building the SQL statement requires programming, lets get started. Now the <script runat="server"> ... </script> is important (remember from step 1)
In the <script runat="server"> ... </script> block add:
|
<script runat="server">
' Enumerations
Private Enum Fields (1)
Code
Description
Active
Division
DivisionName
End Enum
Private Enum States
Active
Inactive
All
End Enum
Private Sub OnGetData(ByVal sender As Object, ByVal e As EventArgs) (2)
' Build the SQL statement
Dim qb As New QueryBuilder(conn) (3)
With qb
.Top = List.PageSize
.Select = "c.kstplcode, c.oms25_0, c.Enabled"
.AppendSelect("d.bedrnr, d.bedrnm")
.From = "kstpl c"
.AppendFrom(JoinType.LeftOuter, "bedryf d", "c.bedrnr = d.bedrnr")
' Where clauses based on the filter section
.AppendWhere("c.bedrnr", Division.Value)
If Status.Value <> States.All Then .AppendWhere("c.Enabled", Status.Value)
' Append sorting mechanism
Select Case List.SortColumn
Case "colCode" ' Do Nothing
Case "colDescription"
.AppendOrderBy("c.oms25_0", List.SortDescending)
End Select
qb.AppendOrderBy("c.kstplcode", List.SortDescending)
' Append paging mechanism
Dim vPaging As Object = List.Paging.Value
If Not vPaging Is Nothing Then
Dim sWhere As New StringBuilder()
Dim Sign As String = " > ": If List.SortDescending Then Sign = " < "
With sWhere
Select Case List.SortColumn
Case "colCode"
.Append("c.kstplcode" & Sign & conn.Literal(vPaging))
Case "colDescription"
.Append("c.oms25_0" & Sign & conn.Literal(vPaging(0)))
.Append(" OR (c.oms25_0 = " & conn.Literal(vPaging(0)))
.Append(" AND c.kstplcode" & Sign & conn.Literal(vPaging(1)) & ")")
End Select
End With
.AppendWhere("(" & sWhere.ToString() & ")")
End If
End With
|
In the code you should note:
1. An enumeration containing the columns in the result set. ASP.Net now allows the use of enumerations. The enumeration will be used when displaying the result set. This approach is recommended because will make the code more readable.
2. When the listview is rendered it will trigger the ongetdata event. This event will point to a function on your page like shown above. The function is used for retrieving the data and fills the table with it.
3. The querybuilder object is used to create a query that is compatible for usage on the support DBMS. This way you can build a query for MSSQL and it will also work with Oracle.
Now the results are retrieved, lets display the results
4. Show the result in the list
Displaying the result requires:
1. A listview control for creating a right formatted table with sorting and paging support
2. Code to write the query result to the listview
The code below should be pasted after the search control
|
<ex:listview runat="server" id="List" ongetdata="OnGetData" defaultsortcolumn="colCode"
showpagesize="True" pagesize="50" autopostback="True" hidecaption="True">
<ex:listviewcolumn id="colCode" caption="Code" captionid="4138" enablesorting="True" />
<ex:listviewcolumn id="colDescription" caption="Description" captionid="4243" enablesorting="True" />
<ex:listviewcolumn id="colDivision" caption="Division" captionid="64" />
<ex:listviewcolumn id="colActive" caption="Active" captionid="2212" />
</ex:listview>
</form>
|
This should be inserted after building the query in the OnGetData function
|
' Execute the query
Dim data(,) As Object = conn.Query(qb.SQL())
' Fill the list with the data returned from the query
If Not IsNothing(data) Then
With List
For i As Integer = 0 To UBound(data)
.RowStart(i)
.RowCellStandardRef(data(i, Fields.Code), StandardRef.Costcenter, data(i, Fields.Code))
.RowCell(data(i, Fields.Description))
If Len(Division.Value) = 0 Then
If IsNothing(data(i, Fields.Division)) Then
.RowCell("")
Else
.RowCellStandardRef(data(i, Fields.Division) & " - " _ & data(i,
Fields.DivisionName), StandardRef.DivisionDossier, data(i, Fields.Division))
End If
End If
.RowCellCheck(data(i, Fields.Active), CheckMark.Both)
.RowEnd()
Next
If UBound(data) + 1 >= List.Pagesize Then
Select Case .SortColumn
Case "colCode"
.Paging.Value = data(UBound(data), Fields.Code)
Case "colDescription"
.Paging.Value = New Object() {data(UBound(data), Fields.Description),
data(UBound(data), Fields.Code)}
End Select
End If
End With
End If
End Sub
</script>
|
3.5 The finishing touch
The last thing to do is take control on when to show data or not. The example below will hide the division column when it is not selected in the search field. And it will initialize the paging mechanism used in the application.
|
Private Sub Page_Load()
' Initialize the result list
Select Case List.SortColumn
Case "colCode"
List.Paging.ValidationAs = ValidationAs.None
Case Else
List.Paging.Validations = New ValidationAs() {ValidationAs.None, ValidationAs.None}
End Select
List.Column("colDivision").Visible = Len(Division.Value) = 0
End Sub
|
4. Conclusion
The Exact Server controls have a lot of basic functionality, what allows the application developer to concentrate on solving the business problems instead of writing heaps of code to get it all to work.
4.1 Often made mistakes when getting started
· No runat="server" attribute specified
· No id="..." attribute specified
Applies to
· Exact Synergy Enterprise
|