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
    3 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.