SSRS Reports development – Tips and Tricks

During the last 6 months, I have been extensively working on SSRS reports, so based on my experience in SSRS reports development, I have compiled together the list of Tips and Tricks which would help AX developers to expedite the report development and saving them heaps of time. Below is the list:

• If the data on the new field is not showing, when you run the report, then try to refresh cache from Tools – > Cache option and then run the report.

• If the new field is not showing under report’s dataset after refresh, then verify the following things:

• Check if the default client configuration is pointing to the AOS instance you are working on.
• Check if your VS instance is connecting to the right AOS instance. To find which report server instance, VS is connecting to, try to deploy the report from Visual studio, and then check the message window. The deployment information shows the AOS and the report server instance the report is being deployed. Example shown below:

If the above report server location doesn’t looks correct, then you may need to change the default client configuration.

• If the report is not getting deployed to the correct instance, then you may need to change the default client configuration. For example, if you are working in DEV environment, change the default client configuration to the DEV and vice versa.

• If you have changed the SSRS report query and the changes are not appearing on the report parameter form, then try to do the following things:

• Delete usage data.
• Delete report related data from SRSReportQuery
• Refresh cache from Tools -> Cache options. Use all four options.

• If you want to move a control either text box or rectangle control or any other control from keyboard slightly towards any direction, then use the arrow key with the combination of Ctrl key.

• If address is appearing on multiple lines, then use the Replace function to remove line feed. Example shown below:

          Replace(First(Fields!CompanyAddress.Value,”PurchPurchaseOrderDS”),Chr(10),”, “)

• If the data in some fields on header or footer is truncating, then increase the size of the header or footer, because header and footer section does not auto grow.

• If you have to develop a new report and some of the sections is similar to one of the existing report, then you can open the report designs side by side and copy paste the sections from existing design to the new one. After doing this, make sure you rename the control in your new design, otherwise you would get errors during report deployment. The duplicate name errors will not be shown when you build the report project or add the project to AOT. Also, change the name of dataset in field’s expression.

• If you want to do the complete redesign of an existing report, then it is better to copy the existing design and create a new design with another name. The advantage is that you can always refer back to the old design for field’s references and positions and also you can see the report’s output based on the old design. You don’t have to go to another environment to check the standard report design. After, you completed developing the report, you may need to update the ‘Name’ column in PrintMgmtReportFormat See example below for free text invoice report:

Say, your new report design name is Report_INT, then you need to update the Name column to FreeTextInvoice.Report_INT. This is not a global table, so you may need to change this record for each company where you intend to use this report. If there are too many companies to update manually, then you can write a job to update this based on the DocumentType column.

• If you add a new field in report’s table in AX, refreshing the dataset in VS brings that new field in report’s dataset. Before using that new field in your report design, you need to

close the design and reopen it again and then only you would be able to refer to the new field in your report design. Sometime, you spend lot of time searching for the new field which is showing under report’s dataset, but not appearing in report design, when you select the related dataset under report design.

• Always export the report as PDF to view the output and verify the formatting as per requirements, as AX report viewer output uses HTML renderer so it is not the output you are going to see when you print the report on printer.

• When preparing the development estimates for static reports, include 1 or 2 hours additional time just for report’s deployment. Sometime, it takes around 4 to 5 minutes to deploy a static report. The details of which reports are static can be found on the following form:

System administration -> Setup -> Business intelligence -> Reporting Services -> Report deployment settings

• When you are modifying a report which is static, then it is better to make most of the changes in one go and then deploy the report to see the output instead of making small changes one by one and check the output. This is just to save the time because static report deployment takes lot of time.

• If on PDF output of the report, the image is not displaying correctly, then most probably it is due to the fact that the image is in png format. Select the image with JPEG format and then it should work. PDF renderer doesn’t support images with PNG format.

• Always try to put the report items in containers such as rectangle. This will help control the way, report items are displayed on the report page.

• If you are getting errors related to any expression used in the controls while compiling the report project in Visual studio and you don’t know which control it is related to, then one way to find out is to search the report RDL for the error text and then look at the parent XML node to see which control it is related to. Just press Ctrl+F on your report design to search the entire solution for the error text and it opens the report RDL in XML format.

• If you have removed any vertical spacing between textboxes on report design, but still when you run the report, there is spacing between the text boxes, then try to decrease the top and bottom padding for the textboxes accordingly. This will reduce the spacing between control showing at run time.

• If you want to display the text on reports in ALL caps, but there is no label exist with upper case, then don’t create new upper case labels, instead use UCASE function in report

with existing labels and then it will be displayed on the report in upper case. Example shown below:

                     Ucase(Labels!@SYS26401)

• If the positions of textboxes or tablix in your report output is not showing as expected, then set the background colour of the controls to certain colour and check the output. You can get an idea of what is exactly happening when the report is rendered.

• For static reports, if you have changed the label text which is used in the report, then you have to deploy the report again to see the updated label, because the label are resolved at the time of deployment for static reports.

• If your report item not being displayed on the location where it should be, then most probably the reason would be that other control is expanding and pushing that item further down, so either set the auto grow to false or leave appropriate width or height at design time

• If your reports are not deploying to the correct reporting server when you are deploying it within AX environment, then a quick workaround is to deploy the report to specific report sever using the following power shell command:

Publish-AXReport –id ReportConfigurationID –ReportName ReportName

Example shown below:

Publish-AXReport –id AXPROD –ReportName PurchPurchaseOrder

You can find the report configuration ID on the report servers form. The path is mentioned below:

System administration -> Setup -> Business intelligence -> Reporting Services -> Report servers.

Enjoy Daxing.