Templates & Tasks

The Proposal templates derive information from the Matter Profile page, Marketing tab. Logo and photos are derived from the Manage Logos option in the Users Menu.

Following are the available fields and placeholders.

Proposal - (Word/PowerPoint)

Matter Descriptions

Field	Placeholder
Client Name	{{client}}
Matter Name	{{matter}}
Scope of Matter	{{scope}}
Responsible Lawyer for Matter	{{responsibleLawyer}}
Billing Lawyer	{{billingLawyer}}
Originating Lawyer	{{originatingLawyer}}

Matter (Marketing Profile) Descriptions

Field	Placeholder
Title	{{Marketing.title}}
Description	{{Marketing.description}}
Comments	{{Marketing.comments}}

Matter (Team Members) Descriptions (Tombstone format)

Field	Placeholder
Photo	{{tombstone.photo}}
Person name for Team	{{team.name}}
Role of Person in Team	{{team.role}}
Office of Person	{{team.office}}
Phone of Person	{{team.phone}}
Email of Person	{{team.email}}
Photo	{{tombstone.photo}}
Name of Member	{{member.name}
Title of Member	{{member.title}
Office of Member	{{member.office}
Phone of Member	{{member.phone}
Email of Member	{{member.email}
Bio of Member	{{member.bio}
Education List of Member	{{member.educationList}}
Bar Admissions	{{member.barList}}
Languages List of Member	{{member.languageList}}

Matter (Deal) Descriptions

Field	Placeholder
Description	{{dealdescription}}

Matter (Proposed Budget) Descriptions

Field	Placeholder
Phase Name	{{budget.name}}
Start Date	{{budget.startdate}}
End Date	{{budget.enddate}}
Fee Arrangement	{{budget.fees}}
Hours	{{budget.hours}}
Average Rate	{{budget.rate}}
Budget	{{budget.budget}}

Matter (Phase Name) Descriptions

Field	Placeholder
Person/Cass	{{Phase.PersonName}}
Title	{{Phase.Title}}
Matter Role	{{Phase.MatterRole}}
Rate	{{Phase.MatterRate}}
Hours	{{Phase.Hours}}
Estimated Fees	{{Phase.EstimatedFees}}

Matter (Charts) Descriptions

Field	Placeholder
Description	{{ChartsSpending}}
Photo (first)	{{tombstone.photo0}}
Matter Description (first)	{tombstone.line0}}
Photo (second)	{{tombstone.photo1}
Matter Description (second)	{{tombstone.line1}}
Photo (third)	{{tombstone.photo2}}
Matter Description (third)	{{tombstone.line2}
Photo (fourth)	{{tombstone.photo3}
Matter Description (fourth)	{{tombstone.line3}

If the templates are not installed with Umbria, you can install multiple templates at once as part of the installation wizard, or you can manually install individual templates. You have the option to import the templates to your computer. You can then import the document back to Umbria. Refer to the instructions below and to a list of available tokens in planning reports

If the templates were not installed with Umbria, you can install multiple templates at once as part of the installation wizard, or you can manually install individual templates. You have the option to import the templates to your computer. You can then import the document back to Umbria. Refer to the instructions below and to a list of available tokens in client reports.

Following are the available client report templates:

• Client Summary (PowerPoint): Fields are associated with the data on the Client Revenue page.
• Matter Performance- Budget (PowerPoint): Fields are associated with data on the Matter Monitor page. (for budgeted matters).
• Matter Performance - No Budget (PowerPoint): Fields are associated with the data on the Matter Monitor page (for matters without an active budget).
• Matter Summary (PowerPoint): Fields are associated with the data on the Client Revenue page.
• Matter Family Performance (Excel): Fields are associated with the data on the Matter Family Monitor page.
• Matter Performance- Budget (Excel): Fields are associated with the data on the Matter Monitor page (for budgeted matters).
• Matter Performance- No Budget (Excel): Fields are associated with the data on the Matter Monitor page (for matters without an active budget).
• Matter Profitability Matrix (Excel): Fields are associated with the data on the Matter Monitor page.

Excel Customization Options

• User's can show/hide:
  • the Profitability and KPI data in the Report by enabling or disabling the relevant permissions. Refer Matter Monitor page to view Profitability and KPI related permissions.
  • the tabs in the export report by enabling/disabling each tab in Global Settings>Budget Settings>Budget Report
  • the Expenses information in the ByMonth and the ByPhase report by turning off “Show Expense in KPI, Month and Phases” button in the Action menu>Report tab.

Use Tokens from Across Sheets to Create Customized Export Reports

This gives you the ability to use token across sheets or add a new sheet and add token from other sheets in the new sheet. This is a hidden work-sheet in the out-of-the-box Excel Templates. Unhide to view the Settings worksheet.

Tip: In the Home menu click Format>Visibility>Hide & Unhide>Unhide Sheet. In the Unhide pop-up window, select the Settings sheet and click OK. You can also right-click on any of the open tabs and select Unhide.

1. Open any of the out-of-the- box Excel Export templates.

2. Right-click on any of the open tabs to unhide the sheet for Settings. In the Unhide window, select Settings.
3. In the Settings sheet, the Sheet Key column displays the sheet data that will be exported to the Sheet Names during export. For e.g. The ByMonth data will be exported to the By Month sheet.
4. To use tokens from another sheet, type the Sheet Key name (to pull tokens from) in the Sheet Data column. To use multiple tokens separate the Sheet Key names with a comma. For e.g. KPI, ByMonth.

5. You can also add a new sheet to the export report. In the Sheet Key column, add a Sheet Key (without space) and add a Sheet Name. The Sheet name entered here will be the worksheet name in generated report. The sheet key data will be exported to the sheet name entered here.

6. Now, manually add the new sheet to the Excel template. Right-click on any of the open tabs and click Insert>Worksheet. Rename the worksheet to exactly match the name entered in the Sheet Key column as entered in Step 5 above.
7. The new worksheet must be included in the named sheet range in the Excel Report. This must be done to ensure the sheet is read from the code and executed at run-time for the values to populate in the new worksheet on export. To add a worksheet to Name Manager, follow the steps listed below:
   1. Click Review>Unprotect Sheet. This sheet is password protected. Enter the password Umbria123@
   2. Click Formula>Name Manager>Select SheetNames. In the Refers To field include the row with the new sheet and click Save. You may password protect the sheet again, this is optional.

   

8. When adding tokens to an existing worksheet or to a new worksheet append the Sheet Key name to the token names. In some cases, the work sheet may have data lists with similar names, appending the key name to the tokens will ensure data is picked correctly from the corresponding data list and avoid any loss of data. Similarly, when referencing a table from a different worksheet, add the sheet key name only to the first table tag. For e.g. to pull data from the KPI tab to the Person tab, in the Person tab, append the KPI sheet key name (i.e KPI) to all the KPI tokens used in the sheet, {{KPI_Entered.Hours.MatterHours}}. (Refer illustration below)

9. To export values in the new worksheet add the tokens from the Sheet Key (as added in Step 5). For e.g. For the NewSheet, if you have added Sheet Key as ByTaskCode, Person, add tokens from the ByTaskCode and Person sheet to the new worksheet.

10. Save and upload the custom template. Generate the export report from the dashboard.

Add Dynamic Queries

The Dynamic Queries sheet allows you to create Embedded SQL Queries to customize the report data at run-time. You can use this option if the excel report does not contain the token/data you want to export. This is a hidden work-sheet in the out-of-the-box Excel Templates. Unhide to view the Dynamic Queries worksheet.

Tip: In the Home menu click Format>Visibility>Hide & Unhide>Unhide Sheet. In the Unhide pop-up window, select the DynamicQueries sheet and click OK. You can also right-click on any of the open tabs and select Unhide.

1. Open any of the out-of-the- box Excel Export templates.

2. Right-click on any of the open tabs to unhide the sheet for Dynamic Queries. In the Unhide window, select Dynamic Queries.

3. The first row of the sheet (highlighted in yellow) shows the available parameters that can be used in the SQL queries that will be defined.

4. In the Data Name column, define the token name to access the result that will be returned by the query.

5. The Query Type column is a lookup and for now only supports SQL queries.

6. The Result Type column is the type of result expected from the query. It can be either Record or View.
   • Record - single row returned as output
   • View - is for a list returned as a table
7. In the Query column enter the SQL query.

8. Include the cell references for the query in the range of cells defined for Dynamic Queries. This must be done to find the values and execute the queries at run-time. Click Formula>Name Manager>Select Dynamic Queries. In the Refers To field include the cell range. Click Save.

9. Data returned from the Dynamic Queries can be displayed in any of the exsisting worksheet of the Excel report, or you can add a new worksheet. Add the Table Name and the relevant tokens to be replaced with the output from the SQL query.

10. Save and upload the custom template. Generate the export report from the dashboard.

Change the Currency Format in the Excel Reports

To change the currency formatting (for e.g. replace $ with AUD) in the Excel export reports, you will have to add Conditional Formatting for the currencies you want to use. Follow the steps listed below:

1. In the Excel Export template, follow the sequential steps as listed here in the illustration.

2. The updated format value will look like this

Export Report Templates and Available Tokens

Following is a list of available fields and their placeholders in the out-of-the-box report templates available in the folder ~\Prosperoware.Umbria\App_Data\Templates\ExportTemplate

Tip: You can show / hide the columns and tokens ( for profit margins and realization) in the export report based on the permissions set in the Matter Monitor page.