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.

Using Multivalued Parameter in Fetch XML based report in CRM 2011 online.

Hi,

Say for e.g. we want a report that shows all the contacts based on owners selected.

If we create the query using advanced find, our query will look like this

Here we have selected three users for owner.

From the query we get the initial impression that it won’t be possible to use the above Fetch XML in our report as the Value Tag needs to dynamic based on the no. of the owners selected.

However to use it in the report we need to make the following change in the query

Here @Owners Query Parameter will takes its value from a Multivalued Report Parameter.

Hope it helps.

CRM 2011 Online Fetch XML based SSRS Report and the In condition.

Hi,

Suppose I have a simple Fetch XML based report that shows all the contact’s Full name and their Email Id.

The query for it

Now let us add a like condition to it. (i.e. get all the contacts where fullname like)

The full name parameter will appear in the report.

Now comes the tricky part.

Suppose we have to find all the contact records based on the owners selected.

For owners we will have drop down field in the report.

The fetch xml query would look like below (if we specify two owners)

But here the issue is that value (tag) part would be dynamic, it has to be equal to the values selected by user in the drop down owner parameter of the report. It seems that we can’t dynamically change the Fetch Xml query in runtime for the report.

So the “In” operator won’t be of much use in case of fetch xml based report.

One way we can overcome this issue is by using Default filters.

Here users will have option of editing the filter and specifying the values for the owner which would be then passed down to the report.

This is one way I can think of getting the multiselect thing working in Fetch XML based report.

Please share and suggest your ideas for the same.

Update :-

The multivalue can be passed in the following comma separated way, so we can make use of Multiselect Report Parameter here.


<fetch mapping="logical" count="50" version="1.0">
 <entity name="contact">
 <filter>
 <condition attribute="ownerid" operator="in">
 <strong><value>48379161-2782-e111-b0d1-78e3b5178a51,48379161-2782-e111-b0d1-78e3b5178a52,A81F00C-D4E4-E011-B58C-00155D2A49C7</value></strong>
 </condition>
 </filter>
 </entity>
</fetch>

The fetch XML will have condition in the following format

<condition attribute=”ownerid” operator=”in” value=”@ActivityOwner”>

Bye.

DateTime Parameter Issue in SSRS (The date/time format is not valid)

Hi,

In one of our SSRS Report we had 2 Report Parameters, one for “From Date” and other for “To Date”.

On selecting values for them through calendar control, the value was being displayed in the proper format (mm/dd/yyyy). However the value being passed to the query was in the format (dd-mm-yyyy), because of which we were getting “The date/time format is not valid” error.

The following was the regional setting in the server.

We changed it to English (United States)

We tried running the report, but we again got the same issue.

Next we tried by changing the Language Settings of Internet Explorer.

This also didn’t solve the issue.

Finally thought of restarting the server, in case if the settings might not have got reflected properly.

After restarting the system, again tried running the report and it ran successfully J

Hope it helps.