SSRS – Page 2 – Nishant Rana's Weblog

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.

A very helpful post on SSRS

Hi,

I have come across this post many times while developing reports in SSRS.

So just thought of sharing the link

http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx

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.