SSRS Report With Dynamic Navigation Links

(0) Share

Report

Posted on by ChrysDW

We are on CRM 2016 on-prem. I'm building an SSRS report to show all phone call & task activities. For each activity, I have a hyperlink to navigate to the record. I need to give the users the link to the Regarding record of that activity as well. The syntax for the navigation link requires the logical name for the entity (=Parameters!CRM_URL.Value & "?ID={"&Fields!regardingobjectid.ToString()&"}&LogicalName=opportunity"). I could hard code the logical names based off of the objecttypecode in the sql query but there are too many entities to account for, the objecttypecodes differ from environment to environment, we would have to update this list as we create new entities, and not to mention that hard coding is just a horrible idea.

In order to grab the logical names dynamically, I was trying to use the lookup function in SSRS to lookup the name from another dataset using the EntityView view, but this didn't work. We tried to create a "filtered" view in another database and use the lookup function but that didn't work either.

(=Parameters!CRM_URL.Value & "?ID={" & Fields!regardingobjectid.Value.ToString() &"}&LogicalName=" & Lookup(Fields!regardingobjecttypecode.Value, Fields!ObjectTypeCode.Value, Fields!Name.Value, "DataSet1"))

I wanted to see if anyone had any ideas on how to get the entity logical names dynamically in order to populate the name into the syntax.

Thanks in advance,
Chrys

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Alex Fun Wei Jie 33,628 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I would create a field in the record, and a workflow to update this field using dynamics url. direct query this field.

but if you want to stick with your method. You can use below method to query the logical name based on objecttype code. (need some tweak on the code)

arunpotti.wordpress.com/.../how-to-get-list-of-all-entities-display-name-logical-name-and-other-info-using-sql-query-in-mscrm

Was this reply helpful? Yes No
ChrysDW on at

Like (0)

Report
Copy link

Link copied!

I appreciate your reply. Unfortunately, I believe the workflow idea may not be worth the effort UNLESS the business really pushes for this. The query will not work because I can only report on FilteredViews.

Thank you again for your ideas. They are much appreciated,

Chrys

Was this reply helpful? Yes No
Alex Fun Wei Jie 33,628 on at

Like (0)

Report
Copy link

Link copied!

Hi,

the workflow would be much more easier, and it is supported. In future you dont have to worry when move to Online. :) So the effort is worth it.

Was this reply helpful? Yes No
talk2.gauravb 120 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I understand the object type code changes in each and every environment, i have personally faced it. The suggested solutions for your issue are:

Solution 1:

Create a new entity with three columns (Entity Name, Object Type Code, Filtered View Name), populate this entity only once and it can be used as a dataset in your report and whenever you have to use it to build link use lookup function.

Solution 2:

Use entity table in your report and from the view only extract columns which are useful for eg: object type code and entity logical name. Put these data into a dataset and whenever you have to use it to build link use lookup function and find the correct object. Using this solution benefit is you dont need to do any data entry.

Regards Gaurav

Happy CRM!NG

Was this reply helpful? Yes No