Showing posts with label SSRS Reports. Show all posts
Showing posts with label SSRS Reports. Show all posts

Wednesday, March 20, 2013

Show different Header/Footer on different pages of SSRS report

Sometimes, there are requirements to show different Header/ Footer on different pages of the report.
Like say, on the first page, header/ footer of report should be some text and on the second page it should be some different text. Please refer below screenshots.
First Page Footer

Second Page Footer

This can be achieved using the solution as described below:
You need to write a simple expression on the textbox control which is placed on the footer. Here you can utilize expression “Globals!PageNumber” which is used to show page numbers. This expression returns the page number, and we can alter this to show different footer on different pages.
You need to write following expression on the textbox control.
=IIf(cstr(Globals!PageNumber)="1","1111-XYZ (2012/12)      © ABC Sol, 2012","1111-XYZ (2012/12)")
If the report page is the first page, then this expression would set the specified text else it would display alternate text.
General Syntax
=IIF(cstr(Globals!PageNumber)=”<Page Number>”,” <Text to display on the specified Page number> ” , ” <Text to display on other page>”)
Similarly, you can use this expression to show different header on different pages as well.
Hope this article helps!

Thursday, October 25, 2012

Report not running on more than 25 records


We created  a SSRS on-premise report using CRMFiltered parameter and the report was working fine except for the case when we ran this report on more than 25 records. In that case the report was crashing. When we used Diagnostic Tool then we found the below error.



The error was that it was concatenating the GUID’s instead of separating each other.

 

We found that the CRM FilteredAccount parameter only returns 25 records, not more than that. Hence, to fix this we used CRMAF_filteredAccount parameter and then the report was working fine.

 

Hope this trick helps!!!

 

Wednesday, May 16, 2012

Reports Not Working After Upgrade to CRM 2011

I have worked on various upgrades and none have really had issues with the reports not working. I make sure that the SRS Data Connector is installed without fail. 


When you install CRM with the SQL server being separate from the CRM server, the setup does not automatically prompt you to install Reporting Services Extensions. You need to manually copy the  SrsDataConnector folder found in the CRM install directory to your SQL server. Run SetupSRSDataConnector.exe from there. This will prompt you to select the SQL server and the Reporting Server instance and go ahead and install all the reports for you.


In my case this was already done and yet the Sub-Reports did not work. The main reports would work but the moment we clicked on Show More Details that would require the sub-report to be executed, it would throw an error "Details could not be displayed as the sub-report could not be found". The Event viewer recorded the following error


Report render failure. Error: The item '/SharedReports/5.0.xxxx/{6177ee54-f42e-e011-a793-001ec9bbc670}' cannot be found. (rsItemNotFound)


Checking the Report Server using http://server/Reports showed that though the sub-report was present in that folder it had a different Guid than the one shown in the event viewer.


Since this was an upgrade, I thought it might still be using CRM 4 Guids and publishing the reports again would probably fix the issue. I found "PublishReports.exe" in the tools folder of CRM Installation directory. 


Executed that exe from command prompt using publishreports.exe uniqueorgname


It showed that it published some reports but that did not resolve my problem :(


I then came across this post that helped me reset the setting in MSCRM_Config database .


There is a setting in MSCRM_Config --> Organization table "AreReportsPublished". You need to set the value of this attribute to 0/False. This will be get the PublishReports to publish the reports once again.


After making this change, executing the PublishReports.exe tool helped in get my reports working again!


No matter how many upgrades you work on.... each one is a different case :)