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

OSAdmin
Valued Contributor
Originally posted by Henri Roest

5/4/2018

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

4 REPLIES 4

OSAdmin
Valued Contributor
Originally posted by Eric Osmanski

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.

Here

julieta
New Contributor III

Hello! i tried accessing the doc named How to suppress cube view parameter popup in spreadsheet upon refresh but i don't have access, is there another way i can read it? Thanks in advance.

ajslack
New Contributor II

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:

ajslack_0-1636759522417.png

 

 

 

Open the Cube View in Excel

ajslack_1-1636759522418.png

 

 

By creating a Cube View Connection

ajslack_2-1636759522419.png

 

 

Here's what the Cube View looks like: 

ajslack_3-1636759522421.png

 

 

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

ajslack_4-1636759522420.png

 

 

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

Nikpowar97
New Contributor III

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.

Nikpowar97_0-1691431577894.png

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.

Nikpowar97_1-1691432013750.png

Nikpowar97_3-1691432136309.png

 

Nikpowar97_2-1691432064107.png