Inserting dropdown box

[Former Member]

Dear all,

it is a business requirement to be able to chose between different seasons within my company. Therefore I need a dropdown box, which is "connected" to the prompt and refers on a variable (not like a usual Excel dropdown, which is more static with pre-defined selections). This means, it should be dynamic and if I change the season within the dropdown, it should also change it in the prompt and therefore change the workbook.

As far as I know this was also possible in BEx Analyzer.

Can anyone please give me some advice?

Thanks in advance!

Kind regards

Dominik Drebinger

[former_member245941 · Accepted solution]

Hello Dominik,

Did you take a look at the FilterComponent from the Analysis Tab?

It is not a Combo Box and works as a filter, as it only links to the Standard Filter Dialog of Analysis. Therefore this might not be exactly what you need, but perhaps it is close enough to be applicable in your case.

Kind Regards,

Werner

[Former Member · Accepted solution]

Update:

Hey Werner,

I just tried it. In general your approach is working fine, but unfortunately the user has to select the season already in the prompt. In the prompt itself he has then 3 options, which he could chose. Therefore the filter is only working for me, if I make the season an optional selection and not mandatory (which it is right now).

Do you have any other idea/solution? Does it e.g. make sense to introduce a new variable, which will then be filtered?

Thanks in advance

Dominik

Hey Werner,

thanks for the advice, I will check that and come back to you.

Kind regards

Dominik

[Former Member]

Finally got it! It was SAPSetVariable that changed the prompt for me. The really hard part was trying to figure out what iPromptName really meant. It is the actual name that it is called in the prompt box, not my technical name, not my key, not my text, but the actual name in the prompt box. Please let me know if you have any questions.

Option Explicit

Sub SetFilter()

Dim lMsg As Long

Dim lResult As Long

'Updates invisible

Application.ScreenUpdating = False

lResult = Application.Run("SAPSetRefreshBehaviour", "Off")

lResult = Application.Run("SAPSetVariable", "Fiscal period/year", Range("AD1").Value, "INPUT_STRING", "DS_1")

If lResult = 1 Then

    lMsg = Application.Run("SAPAddMessage", "The prompt was set.", "INFORMATION")

Else

    lMsg = Application.Run("SAPAddMessage", "The prompt could not be set.", "ERROR")

End If

' Updates visible

Application.ScreenUpdating = True

lResult = Application.Run("SAPSetRefreshBehaviour", "On")

End Sub

[Former Member]

Hi Dominik,

I had a similar problem where the Finance user wanted to view their report by cost centre node without having to use the prompts screen so that the report did not have to refresh each time.

This method allows you to refresh data only once and then you can toggle the data you want to view via the Excel list functionality.

Hope this helps

Using Excel Lists
instead of SAPSetFilter Component

Using Excel lists instead of the SAPSetFilterComponent will
enable you to toggle your report without having to refresh your report each
time. For example if you would like to view each of your cost centre nodes on
one report without having to refresh.

Step 1

Ensure your data source includes all of the centres as a
Column heading. Expand your centres to the correct level .

Month Movement Current
MTH_MOV_CUR
[-] Customer markets	[+] Access Points	[+] Head Office Admin	[+] Points of Representation	[+] Pro-Active Sales	[+] Suites
1000PBB05CH003	1000PBB06CH007	1000PBB06CH008	1000PBB06CH009	1000PBB06CH010	1000PBB06CH011
ZAR	ZAR	ZAR	ZAR	ZAR	ZAR

Step 2

In a separate sheet, create a list of cost centre nodes you
would like to report on including their node names.

[+] Access Points	1000PBB06CH007
[+] Head Office Admin	1000PBB06CH008
[+] Points of Representation	1000PBB06CH009
[+] Pro-Active Sales	1000PBB06CH010
[+] Suites	1000PBB06CH011

Step 3

In the next column create a concatenate formula so that the
node name can be recognised by the SAPGetData formula. =CONCATENATE("ZMIPROFIT=+",F1,"(0HIER_NODE)")

[+] Access Points	1000PBB06CH007	ZMIPROFIT=+1000PBB06CH007(0HIER_NODE)
[+] Head Office Admin	1000PBB06CH008	ZMIPROFIT=+1000PBB06CH008(0HIER_NODE)
[+] Points of Representation	1000PBB06CH009	ZMIPROFIT=+1000PBB06CH009(0HIER_NODE)
[+] Pro-Active Sales	1000PBB06CH010	ZMIPROFIT=+1000PBB06CH010(0HIER_NODE)
[+] Suites	1000PBB06CH011	ZMIPROFIT=+1000PBB06CH011(0HIER_NODE)

Step 4

On the face of your report create a drop down list in Cell
A1. On the MS Excel Ribbon select Data, Then select data validation.

In the allow box select list and choose the list you created
in Step 2 as the Source.

Your List will look like this

Step 5

In the cell next to your list (cell A2), create a vlookup to
find the cell that you created in Step 3.

=VLOOKUP(D1,Parameters!$A$15:$c$19,3,FALSE)

Step 6

