This section describes how to create the three portlets needed for the server-side click-across example. You set up a master “parent” portlet and two “child,” or drill-down portlets, using database elements that extract data from sampledb. The three portlets are:
SSCA Master – displays a list of departments within an organization.
SSCA Department – displays the departments in the organization.
SSCA Employee – displays a list of employees.
The completed example lets you click the department name to display a list of that department’s employees, and click the manager’s name to display information about the manager.
You extend the functionality by assigning a server-side click-across event that lets you click on an employee name to display details about that employee.
Creating the SSCA Master portlet
Define the SSCA Master portlet that displays a list of departments within an organization. The portlet has two columns—one for department name, and one for department manager data.
Log in to Web Studio.
Select Portlets from the Build menu in the left pane, then click New to launch Portlet Builder.
Click the arrow to the right of the Add button and select Database Element.
In the Database Elements Definition window, enter Java Database Connection (JDBC) information. The JDBC connection allows the master portlet to access a database and extract specific database information.
Select the JDBC URL option.
Complete these options:
Username – enter dba,
which is the user name used to access the database.
Password – enter SQL,
which is the password used to access the database.
JDBC Connect URL – enter the URL used by JDBC to connect to the database:
jdbc:sybase:Tds:localhost:4747?ServiceName=sampledb
JDBC driver – enter the JDBC driver used to connect to the database; in this case:
com.sybase.jdbc2.jdbc.SybDriver
SQL Query String – enter the following SQL code to select department heads for each department from sampledb:
select d.dept_name as 'Dept Name', e.emp_fname + ' ' + e.emp_lname as 'Dept Head' from department d, employee e where d.dept_head_id = e.emp_id
UI XSLT – leave this field empty.
Click Preview. A two column table showing Dept Name and Dept Head displays in the Preview panel.
Dept Name |
Dept Head |
|---|---|
R & D |
David Scott |
Sales |
Judy Snow |
Finance |
Mary Anne Shea |
Marketing |
Scott Evans |
Shipping |
Jose Martinez |
Click Next. When you see the Split window, click Next to bypass these options.
When the Define window display, complete these options:
Record Layout – select Rows.
Record Contains Labels – select this option.
Labels Are Displayed in Record – enter
the numeral 1. This option displays
after you select Record Contains Labels.
Transpose Rows and Columns – changes the vertical range of cells to a horizontal range, and the horizontal range of cells to a vertical range. Leave this option unselected (unchecked) for this example.
Click Next.
When the Filter window displays, click Next.
When the window displays where you define variables, click Next to bypass the page. This example does not use variables.
The Window Preview displays.
Enter SSCA Master for
the Element Name. Under Window Preview, notice the grid showing Dept
Name and Dept Head.
Click Next.
In the Continuous Capture Page window, click Finish.
When you return to the Portlet Builder, click Save.
In the Finish window, complete the fields or options on each tab:
Name – enter SSCA
Master.
Content Type, Charset, Content Cache Interval – accept the defaults shown.
Parameter, Secure – leave these options unselected.
In Context – this option is selected by default.
Roles tab: click Add All to add all of the available roles to the assigned roles list, which specifies that only users with these roles can access this portlet.
Presentation tab: select No Popup and accept the defaults for the remaining options and fields.
Click Finish.
When you see a message stating that the portlet was successfully saved, click OK.
Click Close to exit the Portlet Builder.
When you return to the Web Studio main window, select New from the Portlet Builder Status menu. The SSCA Master portlet displays in the detail pane.
Right-click the SSCA Master portlet and select Approval Status | Approved.
When the message displays that the portlet was saved successfully, click OK.
Select Approved from the Portlet Manager Status menu. You see your newly approved SSCA Master portlet.
Creating the SSCA Department portlet
Define one of the drill-down portlets—SSCA Department—that displays a list of a department’s employees. The portlet uses an @OP tag to dynamically select rows from the database and replace the Dept Name parameter.
In Web Studio, select Portlets from the Build menu in the left pane, then click New to launch Portlet Builder.
Click the arrow to the right of the Add button and select Database Element.
In the Database Elements Definition window, enter Java Database Connection (JDBC) information. The JDBC connection allows the master portlet to access a database and extract specific database information.
Select the JDBC URL option.
Complete these options:
Username – enter dba,
which is the user name used to access the database.
Password – enter SQL,
which is the password used to access the database.
JDBC Connect URL – enter the URL used by JDBC to connect to the database:
jdbc:sybase:Tds:localhost:4747?ServiceName=sampledb
JDBC driver – enter the JDBC driver used to connect to the database; in this case:
com.sybase.jdbc2.jdbc.SybDriver
SQL Query String – enter the following SQL code to select department heads for each department from sampledb:
select e.emp_fname + ' ' + e.emp_lname as Employee from employee e, department d where e.dept_id = d.dept_id and '@OP["Dept Name"="Shipping"]' = d.dept_name
Notice the @OP tag. This indicates that the portlet uses a parameter (Dept Name) to replace the tag at run time. If the parameter is not defined, the @OP tag is replaced by the default value, Shipping. This ensures that previews, when parameters are not explicitly defined, result in returning some valid data.
UI XSLT – leave this field empty.
Click Preview. A one column table displays the Employee list in the Preview panel.
Click Next. When you see the Split window, click Next to bypass these options.
When the Define window display, complete these options:
Record Layout – select Rows.
Record Contains Labels – select this option.
Labels Are Displayed in Record – enter
the numeral 1. This option displays
after you select Record Contains Labels.
Transpose Rows and Columns – changes the vertical range of cells to a horizontal range, and the horizontal range of cells to a vertical range. Leave this option unselected (unchecked) for this example.
Click Next.
When the Filter window displays, click Next.
When the window displays where you define variables, specify the @OP tag as a portlet parameter and define Dept Name as the default value. Complete these options and fields:
Variable – select the Variable option to the left of Dept Name.
Display Name – enter or accept Dept
Name.
Default Value – enter or select Shipping.
Type – select or accept Text Field.
Personalize, Key – leave these fields blank.
Kind – accept the Search default to only list the values but not update those values to the database.
Selections other than Search only apply to “update” portlets
that enable users to update a database from a mobile browser.
Required – verify that this option is selected. As a subscriber portlet, it requires the parameters that are sent by the publisher portlets.
Click Next. The Window Preview displays.
Enter SSCA Department for
the Element Name, then click Next.
In the Continuous Capture Page window, click Finish.
When you return to the Portlet Builder, click Save.
In the Finish window, complete the fields or options on each tab:
Name – enter SSCA
Department.
Content Type, Charset, Content Cache Interval – accept the defaults shown.
Parameter, Secure – leave these options unselected.
In Context – this option is selected by default.
Roles tab: click Add All to add all of the available roles to the assigned roles list, which specifies that only users with these roles can access this portlet.
Presentation tab: select No Popup and accept the defaults for the remaining options and fields.
Click Finish.
When you see a message stating that the portlet was successfully saved, click OK.
Click Close to exit the Portlet Builder.
When you return to the Web Studio main window, select New from the Portlet Builder Status menu. The SSCA Department portlet displays in the detail pane.
Right-click the SSCA Department portlet and select Approval Status | Approved.
When the message displays that the portlet was saved successfully, click OK.
Select Approved from the Portlet Manager Status menu. You see your newly approved SSCA Department portlet.
Creating the SSCA Employee portlet
Define the second drill-down portlet, SSCA Employee.
In Web Studio, select Portlets from the Build menu in the left pane, then click New to launch Portlet Builder.
Click the arrow to the right of the Add button and select Database Element.
In the Database Elements Definition window, enter Java Database Connection (JDBC) information. The JDBC connection allows the master portlet to access a database and extract specific database information.
Select the JDBC URL option.
Complete these options:
Username – enter dba,
which is the user name used to access the database.
Password – enter SQL,
which is the password used to access the database.
JDBC Connect URL – enter the URL used by JDBC to connect to the database:
jdbc:sybase:Tds:localhost:4747?ServiceName=sampledb
JDBC driver – enter the JDBC driver used to connect to the database; in this case:
com.sybase.jdbc2.jdbc.SybDriver
SQL Query String – enter the following SQL code to select department heads for each department from sampledb:
select 1 as ID, 'Employee ID' as Item, convert(varchar(255), emp_id) as Data from employee where '@OP["Emp Name"="Fran Whitney"]' = emp_fname + ' ' + emp_lname
UNION
select 2 as ID, 'Employee Name', emp_fname + ' ' + emp_lname from employee e where '@OP["Emp Name"="Fran Whitney"]' = e.emp_fname + ' ' + e.emp_lname
UNION
select 3 as ID, 'Manager Name', e2.emp_fname + ' ' + e2.emp_lname from employee e, employee e2 where e2.emp_id = e.manager_id and '@OP["Emp Name"="Fran Whitney"]' = e.emp_fname + ' ' + e.emp_lname
UNION
select 4 as ID, 'Dept Name', d.dept_name from employee e, department d where e.dept_id = d.dept_id and '@OP["Emp Name"="Fran Whitney"]' = e.emp_fname + ' ' + e.emp_lname
UNION
select 5 as ID, 'Birthdate', convert(varchar(255), birth_date) from employee e where '@OP["Emp Name"="Fran Whitney"]' = e.emp_fname + ' ' + e.emp_lname
order by 1
This includes the @OP tag “Emp Name”, which sets the default value to Fran Whitney. You define the default value later to complete the process.
UI XSLT – leave this field empty.
Click Preview. A table showing Employee ID, Employee Name, Manager Name, Dept Head and Birthdate displays in the Preview panel.
Click Next. When you see the Split window, click Next to bypass these options.
When the Define window display, complete these options:
Record Layout – select Rows.
Record Contains Labels – select this option.
Labels Are Displayed in Record – enter
the numeral 1. This option displays
after you select Record Contains Labels.
Transpose Rows and Columns – changes the vertical range of cells to a horizontal range, and the horizontal range of cells to a vertical range. Leave this option unselected (unchecked) for this example.
Click Next.
In the Filter window, define a filter that excludes column (field) 1.
In the left-most drop-down list, select “Exclude field(s).”
Make sure the second drop-down list is set to “number.”
In the text box, enter 1 to
indicate you only want to exclude column 1.
Click Add. In Preview, field 1 is highlighted in blue, and a new rule is added under Current Filter Rules.
Click Next.
When the window displays where you define variables, specify the @OP tag as a portlet parameter and define Emp Name as the default value. Complete these options and fields:
Variable – select the Variable option to the left of Emp Name.
Display Name – enter or accept Emp
Name.
Default Value – Fran
Whitney is displayed by default.
Type – select or accept Text Field.
Personalize, Key – leave these fields blank.
Kind – accept the Search default to only list the values but not update those values to the database.
Selections other than Search only apply to “update” portlets
that enable users to update a database from a mobile browser.
Required – verify that this option is selected. As a subscriber portlet, it requires the parameters that are sent by the publisher portlets.
Click Next. The Window Preview displays.
Enter SSCA Employee for
the Element Name, then click Next.
In the Continuous Capture Page window, click Finish.
When you return to the Portlet Builder, click Save.
In the Finish window, complete the fields or options on each tab:
Name – enter SSCA
Employee.
Content Type, Charset, Content Cache Interval – accept the defaults shown.
Parameter, Secure – leave these options unselected.
In Context – this option is selected by default.
Roles tab: click Add All to add all of the available roles to the assigned roles list, which specifies that only users with these roles can access this portlet.
Presentation tab: select No Popup and accept the defaults for the remaining options and fields.
Click Finish.
When you see a message stating that the portlet was successfully saved, click OK.
Click Close to exit the Portlet Builder.
When you return to the Web Studio main window, select New from the Portlet Builder Status menu. The SSCA Employee portlet displays in the detail pane.
Right-click the SSCA Employee portlet and select Approval Status | Approved.
When the message displays that the portlet was saved successfully, click OK.
Select Approved from the Portlet Manager Status menu. You see your newly approved SSCA Employee portlet.
| Copyright © 2004. Sybase Inc. All rights reserved. |
|
|
