Custom Display: Roll-Up with Totals

This section walks through the process of using JavaScript to enhance the capabilities of CorasWorks web parts. For the purposes of these procedures, the Finance tab and the Totals view of the Travel Expense Approvals solution is discussed, where the Total Expenses listed in each Expense Report are dynamically added together.

How It Works

For the CorasWorks Travel Expense Total, three web parts are required:

o Two Content Editor web parts – The first Content Editor web part (JavaScript Starter.dwp) is used to initialize the variables and to define the JavaScript functions, while the other (Total JavaScript.dwp) is used to display a grand total of all the groups’ expenses.

o One Spreadsheet Roll-Up Advanced – The main work of the Travel Expense Total is done in Spreadsheet Roll-Up Advanced (Travel Expense Total.dwp), which creates a table to display all of the necessary fields, implements the JavaScript functions defined in the Content Editor web part, and displays a total for each group.

While the Content Editor Web Parts allow the addition of scripts through a Source button in the web part properties, it is necessary to modify the DWP of the Spreadsheet Roll-Up Advanced to get the desired functionality.

As implemented in the Travel Expense Total, totaling uses two Content Editor web parts:

1. JavaScript Starter (click here)

o Creates two variables:

o intCompleteTotal

o intGroupedTotal

o Defines two functions:

o Round – Rounds a number to X decimal places

o Cent – Returns an amount in a $.99 format

2. Total JavaScript (click here)

o Displays cent(intCompleteTotal)

IMPORTANT! When copying and pasting from the online help into FrontPage, hard returns may be inserted. These will need to be removed to avoid any errors.

A specially modified Spreadsheet Roll-Up Advanced (Travel Expense Total.dwp) calls the JavaScript functions and displays groups and group totals. The table is hard-coded in the Display property of the Travel Expense Total.dwp file (click here). There are four calls to JavaScript variables and functions in the Display property:

1. <script language="javascript">
intGroupedTotal = 0;
</script>

2. <script language="javascript">
document.write('$' + cent(<%Total Expenses%>));
</script>

3. <script language="javascript">
intCompleteTotal = intCompleteTotal + <%Total Expenses%>;
intGroupedTotal = intGroupedTotal + <%Total Expenses%>;
</script>

4. <script language="javascript">
document.write('$' + cent(intGroupedTotal));
</script>

If you look at the table in Spreadsheet Roll-Up Advanced, you will see that a number of fields from the List being searched are referenced with a “%”, both before and after the item. These include both the field headers (%Report Title Header%, %Status Header%, %Created Header%, %Total Expenses Header%) and the field values (%EditItemURL%, %DisplayItemURL%, %Report Title%, %Status%, %Created%, %Total Expenses%). These are the fields available to you while building your custom solution with Expense Reports.

Building It

The Scenario

These procedures assume that the CorasWorks’ Workplace Wizard™ has been used to build the complete Travel Expense Approvals solution at http://site/TravelExpApprovals/ and the solution has been populated with travel expense requests. There is a personal dashboard at http://site/yoursite/, where a Travel Expense Total roll-up will be created that mirrors the Travel Expense Total roll-up found at http://site/TravelExpApprovals/CTab3.aspx.

Step 1: Create the JavaScript Starter Roll-Up

1. Add a Content Editor web part to the zone where the modified roll-up will be displayed.

2. Modify the web part properties of the Content Editor web part.

o In the Appearance Properties, change the Title to JavaScript Starter.

o In Layout Properties, disable the Visible on Page option and then ensure that Part Order = 1.

o Open the Source Editor and paste in the following code, then click Save and then OK:

var intCompleteTotal = 0;

var intGroupedTotal = 0;

function round(number,X) {

// rounds number to X decimal places, defaults to 2

X = (!X ? 2 : X);

return Math.round(number*Math.pow(10,X))/Math.pow(10,X);

}

function cent(amount) {

// returns the amount in the .99 format

amount -= 0;

return round((amount == Math.floor(amount)) ? amount + '.00' : ( (amount*10 == Math.floor(amount*10)) ? amount

+ '0' : amount));

}

</script>

Step 2: Create the Travel Expense Total Roll-Up

1. Add a Document Spreadsheet Roll-Up Advanced web part to the zone where the modified roll-up will be displayed.

2. Export the roll-up as a .dwp file.

