Error in Fetch xml based report when implementing aggregates

error

 

error2

Ever faced the above two errors?? and really in a need of some solution? Don’t worry… here you will find the solution.

The scenario is such that you have downloaded your fetchxml from advance find view, made it aggregate = “true” and added the code for count/sum/groupby etc,  copied it and pasted int the query area of your report. And when you execute the query, this dreadful error is popped up. What you need to do is just make sure of the following things:

a) you don’t have any orderby in your code, coz it doesn’t work with aggregates

b) you should be having either group by = “true” or aggregate = “sum”/”count” (as per your need) with each attribute you add along with the alias defined. Remember, you have to add “group by” with all attributes that you have got in your fetchxml query.

here’s an example:

fetchxml

also with this query you can have your data grouped month/year wise

So.. done with the error fixes ? Happy coding! 😀

Fetch XML Based SSRS Reports

1

Let’s start it all from the very first step. If you open your visual studio and don’t see a Business Intelligence Category, don’t worry, this blog will help you out with that too.

(For SQL Server 2012 and Visual Studio 2012)*

Step 1 Download/Install Business Intelligence

Download Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 from the below link:

https://www.microsoft.com/en-pk/download/details.aspx?id=36843

2

Right Click and Run as Administrator to install.

the above setup is available only for 32 bit, so for 64 bit systems, select the “new instance” option when installing.

After installation, you will be able to see the Business Intelligence template category in Visual Studio from where you can create Report Server Projects.

6

Step 2: Download/Install CRM Report Authoring Extension

Download Microsoft Dynamics 365 Report Authoring Extension from the below link:

https://www.microsoft.com/en-us/download/details.aspx?id=45013

4

Right Click and Run as Administrator to install.

After installation, you will be able to see the Microsoft Dynamics CRM Fetch option in Datasource type in Visual Studio when creating reports.

5

Step 3: Create a New Report Server Project

open Visual Studio>>File>>New Project>>Installed>>Templates>>Business Intelligence>>Reporting Services>>Report Server Project>>Ok

3

So, now you are done with the Installations of the prerequisites of your fetch xml based SSRS report. More to come next week, till then enjoy! 🙂

*For visual Studio 2013, use the following link to download Business Intelligence and all rest of the steps remain same

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2013:

https://www.microsoft.com/en-pk/download/confirmation.aspx?id=42313