IViews –Reports & Lists

About IViews in Axpert

IViews provide the result of an SQL statement displayed as a tabular report. The SQL may have parameters. Parameter values are accepted by the user at run time. Each column may have hyperlinks that may be used to drill down to another IView or open TStruct/form along with form data. Scripts may be written to do special functions in an IView. These may be executed with the click of a button.

IView parameters

Parameters SQL are automatically considered as IView parameters. Users can enter values for each IView parameter. Each parameter value will be auto-populated or accepted by a user based on the definition made for each parameter.
The parameter values may be calculated using a formula that can be provided in the expression property. The expression may use other parameter names as variables. App variables may also be used in expressions. Use the validate-expression property to validate the user input for the parameter. If valid values are not provided, the IView will not bring results.
Parameter values may be selected from a drop-down. The drop-down values may be populated from a SQL result. The SQL text can be defined in the SQL property. Other parameters & app variables may be used as SQL parameters. A picklist is used when the SQL result is expected to bring more than a few thousand rows. In this case, the data will be fetched from the database in blocks of a few hundred records depending on the characters entered in the parameter. For example, Create an Iview with employee details as shown below

In the params section, Select the mode of entry as ‘Pick List’ and provide the corresponding query like select ename from ivifo1 {dynamicfilter ename ~ name} as shown below

Navigate to the run site and find an existing value from the Iview in the search box. If the corresponding values exist, it is displayed on the screen

Multiselect parameters

The IView SQL should be joined with an Axpert table named axpselectionc. This table will be populated with user-selected values in a multi-select parameter.
In the params tab, add a parameter. Set its mode of entry as Multi-select. The multi-select is like a drop-down parameter. It allows users to select more than one value. The selected values are added as rows to the axpselectionc. The selected values are stored in the Selection field in this table.

Example: Consider an IView that displays Employee details based on selected employees.

IView SQL : Select EmployeeName, Gender, Age, GrossSalary from EmployeeMasterA, AxpSelectionC B where A.EmployeeName = B.Selection

Add a parameter name pEmpName. This name can be anything. Set mode of entry as Multi-Select.

Parameter SQL – Select EmployeeName from EmployeeMaster order by EmployeeName

IView columns

The SQL columns will automatically be added as columns in the IView. Select any column and change its column properties. Columns may be hidden. The table kind of a view displayed at the top can be used to resize column width and reorder them. New columns may be added to the IView. The values for the user added columns may be set using column expressions. The expressions may use other column names as vars and app vars.

Hyperlinks

The values in a column may be displayed as a hyperlink. The option to create a hyperlink is available under the options button that is displayed on each column at the top of the table in the column section. The hyperlink may be configured to load form data, open a struct, or another Iview. The data from the Iview columns/params can be passed as parameters. The parameter may be assigned to a field in a selected tstruct or another parameter in an Iview.

Dynamic Hyperlinks

We employ one on one mapping, manually to set a hyperlink. It becomes a tedious task if there are more than 20 rows that need a mapping from corresponding tstructs into a single form. Individual rows can define separate forms. For instance, A Bank transaction can have multiple entries, it can have transactions related to different individuals and companies associated with it. Each row will be linked to a corresponding form detail.

Create a new form with a suitable name like DHF1. Create an autogenerate field for id, with appropriate prefix and length. Similarly, create another form DHF2 with autogenerate field for id, add appropriate prefix and length. Create an Iview with the name dhliv1 (dynamic hyperlink F1). In the SQL section add the snippet mentioned below
Select parameter_name from transid

Create an Iview with the name dhliv2 (dynamic hyperlink report). In the SQL section add the snippet mentioned below

In the above code snippet, the syntax includes a ‘t’ as a prefix for tstruct, ‘i’ as a prefix for iview followed by transid of tstruct or iview respectively.

IView properties

