Help with SSRS Expression

[miwundu]

I have 2 datasets in my report but using a common table. dataset 1 is named XXX and dataset 2 is named UNKNOWN . I have a column cell_1 with this ssrs lookup expression from dataset XXX

=Sum(Fields!total_complete_1_Count.Value, "XXX") + Sum(Fields!total_complete_2_Count.Value, "XXX") + Sum(Fields!total_complete_3_Count.Value, "XXX") and

 another column cell_2 from Unknown dataset with this expression 

Sum(Fields!total_complete_1_Count.Value) + Sum(Fields!total_complete_2_Count.Value) + Sum(Fields!total_complete_3_Count.Value).

I want to divide cell_1 by cell_2.  XXX EXPRESSION DISPLAYS correctly but unknown displays a wrong value

=Sum(Fields!total_complete_1_Count.Value, "XXX") + Sum(Fields!total_complete_2_Count.Value, "XXX") + Sum(Fields!total_complete_3_Count.Value, "XXX") / 

Sum(Fields!total_complete_1_Count.Value, "UNKNOWN") + Sum(Fields!total_complete_2_Count.Value, "UNKNOWN") + Sum(Fields!total_complete_3_Count.Value, "UNKNOWN").
The value for cell displays correctly but cell 2 does not so the result is wrong. I have also tried to use the numerator and denominator in text boxes properly named and then call them in this expression
=CD(Reportitems!XXX_Sum.Value) / CD(Reportitems!UNKNOWN_Sum.Value). 

THESE ARE THE TEXT BOX NAMES XXX_Sum AND UNKNOWN_Sum.

I am not sure what I am doing wrong

[Anonymous · Accepted solution]

Hi, @miwundu 

Based on the expression you provided, I created the following sample data:

In Report builder I have the following two Dataset:.

I use the following expression to find dataset xxx.

=Sum(Fields!total_complete_1_Count.Value, "XXX") + Sum(Fields!total_complete_2_Count.Value, "XXX") + Sum(Fields!total_complete_3_Count.Value, "XXX")

I use the following expression to find dataset xxx and dataset UNKNOWN:

= (Sum(Fields!total_complete_1_Count.Value, "XXX") + Sum(Fields!total_complete_2_Count.Value, "XXX") + Sum(Fields!total_complete_3_Count.Value, "XXX")) / 
  (Sum(Fields!total_complete_1_Count.Value, "UNKNOWN") + Sum(Fields!total_complete_2_Count.Value, "UNKNOWN") + Sum(Fields!total_complete_3_Count.Value, "UNKNOWN"))

The results are as follows:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.