InformationTitleBite Size Analytics: How to Build a Lens (Hands-On Exercise Guide)URL NameBite-Size-Analytics-How-to-Build-a-Lens-Hands-On-Exercise-GuideDetailsProduct AreaHelp Product AreaReporting & AnalyticsRelease VersionHelp Release VersionEnvironmentHelp EnvironmentApplicable to all versionsAdditional NotesHelp Additional NotesWhen getting Analytics licenses from Certinia, users get access to not only the out-of-the-box dashboards and datasets, but also to the analytics platform to build dashboards, lenses, and datasets. This byte concentrates on building lenses. A lens is simply a report. It has several aspects to it: it’s how users view data in a dataset, it’s the place where users explore the data graphically, and it’s the basis for building any dashboard. Given that lenses are the building blocks to dashboards and other useful stuff, it's a great place to start when learning how to use Analytics. The materials above were taken from the SummitX event, where Certinia held a live hands-on session. Users can follow the instructions in their own environment with modifications to details relevant in their company such as company and period. ProcedureHelp ProcedureExercise 1: Create A Lens Using Compare Table Open the App Launcher Search for and select Analytics Studio Click All Items under Browse header (left frame)Open the Analytics app created during implementationClick Datasets > Financial Balances to openNavigate to the Locate Chart Mode | Table Mode| Query Mode menu in the top-right of the tableClick Table ModeClick Compare TableClick Save in Object Header (Disk Icon) Title for Lens = "Year to Date Income Statement Training"Description = "My year to date Income Statement" Click Save A lens is now created and saved. Keep reading to learn how to add detail to the new lens. Exercise 2: Group by Trial Balance 1 & 2 and General Ledger Name Click + in the Group By section (the Data tab is at the far left with tooltip “Add column”)Search and select the following dimensions (click “+” in the Group By section to add additional dimensions): Trial Balance 1 (do not choose Local Trial Balance 1)General Ledger NameTrial Balance 2 (do not choose Local Trial Balance 2) Drag the Trial Balance 2 field above the General Ledger Account Name in the Group By section to reorder the columns from left to right to be Trial Balance 1, the Trial Balance 2, then General Ledger Account NameClick Save in the Object Header (Disk Icon) > Save The groupings on the lens is now reorganized. Keep reading to learn how to add value columns to the report. Exercise 3: Add Measure Columns to the Lens Now add value columns to the reports. These are known as measures within Einstein Analytics. A lens can be thought of as an aggregation of all data within that dataset for those grouping criteria. If the user is a multi-company organization, the lens is for all companies. This can be changed later, but for now, the lens is an aggregate of all companies. Now follow the steps below to add dual currency values that make sense (for example, wanting to create a lens that adds USD to GBP): Click Count of Rows under the Data tab (far left frame)Click SumLocate and click Dual Value measureClick + in the columns sectionClick SumLocate and click Dual Value measureClick the dropdown in the left frame on the first Sum of Dual Value (it has a blue handle)Click Edit this ColumnIn the column Alias, type PeriodIn the column Name, type PeriodClick Apply The column heading now states Period for the first measures column in the table. To change the heading of the other column: Click the dropdownClick Edit this ColumnIn the column Alias enter Year_to_Date (either type the word together or use underscores when entering the alias)In the column Name enter Year to DateClick Apply Click the Measures dropdown to see an option to clone a column. Rather than clicking the plus icon and adding the column twice, users can also change the first Dual Value column, edit its name, then click Clone. This generates a second measures column. To save time in the future, add the formula etc to a column to use the clone feature. Now save this lens: Click Save in Object Header (Disk Icon)Click Save Exercise 4: Filtering the Lens. Now filter the lens to give it more context. First, to make this an Income Statement, only include data that relates to the Income Statement rather than Income Statement, Balance Sheet, etc. There are many ways to do this, but the easiest is to use the General Ledger Account Type and filter ALL of the lens by this type. When filtering all of the lens, it is called a Global filter: Click the Filters tabClick +Click General Ledger Account TypeSelect Profit and LossClick Add Repeat the process above to add more filter criteria (for example Company) to reduce the data displayed in the table further. When a filter is added, the circle with the number of filters applied is shown next to the filter tab. Note all dropdowns presented are multi-select; so it is possible to select Profit and Loss as well as Retained Earnings. Click the Data tab to review the data, then filter the data shown in each of the Measures columns. Currently the data is the same in both columns: Click the Period Measure dropdown Click Add filterSelect Financial Year and Period(s) in the measure sectionClick Equals in the operator dropdownEnter 2019012 in the values boxClick Add The financial year and period looks odd formatted as a number rather than as a date. However, this is incredibly useful as it enables users to perform simple math such as "greater than", "equals to" , "less than", and so on. These operations would not be possible as a date format. Now filter the Year to Date column: Click the Year to Date Measure dropdownClick Add filterSelect Financial Year and Period(s) in the measure sectionClick Less than or equal to in the operator dropdownEnter 2019012 in the values boxClick Add This now filters the year to date column to show all data up to and including period 12 2019. Save the lens: Click Save in Object Header (Disk Icon)Click Save Exercise 5: Sorting and Formatting the Lens Within the Measures column, each number is displayed with decimal precision. This makes it difficult to read. To format the number so a decimal precision is not shown: Click the dropdown on the Period MeasureClick Format NumbersClick CustomSelect the second preset 1,235Click Done The Period column is now formatted in a sensible way. Now do the same for Year to Date: Click the dropdown on the Year to Date MeasureClick Format NumbersClick Custom’Select the second preset 1,235Click Done Now order the grouping columns. Ideally these are based on the picklist order for the Trial Balance levels that were set in Salesforce. For an Income Statement use the following order: SalesCost of SalesGross ProfitExpenses However, the above order is not alphanumeric ascending or descending. To fix this within our datasets for Financial balances and transactions, the measure columns TB1 Order -TB 4 Order is introduced. In principle, each picklist value within a trial balance level is given a number dependent on its order in the picklist. This means users can sort these columns and get their order. Lens contain Trial Balance levels 1 and 2. Certinia recommends using TB levels as these provide a financial reporting structure that minimizes excessive maintenance: Click + in the Columns sectionClick MaximumSelect TB 1 Order measureSelect TB 2 Order measureClick the dropdown on the TB1 Order measureClick Sort ascendingClick the dropdown on the TB2 Order measureClick Sort ascending Two new columns have been added and the order is now complete. Now hide these columns on the lens: Click the dropdown on the TB1 Order measureClick HideClick the dropdown on the TB2 Order measureClick Hide The columns are now hidden. Save the lens: Click Save in Object Header (Disk Icon)Click Save Exercise 6: Exporting The Lens Now share the lens so it can be included in the board pack or format the data in a spreadsheet tool, etc: Click the More icon in Object Header (three dots Icon)Click ShareClick the Download tabClick the appropriate option (CSV, Excel etc) Create a Rolling Trial Balance Navigate to the App LauncherClick Analytics StudioClick All Items under Browse headerOpen the Analytics app created during implementationClick Datasets > Financial Transactions to open Create a new lens that: Groups by General Ledger Name and Transaction DateHas three measures columns: Opening, Net, and ClosingContains measures filters as shown in the image. Remember to use Financial years and period(s) measure to perform less than type operationsSave the lens and name it "Rolling TB" To clean-up the look of the lens, click on the roller icon on the far right and select format tables. To add the rolling column: Click the dropdown on the Net measureClick CloneClick Edit Change the name and Alias to "Rolling_Trial_Balance". Remember to add underscores for the Alias: Click Function Icon (mathematical symbol)Click Running totalClick on the Column dropdown > select NetClick on the Reset group dropdown > select General Ledger NameClick on the Format dropdown > select CustomClick 1235Click DoneClick ApplyClick Close Now there is a running total on the lens. Remember to save it. Click Total on the top right of the table. This allows users to toggle on and off a grand total. Unfortunately, the grand total is displayed at the top rather than the bottom of the table. Be aware that the rolling total grand total is meaningless. ObjectiveHelp ObjectiveAt the end of this exercise, users will be able to create an income statement with period and year-to-date columns in dual currency that maps to the chart of account and is filtered in a way that is meaningful. Users will be able to apply the skills learned to create other income statements and a balance sheet. Simple Income Statement Using Financial Balances Dataset Stepped Approach Use of Compare tableSaving a lensGroupings Based on best practice chart of accountAdding Measure columnsFiltering global and by measureSorting and Formatting columnsExporting Simple Trial Balance Using Transactions Stepped Approach Formula (basic overview)Rolling featureSharing
Was this article helpful?00Choose a general reason-- Choose a general reason --FeedbackUpload FilesUpload FilesOr drop filesSubmit