The Iview properties are used to change the look and behavior of an IView. The group field can be used to group rows and show them as one row. All rows that have the same value in the group field will be shown as one row in the IView.
Load TStruct property can be selected to load a form from the IView. In this case, the IView toolbar will have an “Add New” option. This will load the selected tstruct form in the new mode for the user to add new data. It will also provide a hyperlink to load a record in edit mode.

Include multiple report headers in Iview’s excel format
By default the Iview data in excel format has single header as below

To add multiple headers, go to Iview properties and enter suitable names in the report fields

After changing the headers in the Iview properties, the excel data will be as follows:

On DbClick
Define a script in IView properties

Select the script under On Dbclick and save the Iview.

On the run site, select an entry and double click on it. The Message specified in the script appears on the screen.

IView Layout

There are four types of Iview layout

1. Table – This is a default layout. It has an additional configuration to set width and alignment.
2. Tile – This layout displays cards next to one another
3. Card – This layout displays cards one below the other.
4. Custom – This layout supports customization using HTML.

There are a few mandatory keywords to consider while creating a Card or Tile layout.

• axp_icon – This keyword allows to set a 3 character text, that will be displayed as an icon. If axp_icon_img is specified then this column can have an empty string ‘ ‘ as a value.
  Example: AGL, SBI, KMB
• axp_icon_img – The image which needs to be displayed should be available in the following path
  ../{{webDirectory}}/{{projectConnectionName}}/Images/
  If axp_icon keyword is used then the column can have empty string ‘ ‘ as a value
  Example: a.jpeg, abc.png
• axp_title – A mandatory keyword which is used to specify the title on every card.
• axp_description – A mandatory keyword which describes the functions available in the particular card.
• axp_status – An optional keyword that is used to specify the status of the project. The values in axp_status can be active, delayed, progress
• axrowoptions – An optional field that needs input in the format as shown below
  Name,Caption,action/hyperlink/script(type),materialIcon

Use the below code snippet and try creating Iview layouts providing suitable values:

SELECT 'wifi.png' as axp_icon_img, '' as axp_icon, 'Axpert 10' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Active' as axp_status, 'link_ttl,Commercials,hyperlink,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT 'agile.png' as axp_icon_img, '' as axp_icon, 'Axpert 11' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Delayed' as axp_status, 'commercials,Commercials,script,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT '' as axp_icon_img, 'abc' as axp_icon, 'Axpert 9' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Active' as axp_status, 'link_ttl,Commericals,hyperlink,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT '' as axp_icon_img, 'png' as axp_icon, 'Axpert X' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Delayed' as axp_status, 'commericals,Commericals,script,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual

Create an Iview and add SQL query. Submit and save the structure. Visit the run site and find the Iview created.

Custom Layout Query example


SELECT '' as axp_icon_img, 'shopping_basket' as axp_icon, 'Card 1' as axp_title, 'Card is a responsive content container with an extensible option for headers, footers, images, and a wide variety of content.' as axp_description, 'Active' as axp_status, 'profile,Profile,hyperlink,account_circle~schedule,Schedule,script,pending_actions~notifications,Notifications,script,circle_notifications~settings,Settings,script,settings_suggest' as axrowoptions, '#FF00FF' as cardcolors from dual
UNION
SELECT '' as axp_icon_img, 'card_giftcard' as axp_icon, 'Card 2' as axp_title, 'Card is a responsive content container with an extensible option for headers, footers, images, and a wide variety of content. Many examples and tutorials.A card is a flexible and extensible content container.' as axp_description, 'Delayed' as axp_status, 'profile,Profile,hyperlink,account_circle~schedule,Schedule,script,pending_actions~notifications,Notifications,script,circle_notifications~settings,Settings,script,settings_suggest' as axrowoptions,'#33691E' as cardcolors from dual


Pivot in IViews/Reports

