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.

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.

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.

Parameter Value not getting passed while using SetExecutionParameters method of ReportExecutionService

I was recently working on generating pdf for a report deployed in CRM through code using ReportingExecution2005 web service.

https://nishantrana.wordpress.com/2012/01/29/sample-code-to-use-reportexecution2005-asmx-to-generate-pdf-in-crm-2011/

While writing the code, we faced a very strange issue where values for certain ReportParameters were not getting passed.

I will try to explain the scenario using a very simple report.

Suppose I have report that has 4 report parameters and I am setting values for only 3 of the parameters through code. (Note:-ReportParameter4 is set as second parameter)

Suppose they all are Boolean parameter with default value as True.

Now I am using the following code to set their values as false.

 ParameterValue[] myPVArray = new ParameterValue[3];

ParameterValue myPV1 = new ParameterValue();
 myPV1.Name = "ReportParameter1";
 myPV1.Value = "False";
 myPVArray[0] = myPV1;

ParameterValue myPV2 = new ParameterValue();
 myPV2.Name = "ReportParameter3";
 myPV2.Value = "False";
 myPVArray[1] = myPV2;

ParameterValue myPV3 = new ParameterValue();
 myPV3.Name = "ReportParameter2";
 myPV3.Value = "False";
 myPVArray[2] = myPV3;

rs.SetExecutionParameters(myPVArray, "en-us");
 

This is the output of the pdf

i.e. the value appears properly for the ReportParameter 1 to 3 as set as false. For the report parameter 4 it comes as True (i.e. its default value as we are not setting it).

Now I go forward and make a small change instead of specifying default value for ReportParameter 4 as True, I modify it to take the value from the DataSet instead.

From

To

And Data Type from Boolean to Text.

Now I run the same code. This time again we would expect the value for ReportParameter 1 to 3 as False and ReportParameter4 to be whatever value there in the DataSet.

However we will get the following pdf output


For Report Parameter 2 and 3 we see the default value (i.e. True) instead of False as we are passing through code.

The solution to this that I found was to move the reportparameter 4 as the last parameter in the report.

Currently in our report it is set as the second one.

To

The output after running the code

i.e. the one we were expecting.

So one thing we need to remember over here is that if we have any of our report parameter taking its value from dataset then we should make it the last parameter or should put it after all the other report parameters whose value we are passing through code.

Hope it helps.

Sample Code to use ReportExecution2005.asmx to generate PDF in CRM 2011.

Hi,

Just sharing a sample code that uses ReportExecution2005 web service of SSRS 2008, and generates pdf of one of the custom reports deployed in CRM.

Here we are also passing value to one of the report parameter.

 // Add web reference to following service
 // http://server/reportserver/ReportExecution2005.asmx

ReportExecutionService rs = new ReportExecutionService();
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Render arguments
 byte[] result = null;
 // Get the report path from the report server
 // http://reportserver/Reports/Pages/Folder.aspx?ViewMode=Detail

string reportPath = "/MyOrg_MSCRM/CustomReports/{837a8e7e-d949-e111-996a-00155d2a49c7}";
 string format = "PDF";
 string historyID = null;

string encoding;
 string mimeType;
 string extension;
 Warning[] warnings = null;
 string[] streamIDs = null;


 ExecutionHeader execHeader = new ExecutionHeader();
 rs.ExecutionHeaderValue = execHeader;
 rs.LoadReport(reportPath, historyID);

// Set DataSource Credentials
 // the user name is your userid and the password is the id of the organization
 // get more info here
 // http://ronaldlemmen.blogspot.com/2009/01/log-in-name-and-password-required-by.html
 DataSourceCredentials datasetCredential = new DataSourceCredentials();
 datasetCredential.DataSourceName = "DataSource1";
 datasetCredential.UserName = "F45D4E20-44DE-E011-8A08-005056860004";
 datasetCredential.Password = "D0E60C19-44DE-E011-8A08-005056860004";

// Set Report Parameter values
 ParameterValue[] myPVArray = new ParameterValue[1];
 ParameterValue myPV = new ParameterValue();
 myPV.Name = "ReportParameter1";
 myPV.Value = "True";
 myPVArray[0] = myPV;

rs.SetExecutionParameters(myPVArray, "en-us");


 DataSourceCredentials[] credentials = new DataSourceCredentials[1];
 credentials[0] = datasetCredential;
 rs.SetExecutionCredentials(credentials);

result = rs.Render(format, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

Response.Clear();
 MemoryStream ms = new MemoryStream(result);
 Response.ContentType = "application/pdf";
 Response.AddHeader("content-disposition", "attachment;filename=labtest.pdf");
 Response.Buffer = true;
 ms.WriteTo(Response.OutputStream);
 Response.End();
 

Bye.

“The CRL type does not exist or you do not have permissions to access it” error while refreshing dataset in Query Designer (SSRS 2008)

I was getting the below error while trying to refresh the dataset in the query designer.

The workaround was to refresh the dataset from Report Data panel

Right click the dataset and select Dataset properties

Click on Refresh Fields button and Ok to close the dialog box.

Hope this helps.

Hiding and shrinking the width of the Column in SSRS 2008

Hi,

 We recently had a requirement wherein we were hiding few columns of our SSRS report dynamically based on value of a parameter.

The problem was that the hidden column although not visible was still consuming the space.

To solve this issue

  • Select Advanced Mode in the report Column Groups


  • Select the static column referring to the column we want to hide and set its Hidden property


The helpful post

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/report-design-shrinking-hidden-static-columns.aspx

Bye.

Showing SSRS report in a single page

Hi,

We had a requirement to show one of our SSRS report (in CRM 2011) in a single page so that users don’t have to click on Previous Page and Next Page buttons.

.

Setting InteractiveSize’s Height property of the report to 0 solved the issue.

The helpful posts

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/5733e039-7031-454a-b9ad-981a93d97f1f/

Bye.

Using Web Service as a Data Source in Sql Server Reporting Services

Suppose this is a simple web method inside a web service in ASP.NET.

[WebMethod]

public string HelloWorld(String FirstName, String LastName) {

return “Hello “ + FirstName+ ” “ + LastName ;

}

Now we would to add the above as a xml data source in out Report.

For this we need to do the following

  • Create a new Report
  • Select <New DataSet..>
  • For Type (inside General Tab) select XML as DataSource type.
  • Inside connection string give the path to the web service.
  • i.e. http://localhost:2584/WebSites/Service.asmx
  • Define query for the data source in the following manner

<Query>

<Method Namespace=http://tempuri.org/ Name=HelloWorld>

</Method>

<SoapAction>http://tempuri.org/HelloWorld</SoapAction>

</Query>

 

We can create report parameters for FirstName and LastName parameters of the method.

Or we want to define default values for these parameters define the query as following

 

<Query>

<Method Namespace=http://tempuri.org/ Name=HelloWorld>

<Parameters>

<Parameter Name=FirstName>

<DefaultValue>Nishant</DefaultValue>

</Parameter>

<Parameter Name=LastName>

<DefaultValue>Rana</DefaultValue>

</Parameter>

</Parameters>

</Method>

<SoapAction>http://tempuri.org/HelloWorld</SoapAction>

</Query>

 

Bye..