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.

http://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.