The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

OSAdmin's avatar
OSAdmin
Icon for OneStream Employee rankOneStream Employee
6 years ago

Can we pull a parameter in Spreadsheet which is in a Dashboard ?

Originally posted by Henri Roest 5/4/2018 Can we pull a parameter in Spreadsheet which is in a Dashboard ?
Excel Add In
ajslack's avatar
ajslack
New Contributor II
4 years ago

I believe this is what you're looking for: 

 

Can Parameter Prompts be Removed When the Cube View is Opened in Excel?

Authored by Paula Mihalko

 

Question

If you have a cube view that has a prompt (say for Entity) and you open the cube view in Excel, every time you refresh the prompt will pop up.  Is there a way to "remember" the selection for that parameter or otherwise remove the prompt?

 

Answer

Yes, by setting up a NAMED RANGE cell with the same name as the parameter.  

 

Example cube view - note there's a prompt for the Entity:

 

 

 

Open the Cube View in Excel

 

 

By creating a Cube View Connection

 

 

Here's what the Cube View looks like: 

 

 

To "replace" the need for the prompt, add a single cell named range with the same name as the parameter:

 

 

Then when the sheet is refreshed, there's no popup for the parameter.

Nikpowar97's avatar
Nikpowar97
Contributor
2 years ago

Hey ajslack ,

Thanks for posting this thread. I have acheive this but I am facing an issue in to make my the CV more generic. We have a solution implemented using table views and the parameters in an EXCEL. 

The solution works flawlessly when you refer a parameter in the CubeView. 

I wanted to make this solution available to achieve this I am passing the cubeview name in Rows and column sharing. 

When I try to refer the XFBR in the CubeView POV refering  the paramter in a XFBR paramter. the XFBR return the value in error log as epected but Does not render the Cube View in Excel 😞

 

Image below using XFBR retrun value in error log but does bot render the CV in excel.

If args.FunctionName.XFEqualsIgnoreCase("GetPOVEntity") Then
Dim result As String
Dim CV As String = args.NameValuePairs.XFGetValue("CV") & ")"
Dim excelEntity As String = args.NameValuePairs.XFGetValue("prmEntity", "Not found")

Dim povEntity As String = BRApi.CubeViews.Metadata.GetCubeViewItemUsingName(si, CV, False, False, args.CustomSubstVars).CubeView.CubeViewPovMembers.Entity.Name
If povEntity.XFEqualsIgnoreCase("|!prm_WorkspacePov_Entity_CWM!|") Then
result = excelEntity
' result = "|!prm_WorkspacePov_Entity_CWM!|" 'NIK WAS TESTING COMMENTED
Else
result = povEntity
End If


' Brapi.ErrorLog.LogMessage(si,"povEntity: " & povEntity)
' Brapi.ErrorLog.LogMessage(si,"excelEntity: " & excelEntity)

Brapi.ErrorLog.Logmessage(si, "result = " & result)

Return result

'******************************************************************

ERROR LOG

however the normal parameter works.