While working on Report we suddenly started getting the below error
The strange thing was that the other reports that were using the same data source in the same Report Project were working properly.
Find few posts
but none applied here.
Tried creating a new data source, entered credentials repeatedly but nothing worked, eventually restarted the SQL Server Data Tools (IDE) and it fixed the issue.
Hope it helps..
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
Hope it helps.
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.
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.
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.
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
Hope it helps.
Suppose we have simple master (Person) and detail (Favorite
Actor and Favorite
Movie) tables in our database .
To write a report that doesn’t make use of sub reports we need to have a single dataset as shown below
Create a new report project and drag the List control for Master table and two Tablix inside List for the detail records.
Select the list and set Group Properties for the Row Groups as shown below.
Select the row for the first tablix that shows Favorite Actor information, right click it and specify group properties as FAID (i.e. primary key for that table)
Do the same for the other tablix (Movie table).
Run the report, it will show the following output
To hide the tablix if there is no data, set its Hidden Property using the following expression
For tablix 1 à
=IIF(IsNothing(Fields!FAID.Value),True,False)
For tablix 2 à
=IIF(IsNothing(Fields!FMID.Value),True,False)
The DataSet query
</p> <p>select P.Id, P.FirstName, P.LastName,fa.Id as FAID,fa.firstname,fa.lastname, fm.Id as FMID,fm.Movie from Person P<br /> left outer join FavoriteActor as FA on P.Id = FA.PersonId<br /> left outer join FavoriteMovie as FM on P.Id = FM.PersonId</p> <p>
Hope it helps
Nishant Rana's Weblog 