Using World Map \ Bing Map in SSRS Report in Dynamics 365 (and earlier)

Hi,

Recently I was working on a SSRS Report that will show the countries in the world map (Country was a custom entity in our CRM)

To create such a report, drag the Map report item to the report designer.

OOB we do not have world map in the Map Gallery.

Download it from the following location

https://mapgallery.codeplex.com/releases

Copy the download RDL to the following location (in my case – SQL Server Data Tools for VS 2012)

Select the world map

Add a Bing Map layer

Select Basic map

Click Next and select appropriate value

Click on Finish.

Next inside report designer, right click Layer Data

And specify mapping between the spatial and analytical dataset (our custom dataset here which has 2 column in it, name of the country and A2.

Suppose we have following countries in our CRM

The final report with countries highlighted in colors.

Hope it helps..

Validating Input Parameters in SSRS

We recently developed a report which had Days Input Parameter in it. Now the requirement was if the user enters any thing other then number (integer) over there they should get the appropriate error message.

Here if we define the Days parameter of type Integer and if we enter say for e.g. value = ‘x’ over there

We will get the following error message.

And if we upload the same report in the CRM, we will get the following error message, which doesn’t seem that user friendly.

Now to display proper error message to the end user we can take the following approach.

Change the parameter type to Text first. (From integer type)

Add a text box to report to show the error message, have following expression. If the value entered for Days parameter is not of numeric type display error message.

To hide the table if the Days field is not numeric, set the Hidden property with the following expression. If the Days parameter value is not numeric then hide the table.

And the following change in the DataSet’s query

Original Query

SELECT
Name, lss_First_Name, lss_Last_Name

FROM
Account

WHERE (DATEDIFF(day,CreatedOn,GETDATE())@Days)

New Query

If Days is not a numeric field then then modified select query to retrieve blank data from the table (using createdon field null criteria here in our case)

DECLARE
@SQL
Nvarchar(1000)

IF  ISNUMERIC(@Days)= 1 BEGIN

SET
@SQL ‘SELECT Name, lss_First_Name, lss_Last_Name FROM Account WHERE (DATEDIFF(day, CreatedOn, GETDATE())) >’+Convert(nvarchar,@Days)+

End

Else

Begin

SET @SQL=‘SELECT Name, lss_First_Name, lss_Last_Name FROM Account WHERE createdon=Null’

End

exec(@SQL)

The report with proper Days value for e.g. 1

If entered incorrect Days value for e.g. abc

Helpful post

http://geekswithblogs.net/Compudicted/archive/2012/08/14/validate-ssrs-report-input-parameters-the-proper-way.aspx

Hope it helps.

Open Report in a new window from Action in SSRS

Hi,

While developing a SSRS report, initially we set the Action property of the text box with the following value that will open the CRM’s Account record

=Parameters!CRM_URL.Value & “?ID={“&Fields!accountid.Value.ToString()&”}&OTC=1″

It was working fine however when we were using the same report inside the Dashboard, the report was not showing up properly as it was inside the IFrame.

So we had to use window.open for the Action’s Expression to open the report properly in a new window from inside Dashboard’s iframe.

=“javascript:void(window.open(‘”+ Parameters!CRM_URL.Value + “?ID={“ + Fields!accountid.Value.ToString() +“}&OTC=1′ ,’_blank’))”

Hope it helps.

Hiding Tool Bar from the SSRS Report while using them in Dashboard (CRM 2011)

Hi,

We were showing a custom SSRS report inside an iframe in one of our CRM 2011 Dashboards. We wanted to hide the Tool Bar from the report.

To achieve that we need to pass the &rc:Toolbar=false parameter to our report’s URL, which we set for the IFrame.

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCIBPhaseIIAnD_MSCRM%2fBEA&rs:Command=Render&rc:Toolbar=false

This hides the tool bar.

Now in one of our other SSRS reports, we were doing filtering based on a report parameter. So we wanted to show only the Parameter toolbar to appear and hide the other toolbar that shows Export, Zoom, Print etc. option.

To do so we first figured out the CSS class being used for it,

this turned out to be ToolBarButtonsCell

Now what we did next was to create a simple CSS file with the following content to hide the tool bar

.ToolBarButtonsCell{display: none;}

And placed that file at the following location of the server

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\Styles

Now next we had to use the rc:stylesheet parameter for our report’s URL and pass the name of the above CSS file just created ( for e.g. hideToolBar.css)

So our new URL was like this

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCIBPhaseIIAnD_MSCRM%2fBEA&rs:Command=Render&rc:stylesheet=hideToolBar

After applying the new style sheet parameter to the report’s URL

Check out the following link

http://msdn.microsoft.com/en-us/library/ms152835.aspx (URL Access Parameter Reference)

Hope it helps.

Hiding Rectangle in SSRS

In one of our reports we were using List Control to show information related to Account(Business) and its related Task and Appointments. The information was being shown in three different Tablix respectively and if there were no Task and Appointments then we were hiding the Account (Business) Tablix and Rectangle by setting their Hidden Property.

Fig. Three Tablix inside the List Control.

However after running the report we realized that Rectangles although were hiding properly but were still consuming the white space. Because of this we had blank white spaces in our report.

So to display the reports properly we had to set the Hidden Property of the Row also.

Select the Row and Right click it.

Select Row Visibility and set its property using the same expression used for Rectangle’s Visiblity property.

After making this change report started showing up properly.

The helpful thread

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a34974ea-af84-4fa6-b445-25f9e490def0

Hope it helps.

Hierarchical report in SSRS to show users and their managers (CRM)

Hi,

Just created a simple SSRS report that shows the Hierarchy of the users and their manager.

This is how the report looks like

The DataSet query used

Select the row, right click and select group properties option

Select Grouping based on systemuserid

Toggle the visibility based on FullName

Select parentsystemuserid as Recursive Parent from the Advanced option

Set the Left Padding property for the fullname TextBox.

Set the expression for level as Level()

The posts from which I learned creating the hierarchical reports

http://blog.infotoad.com/post/2009/08/10/Working-with-a-Recursive-Hierarchy-Group-in-SQL-Reporting-Services-2008.aspx

http://blog.davyknuysen.be/2010/04/16/parent-child-hierarchies-in-reporting-services/

Bye.