Showing all Characteristic values from Master Data in WebI report

[Former Member]

Hi all,

I have a requirement in WebI report to show all characteristic values from master data, whether this characteristic value exists in transaction data (InfoProvider) or not.

From the following post: , I found that it's possible to show characteristic value from master data if we don't include key figure in query panel, but in my case I need to include and use the key figure.

This is possible in BEx, by setting the characteristic property (Advanced tab) for "Access Type for Result Values" = "Master Data" in Query Designer. Unfortunately, eventhough I have set this in Query Designer, the WebI report that's based on this query via Universe doesn't apply this setting.

Does anyone know solution for this?

Thank you in advance.

regards,

arie

[Former Member]

hi Arie,

was this issue resolved?

thanks!

[IngoH]

the setting you referring to is a Display setting for BEX Analyzer. if you want to achieve the same you can for example include a simple constant like the value "1" into the query

ingo

[Former Member]

Hi Ingo,

Thanks for the reply. Sorry, can you elaborate a bit more about using this constant technique? I haven't got clear understanding of how to implement it.

Below is a simple illustration using Student ID, Gender and Count key figure. The query result will still show the count for female, although no such gender in transaction data, but female is in master data gender.

Data in CSV format:

ZSTD_ID, ZGENDER, ZCOUNT

S001, MALE, 1

S002, MALE, 1

Query Result:

ZGENDER, ZCOUNT

Male, 2

Female, NULL

regards,

arie

[IngoH]

you simply add a formula with a constant value to the query

Ingo

[Former Member]

Hi,

You can achieve this result in following steps:

1. Create two BO queries (data providers).

Query 1 -> Gender Count

Query 2 -> Gender

2. At report level you need to merge the two Gender objects from the quries using merged dimension properties. Once you

merged the two dimension objects one new dimension will be created.

3. Now in the block you can pull the Gender from the merged dimension object or from Query 2.

Regards,

Rohit

[Former Member]

Thanks Ingo for the reply. Today I tried some methods:

- Using constant value inside formula defined in Query Designer (characteristic gender doesn't show all values from master data)

- Using constant value inside calculated measure defined in Universe as measure object (characteristic gender can show all values from master data only if we drill down by 1 characteristic only, which in this case is gender, drill down multiple characteristics showed error "A database error occured. The database error text is: Error in MDDataSetBW.GetCellData. See RFC trace file or SAP system log for more details. (WIS 10901)")

- Using merged dimension in WebI, combining query contains transaction data and query based on master data InfoObject gender. I can drill down by more than 1 characteristic, ie. Nationality & Gender, but got the following behaviors:

It shows all gender values from master data only if filtered by 1 nationality:

Nationality, Gender, Count

Indian, Female, 3

Indian, Male, NULL

Indian, Not assigned, NULL

If I show all nationalities in transaction data, it reverts to showing only if there's record in transaction data for that gender (but "Not assigned" is still shown here, which is from master data gender, cos in transaction data all records have gender specified):

Nationality, Gender, Count

Indian, Female, 3

Indonesian, Female, 4

Vietnamese, Male, 1

NULL, Not assigned, NULL

While in BEx Analyzer, the same query will show result below (which is what I require):

Nationality, Gender, Count

Indian, Female, 3

Indian, Male, NULL

Indian, Not assigned, NULL

Indonesian, Female, 4

Indonesian, Male, NULL

Indonesian, Not assigned, NULL

Vietnamese, Female, NULL

Vietnamese, Male, 1

Vietnamese, Not assigned, NULL

Is there any other possible way to have output similar like BEx Analyzer above?

Thanks.

regards,

arie

[Former Member]

Thanks Rohit for the reply. Yes, I have tried like what you mentioned, please see my post right after your post, the third point and its result behaviors.

[IngoH]

Hi,

can we first of all clarify if we are talking about a characteristic or a display attribute or a navigational attribute ?

ingo

[Former Member]

Hi Ingo,

The Nationality and Gender in example above are characteristics in InfoProvider, not display or navigational attribute.

Particularly for characteristic Gender, in its property in Query Designer (Advanced tab) I set for "Access Type for Result Values" = "Master Data", this shows all gender values from master data in BEx Analyzer as shown in example above, but not in WebI report.

Thanks.

[IngoH]

Hi Arie,

we clarified that already, that the settings are DISPLAY settings for BEx Analyzer and are not leveraged by Web Intelligence.

Did you try to create a constant formula by simply using for example "1" as value ?

thanks

Ingo

[Former Member]

Hi Ingo,

Thanks for the clarification. Yes, I have tried using constant value in formula. For further detail and some other ways I tried, please see in my post dated "Dec 28, 2009 8:11 PM" above.

Thanks.

regards,

arie

[LaurentS]

Arie,

The only solution we have for that issue is already mentionned above. You've to create two BO queries and to merge dimensions. This is the way we implemented and it works fine. In BO whenever you add a measure in a BO query, transactional data acts as a filter.

Add a 1 will not change anything as you noted previously.

Hope that helps!

Laurent

[Former Member]

Hi Laurent,

Thanks for the reply. Yes, I have tried using 2 BO queries and merge dimension, it works but not perfectly as in BEx Analyzer, hence still doesn't solve my problem. You can check my post dated "Dec 28, 2009 8:11 PM" above for further detail about the anomaly.

Thanks.

regards,

arie

[Former Member]

Hi Ingo,

I have the same issue, I have created formula with constant value 1 in Bex and I am using that formula in my report but it's still not pulling masterdata if there is no data in cube.

Thanks,

Ravi

[Former Member]

Your solution worked for me.

Thank you Rohit!

Liliana