Using Format() and CurrencyFormat() function for SSRS report design in Visual Studio (Dynamics AX)

Hi Guys,
Here I found one more really interesting thing in the SSRS expression scripting.
1. Format()
2. CurrencyFormat()

If you're dealing with the string datatype that needs to display the value with out any separation you can go head with this

Str YourFields = "10000";
Format(=Fields!YourFields.Value, "0.00")
the results will be  "10000.00".

This will not give you the separated values like  "100,00.00"

If it's a real data type you're dealing with than the above statement won't work to return the value with the separated, So the expression goes below to achieve this:

Real YourFields = "10000";
CurrencyFormat(=Fields!YourFields.Value)  or Format(=Fields!YourFields.Value, "C")
the results will be  "100,00.00" and the out put with the separator are pulling from the system and regional setting in the system.

Note: There are several parameters are available with the "CurrencyFormat()" function one can utilize according to their needs.

 

SSRS Report Row Color for Each Row in AX Dynamics

Hi All,
Here’s short tips to add the cosmetic look for your report design based on the line number of the data in the table, List and Matrix.
We can give the different colors for the each rows in the report.
The simple expression is below.

=IIf((RowNumber(“YourDataSource”) Mod 2) = 0, Nothing, “#F0F0F0″)

SSRS Report Not Rendering the Output to the Screen in Dynamics 2012

Normally this issue or error won’t occur in the all the report where the report using query or temp table as a data source which using the limited fields (For E.g.: Less than 240 Columns Approx*).
One or more fields rendering the output (Figure Shown below) to the report possible falls under the below listed issue.

Fig: 1.00
List of Causes:

1. Tables Buffer
2. Check the user having the privilege to access the table data or related objects to the report.
3. Refresh Issues on the SQL server or AX Server.
4. Temp File causing the problem on the report or on the AX server.
5. More overlapping fields in the report design.

Scenario:
I had a requirement to modify the SalesInvoiceReport in AX 2012 CU3, so I just added some four fields in the table. Adding the fields to table “SalesInvoiceTmp” where it already has 240+ columns in it after the columns added to the table the report renders no output on the screen (Both VS preview & AX).
I found this issue rose because of the table buffer size. To fix this issue the following solution can be helpful.

1. Try increase the buffer size of the dynamics which is available in the AX server setting.

Even after tried to increase the buffer size there was no sign of progress on the output of the report so started looking the issue on the distinct view.
     2.    Removed my customization on the report design and table as well I really do not know the    surprise was waiting ahead on the report. Soon after the removal of my customization the report renders the same output (No data displaying on the screen). Addition to this issue I faced a one more error on the VS (While refreshing the DataSource) and the AX like the below one.

Fig: 2.00
*This error basically appear when you have corrupted .dll, temp files in the user temp directory or in the .app folder for the user.
 Fixing the above error steps:

1. Try restoring the table and refreshing the DS on the report.
2. Stop the AOS & SSR Servers and delete .auc files for the users from the C:\ Directory.
3. Generate Incremental CIL & Full CIL(After generating the full CIL delete the files that has been generated by the CIL operation from the directory Xppill which can be found under the AX Server directory).
4. Sync the data dictionary.
5. Restore & compiled (Compile Forward) the entire class related to the report and some framework class like SysDictFieldMetadata.
6. Reset the AX user data and refresh the AOT Elements
7. Delete the SSRS report from the report server.
8. Check for the corrupted “.dll” file in Visual Studio Report path or in the “.AppData” directory for the user. (If you able to find the suspicious file then delete the files)

Finally I fixed the issue (Fig:2.00) by removing the  “.temp” files from the user directory (Step 6)
I was able to refresh the data source of the report and there was no error in the VS and in dynamics as well.
Back to the main Issue:

Still the report doesn’t render the output on the screen.

1. Now again generating the Full CIL & Deleted the files that has been generated by the AX in the Xppil folder from the user directory.

     2. Deleted the user usage data & refresh the AOT elements.
     3. Restarted both the servers (AX & SQL).

Finally the report started working and renders the output perfectly.
So basically this issue arose because of:

1. Adding too many fields in the table (Renders no output on the report screen)
2. Corrupted .dll files in the user directory & in the VS Directory for the user. (Fig.2.00)