So you’ve bought the OneStream Advanced Reporting and Dashboards book - congratulations on being on your way to mastering these tools! The book is chocked full of examples and guidance on how to tailor your user experience, but here’s something extra – a tutorial on how to use Map Components in your application. We’ll walk through setting up a Dashboard with an interactive map to provide users with a visual display of locations, from collecting coordinates to displaying locations that are click-enabled to display relevant data.
Need a Copy?
Looking for the OneStream Advanced Reporting and Dashboards book? Physical and PDF Copies are available.
Purchase a physical copy from Amazon or a PDF copy from OneStreamPress.com
1.) Collecting GPS Coordinates
In order to plot locations on a map, you’ll need the GPS coordinates for each location. What if you don’t know the coordinates? An easy way to find the latitude and longitude is by using Google Maps’ coordinate finder feature. Simply navigate to https://maps.google.com/ and right-click on a map location, you’ll see a menu appear where you can click on the coordinates to copy the GPS points to your clipboard. Save yourself some time by collecting the GPS coordinates for all locations you want to map and save these in a spreadsheet that we’ll use in the next step.
2.) Storing GPS Coordinates
In this example we’ll use the entity dimension to display coordinates, but any dimension can be used. From the Dimension Library, select the entity dimension and select the Grid View tab. Use the Member Filter Builder to select the entities to be mapped, then use the Grid Settings dialog to display the Name, Description, and Text5 properties. In the Text5 property, enter the GPS coordinates for each entity, this will be used to plot our entity locations on the map.
3.) Map Dashboard Data Set Business Rule
Navigate to the Business Rules menu and click Create Business Rule. Select the Dashboard Data Set Type and type in MapLoc as the Name, then click OK.
Copy & paste the syntax below into the Business Rule editor window and save the rule:
Namespace OneStream.BusinessRule.DashboardDataSet.MapLoc
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
Try
Select Case args.FunctionType
Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("LocationPinPoints") Then
Dim tempDataSet As New XFDataSet
Dim collection As New XFMapItemCollection
collection = OneStream.Shared.Wcf.XFMapDataSetHelper.CreateXFMapItemCollection(si, tempDataSet)
'Organization is the name of the entity dimension.
Dim EDimPK As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Organization")
'E#20000.Base is the entity filter that includes the members to be mapped.
Dim LCList As List(Of MemberInfo) = BRApi.Finance.Members.GetMembersUsingFilter(si, EDimPK, "E#20000.Base", True)
Dim LatLong, LCLat, LCLong As String
Dim LocationList As String()
For Each LC As MemberInfo In LCList
Dim entityID As Integer = BRApi.Finance.Members.GetMemberId(si, DimType.Entity.Id, LC.Member.Name)
'This indicates the entity members' Text5 property which is where we've stored our GPS coordinates.
Latlong = BRApi.Finance.Entity.Text(si, entityID, 5, False, False)
If LatLong.Length > 1 Then
LocationList = LatLong.Split(",")
LCLat = LocationList(0)
LCLong = LocationList(1)
' The first file creates the location image. The second file is the hover image.
Dim pin As New XFMapPinPoint(LCLat, LCLong, LC.Member.Description, XFIMageFileSourceType.ClientImage, "StatusBlueBall", 0, 0, LC.Member.Name, XFImageFileSourceType.ClientImage, "StatusGreenCheckMark")
Collection.AddPinPoint(pin)
End If
Next
Return collection.CreateDataSet(si)
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
The items defined below should be modified in the Business Rule according to your dataset:
| Property | Value | Action/Explanation |
| Dimension Name | Organization | Replace with your dimension name |
| Dimension Filter | E#20000.base |
Replace with your dimension filter |
| Text Property | 5 |
Replace with your selected Text property |
| Location Image | StatusBlueBall |
Icon to appear at each location |
| Hover Image | StatusGreenCheckMark |
Icon to appear at location hovered over |
OneStream has built-in images that we’ll use to display the locations and hover images, listed below are the valid images that are readily available.
- StatusGrayBall
- StatusWhiteBall
- StatusOrangeBall
- StatusBlueBall
- StatusRedBall
- StatusLightGreenBall
- StatusGreenBall
- StatusGrayCheckMark
- StatusGreenCheckMark
- StatusLockedWithCheckMark
- StatusLockedWithFolder
Keep in mind that you can use any image as the location points on the map, these images are intended to make the coordinates more obvious to users so they are drawn to click on them. To use your own images, click on the DMU’s File folder and click Create File, then upload your images. Replace “StatusBlueBall” with “yourimage.png” and replace “StatusGreenCheckMark” with “yourhoverimage.png” in the business rule syntax to apply these images.
4.) Map Data Adapter
Navigate to the Application Dashboards menu, select the DMU and click Create Data Adapter. Name the data adapter da_Locations_MAP, then select Method Command Type, BusinessRule Method Type, insert {MapLoc}{LocationPinPoints}{} into the Method Query editor, and finally name the Results Table Name PinPoints.
Save the data adapter and click Test Data Adapter to verify that the table is populated.
The Data Preview displays the filtered entities and coordinates entered in the members’ Text5 property.
5.) Map Component
Select Components and click Create Dashboard Component, select Map and then click OK.
Select the Data Adapters tab, attach da_Locations_MAP, then return to the Component Properties tab. The screenshot below shows the configuration which uses the central US GPS coordinates. The Bound Parameter and Dashboard to Open In Dialog will be explained in the next steps.
6.) Drilled Data Adapter
Create a new data adapter named da_PeoplePlanningRegister_MAP, then select Command Type SQL, Database Location Application, PeoplePlanning_MAP as Results Table Name, and paste the following into the SQL Query editor:
SELECT
RegisterID, FirstName, LastName, FTE, JobTitle, Status, Wage
FROM XFW_PLP_Register
WHERE wfscenarioname='Budget'
and wftimename='|GlobalTime|' and entity='|!EntityPicked!|'
The data adapter queries our People Planning Register that we will use to display personnel information by location, but you can use any database table can be queried. Notice the Bound Parameter we applied to the Map Component previously is used in the new data adapter’s syntax – this creates a run-time query that returns location-specific data. See the completed data adapted in the screenshot below.
7.) Drilled BI Viewer
Create a BI Viewer component and name it biv_PeoplePlanningRegister_MAP, then attach the da_PeoplePlanningRegister_MAP data adapter from the Data Adapters tab.
Click on the BI Designer tab and add dashboard items to the BI Viewer to display relevant data points of interest. For this example, we’ve created cards and a grid to display headcount information by location.
8.) Drilled Supplied Parameter
Create a Supplied Parameter Component and name it spp_EntityPicked_MAP, then enter EntityPicked in the Bound Parameter Action field. This component will be attached to the 2_LocationPLPRegister_MAP dashboard, allowing us to pass the Bound Parameter from the location clicked into the Drilled Dara Adapter query.
9.) Dashboard Creation
Click Create Dashboard and name the dashboard 1_Locations_MAP, then enter Field Office Locations into the description.
Select the Dashboard Components tab, attach the map_Locations_MAP component and then save.
Click Create Dashboard again and name the dashboard 2_LocationPLPRegister_MAP, then enter People Planning Register into the description. Configuring the Display Format is optional, this controls the size of the dialog when displayed.
Select the Dashboard Components tab, attach both the biv_PeoplePlanningRegister_MAP and spp_EntityPicked_MAP components, and then save.
10.) Completed Dashboard
Select the 1_Locations_MAP dashboard and click View Dashboard.
The dashboard displays the US Map as expected, hover over a location to view a green check mark.
Click on a location and a dialog appears displaying location-specific personnel information.
2 Comments
