In an existing quick view, you can convert to formula-based retrieves (XFGetCell) on intersections of data using the Convert To XFGetCells option in the top ribbon toolbar.
After selecting Convert To XFGetCells, OneStream displays the following prompts:
-
Are you sure you want to convert all the data in Quick View ‘Name of the Quick View’ to XFGetCells?
-
By clicking OK, the Quick View definition will be deleted and specific intersections of data will be converted to XFGetCell formulas.
This is a great way to set up a lot of formulas at once. However, these will hardcode all the dimension members in your formulas. If the member name is being displayed, the rows and columns can be synchronized to the pre-made quick view and cube view rows and columns. Your quick view can be adjusted to display the member name instead of the description through the quick view options.
NOTE: Quick view data that has been converted to XFGetCell cannot be converted back into a quick view. However, quick view content may be saved as a template before converting.
Additionally, XF Functions can be manually entered:
-
Select a cell and click on the insert function icon.
-
Select an XFGet function from the pop-up menu.
-
Fill out the necessary dimensions to build your XF Retrieve. If you do not want to fill one out, place a “” in the property and it defaults to the data in your cube POV.
-
Click OK to save the dimensions entered and the selected cell data changes to #REFRESH.
- If your query is in the correct syntax, data populates in the cell after refreshing the sheet.
NOTE: If a member name has been misspelled, the cell returns an error calling out the first misspelled member name.
Absolute Cell References
Absolute cell references are a functionality in the Excel Add-in that allows an exact cell reference to be kept when you copy the formula to another cell. To do this, put a $ in front of the portion of the cell reference that you want to keep.
For example, if you want to keep the entire A1 cell reference to copy to another cell you would change the cell reference to $A$1. If you only want to keep the column, the cell reference would be $A1 and if you only want to keep the row it would be A$1.
Relative Cell References
Relative cell references are the default state of cells in an Excel workbook. The reference is relative to the location of the cell. Formulas copied to new cells retain the location information and apply it to the new set of cells.
For example, if you reference A1 in cell C1 and then copy the formula into D1, the results will be a reference to B1 in cell D1 because the formula is referencing 2 columns before.
