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





45 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

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

DevOps said...

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

Mr Rahman said...

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

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.cleanriyadh.com/

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

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

Ruhi Sukhla said...

Exploring in Yahoo I at last stumbled upon this website.

VKU Ujjain BA 2nd Year Result

zianab zyada said...



زينب زيادة

زينب زيادة

zianab zyada



Kailey Robinson said...

Download Video Player | Windows Media Player | Best Media Player

Haider said...


شركات نقل الاثاث بجازان افضل شركة نقل عفش بجازان نقوم بنقل الاثاث من والى جازان فى اى وقت سيارت مغلقة خدمة عملاء على اعلى مستوى من المهنية عمالة فلبينية ماهرة فى نقل الاثاث من والى جازان

QuickBooks xpert said...

Nice & Informatic Blog !
Our team at QuickBooks Customer Service is constantly working around the clock so that you can work peacefully on your system in times of financial crisis.

Narender Singh said...

Thanks for sharing this Information. SAP CRM Training in Gurgaon

Dynatech Solutions said...

Microsoft Dynamics CRMs are intelligent cloud business apps that helps a business run more efficiently and deliver better results through predictive insights driven by Artificial Intelligence.

Parul Pathak said...

This site helps to clear your all query.mg university bcom result
rajasthan university bcom final year result This is really worth reading. nice informative article.

QuickBooks Error said...

Nice Blog !
QuickBooks Error 1712 . It is generally popped due to installing QuickBooks software in a false or a damaged folder. Apparently, these errors are very easily mitigated with the help of our experts any hour of the day.

enstyleplanners said...

Nice Article! Are you looking for the best mobile app development companies in madhapur that will be helpful for your business. We provide efficient methodologies for the successful implementation of app development company in hyderabad | mobile app development hyderabad | mobile application development company hyderabad | mobile application development companies in hyderabad | ecommerce solutions hyderabad | best mobile app development company in hyderabad | mobile application development company in hyderabad | mobile development company hyderabad | mobile app development services in hyderabad | best mobile app developers in hyderabad .
Thank you!

Animal Zone said...

lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance

Animal Zone said...

lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance
lanlords insurance

ahmed said...

I have read your whole post it's very informative

A2 Jackets

G1 leather jackets

خدمات said...

 شركة تنظيف سجاد بالاحساء
شركة تنظيف خزانات بالاحساء
شركات التنظيف بالاحساء

Zainiseowriter said...

Thanks for sharing this awesome post it was very informative Plz share more post like this
WOMEN LEATHER BOMBER JACKETS

Womens Leather Vest

Men Leather Vest

BASANT KUMAR said...

I luckily stumbled on your ba 3rd year time table purvanchal university website and I'm really impressed with what I got.

rfxleather said...

V Bomber Leather Jackets Men Available to be purchased
Harley Davidson Leather Jacket Men Clothing Available to be purchased
Mens B3 Bomber Leather Jacket Available to be purchased
RFX Leather

Business Services said...

مسلسلات رمضان 2021

Aaina said...

A must read post! Good way of describing and pleasure piece of writing. Thanks!

B Com Part 2nd Admit card

mrbobystone said...

I love to recommend you Where can crawl Exciting Products latest Jackets, Coats and Vests Click Here Jacob Assassins Creed Syndicate Coat

BRANIDNG AGENCY said...

It's Natural blog

Visit our sites,


Ideal Designs is committed to creating functional and innovative designs that suit the client’s needs. Our endeavors are to partner with Indian clients where design plays a huge role in their business. Our research techniques help our clients develop identity and designs, which are drawn from the immense depth and the very fabric of Indian culture. We believe that effective designs act as a powerful tool to drive the corporate vision and align its internal and external forces. Brand Identity and Packaging.

branding agency Hyderabad said...

great blog interesting content.


Visit our site


Ideal Designs is committed to creating functional and innovative designs that suit the client’s needs. Our endeavors are to partner with Indian clients where design plays a huge role in their business. Our research techniques help our clients develop identity and designs, which are drawn from the immense depth and the very fabric of Indian culture. We believe that effective designs act as a powerful tool to drive the corporate vision and align its internal and external forces. Brand Identity and Packaging.

eddielydon said...

I am very much pleased with the contents you have mentioned. I wanted to thank you for this great article. Arthur Morgan Jacket

Jobi Johnson said...

I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful. john wayne vest

BRANIDNG AGENCY said...

At Ideal designs, we believe that design should not just look good. It should also work well; it should be memorable and it should connect with the end-user. The success of many businesses today depends on how vital a role ‘good’ design plays in their business strategy. Design is often the first thing that the consumer comes in contact with, before you can meet the company representative, you see the logo… and ‘good’ or ‘effective’ design has already started playing a vital role. Logo Design Bangalore, Logo Design in Bangalore, Brand Logo Design in Bangalore, Corporate Logo Design in Bangalore, Logo Design in Bangalore.

idealbranding said...


Hah! Good to know.
Over the years, India has been experiencing rapid economic change as various sectors have undergone greater development.
In a business environment filled with opportunities and challenges, brand-building may be the direction SMEs and start-ups need to take to enhance their competitiveness in the dynamics of the changing market.
The entrepreneur market has undergone a sea of change today. Earlier only those who belonged to family businesses ventured into doing business and licenses to run a specific business also played a large part in creating new entrepreneurs.
But everything has changed today.

# 6-3-563/1, Seacon Prime Ansar Apartments, Flat no. 302, Before Silver Star Mercedes Benz Showroom,
Erramanzil Colony, Banjara Hills, Hyderabad, Telangana 500082

Contact: 91-9949645564, 040–23316416
http://idealbranding.in/

idealbranding said...

Hah! Good to know.
In today’s cut-throat competitive market, the value of differentiation and the necessity to establish your brand’s identity and reputation amongst your target audience is indispensable.
To solve business problems and make your business more inclusive and driven, we constantly draw from the working ideals of Innovation: paradigm, process, position and product.
http://idealbranding.in/expertise/corporate-branding-agency-hyderabad/

Rafid said...

touch up paint bottles with brush

shrok said...

home appliances
juicer range

Trends Fort said...

I like that you have contributed such a nice content I appreciate you in this blog thanks for share. best jackets for men and women

Priya said...

Grate blog. It's very interesting to read...

Manual-Testing-Training course at Edukators in Coimbatore

Version IT said...

Grate blog. It's very interesting to read
SAP SD Training in Hyderabad

john alex said...

Being a blogger allows for creative expression, and for those who embrace style, a black moto leather jacket can be a key fashion statement. It's perfect for bloggers who want to project confidence and edge, whether they're writing about lifestyle, fashion, or travel. A **black moto leather jacket** adds an iconic, bold look that complements any blogger's personal brand.