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:


Code
Description
A0..A9, T0..T9, I0..I9, C0..C9, M0..M9, F0..F9 ….
Phoebus Analysis Codes
All categories defined with CAT function
Phoebus lookup list
ABBR
Timesheet abbreviation
AD
Asset Details
AR
Asset Record
AST
Assembly Structure
BIV
Budget Values
BK
Bank details
CA
Chart of accounts
CL
Clients
CN
Conversion Definition
CNT
Contact
CTR
Contracts
DB
Database Definition
ELC
Employee Contract
EMP
Employees
ESR
Ledger Extended Descriptions
FCA
EFC Adjustment
FCV
Forecast Value
IB
Item Balance
ID
Item Description
IR
Item Record
JD
Journal Type
LKU
Lookup Lists
LO
Location (Inventory)
LOC
Location (EAM)
MD
Movement Definition
MST
Milestones
NA
Name and Address
ND
Analysis Definition
OD
Operator Definition
PBD
Form Definitions
PTI
Payment Term Installments
RTP
Room type (Real Estate)
WBI
Work breakdown structure
WBS_ID
Work breakdown structure - Budget items
WBS_NBI
Work breakdown structure - Non Budget items
WOT
Work order type