Overview
In this session, you'll start building forms with the P&L Report.
You will:
- Define the structure of the report.
- Layer in details and fine-tune settings.
- Format and style the report.
Transcript
[transcript]
In this session, we’ll begin building the Forms we need. We’ll start with the P&L Report.
First, we need to define the structure of the Report. Then we’ll layer in some details and fine-tune a few of the settings. And lastly, we’ll make some cosmetic changes and style the report appropriately.
The P&L report is in Reporting Model. We can go to the FORMS node, and add it here. Click Edit and open the Layout tab. This is where we define the Form structure. Note, that we don’t need to build all the calculations at this point, we only need to setup the general layout. We can see all the Model Dimensions listed here. Under each Dimension node, there are three types of icons:
Member Lists, Leveled Hierarchies, and Attribute Hierarchies. Attribute Hierarchies allow us to reference the attributes of a Dimension in the layout of the Form. On the columns, we see the three axes – Row, Column, and Filter.
We just need to assign the Dimensions to the axis they belong to. To do so, let’s refer back our Architecture Document. The P&L Report has Account on the rows, Months on the columns, and the rest of the Dimensions on the Filters.
So in Kepion, let’s check Row for Account, Column for Months, and then Filter for the other Dimensions. Make sure to select the Product and Entity Hierarchies, rather than the flat Member Lists. Let’s also include the FiscalYear attribute from the Time Dimension.
Next, let’s configure which Dimension Members we want to display on each axis. We can start with the Row.
On the left-hand side we have the Account Member List. We can either click on the members individually to add them to our Form, or we can use the actions here to bring in whole sections of the Hierarchy together. Clicking Single will bring in only the selected member; All will bring in all the descendants of the selected member, Children will bring in just the members on the next level of the hierarchy, and Leaves will bring in all the members on the lowest level of the hierarchy.
As we need all of the members under the P&L Header, we can select P&L and click All.
Moving onto the Column tab, we want the ability to view any month, so let’s begin by selecting the All Node and choosing Leaves. If we return to the Main tab, we can see our Form start to take shape. However, listing out every month on the columns makes the Report much too long. Let’s setup the filters to scope it down.
In the Filter tab, we have a few options available to us.
Under Header Display we can change the display name of the Filter. As the name suggests, Default Member allows us to choose which Member is selected by default. The scope option allows us to limit how many Dimension Members are available as options in the Filter. Selecting Read-Only prevents users from changing the Filter Selection and Multi-Select allows them to choose more than one Member.
Let’s select default options for each of our Filters.
Let’s take a look at the Form we have now. Since we selected FY20 in our Fiscal Year Filter, only months from that Fiscal Year are visible on the Form. Now the general structure matches our Architecture Document, but we still need to fine-tune some details.
First, we want to be able to see the total values for the year, so let’s return to the Column tab and select the All node itself by double-clicking All in the Member List. In our Report, this column will now show the total values for each account.
Now let’s work on the rows. First, we want to show the indents in the Hierarchy. To do this, we need to go to the Property tab. Here we can override the default display settings. To show the Hierarchy structure, we need to override the row definition. We’ll choose to show the MemberName with the Indent Hierarchy option checked.
Now we can see the Account Hierarchy.
In the Architecture Document, we have the parent members listed under the Child members. We can quickly change this by going to the Layout tab and checking the Reverse box for Account. Now our Bottom Line is at the bottom of the Report.
We’ll finish the Report by adding some general formatting. As a best practice, try to set the formatting by sheet, row, or column if possible, as this will decrease the Form size compared to formatting individual cells.
In the ‘Advanced Settings’ tab we can change the default Cell Colors, Show or Remove Gridlines, and enable our Hierarchy to be collapsible. Now our P&L Form is complete.
In this session, we looked at the basics of Form Design. Most Forms you build will follow a similar process. First, we defined the overall layout and structure of the report. Then we layered in some details and configured a few settings. And lastly, we styled and formatted the report appropriately.
In the next session, we’ll create our three Input Forms and explore some additional options available to us when designing Forms.
[/transcript]