Friday, 5 April 2019

Using the Lookup function to create reports on multiple DataSets

With Dynamics 365 Online, the greatest thing I miss compared to OnPremise is the relative limitation on what you can do with reports, as FetchXml is not so powerful as SQL. One option is to use the Data Export Service to get the data into a SQL database, and get the SQL queries out, but there is still a lot that can be done with FetchXml and Reporting Services. This post shows how to combine data from multiple datasets using the Lookup function.

My first rule of thumb when working out if a given SQL query can be implemented as a FetchXml query is, 'can the SQL query be written so that there is just one SELECT statement ?' If so, you've got a good chance of being to rewrite as FetchXml, but if not, you won't be able to do this in FetchXml. This test is useful, as it immediately eliminates Union queries, sub-queries and table expressions, which you can't do with FetchXml.

So, in many cases you can't do get the result that you want with one query, but Reporting Services allows you to define multiple Datasets, and hence multiple queries, in one report, and the Lookup function allows to connect the data across the Datasets.

For this post, I'll use an example I came across recently when the requirement was to get a count of records created per user, broken down by entity type. The simplified output I was looking for was:

UserLeadsOpportunities
David Jennaway2010
My Friend158

This will need to get data from the systemuser, lead and the opportunity entities. It is possible to join these in one query, but not in a way that is useful, as you end up multiplying the opportunity and lead records.

Instead, we can create separate queries. Here I'm doing one for each entity, systemuser, lead and opportunity. The systemuser query will end up as the main source for the table, with lookups to the other queries to get the respective record counts. The 3 datasets and queries are:

dsUser:
<fetch > <entity name='systemuser' > <attribute name='systemuserid' /> <attribute name='fullname' /> </entity> </fetch>

dsLead:
<fetch aggregate='true'>
    <entity name='lead' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='leadid' alias='lead_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsOpportunity:
<fetch aggregate='true'>
    <entity name='opportunity' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='opportunityid' alias='opportunity_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsLead and dsOpportunity are both simple aggregate queries to get the respective record counts for each entity by user.

Then, to create the report, I add a table based on the dsUser dataset, with the Fullname in the first column. Then for the count of leads, I can use the following Lookup expression:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead")

Taking each of the parameters in turn:
  • Fields!systemuserid.Value - this is the Guid for the systemuserid in the dsUser dataset. This value will be compared against...
  • Fields!createdbyValue.Value - this is the Guid of the createdby in the dsLead dataset. Note that I use createdbyValue to get the Guid, as for lookup attributes the createdby will be the name
  • Fields!lead_count.Value - this is the field in the dsLead dataset that I want to display
  • "dsLead" - this is the name of the dataset that the Lookup works on
We can then do the same for the expression for the opportunity count:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")

And that's it to get the basic report. As a nicety, I can add a filter for the row visibility, so that it hides rows where there is no count across any of the datasets. The Lookup function returns Nothing if no record is found, so we can use the IsNothing function.

=IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")) AndAlso IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead"))

We can keep adding extra datasets to count other entities, using the same approach. I don't have the patience to work out if there's a practical limit to the number of datasets we can use in one report. 

A couple of points to note:
  • You can't use the Lookup function as a calculated field, which is slightly annoying, as I think it would be neater if this were possible. I expect this is due to how Reporting Services first processes the datasets, and will then render the results
  • When testing in Visual Studio, you get prompted for credentials (or to use cached credentials)for each dataset in turn. I don't think you can do anything about this. Interestingly, it looks like Visual Studio caches the credentials per dataset, and they can be differ even if they use the same datasource. I once managed to have different datasets querying different CRM organisations, even though they were using the same datasource
I'm intending to post the full report up on GitHub in the next few days, once I've got that working properly





31 comments:

kirankumar said...

Good blog information by the author
Sanjary Academy is the best Piping Design institute in Hyderabad, Telangana. It is the best Piping design Course in India and we have offer professional Engineering Courses like Piping design Course, QA/QC Course, document controller course, Pressure Vessel Design Course, Welding Inspector Course, Quality Management Course and Safety Officer Course.
Piping Design Course

kirankumar said...

Nice blog information of the topic

Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,Welding Inspector Course, Quality Management Course, #Safety officer course.
Document Controller course
Pressure Vessel Design Course
Welding Inspector Course
Safety officer course
Quality Management Course
Quality Management Course in India

kirankumar said...

Great blog article informative I liked it

Best QA / QC Course in India, Hyderabad. sanjaryacademy is a well-known institute. We have offer professional Engineering Course like Piping Design Course, QA / QC Course,document Controller course,pressure Vessel Design Course, Welding Inspector Course, Quality Management Course, #Safety officer course.
QA / QC Course
QA / QC Course in India
QA / QC Course in Hyderabad

