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:
User | Leads | Opportunities |
David Jennaway | 20 | 10 |
My Friend | 15 | 8 |
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:
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
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
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
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
Informative blog. Thanks for sharing.
MS Dynamics Operation Trade and Logistics Training
Really Nice Article & Thanks for sharing.
Oflox Is The Best Website Design & Development Company In Dehradun
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
شركة الصفرات للتنظيف بالرياض
شركة الصفرات لتعقيم المنازل بالرياض
شركة الصفرات لتنظيف الخزانات بالرياض
شركة الصفرات لمكافحة الحشرات بالرياض
شركة الصفرات لنقل العفش بالرياض
شركة الصفرات لنقل الاثاث بالرياض
شركة الصفرات لكشف التسربات بالرياض
شركة الصفرات لتنظيف المكيفات بالرياض
https://www.cleanriyadh.com/
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
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
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
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
Exploring in Yahoo I at last stumbled upon this website.
VKU Ujjain BA 2nd Year Result
زينب زيادة
زينب زيادة
zianab zyada
Download Video Player | Windows Media Player | Best Media Player
شركات نقل الاثاث بجازان افضل شركة نقل عفش بجازان نقوم بنقل الاثاث من والى جازان فى اى وقت سيارت مغلقة خدمة عملاء على اعلى مستوى من المهنية عمالة فلبينية ماهرة فى نقل الاثاث من والى جازان
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.
Thanks for sharing this Information. SAP CRM Training in Gurgaon
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.
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.
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.
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!
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
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
I have read your whole post it's very informative
A2 Jackets
G1 leather jackets
شركة تنظيف سجاد بالاحساء
شركة تنظيف خزانات بالاحساء
شركات التنظيف بالاحساء
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
I luckily stumbled on your ba 3rd year time table purvanchal university website and I'm really impressed with what I got.
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
مسلسلات رمضان 2021
A must read post! Good way of describing and pleasure piece of writing. Thanks!
B Com Part 2nd Admit card
I love to recommend you Where can crawl Exciting Products latest Jackets, Coats and Vests Click Here Jacob Assassins Creed Syndicate Coat
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.
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.
I am very much pleased with the contents you have mentioned. I wanted to thank you for this great article. Arthur Morgan Jacket
I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful. john wayne vest
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.
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/
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/
touch up paint bottles with brush
home appliances
juicer range
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
Grate blog. It's very interesting to read...
Manual-Testing-Training course at Edukators in Coimbatore
Grate blog. It's very interesting to read
SAP SD Training in Hyderabad
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.
Post a Comment