0

I am simply trying to divide a cost by a count from a pregrouped view. It worked fine for me with a different view, but this one is displaying numbers that are all over the place. Any insight would be much appreciated.

SSRS Preview

Thank you

SSRS Tablix

Chris DellaDonna
  • 1
  • 3
  • What is the expression you are using? Are Cost and ID Number of Leads fields in your dataset? – alejandro zuleta Oct 27 '16 at 19:36
  • I am doing: =CDbl(Fields!COST.Value)/CDbl(Fields!ID_Number_of_leads.Value) I originally was not converting, but the numbers were just as off. – Chris DellaDonna Oct 27 '16 at 19:37
  • Check if you have groups in you tablix, also double check the expression used in Cost and ID Number of Leads it shuld not be using aggregations. – alejandro zuleta Oct 27 '16 at 19:56
  • I have a parent group - Teams that has a child group - Agents. I want to calculate the cost per lead for each agent. I know for a fact that there are no aggregations within the calculated field. Thanks – Chris DellaDonna Oct 27 '16 at 20:01
  • It seems the `Team` and `Agent` groups is causing that behaviour, could you add a screenshot of your tablix in the design view? – alejandro zuleta Oct 27 '16 at 20:11
  • I have added the picture to the post. Thanks – Chris DellaDonna Oct 27 '16 at 20:35
  • I don't understand why you have a column group, however try using: `=SUM(Fields!COST.Value) / SUM(Fields!ID_Number_of_leads.Valu‌​e)` it should work. – alejandro zuleta Oct 27 '16 at 20:39
  • Even without the group defined, the math is still wrong. Super frustrating – Chris DellaDonna Oct 28 '16 at 13:54
  • Share with us a sample dataset and your expected result. – alejandro zuleta Oct 28 '16 at 14:02
  • Here is what I want to recreate. This is using the same exact view in SSRS as I am using within an excel pivot table. I am unable to get as far as calculating the cost per lead. http://imgur.com/IhZ5ND8 – Chris DellaDonna Oct 28 '16 at 14:12
  • I think the problem is the context where you are placing the expression. Try deleting the tablix and creating a new one only with the team and Loan_Officer groups. Then use the expression I posted in previous comment. – alejandro zuleta Oct 28 '16 at 14:42
  • When I put aggregates in the calculated fields, the preview fails and says that I cannot use aggregate functions in a calculated field. For the record, I REALLY appreciate your help. – Chris DellaDonna Oct 28 '16 at 14:45
  • Delete the calculated field you are trying to create in the dataset properties, In SSRS you cannot use aggregation functions like SUM in calculated field expression. Those calculation must be perfomed in the report itself. – alejandro zuleta Oct 28 '16 at 14:50
  • You are the man, sir. Thank you so much. It doesn't make any sense to me as to why you can define an aggregate expression in the report itself, but not in a calculated field. Anyway, I'm happy that it works. Thanks again. – Chris DellaDonna Oct 28 '16 at 15:17

1 Answers1

0

This worked for me!!!

Delete the calculated field you are trying to create in the dataset properties, In SSRS you cannot use aggregation functions like SUM in calculated field expression. Those calculation must be perfomed in the report itself. – alejandro zuleta

Chris DellaDonna
  • 1
  • 3