The way you have above is only available through Extensible Docs. The other ways are selecting the parameter in the Excel workbook and setting your selection to be that parameter (there is a document in the files tab on this PIE channel). What I ended up going with in a similar situation you are in (I think) is creating a small CV on a sheet which has all of my parameters in there and my other tabs reference that sheet.
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
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?
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.
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
result = povEntity
' Brapi.ErrorLog.LogMessage(si,"povEntity: " & povEntity)
' Brapi.ErrorLog.LogMessage(si,"excelEntity: " & excelEntity)
Brapi.ErrorLog.Logmessage(si, "result = " & result)
however the normal parameter works.