Forum Discussion
Help with SSRS Expression
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
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.
1 Reply
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.