The Lookup Entity and the Friendly Name in the Report Builder

July 15, 2008

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.

Add comment
facebook linkedin twitter email

Leave a Reply to Ella Maschiach Cancel Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

5 comments

  1. PedroCGDJuly 19, 2008 ב 3:35

    oooohhoo… Microsoft in action! 🙂
    I dont use Report Builder for now… 🙂
    Cheers!!!!!!!!!

    Reply
  2. shivaniApril 1, 2009 ב 21:12

    If instead of defining friendly names in DSV, I change the names of entities in the model itself, do I need to change the corresponding role names as well? Even if the role is hidden?

    Reply
  3. Ella MaschiachApril 2, 2009 ב 9:33

    Hi Shivani,

    If the corresponding role in hidden then I guess that changing the entity name in the model should be enough

    All the best,
    Ella

    Reply
  4. MihkelNovember 4, 2009 ב 1:42

    You said:
    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.
    —-
    But then I generate my model, only table names are friendly, on columns I get “ugly” database names.
    Is there some trick to force generation put friendly names on columns too?

    Reply
  5. Ella MaschiachNovember 5, 2009 ב 9:59

    Hi Mihkel,

    If you put the friendly names after you generated the model, then perhaps you should try deleting the relevant attributes and re-generate the model. The attributes should regenerate with their friendly name.

    Hope that helped,
    Ella

    Reply