Ever used Excel before? Good! Spreadsheets in Spry are very similar to Excel and because you’ve already looked at the Expression Editor there aren’t too many concepts you shouldn’t already be familiar with. Just be aware that although it’s very similar to Excel, it is not fully featured and will never be a full replacement.
Spreadsheets are extremely useful when a set data structure is required, and most Excel spreadsheets can be imported into and modified in Spry use with ease. This includes in-sheet charts and contextual formatting!
When you switch to the Spreadsheet window in Spry, note the additional tabs available, specifically the Formulas tab which you will use quite frequently.
The Function Library includes over 250 Excel type Functions such as SUMIF, VLOOKUP and the full documentation for these and their application is available here.
The Spry Function Library
Settings: These will provide you with Constants from your Scenario. Includes SCHEDULESTARTDATE which is used in our examples below. Full List:
- SOURCETABLE – Source Table Name (string)
- DESTINATIONTABLE – Destination Table Name (string)
- CALENDARTABLE – Calendar Table Name (string)
- SCHEDULESTARTDATE – Scenario Start Date (date)
- SCHEDULEENDDATE – ScenarioEnd Date (date)
- RUNSOURCESCHEDULING – Source Scheduling Switched On? (true/false)
- RUNDESTINATIONSCHEDULING – Destination Scheduling Switched On? (true/false)
- RUNHAULAGE – Haulage Switched On? (true/false)
- AssignedTruck – Truck assigned to Equipment/Process combination (string)
- ProjectName – Project Name (string)
- CaseName – Scenario Name (string)
- Reporting Period Count, Name, Start and End Dates (various)
Results: Pulling values off the Output Schedule (numbers only) requires these functions. Includes Schedule Sum, Schedule Min, Schedule Max and Schedule Weighted Average contextual menu appears after selecting, the most complex is for Schedule Weighted Average which is shown below.
All Results functions require a Value, a Reporting Level and Reporting Period (and Weight when Schedule Weighted Average) with an optional Filter. With the exception of the Reporting Level, these use the Expression Editor.
User Note: Generally after selecting your Reporting Period in the menu above and populating the cell, you would return and change the Reporting Period reference to a Date Cell. e.g. =ScheduleSum(“SourceQuantity”, “Process=ProcessCoal”, “Month”, DATEVALUE(“1/10/2013”)) changes to =ScheduleSum(“SourceQuantity”, “Process=ProcessCoal”, “Month”, B$1)
Data: There is only one Data function, which is TABLEVALUE. This pulls data directly from your table of choice and like a Results function has a menu to populate the options.
Have a #VALUE error? The Expression Errors button will give you an idea of what could be wrong
You can export your Spreadsheet either keeping Spry formulas intact, or make Spry calculate out all internal (non-Excel type) functions. Once exported, if functions have been pre-calculated the Spry functions are lost.
Start with your reporting periods. Use the SCHEDULESTARTDATE() to set your first time period, and then use the DATE() function to add increments that match the reporting periods you plan to use. For example =DATE(YEAR(C1),MONTH(C1)+1,DAY(C1)) adds 1 month to each sequential cell and you can just drag and drop across as many cells as you need.
Next format your date cells by right-clicking the selection of cells, choosing Format Cells… -> Date, and selecting the preferred Date Format.
Look below for some examples of how formulas have been used in Spreadsheets in Spry. You’ll note how instead of referencing a Constant explicitly, the formulas reference other cells that help to complete the formula and make quick changes. For example instead of “Process=ProcessWaste”, the formula is written “Process=Process”&$A$4&. The contextual formatting in the first example comes from outputting the schedule to Excel