Now reference the vlooked up cell (Cell A2) you created in
Step 5 in your SAPGetData formula and lock the cell using as follows ,$A$2. If
you have referenced your measure type, and GL account (Please refer to manual
on referencing your own you can drag the formula across your report.

SAPGetData("DS_1",D$4,$B8,$A$2)

[Former Member]

Gary, this works well if your user are willing to wait for that initial data dump, but the size of my data dump and the slow download speed are deterants to my users. I can't download 3000 cost centers and let  them filter through them all. They need to be able to interact with the prompts to reduce the size of the data dump. Once this is accomplished I will be looking to send 1 prompt to multiple data sources to simulate a multi provider in excel and join several different types of data along the same dimensions they have in commen.

[Former Member]

Hi Mike,

Agreed this method will only be useful to the user if they are willing to wait for the initial dump. To get around this we used an Excel scheduler (macro) that refreshes all the data sources in the workbook before the user gets to the office. This way they have all the data they require to do their anaysis during the day. In this case we were working with around 200 cost centre nodes. The Finance team needs to analyse all of these cost centres each day so they require instantaneous access to the information in their set format. They also require this in different views as they want to filter another report by GL account so that they can look at suspense accounts by cost centre node. We used the same data sources to provide this view.

In this example we also referred to multiple data sources for each column in the main report which brought in the different measure types (actual, budget, revised estimate). If possible please can you explain further how you send 1 prompt to multiple data sources?

Regards,

Grant

[Former Member]

i could try that grant, but I think my data set is still to large to try to download it. I am basically emptying the cube into excel at that point.

i am still working on send one prompt to one data source in analysis, which is why I am on this thread. But in the past I have been using your methodology of dumping and storing the data. For example: dump cost cube data then dump sales cube data. Then place a pivot table on each data set where I set the shared dimensions in the filter for each. Both can be filtered by organization and region. So I set one drop down list for the users to interact with and then set a macro the force both pivot tables to filter by the users selection. Then use the get pivot formula to bring back my formatted report.

[Former Member]

In the WDEAN1 course there is an example for your drop-down requirement, to set a filter.

When you chose the value from drop-down you can use the attribute to assign it to the respective variable in the prompt and execute a refresh (all via VBA).

Regards, Martin

[Former Member]

Thanks for the advice, Martin!

This course sounds pretty useful, but unfortunately the next session is in 7-8 weeks and I would need a solution before that.

Do you know some more details about that approach?

Kind regards

Dominik

[Former Member]

Hi Dominik,

if you let me know your E-Mail address I can send the example file to you.

Regards,

Martin

[Former Member]

Hey Martin,

thanks for your help. Can you follow me, so I can send you a direct message with my mail contact?

Kind regards

Dominik

[Former Member]

Any chance you can send me the same?

[Former Member]

"Follow me" and send me your mail address.

Regards, Martin

[Former Member]

Dear all,

another question would be, if it is possible to insert a dropdown into a ribbon:

The goal is to have a button, a dropdown selection and the percentages next to each other.

I'm thankful for any advice!

Kind regards

Dominik

[former_member245941]

Hello Dominik,

This might be a new Topic under the SCN Rules of Engagement and probably should have been raised in a new Diskussion. I'll try to give you some advice anyway.

It is indeed possible to modify the Ribbon UI. That includes adding a Dropdown Box. I don't consider it an easy Task, though. You might be better off working with a solution that does not make the choice of a season mandatory in your users query prompts. You could use VBA macros to change the Prompt without modifying the Ribbon UI, which should be far easier.

For further Information on extending the Ribbon UI you might want to take a look at the MSDN pages:

Customizing the 2007 Office Fluent Ribbon for Developer

Some Information on the Topic can be found in the Analysis Help. At the end of the chapter for using Macros within Analysis Workbooks. Search for "idQ" in the helpfile.

There were some discussions about modifications to the Ribbon UI here on SCN too, but for some reason my superficial search to provide a link failed.

You would have to inject something like the following example into your specific workbook:

a) a modification to the.rel XML of the workbook as explained on the MSDN pages and in the Analysis help

<Relationship Type=http://schemas.microsoft.com/office/2006/relationships/ui/extensibility Target="customUI/customUI.xml" Id="customUIRelID1234"/>

b) a customUI.XML like this:

customUI xmlns="http://Schemas.microsoft.com/office/2006/01/customui xmlns:x="SBOP.AdvancedAnalysis.Addin.1">
<ribbon>
<tabs>
<tab idQ="x.com.sap.ip.bi.Analysis.menu">
<group id="TestGroupID" Label="TestGroupText">
<Combobox id="Combo1" Label="Season" onChange="Modul1.MyComboBoxMacro">
<item id="S1" label="Season1" />
<item id="S2" label="Season2" />
<item id="S3" label="Season3" />
</Combobox>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

c) a modul (Modul1) in your workbook with a VBA Sub registered to the onChange of the Combobox:

Sub MyComboBoxMacro(ByVal control as IRibbonControl, strChoice as String)
'[...] your code here
'lresult = Application.Run("SapSetVariable", <Prompt>, <Value>, <Datasource>, <Format>)
'[...] your code here
End Sub

Kind regards,

Werner