Websheets are an important component of any planning or budgeting application. Proper design will ensure effortless deployment from one environment (e.g., Development) to another (e.g., Production). It will also make it easier for subsequent users or developers to understand and modify the websheet.
To demonstrate the design parameters, we will create a websheet for users to enter budget expenses by department. In Planning Analytics for Excel (PAX), start by creating an Exploration from the Department Input view in the Budget Input cube.
Next, convert it to a Dynamic Report, and click Show Format Area to unhide the format rows.
Insert columns at the left of the websheet for holding important variables used in the rest of the websheet.
From a design perspective, the above screenshot details how the websheet now has four sections. The upper left quadrant will contain variables used in the websheet. The upper right quadrant contains the formatting information that defines how the Input Form (the lower right quadrant) displays. The lower left quadrant can be used to contain data specific to each row that can facilitate conditional formatting, or as an error check. Finally, the lower right quadrant is the Input Form. Once the websheet design is complete, this is the only quadrant users will see when they access the sheet on the web.
The first variable to set is the server. In the latest version of Planning Analytics (PA), a new function simplifies determining the server name. The function is TM1PrimaryDBName(). Add the function to cell B1, and give it a range name, server.
Replace the references to the server in all the formulas with the range name. For example, the TM1RPTVIEW formula would be revised as indicated below:
In this example, users need to run a TI process that will copy the data from the Input cube to a Summary cube. This requires adding an Action Button that users will click to copy data to the Summary cube. When creating an action button that runs a TI process with multiple parameters, you can define the parameters as a set with a single range name. To prepare the sheet for the Action Button, we will create variables that we can reference in the Action Button. First, give range names to the three title dimension selections as they correspond to the three parameters that will pass to the TI process upon execution:
- pVersion – assigned to cell G16
- pYear – assigned to cell G18
- pDept – assigned to cell G20
Then create the variables in the upper right quadrant of the websheet. In cell B3, enter the name of the TI process, and give the cell a range name, pProcess. In cells B5, B6 and B7, respectively, enter =pVersion, =pYear, and =pDept. Give the range B5:B7 the range name pParameters.
The Action button properties window opens when an Action Button is added to a websheet. The first property to modify is the TM1 Server. We add =server and check the Use reference checkbox.
In the Process section, reference the range name given to the cell containing the TI process name, =pProcess.
Under Parameters, we simply reference that range name, =pParameters.
A couple of important points:
- Notice you don’t need to specify the parameter names, only the parameter values
- This means that the parameters must be listed in the same order that they are listed in the Parameters tab in the TI process.
Now hide the rows (i.e., 1:14) and columns (i.e., A:E) and the websheet is ready to publish. Using Excel’s group feature rather than explicitly hiding the rows and columns makes it easier to access them when modifying the websheet.
