Build navigation key

[mansart]

Hello, long time I try to fix this issue with no success, let me explain....
I would like to create a drill/Down report in report studio able to show key made of concatenation of current dimension member.
I created 2 hierarchies on dimensions: The 1st hierarchy called Family including 4 levels FA_GROUP/SUPER/COUNCIL/FA, the second hierarchy called Geography including 4 levels GEOGRAPHY/IOT/IMT/COUNTRY
The report should be:
Family   Geography   navigation_key
ALL           EMEA           ALLEMEA
When the user click the ALL he should see
ALL                                                                      Geography           navigation_key
MARCOM AND PRINT SVCS                       EMEA           MARCOM AND PRINT SVCSEMEA
SOFTWARE                                              EMEA           SOFTWAREEMEA
SERVICES                                                EMEA           SERVICESEMEA
When the user click the EMEA in the first row, he should see
ALL                                                                  EMEA                  navigation_key
MARCOM AND PRINT SVCS                        EUROPE                   MARCOM AND PRINT SVCSEUROPE
MARCOM AND PRINT SVCS                        MEA                      MARCOM AND PRINT SVCSMEA
SOFTWARE                                              EUROPE                SOFTWAREEUROPE
SOFTWARE                                               MEA                      SOFTWAREMEA
SERVICES                                                 EUROPE                SERVICESEUROPE
SERVICES                                                 MEA                     SERVICESMEA

Could you help in writing the corresponding formula for the navigation_key, please?
Regards
Claude

[bdbits]

Is this "navigation key" something in the database that you have to use?

As I read it, you want them to be able to select a subset, or be able to select all of the data. This strikes me as a user interface issue. The built-in prompting can accommodate this based off of your data hierarchies. The "navigation key" would then not be needed.

That, or I am missing something.

[mansart]

Hello, thanks for your answer. The key mechanism (join) cannot be handled in the DB because the model will become too complex and user will experience performance issue. The above solution proposal could work according to other tests I performed using not drill/down report.
My objective is to compute the key each time the user drills/down: The key will join existing key in budget table which is join to the Result table in the Report Studio itself. This is why I wanted to be able to find the right formula to concatenate the current dimension members.
thanks

[bdbits]

Joins in Report Studio invoke local processing on the Cognos server and are nearly always going to perform a lot worse than the database.

But, I now see you are using a dimensional model anyway. So, are you using filter() expressions as slicers on the target report? I hope you are not using detail filters on dimensional data. What you have detailed for key values is not predictable enough to split into two separate values. There are no delimiters, and the individual items are variable in length. Is it possible to change it so there is a delimiter between the two values? Why not just pass both values separately and do any concatenation or logical inspection on the receiving end?

[mansart]

Hello, thanks for your answer. you spoke I could find solution changing the data model....Even it is complex I guess you are right. So, I am thinking ways to update in order to achieve my need. Not easy but let me think again. I am working on solution right now. Thanks to push me that way. regards