Ruhi Sukhla said...

BA Exam Result - BA 1st Year, 2nd Year and 3rd Year Result

Bsc Exam Result - Bsc 1st Year, 2nd Year and 3rd Year Result

Chandra Sekhar Reddy said...

Thanks for sharing information
We are the best piping design course in Hyderabad, India. Sanjary academy Offers Piping Design Course and Best Piping Design Training Institute in Hyderabad. Piping Design Institute in India Piping Design Engineering.
Piping Design Course
Piping Design Course in india
Piping Design Course in hyderabad

Tripu Design said...

This is an amazing blog, thank you so much for sharing such valuable information with us.
Visit for best logo and brochure designing services at- brochure designer in gurgaon.
Freelance Graphic Designing:
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon

Chandra Sekhar Reddy said...

Thanks for sharing
Sanjary kids is the best playschool, preschool in Hyderabad, India. Start your play school,preschool in Hyderabad with sanjary kids. Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and Teacher Training Program.
play school in hyderabad
Preschool in hyderabad
Preschool teacher training course in hyderabad
pre and primary teacher training course in hyderabad

D365 said...

Informative blog. Thanks for sharing.
MS Dynamics Operation Trade and Logistics Training

TNK Design Desk said...

Very useful and informative blog. Thank you so much for these kinds of informative blogs.
who provides seo services and e-commerce development services.
website designing in gurgaon
best website design services in gurgaon
web company in delhi
web desiging company
web design & development banner
web design & development company
web design & development services
web design agency delhi
web design agency in delhi
web design and development services
web design companies in delhi
web design company delhi
web design company in delhi
web design company in gurgaon
web design company in noida
web design company list
web design company services
web design company website
web design delhi
web design development company
web design development services
web design in delhi
web design service
web design services company
web design services in delhi
web designer company
web designer delhi
web designer in delhi
web designers delhi
web designers in delhi
web designing & development
web designing advertisement
web designing and development
web designing and development company
web designing and development services
web designing companies in delhi
web designing company delhi
web designing company in delhi
web designing company in gurgaon
web designing company in new delhi

Tripu Design said...

This is an amazing blog, thank you so much for sharing such valuable information with us.
Visit for best logo and brochure designing services at- brochure designer in gurgaon.
Thank you for sharing your wonderful information. for professional graphics,
work contacts me. I am a freelance designer in gurgaon.
Freelance Graphic Designing
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance logo designer in gurgaon
freelance logo designer in gurgaon
freelance web designer in gurgaon

Kshetra Media House said...

Interesting Articlebrochure designing services in hyderabadHoping that you will continue posting an article having a useful information.
brochure design hyderabad
brochure designing in hyderabad
brochure design companies in hyderabad

Kshetra Media House said...

The post is quite informative.logo designing in hyderabad Thanks for sharing a wonderful Blog.
logo designers in hyderabad
logo design companies in hyderabad

Mr Rahman said...

Really Nice Article & Thanks for sharing.
Oflox Is The Best Website Design & Development Company In Dehradun

balu said...

kunkumadi face Oil

B Best Hair Oil

wheatgrass powder

B on

Balu Herbals

keerthana said...

very impressive thank you for sharing
PHP Training in Chennai | Certification | Online Training Course | Machine Learning Training in Chennai | Certification | Online Training Course | iOT Training in Chennai | Certification | Online Training Course | Blockchain Training in Chennai | Certification | Online Training Course | Open Stack Training in Chennai |
Certification | Online Training Course

Kayan Web said...

شركة تنظيف بالاحساء
شركة تعقيم بالاحساء
شركة تنظيف خزانات بالاحساء
شركة تنظيف مكيفات بالاحساء
شركة مكافحة حشرات بالاحساء
شركة تسليك مجاري بالاحساء
شركة مكافحة النمل الابيض بالاحساء
شركة مكافحة الحمام بالاحساء
شركة جلي رخام بالاحساء
https://www.a-lnagah.com/

Kayan Web said...


شركة تنظيف بالرياض
شركة نظافة بالرياض
شركة تعقيم بالرياض
شركة مكافحة الحمام بالرياض
شركة عزل فوم بالرياض
شركة تنظيف بالخرج
شركة تعقيم بالخرج
شركة مكافحة حشرات بالخرج
شركة تسليك مجاري بالخرج
شركة كشف تسربات المياه بالخرج



Kayan Web said...


شركة تنظيف بالمدينة المنورة
شركة تنظيف منازل بالمدينة المنورة
شركة نقل عفش بالمدينة المنورة
شركة نقل اثاث بالمدينة المنورة
شركة مكافحة الحمام بالمدينة المنورة
شركة تركيب طارد الحمام بالمدينة المنورة
شركة مكافحة حشرات بالمدينة المنورة
شركة تنظيف خزانات بالمدينة المنورة
شركة جلي رخام بالمدينة المنورة
شركة تنظيف فلل بالرياض