o Click on the Actions menu and then Export…

o Save the file onto your computer as Travel Expense Total.dwp

3. Delete the generic Spreadsheet Roll-Up Advanced from your site.

o Click on the Actions menu and then Delete

o Click OK to confirm

4. Edit Travel Expense Total.dwp on your computer

o Change the Title of the web part by replacing the text between the <Title></Title> tags with “Travel Expense Total”

o Insert this content (click here) between the last property tag, <TypeName></TypeName>, and before the closing tag, </WebPart>

5. Be sure to modify the highlighted portions to customize the roll-up for your Travel Expense Approvals site, and then save the Travel Expense Total.dwp file.

6. Import the modified web part to your personal site.

o Click Modify Shared Page/Add Web Parts/Import

o Enter the name and location of your .dwp file or click Browse to find it on your computer

o Click Upload

7. Drag the uploaded web part to the zone where it should be displayed, ensuring that its Part Order places it below the JavaScript Starter.

8. Select your list in the web part’s administration interface. When you close the administration interface, the Travel Expense Requests will roll up immediately.

Step 3: Create the Total JavaScript Roll-Up

1. Add a Content Editor web part to a zone below where you want the modified roll-up to be displayed.

2. Modify the web part properties of your new Content Editor Web Part.

o In Appearance Properties, change the Title to “Total JavaScript”

o Open the Source Editor and paste in the following code:

<br><H2>Total Expenses: <FONT style="BACKGROUND-COLOR: transparent" color=#000080>

document.write('$' + cent(intCompleteTotal));

</script>

</FONT></H2>

o Click OK and then click Save.

Roll-Up with Totals – JavaScript Starter

<?xml version="1.0" encoding="utf-8"?>

<Title>Javascript Starter</Title>

<Description>Use for formatted text, tables, and images.</Description>

<FrameState>Normal</FrameState>

<Dir>Default</Dir>

<PartImageLarge>/_layouts/images/mscontl.gif</PartImageLarge>

<Assembly>Microsoft.SharePoint, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>

<TypeName>Microsoft.SharePoint.WebPartPages.ContentEditorWebPart</TypeName>

var intCompleteTotal = 0;

var intGroupedTotal = 0;

function round(number,X) {

// rounds number to X decimal places, defaults to 2

X = (!X ? 2 : X);

return Math.round(number*Math.pow(10,X))/Math.pow(10,X);

}

function cent(amount) {

// returns the amount in the .99 format

amount -= 0;

return round((amount == Math.floor(amount)) ? amount + '.00' : ( (amount*10 == Math.floor(amount*10)) ? amount

+ '0' : amount));

}

</script>]]></Content>

</WebPart>

Roll-Up with Totals – Total JavaScript.dwp

<?xml version="1.0" encoding="utf-8"?>

<Title>Travel Expense Total</Title>

<FrameType>Default</FrameType>

<FrameState>Normal</FrameState>

<HelpLink>http://download.corasworks.net/WPSupport/Adv-RollUp-Spreadsheet/default.aspx</HelpLink>

<Dir>Default</Dir>

<MissingAssembly>This Web Part has failed to load due to a missing software file. Please contact CorasWorks (http://www.corasworks.net) and provide them with the name of the Web Part [CorasWSC.Document.Spreadsheet.Rollup.Adv - Created with Roll-Up Wizard] and the URL in your browser.</MissingAssembly>

<PartImageLarge>_WPR_/Document.gif</PartImageLarge>

<Assembly>CorasWSC.Document.Spreadsheet.Rollup.Adv, Version=51.0.28.3, Culture=neutral, PublicKeyToken=bc991cc32b483a9c</Assembly>

<TypeName>CorasWSC.Document.SpreadSheet.RollUp.Adv</TypeName>

<tr>

</tr>

intGroupedTotal = 0;

</script>

<END>

<tr>

document.write('$' + cent(<%Total Expenses%>));

</script>

</td>

</tr>

intCompleteTotal = intCompleteTotal + <%Total Expenses%>;

intGroupedTotal = intGroupedTotal + <%Total Expenses%>;

</script>

<END>

<tr>

<th align=right>Total: </td>

document.write('$' + cent(intGroupedTotal));

</script>

</td>

</tr>

</table>]]></Display>

<ShowIcons xmlns="CorasWSC.Document.SpreadSheet.RollUp">false</ShowIcons>

