Adding underlying ClientSpace data to Business Intelligence reports

You can add functionality to Izenda reports by drilling into ClientSpace records from ad-hoc reports.

The numbers in curly braces are the field positions for the parameter you want to pass, so {0} indicates the field you are on, {2} indicates the second column (field) in the Izenda report.  It is important to know that fields (columns)  that are visually grouped in a report are not included when counting columns for use in these parameter curly braces. So if the first two fields on your report are visually grouped and you wanted to send the parameter in field #4, you would actually use {2} unless the link is on field 4 in which case you would use {0}.

By using the following code samples and the appropriate data fields, you can add robust functionality to ad-hoc reports. 

To add linkto drill-down functionality inline on a report:

1. Add the following codes (see the following list) on the Fields tab > Advanced Field Settings > Url field.

Standard formatting is as follows: /next/linkto/{TableCode}/{ID}

• next/linkto : Alerts BI that you are creating a link to another record.
• {TableCode} is the predetermined Code of the dataform or core table that you want to link to. This code is hardcoded into the URL you are embedding into your report. 
• {ID} is the key of the detail record you want to drill into and is dynamically replaced in your URL by Izenda. Keep in mind that Visual Group fields on reports do not count when figuring the order.

Dataform TableCode: You can link to dataform pages with the following format:

  • /next/linkto/clientservicecase/{ID}

A new column, called TableCode, has been added to the ProjectTypes table, this has been defaulted to the AttributeTableName with the "gen_" prefix stripped off.

So, in the example , it uses the TableCode clientservicecase to hit the ProjectTypes table, find the associated ProjectTypeID, and build the URL to the dataform edit page with that ProjectTypeID and the provided RecordID.

Core TableCode: You can also link to Core detail page through this mechanism, a predefined set of core TableCode-to-detail page URL mappings are checked first:

• Organization 
usage: /next/linkto/organization/{ID}
• Contact 
usage: /next/linkto/contact/{ID}
• Workspace
usage: /next/linkto/workspace/{ID}
• Task 
usage: /next/linkto/task/{ID}
• Activity 
usage: /next/linkto/activity/{ID}
• TimeTracker 
usage: /next/linkto/timetracker/{ID}
• User 
usage: /next/linkto/user/{ID}

Secondary TableCode: If the specified TableCode is not found in the list of known core/peo TableCodes, it then hits tblTableMetadata, where there are two new columns: TableCode and DetailUrl. It uses the specified TableCode to lookup the tblTableMetadata row with the matching TableCode, and pull the associated DetailUrl field, it looks for the string {ID}, and replace it with the record Id that was passed to the LinkTo page.

So, if you have a row in tblTableMetadata with a TableCode = "Foo", and a DetailUrl = "~/foo/bar/{ID}

Then, embed a link using the LinkTo page like this: /next/linkto/foo/123

An example of this is /next/linkto/workspace/{0} where the Projects Table entry in tblTableMetadata has been updated to allow direct linking to the Workspace Landing Page.

A few caveats

• * None of these new columns (TableCode or DetailUrl) are exposed on the UI. Look directly in the database, or run an Ad Hoc report to see the values.
• *New values can be added on request by PrismHR.
• * When you load up a detail page from the Link To page, you don't get your modules across the top nav, nor do you get the user name drop-down in the upper right of the top nav. This is by design. This is meant to be a stripped down version of the navigation, for drilling into detail pages.
• *To encode a link to the Pricing Console of a workspace, use the following format in the fields Advanced | Url field: /next/PEO/PricingConsole/pricingconsole/{projectId} where /{projectid} represents the column on the report containing the ProjectID column on your report. Remember to remove formatting such as commas. from the ProjectID column or the report returns an error.

