Did you, just like me, define a Lookup Entity in your Report Model? And did you, just like me, define friendly names in your Report Model's DSV? If you have, then you may have come across a problem similar to mine…
So how did we get there? First and foremost, when you click the relevant tables and columns in your Report Model's DSV, you defined a FriendlyName property for them. That way, when you generate a Report Model based on the DSV with the friendly names, you get the Model with the user – friendly names you defined.
In the example below, you'll see this demonstrated for Product Category (which I changed to Category) and Product Subcategory (which I changed to Subcategory) in the Adventure Works sample DB:
Now, let's say you have a table in your DSV which is connected to a description table which has two columns in it: one is a code attribute, and one is the matching description. In such a case, you can define the entity based on that table as a Lookup Entity. That means that instead of navigating to that entity in the Report Model, you see it's attribute in the main entity connected to it.
For a Lookup Entity, you have to define just one attribute in its IdentifyingAttribute property. I would recommend that that attribute will be the description column. Then, after you defined that the IdentifyingAttribute is the description column, you need to add to the main entity the code column. You do that by right clicking the main entity and choosing New > Source Field. You get a NewAttribute. You go to the Binding property of the NewAttribute and choose the code column which connects it to the Lookup Entity. That way, when your user just wants to look at the code the report model can give it to you without having to go through a join to the Lookup Entity table. In the example below, I've decided to add the Product Category ID to the Subcategory Entity:
Still, you have a problem. Though most of the names are now in your Friendly Name the role you have to the Lookup Entity is in its original name. In the example below, you'll see the role connecting the Subcategory Entity the Category Entity is called "Product Category", though that friendly name isn't used in either of the tables of my DSV (as shown in the first example):
Perhaps the problem wouldn't just jump at you if you were using friendly names in English, but I admit I was using friendly names in Hebrew and for me the problem just popped off from the user interface, as there I saw the name given in English to the role and next to it the friendly name in Hebrew of the relevant identifying attribute. I tried at first to simply change the name of the role to Hebrew as well, but that didn't work. I would see twice Hebrew now – the name I gave in Hebrew to the role and the name in Hebrew of my identifying attribute.
So how can you solve your problem? How can you influence the role so that it only shows one name, a name which you desire?
You need to pick the relevant role, right click it and choose "Rename". Give it the friendly name of the IdentifyingAttribute (as that will be the attribute your user gets when he clicks that role). Now, all that's left is to change the property of ContextualName to Role.
So I guess when you do define a friendly name and a Lookup Entity in your Report Model, it's important to remember your identifying attribute and your contextual name, because again, it's the little properties that count.
Microsoft is giving you the chance to learn more about SQL Server 2008 with free chapters from Peter DeBetta upcoming book - “Introducing SQL Server 2008”. Chapter 1 details Policy-based Management and you can view it online. If you'll sign up, then you'll be able to read also about Transact-SQL Enhancement in SQL Server 2008 (which are detailed in the 11th chapter of the book).