Sunday, March 11, 2012

Creating reports in MS SQL Server Report Server

Hi...
Need some help with creating reports in MS SQL Server Report Server and column name "Profit / Loss (%)".
Got an error when I try to run the code below.
The purpose is to avoid a division by zero... or do anyone know a better way solve this?

=IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 0, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, -1, SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)))
/
IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 1, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, 1, SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)))
Error message:
The value expression for the textbox ‘textbox86’ refers to the field ‘PROFIT_AND_LOSS_AMOUNT’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

//chwa

This error message is referring to the scope of the field ‘PROFIT_AND_LOSS_AMOUNT’. It cannot find it in the dataset. Try doing

SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value, "dataset name here")

for all those things above.

No comments:

Post a Comment