Kayan Web said...


شركة تنظيف بجدة
شركة تنظيف منازل بمكة
شركة تنظيف بمكة
شركة تنظيف بالبخار بمكة
شركة تنظيف خزانات بمكة
شركة مكافحة حشرات بمكة
شركة مكافحة الحمام بالدمام
شركة تنظيف بينبع
شركة نقل عفش بينبع
شركة مكافحة حشرات بينبع
https://www.allsaad.com/


Kayan Web said...

شركة تنظيف بالمدينة المنورة
شركة تنظيف منازل بالمدينة المنورة
شركة نقل اثاث بالمدينة المنورة
شركة نقل عفش بالمدينة المنورة
شركة مكافحة حشرات بالمدينة المنورة
شركة تنظيف خزانات بالمدينة المنورة
شركة تنظيف بينبع
شركة نقل اثاث بينبع
https://fajr-sate3.com/

Kayan Web said...


شركة الصفرات للتنظيف بالرياض
شركة الصفرات لتعقيم المنازل بالرياض
شركة الصفرات لتنظيف الخزانات بالرياض
شركة الصفرات لمكافحة الحشرات بالرياض
شركة الصفرات لنقل العفش بالرياض
شركة الصفرات لنقل الاثاث بالرياض
شركة الصفرات لكشف التسربات بالرياض
شركة الصفرات لتنظيف المكيفات بالرياض
https://www.cleanriyadh.com/

Kayan Web said...


شركة تنظيف بالدوادمي
شركة تعقيم بالدوادمي
شركة مكافحة حشرات بالدوادمي
شركة تسليك مجاري بالدوادمي
شركة جلي رخام بالدوادمي
شركة تنظيف بالمزاحمية
شركة تعقيم بالمزاحمية
شركة مكافحة حشرات بالمزاحمية
شركة تسليك مجاري بالمزاحمية
شركة جلي رخام بالمزاحمية

Kayan Web said...

شركة تنظيف بالدلم
شركة تعقيم بالدلم
شركة مكافحة حشرات بالدلم
شركة تسليك مجاري بالدلم
شركة جلي رخام بالدلم
شركة تنظيف بالمدينة المنورة
شركة تنظيف بينبع
شركة نقل اثاث بينبع

dhinesh said...

An overwhelming web journal I visit this blog, it's unfathomably amazing. Unusually, in this present blog's substance made inspiration driving truth and reasonable. The substance of data is enlightening.


Full Stack Course Chennai
Full Stack Training in Bangalore

Full Stack Course in Bangalore

Full Stack Training in Hyderabad

Full Stack Course in Hyderabad

Full Stack Training

Full Stack Course

Full Stack Online Training

Full Stack Online Course



rocky said...

Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

python training in bangalore

python training in hyderabad

python online training

python training

python flask training

python flask online training

python training in coimbatore

pavi said...

Am really impressed about this blog because this blog is very easy to learn and understand clearly.This blog is very useful for the college students and researchers to take a good notes in good manner,I gained many unknown information.



Data Science Training In Chennai

Data Science Online Training In Chennai

Data Science Training In Bangalore

Data Science Training In Hyderabad

Data Science Training In Coimbatore

Data Science Training

Data Science Online Training

Unknown said...

Thanks for provide great informatics and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you
DevOps Training in Chennai

DevOps Online Training in Chennai

DevOps Training in Bangalore

DevOps Training in Hyderabad

DevOps Training in Coimbatore

DevOps Training

DevOps Online Training

sharath said...

This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
Java Training in Chennai

Java Training in Bangalore

Java Training in Hyderabad

Java Training
Java Training in Coimbatore

MS Dynamics said...

Hi there, I enjoy reading through your article post. Thank you for sharing.
MS Dynamics Technical Online Training
MS Dynamics AX Technical Training in Hyderabad
MS Dynamics AX Online Training
D365 AX Online Training
d365 ax technical online training
d365 ax technical training

ajith said...

thank you for the information

angular js course in chennai

angular course in chennai

angular js online course in chennai

angular js course in bangalore

angular js course in hyderabad

angular js course in coimbatore

angular js course

angular js online course

monoaat2020 said...

شركة تنظيف منازل بالدمام
شركة تعقيم بالدمام
http://kenanaonline.com/users/alkoyacompany/posts/1102827
شركة مكافحة حشرات بالاحساء
شركة مكافحة حشرات بالجبيل
شركة مكافحة حشرات بالخبر
شركة مكافحة حشرات بالقطيف
شركة مكافحة حشرات بالدمام