Tabular reports are collections of rows and columns. In a pivot report, the data at the pivot column will be transposed from rows into columns. The cell values that follow the pivot column will be plotted as values in the transposed columns.
This is defined in an IView by naming the pivot column as “pivot”. The IView should be ordered by the pivot column to ensure the same values in pivot columns are listed together.

Example:
Consider a report as below:

Month	Product Group	Sale Qty	Sale Value
Jan	Electronics	500	50,00,000
Feb	Electronics	3000	18,00,000
Mar	Electronics	1500	20,00,000
Jan	Mobile	400	45,00,000
Feb	Mobile	3200	20,00,000
Mar	Mobile	1100	18,00,000
Jan	Wearables	600	58,00,000
Feb	Wearables	3500	28,00,000
Mar	Wearables	1800	30,00,000

If the above report is pivoted at Product Group, the result will be:

Month	Electronics		Mobile		Wearables
	Qty	Value	Qty	Value	Qty	Value
Jan	500	50,00,000	3000	20,00,000	600	58,00,000
Feb	3000	18,00,000	400	45,00,000	3500	28,00,000
Mar	1500	20,00,000	3200	20,00,000	1800	30,00,000

Tool bars in IViews

IView will have the following options by default:

• New (if a tstruct is attached to the IView)
• Search
• Data
  1. Parameter
  2. Add view
  3. Graphs
• Options
  1. User added options

Row options

Add a column named AxRowOptions in the IView. It may be added as part of the IView SQL. The value should be a string as below:

• (Name1),(Caption1),(hyperlink name or script name),(MaterialIcon1) ~ (Name2),(Caption2),(hyperlink name or script name),(MaterialIcon2)…

This definition will provide the given set of options for every row in the row options column.

Download files from IViews

Download the file/attachments in IViews when provided with the mapped path. To achieve this, the below view configurations need to be applied.
 Concerned Reviews should have the following columns by default.

• transid: transid of the tstruct
• If isdbattach column value is true then transid column value should be (“transid+fieldname”)
• If isdbattach column value is true and is DB attachment is header attachment then only “transid” should be passed.
• fieldname: field name in tstruct(to be kept empty for header attachment)
• recordid: transaction record id.
• isdbattach: this is an optional column which determines wheater the attachment is db attachment or file attachment, if the attachment type is db attachment then this column value should be true
• axp_attach (can be any one of the following):
• Text to be shown on download hyperlink (this column is a flag to enable/disable attachments component)
• Grid attachment name (value of grid attachment)
• Full mapped path if transid, field name, recordid is not provided
• ResolvePathForTemplates: “resolve attachment path” property is used for view templates in case of resolving image path to show image in iview templates. “Resolve attachment path” Advanced Setting configuration property with property code as “resolve attachment path” to be added as true or false.

Note: If File Server and App Server are different systems, then File Server needs to be authenticated for downloading or uploading the files with the following Application Variables, provided both servers should be in same domain.

• axp_mapusername
• Username for the domain
• axp_mappwd
• Password for the domain
• axp_domain
• Domain Name

Example:

• DB Attachment
  • select ‘gddwn’ transid_real,gddwn1id recordid,’dc2_image’ fieldname,dc2_image axp_attach,’gddwndc2_image’transid, ‘true’ isdbattach from gddwn2

Non DB Attachment

• • select null transid_real,gddwn1id recordid,null fieldname,replace(axpfilepath_img,’*’,”)||axpfile_img axp_attach,null transid, ‘false’ isdbattach from gddwn1

DB + Non DB Attachment

• select ‘gddwn’ transid_real,gddwn1id recordid,’dc2_image’ fieldname,dc2_image axp_attach,’gddwndc2_image’transid, ‘true’ isdbattach from gddwn2
  union all
  select null transid_real,gddwn1id recordid,null fieldname,replace(axpfilepath_img,’*’,”)||axpfile_img axp_attach,null transid, ‘false’ isdbattach from gddwn1.

Try Out

List all the active employees in the employee master using IViews.