<ListTemplates xmlns="CorasWSC.Document.SpreadSheet.RollUp"><?xml version='1.0' ?><Lists><List><ListType>XMLForm</ListType><ListTypeName>TEF0005</ListTypeName><NumberOfFields>22</NumberOfFields><OrderBy>ReportTitle, Status, CreatedBy, CreatedDATE, ManagerName, TotalExpenses</OrderBy><ListFields>Report Title,Status,Created By,Created~DATE,Manager Name,Total Expenses</ListFields><DisplayPer>,,,,,</DisplayPer><SearchFields>Report Title,Status,Created By,Modified By,Modified,Created~DATE,Manager Name,Total Expenses</SearchFields></List></Lists></ListTemplates>

<DynamicGrouping xmlns="CorasWSC.Document.SpreadSheet.RollUp">Status</DynamicGrouping>

<DynamicGroupingProperties xmlns="CorasWSC.Document.SpreadSheet.RollUp">blue,font-family: Verdana; font-size: 8pt; color: white; font-weight: bold,true</DynamicGroupingProperties>

</WebPart>

Roll-Up with Totals – Travel Expense Total.dwp

<?xml version="1.0" encoding="utf-8"?>

<Title>Travel Expense Total</Title>

<FrameType>Default</FrameType>

<FrameState>Normal</FrameState>

<HelpLink>http://download.corasworks.net/WPSupport/Adv-RollUp-Spreadsheet/default.aspx</HelpLink>

<Dir>Default</Dir>

<PartImageLarge>_WPR_/Document.gif</PartImageLarge>

<Assembly>CorasWSC.Document.Spreadsheet.Rollup.Adv, Version=51.0.28.3, Culture=neutral, PublicKeyToken=bc991cc32b483a9c</Assembly>

<TypeName>CorasWSC.Document.SpreadSheet.RollUp.Adv</TypeName>

<tr>

</tr>

intGroupedTotal = 0;

</script>

<END>

<tr>

document.write('$' + cent(<%Total Expenses%>));

</script>

</td>

</tr>

intCompleteTotal = intCompleteTotal + <%Total Expenses%>;

intGroupedTotal = intGroupedTotal + <%Total Expenses%>;

</script>

<END>

<tr>

<th align=right>Total: </td>

document.write('$' + cent(intGroupedTotal));

</script>

</td>

</tr>

</table>]]></Display>

<ShowIcons xmlns="CorasWSC.Document.SpreadSheet.RollUp">false</ShowIcons>

<ShowAdmin xmlns="CorasWSC.Document.SpreadSheet.RollUp">false</ShowAdmin>

<DynamicGrouping xmlns="CorasWSC.Document.SpreadSheet.RollUp">Status</DynamicGrouping>

<DynamicGroupingProperties xmlns="CorasWSC.Document.SpreadSheet.RollUp">blue,font-family: Verdana; font-size: 8pt; color: white; font-weight: bold,true</DynamicGroupingProperties>

</WebPart>

Roll-Up with Totals – Content for Travel Expense Total.dwp

<tr>

</tr>

intGroupedTotal = 0;

</script>

<END>

<tr>

document.write('$' + cent(<%Total Expenses%>));

</script>

</td>

</tr>

intCompleteTotal = intCompleteTotal + <%Total Expenses%>;

intGroupedTotal = intGroupedTotal + <%Total Expenses%>;

</script>

<END>

<tr>

<th align=right>Total: </td>

document.write('$' + cent(intGroupedTotal));

</script>

</td>

</tr>

</table>]]></Display>

<ShowIcons xmlns="CorasWSC.Document.SpreadSheet.RollUp">false</ShowIcons>

<SiteURL xmlns="CorasWSC.Document.SpreadSheet.RollUp">http://site/TravelExpApprovals/</SiteURL>

<ShowAdmin xmlns="CorasWSC.Document.SpreadSheet.RollUp">false</ShowAdmin>

<DynamicGrouping xmlns="CorasWSC.Document.SpreadSheet.RollUp">Status</DynamicGrouping>

<DynamicGroupingProperties xmlns="CorasWSC.Document.SpreadSheet.RollUp">blue,font-family: Verdana; font-size: 8pt; color: white; font-weight: bold,true</DynamicGroupingProperties>