How to restrict a drop down to show only months that have data in it

[Former Member]

Hello,

I have created a drop down which is based on a month characteristic.

When I run the model it is working OK and I see all the available months.

I would like to restrict this drop down to show only months that have data for them.

How can I do that?

[Former Member]

Hi Roy,

Were you able to solve your problem?

Your Mark

[Former Member]

Hi Mark,

The best thing will be to create a new query which will bring you only months with data on it and base the drop down on it.

Haven't tried it yet but it looks like the best solution to me...

Let me know if it is working for you

Roy

[Former Member]

Hi Roy,

when you use a dynamic list, then it's the best way to do this in the backend system as Mathias mentioned.

Best Regards,

Marcel

[Former Member]

Hi Guys,

I can use either static or dynamic but I assume dynamic would be best for this case (right?). Can you please elaborate this solution with an example perhaps?

Roy

[Former Member]

Hi Roy,

of course it is better to use dynamic, because your data changes time by time.

You want an example? Ok, when you have e.g. a table with orders and order date,

then check for each year if you have an entry in your table. If you had a table then add the date (year) to your list (for your dynamic entry list).

Best Regards,

Marcel

[Former Member]

Hello Marcel,

But where do I do it?

Do I need to create new query with the relevant rows and base the drop down on it?

Roy

[Former Member]

Hi Roy,

this depends on your backend system. If you use ABAP RFCs for the dynamic entry list then you can do this in your function module. If you use other data services from other backend systems you must do it there, e.g. oracle database you have to programm it with a stored procedure.

Can you give us the information what backendsystem do you use and what kind of data service?

Best Regards,

Marcel

[Former Member]

Hi Marcel,

I am using BI 7.0 as a backend and an XMLA Connector.

Roy

[Former Member]

Hi Roy,

I haven't work a lot of time with XMLA, but I think there is also way to resolve this issue.

As I know XMLA is similar to SQL and so you should be able to select all the years, which are in the records of your cube. Is there something similar as "distinct" in SQL?

Best Regards,

Marcel

[Former Member]

Hi Marcel,

DISTINCT is SQL is in case you have two identical results and you want to present just one of them.

I can use other connectors as well. For simplicity, how would you do it with the BW standard connector?

[Former Member]

Hi Roy,

Ok if there is nothing similar like distinct available in XMLA then use a function module.

Use a select distinct statement on the records of the BI cube. Therefore you must search the corresponding table of the data. You must have a knowledge about your system tables of your cube. There is no default service from the SAP available as I know.

Best Regards,

Marcel

[Former Member]

Hello Marcel,

But still on the abstract level: Should I create brand new query and reference the drop down to it?

Roy

[Former Member]

Hi Roy,

you have to fill your dropdown with a data service. You need to programm this data service in an ABAP function module.

Best Regards,

Marcel

[Former Member]

Hi Roy

How do you fill the drop down list? Static or dynamic.

If you fill it dynamic, the implementation of the restriction in the filling RFC or BAPI would be best.

Regards,

Matthias