- Phoebus API
- E- Invoice
- General (CMS)
- Phoebus Form Designer (FORM)
- Dashboard Engine (DDB)
- Ledger Accounting (LA)
- Phoebus Web (WEB)
- Human Resource Management (HRM)
- Fixed Asset Registry (FAR)
- School Management (PSM)
- Phoebus XL (PXL)
How to effectively using the User Table Tag with Phoebus Report Template
User table(Params) is a very simple tag, but it allows a lot of things. You write it on the Source Table column in the config sheet, and you can add an additional parameter on the Table Name column. You can leave the “Sort” and “Filter” columns empty or with values, their values do not matter.pbsQD_QDID user table pbsQD_QDID user table is used for adding to the report a fixed query, designed with Phoebus QD function. Syntax: user table(pbsQD_QueryID) Phoebus will find a query, having code = QueryID, and add it to the report. pbsUTD_TableID user table pbsQD_QDID user table is used for adding to the report a custom table, designed with Phoebus UTD/UTE function. Syntax: user table(pbsUTD_TableID) Phoebus will find a custom table, having code = TableID, and add it to the report. TT_XLB_EX user table This is the most flexible way for adding to the report any arbitrary data. The data is defined using TT_XLB_EB syntax. PhoebusXL provides a function called FormulaLink, which help user easily create a TT_XLB_EB query. FormulaLink uses the built-in data schema or any additional schema defined with function QDADD inside Phoebus. QDADD allows user to connect to any datasource via SQL native driver/ ODBC driver or OLEDB driver. Syntax: USER TABLE("0,2,PVE,CMM,V=4,F=A,K=\LA\Ledger,F={P}1,T={P}2,K=CMM\PERIOD,F={P}3,T=,K=CMM\WBS_ID,E=,O=CMM\ANAL_N0,E=,O=CMM\PERIOD,E=1,O=CMM\VALUE_2,E=,O=CMM\ANAL_N1,E=1,O=CMM\AMOUNT,";$B$35;<#M1>;<#M12>;<#P2>) It’s a little bit confusing reading this definition without PhoebusXL. If you have PhoebusXL installed, all you need to do is double click to the Excel cell and the above statement will be handled with a user friendly interface below:  <QD /> user table In many case FormulaLink is not enough for advanced requirements, like adding an expression column to the user table. This is why you need <QD /> user table. TT_XLB_EB syntax is not sufficient to hold all information of expression columns. <QD /> syntax does : USER TABLE(<QD Code="PXL" Descriptn="PXL" DTB="CDR" LDG="A" AnalQ0="LA" AnalQ1="" AnalQ2="" AnalQ3="" AnalQ4="" AnalQ5="" AnalQ6="" AnalQ7="" AnalQ8="" AnalQ9="" Layout="" SQLText="" HeaderText="" FooterText=""> <Filter Code="LA\ACCNT_CODE" Description="Account Code" FType="" FilterFrom="<#ACC>" FilterTo="<#ACC>" /> <Selection Code="LA\ACCNT_CODE" Description="Account Code" FType="" SubFieldStart="0" SubFieldLen="0" Agregate="" Expression="" OrderBy="0" /> <Selection Code="LA\PERIOD" Description="Period" FType="SP" SubFieldStart="0" SubFieldLen="0" Agregate="" Expression="" OrderBy="-1" /> <Selection Code="LA\BASE_AMOUNT" Description="Base Amount" FType="N" SubFieldStart="0" SubFieldLen="0" Agregate="SUM" Expression="" OrderBy="0" /> <Selection Code="LA\OTHER_AMOUNT" Description="Other Amount" FType="N" SubFieldStart="0" SubFieldLen="0" Agregate="SUM" Expression="" OrderBy="0" /> <Selection Code="TOTAL" Description="Total" FType="N" SubFieldStart="0" SubFieldLen="0" Agregate="" Expression="=[BASE_AMOUNT]+[OTHER_AMOUNT]" OrderBy="0" /> <Selection Code="Year" Description="Year" FType="" SubFieldStart="0" SubFieldLen="0" Agregate="" Expression="=FPeriod([PERIOD],"yyyy")" OrderBy="0" /> </QD>)  Here again, PhoebusXL will handle above complexity with a user frienly interface call User Table. It looks similar to Formular Link function, the difference are: · it supports creation of User defined outputs. · The output is XML syntax TSM user table TSM stands for TimeSheet Monthly. From the name you may guess the purpose of this type of user table: add a monthly timesheet to the report. This user table put all timesheet record to a calendar table. So the next step of present data to user is much more easier. Also TSM user table combines other information like :ABBR – Abbreviation and Timesheet Ts Analysis code into the dataset. If you want to print out the timesheet in the format of TSM function. This is the user table you must choose Important Notes: this TSM will not include all timesheet record created with TSH function (which have field SITE empty). TimeSheet records created with TSM functions always have the field SITE = ‘TSM’ Syntax: User table(TSM;EMPL_CODEs;PERIOD). Ex. User table(TSM;E0001;2011-012). E0001 can be multiple employees like <<E0001|E0002 ..." TSA user table TSA stands for TimeSheet Approval. From the name you may guess the purpose of this type of user table: prepare aggregated timesheet data for whole department or a team. Syntax: User table(TSA;EMPL_CODEs;PERIOD). Ex. User table(TSA;E0001;2011-012). E0001 can be multiple employees like <<E0001|E0002 ..." CalendarList user table Prepare an empty calendar from T -> T + n days. Output data includes weekend, holidays as defined in Phoebus. Syntax: User table(CalendarList;Starting_Date;Size) Ex. User table(CalendarList;2012-01-01;31) Calendar user table Prepare an empty calendar for a selected Month Syntax: User table(Calendar;Period;date_format;ShowDateOfTheMonth as Y/N) Ex.User table(Calendar;2014-001;yyyy-MM-dd;Y) Year Calendar user table Build a table as in the picture below:  Syntax: User table(YearCalendar;any_period_of_the_year) Ex.User table(YearCalendar;2014-001) Audit user table Extract the audit trail of a type (like CA) or a selected record like Account 111100 Syntax: User table(Audit;pbs.BO.LA.CA) à Audit trail for all accounts User table(Audit;pbs.BO.LA.CA;111100)à Audit trail for account 111100 Variables user table Convert multi-select variables to table. For example, <<A001|B001|A005 return: ----------------- Parameter ----------------- A001 B001 A005 This is used when you want to create a master detail report. Where master band is fixed and defined by end-user . Another use case is convert selected variables to columns, User select years <<2014;2015;2016 -> report will render 3 columns above. Syntax: User Table(<<A0001|B0002|C0003) User Table(<<A0001,B0002,C0003) User Table(<<A0001;B0002;C0003) Image user table Prepare dataset of all images with extension : *.png|*.jpg|*.gif|*.tiff|*.bmp from a directory to a dataset. Syntax: User Table(Image;dir=My Documents) User Table(Image;dir=\\shared_host\shared_folder;filter=a[0-9]{2}) Predefined user table Predefined user tables are reference data of Phoebus. Let’s say extracting all the Contact information or the list of all customer to report can be done with a simple statement User table(CNT) or User table(CL) Syntax: User Table(The_Reference_Data_Code) The reference data code is listed below:
|
Get help for this page
Jitbit HelpDesk