A

Anonymous

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

Forum|Forum|6 years ago
June 16, 2020
2 replies
0 views

Originally posted by Henri Roest

5/4/2018

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

Excel Add In

A

Anonymous

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.

https://teams.microsoft.com/_#/docx/viewer/teams/https%3A~2F~2Fonestreamsoftware.sharepoint.com~2Fsites~2FServices~2FShared%20Documents~2FPIE%20%28Prod%20Info%20Exchange%29~2FHow%20to%20Suppress%20Cube%20View%20Parameter%20Popup%20In%20Spreadsheet%20Upon%20Refresh.docx?threadId=19%3A4afda24c88ed4a35b85f89ed3c4efadd%40thread.skype&baseUrl=https%3A~2F~2Fonestreamsoftware.sharepoint.com~2Fsites~2FServices&fileId=23541273-4096-4E51-AC54-3477EA2A8EAD&ctx=files&viewerAction=view

Contributor

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.

Newcomer

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.

Contributor

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.