To create SSRS report for MSD 365, we need SQL Server Data Tool, its part of Visual studio itself. To create report follow the below steps ..
Reports in MSD 365
- Open SQL Server Data Tool (if you don’t have then you can install it from download and install), my recommendation for SSDT is install older version, because it will have Microsoft Dynamics 365 Report Authoring Extension compatibility.
- Click on new project
- Select ‘Business intelligence’
- Reporting Services
- Report Server Project
- Now create SQL Stored Procedure, Fetch XML from MSD or off course you can design that from report wizard as well while creating rdl
- If you are creating Fetch XML query then Microsoft Dynamics 365 Report Authoring Extension is required to author Fetch-based reports used with Microsoft Dynamics 365 by using SQL Server Data Tools. Click here to download and install.
- To create new RDL report in the project,
- Right click on Report folder and click on add new item
- From here you can choose Report Wizard or Report
- For now, lets select Report, it will add a blank report
- Now, add Data-source to for the report
- On left side window, in Report Data (if this one is not visible the add it from Tools)
- Right click on ‘Data Source’ folder and add new Data Source
- Just follow the wizard, name the DS, Connection string (here don’t use shared data source, else it will give issue when we put in 365, at least I faced it.. 😀 ), then provide the credentials and click OK button
- Now, add Dataset
- Right click on Dataset folder and click add new Dataset
- now follow this wizard, ref below screen shot for this. Here you will have option to provide your Query, you can use fetch XML, SQL Query or Stored Procedure. Let use Stored Procedure for the tutorial.
- Once Query is provided, it will show you all the Fields in its tab and all the Parameter required for the Stored Procedure in that tab.
- Once done Click Ok ..
- Now, design your report
- If you parameter is a date then automatically it will show you calendar control on the report
- if the parameter is lookup reference then you can display it as dropdown
- Create a one dataset for this, just pull the Name and id of the reference
- double click on the parameter field on report, it will open ‘Report Parameter Properties’ window
- In general tab, you can set basic things
- in ‘Available Values’ tab, you can set below properties
- Get values from a query
- Select Dataset
- Select Value field
- Select Label field
- ref below screenshot
- From the Toolbox, drag you any component based on requirement
- and so on ..
- Now, everything is done, now just import it in the MSD environment
- Go to Report area or Solution area and import the report
- specify where you want to see this report
- and to deploy from one environment to another just follow the standard solution deployment process of deployment for MSD and if you are using stored procedures then you have create script and execute on target DB.
Sub-reports in MSD 365
For complex reports, we can use sub-report, sub report is nothing but a another report. you can load it as just not sub part of main report or can be added in table format to repeat along with some other data by passing dynamic parameter.
- to add sub report, just add sub-report area from toolbox
- you can add just small part of main report
- and configure the parameter
- Or you can add it in table
- To pass dynamic parameter to sub-report, just right click on it and configure the parameter from available values of the main table from main report.
- Now, this part will repeat based on that table data
- That’s it for sub-report.
You can explore more on same topic here.
Pre-Filter in reports
We can pass current records GUID to the report as well with a small trick. Pre-filtering allows filtering of the report data using the Advance Find functionality and enables users to create context-sensitive reports and return more relevant data.
Pre-filtering passes the set of selected record or a specific record (such as Quote) for which the report is being run in such a way that the report is executed and the data is pulled from the context of the current (or selected) record. Here enableprefiltering=”1″ prefilterparametername=”CRM_FilteredIncident” will do the trick…. Here is the sample query to pass a single parameter ….
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="incident" enableprefiltering="1" prefilterparametername="CRM_FilteredIncident"> <attribute name="ticketnumber" /> <attribute name="prioritycode" /> <attribute name="title" /> <attribute name="customerid" /> <attribute name="ownerid" /> <attribute name="statuscode" /> <attribute name="createdon" /> <attribute name="caseorigincode" /> <order attribute="title" descending="false" /> <filter type="and"> <condition attribute="statecode" operator="eq" value="0" /> <!-- <condition attribute="incidentid" operator="eq" uitype="incident" value="@CaseId" /> --> </filter> </entity> </fetch>
In a same manner, Pre-filtering can also be applied to related entities.
In the below screenshot, we are retrieving Opportunity and have applied pre-filtering for both Opportunity and Account. Though I never tried it… but here is the ref for it ..
Common issues you might face during the development
- To avoid error while import, like ‘unable to upgrade the file’,
- Solution: you have to change the project property, right click on project property and change the Target Server Version field, select SQL Server 2008 R2, 2012 or 2014 and click Apply then rebuild the project.
- Don’t use shared data source, else you will land up in exception while running the report, atleast it happened with me. 😀
- While importing the RDL file into MSD, please select rdl file from bin folder not the other one else it will throw a exception.
- If you want to print the report or export it in PDF or Word then you need to take care that report width should not go beyond A4 size paper (or you required paper size), other wise, exported or printed pages will move remaining part to the next page or will add blank pages or both of them. TO fix this issue you need to adjust your report width.
- [rsParameterReference] The Value expression for the query parameter ‘@siteId’ refers to a non-existing report parameter ‘siteId’. Letters in the names of parameters must use the correct case.
- Reason: it happens when you rename or adding or removing the parameter from the SQL query
- Solution: Right click the dataset and open properties then go to Parameters. You’ll see parameter name and value. While it may look correct, click on the expression button next to the parameter value and look at the expression within. If it’s in the incorrect case e.g. =Parameters!siteId.Value you need to correct it here. ref below screen shot …
That’s it for now .. !!