Tips

  • When a report is designed to run within a workspace (using #currentworkspace as a hardcoded filter in the report against a ProjectID column), you can pass the projectid of the workspace you are on using the querystring parameter projectid. For example:
    • In the report:
    • In the link configuration: /BusinessIntelligence/ReportViewer.aspx?rn=workspacetest&projectid={fkProjectID}
    • If the link is configured to run from the Workspace Landing Page: /BusinessIntelligence/ReportViewer.aspx?rn=workspacetest&projectid={ProjectID}

  • When a report is designed to filter records for only the current user (using #currentuser as a hardcoded filter in the report against a UserID column), the system automatically passes the current user ID into this filter. For the example report above
    • In the report:
    • In the link configuration: /BusinessIntelligence/ReportViewer.aspx?rn=workspacetest

To add custom links to dataform File fields: 

If a dataform in your report contains a file upload field, you can create a link to download the file using the following format:

• /next/Files/Download?id={ID}
• {ID} is the file upload field in your report (containing the file GUID), and is dynamically replaced in your Url by Izenda. Keep in mind that Visual Group fields on reports do not count when figuring out the order.

PEO TableCodes

The following is a list of standard PEO dataform tables and the associated TableCodes. If you don't find what you are looking for on this list or would like to add a TableCode for a custom table, log an Extranet case.

Dataform Table TableCode
gen_AcctManagerVisit AcctManagerVisit
gen_AdminCaseCategory AdminCaseCategory
gen_AdminCaseTypes AdminCaseTypes
gen_AdminCompCodeMaster AdminCompCodeMaster
gen_AdminHoliday AdminHoliday
gen_AdminLegalEntity AdminLegalEntity
gen_AdminLossRunImport AdminLossRunImport
gen_AdminLossRunImportMapping AdminLossRunImportMapping
gen_AdminLossRunImportMappingLookup AdminLossRunImportMappingLookup
gen_AdminPolicyCompCodes AdminPolicyCompCodes
gen_AdminServiceTeam AdminServiceTeam
gen_AdminStateLegalEntity AdminStateLegalEntity
gen_AdminStateRates AdminStateRates
gen_AdminWorkersCompPolicy AdminWorkersCompPolicy
gen_AnnualAdministration AnnualAdministration
gen_ApprovalDependency ApprovalDependency
gen_Attorney Attorney
gen_BenefitPlan BenefitPlan
gen_BenefitPlanTier BenefitPlanTier
gen_Benefits Benefits
gen_BenefitsBenefitPlan BenefitsBenefitPlan
gen_BenefitsPlanCategoryMetadata BenefitsPlanCategoryMetadata
gen_BenefitsPlanGroup BenefitsPlanGroup
gen_BenefitsPricing BenefitsPricing
gen_BenefitsReconAdmin BenefitsReconAdmin
gen_BenefitTierDefinition BenefitTierDefinition
gen_BodyPartMetadata BodyPartMetadata
gen_CauseofInjuryMetadata CauseofInjuryMetadata
gen_CensusEmployeeImport CensusEmployeeImport
gen_CertificateOfInsurance CertificateOfInsurance
gen_ClaimDeductibleBill ClaimDeductibleBill
gen_ClaimNotes ClaimNotes
gen_ClaimTypeMetadata ClaimTypeMetadata
gen_ClientActivity ClientActivity
gen_ClientCertificates ClientCertificates
gen_ClientDistressCall ClientDistressCall
gen_ClientLocation ClientLocation
gen_ClientMaster ClientMaster
gen_ClientMasterStatusMetadata ClientMasterStatusMetadata
gen_ClientProcessingTeam ClientProcessingTeam
gen_ClientSatisfactionLevelMetadata ClientSatisfactionLevelMetadata
gen_ClientServiceCase ClientServiceCase
gen_ClientServiceCaseDiscussion ClientServiceCaseDiscussion
gen_ClientServiceCaseEscalation ClientServiceCaseEscalation
gen_ClientServiceCaseExpenses ClientServiceCaseExpenses
gen_ClientServiceCasePriorityMetadata ClientServiceCasePriorityMetadata
gen_ClientServiceCaseTime ClientServiceCaseTime
gen_ClientStatistics ClientStatistics
gen_ClientTeamRole ClientTeamRole
gen_ClientTeamRoleMetadata ClientTeamRoleMetadata
gen_ClientVisitTypeMetadata ClientVisitTypeMetadata
gen_COBRA COBRA
gen_COICertificateHolder COICertificateHolder
gen_COIVersionMetadata COIVersionMetadata
gen_Collateral Collateral
gen_CommissionDetail CommissionDetail
gen_Company401k Company401k
gen_CompanyInformation CompanyInformation
gen_CompClaim CompClaim
gen_CompClaimClaimant CompClaimClaimant
gen_CompClaimFinancial CompClaimFinancial
gen_CompClaimInjury CompClaimInjury
gen_CompClaimLegal CompClaimLegal
gen_CompClaimPayment CompClaimPayment
gen_CompClaimPredictiveScore CompClaimPredictiveScore
gen_CompClaimReserve CompClaimReserve
gen_CompClaimRTW CompClaimRTW
gen_CompClaimRTWStatusMeta CompClaimRTWStatusMeta
gen_CompClaimSubrogation CompClaimSubrogation
gen_CompClaimTreatment CompClaimTreatment
gen_CompClaimWitness CompClaimWitness
gen_CompCodeTranslation CompCodeTranslation
gen_CompCodeTranslationLink CompCodeTranslationLink
gen_Contact Contact
gen_ContactOtherInfo ContactOtherInfo
gen_ContactTypeOtherInfoMetadata ContactTypeOtherInfoMetadata
gen_ContractTypeMetadata ContractTypeMetadata
gen_Correspondence Correspondence
gen_CPEhrRAF CPEhrRAF
gen_DeductionVoucher DeductionVoucher
gen_DefaultAdminFee DefaultAdminFee
gen_DefaultClientTeam DefaultClientTeam
gen_DefaultCompDiscount DefaultCompDiscount
gen_Dependent Dependent
gen_DiscountThreshold DiscountThreshold
gen_DiscussionMessage DiscussionMessage
gen_DiscussionRecipient DiscussionRecipient
gen_DiscussionThread DiscussionThread
gen_Employee401k Employee401k
gen_EmployeeBenefits EmployeeBenefits
gen_EmployeeCertifications EmployeeCertifications
gen_EmployeeDeductionVoucher EmployeeDeductionVoucher
gen_EmployeeEmploymentInformation EmployeeEmploymentInformation
gen_Employees Employees
gen_EmployeeVoucher EmployeeVoucher
gen_EmploymentStatusMetadata EmploymentStatusMetadata
gen_EntityQualifications EntityQualifications
gen_EPLIClaim EPLIClaim
gen_EPLIClaimFinancial EPLIClaimFinancial
gen_EPLIClaimLegal EPLIClaimLegal
gen_EPLIPolicy EPLIPolicy
gen_ExecTeam ExecTeam
gen_FMLA FMLA
gen_FSA FSA
gen_HSA HSA
gen_I9 I9
gen_ImportInvoiceRecords ImportInvoiceRecords
gen_IndustryCodeMapping IndustryCodeMapping
gen_InterfaceException InterfaceException
gen_JurisdictionReportFormat JurisdictionReportFormat
gen_LifeInsurance LifeInsurance
gen_LossDataFactor LossDataFactor
gen_LossHistory LossHistory
gen_MasterBenefitsRecord MasterBenefitsRecord
gen_MBRMatchingRecord MBRMatchingRecord
gen_MBRReconcile MBRReconcile
gen_MedicalProvider MedicalProvider
gen_NatureofInjuryMetadata NatureofInjuryMetadata
gen_NewQuestionnaire NewQuestionnaire
gen_OASISInvoice OASISInvoice
gen_OfferedBenefitPlanSummary OfferedBenefitPlanSummary
gen_OfferedPlanClassification OfferedPlanClassification
gen_OrderAssignment OrderAssignment
gen_OrganizationSourceMetadata OrganizationSourceMetadata
gen_OrganizationStatusMetadata OrganizationStatusMetadata
gen_OrgOtherInfo OrgOtherInfo
gen_OrgTransferRequest OrgTransferRequest
gen_OSHACode OSHACode
gen_PayFrequencyMetadata PayFrequencyMetadata
gen_Payroll Payroll
gen_PayrollCycle PayrollCycle
gen_PayrollInformation PayrollInformation
gen_PayrollStatistics PayrollStatistics
gen_PensionPlans PensionPlans
gen_PensionPlanSupplemental PensionPlanSupplemental
gen_PolicyCodeMetadata PolicyCodeMetadata
gen_Positions Positions
gen_PreSaleBenefitPlan PreSaleBenefitPlan
gen_PreSaleBenefits PreSaleBenefits
gen_PricingBatch PricingBatch
gen_PricingBatchState PricingBatchState
gen_PricingCompCode PricingCompCode
gen_ProjectMilestone ProjectMilestone
gen_Qualifications Qualifications
gen_RevenueAdjustors RevenueAdjustors
gen_RFPQuestionnaire RFPQuestionnaire
gen_RiskProfile RiskProfile
gen_SalesEntity SalesEntity
gen_SlavicPlanHub SlavicPlanHub
gen_StaffingAdministration StaffingAdministration
gen_StaffingClientMaster StaffingClientMaster
gen_StateContinuation StateContinuation
gen_StateMetadata StateMetadata
gen_Surcharges Surcharges
gen_SurchargeType SurchargeType
gen_SurchargeTypeDependency SurchargeTypeDependency
gen_SurchargeTypeMetadata SurchargeTypeMetadata
gen_SurchargeTypePriceThresholds SurchargeTypePriceThresholds
gen_TaxNotices TaxNotices
gen_Termination Termination
gen_TPA TPA
gen_UnderwriterApproval UnderwriterApproval
gen_UnderwritingDefinition UnderwritingDefinition
gen_UnemploymentBenefitCharges UnemploymentBenefitCharges
gen_UnemploymentClaim UnemploymentClaim
gen_UnemploymentClaimFinancial UnemploymentClaimFinancial
gen_WCClaimNote WCClaimNote
gen_WorkOrder WorkOrder

tblTableMetada

tablename DetailUrl TableCode
Projects ~/workspaces/landing/{ID} Workspace
tblContact ~/contacts/detail/{ID} Contact

For more information about configuring advanced functionality in ad hoc reporting, log an Extranet case to schedule a training session.