Hello! (are y'all sick of my questions about custom reports yet? :-) )
Thanks to the help of several members of this group, I was able to get my first custom report up and running. Woohoo! Of course, now I've been asked to add one more field -- a contribution/gift count. The report is based on the Current Constituent Giving by List standard report. I've tried looking at other reports with counts and have tried to create a new calculated field based on an existing field, but I'm getting an error message. Has anyone included this kind of field in a custom report? Do I need to add anything to the stored procedure? (which I tried doing but that didn't seem to help either). I can always kick this over to consulting, but it is so much more satisfying to do it myself.
Thanks!Anne Robichaux
Anne M Robichaux,
By this, I assume you are trying to do your calculations in the SSRS definition rather than the stored procedure. Either way will work, and, depending on the situation, I will do either one. What error message are you getting? The language for SSRS manipulation is based on VisualBasic and a little quirky, but easy enough once you get the hang of it.
John A. Moskal II
Yes, I'm trying to do the calculation in the SSRS definition. The error when I try to deploy says "Severity Code Description Project File LineError The definition of the report '\CurrentConstituentGivingbyList_Custom.rdl' is invalid."
My calculated value is =Count(Fields!gift_amt.Value)
Yeah, that means there is an error in your calculation somewhere. If you click "Preview", it should give you a more targeted error message about what is actually wrong; it will likely tell you which cell has the issue (though you likely already know that).
I assume you have checked that "gift_amt" is a valid field from that dataset? If you added something to the stored procedure after creating the SSRS definition, you may need to open that dataset's properties and "refresh" it.
Ah. It says I can't use an aggregate value in a calculated field expression. I guess that means it has to come from the stored procedure, right?
Well, there are always ways around that if you want to find them. The easiest in SSRS would be to add a header/footer to the group and use the aggregate there. You could also put the calculated field somewhere outside of the grouping, hide that cell and then essentially REFERENCE that cell using "ReportItems!" where you want it to go. Or you could just do it in SQL and select that additional column as well.
There are almost always multiple ways to do something. It depends where you want to do the majority of your calculation work. I tend to do about 95% of my work in SQL and save the SSRS work for VERY simple things like calculating column or row totals. For counts, I almost always do that in the SQL as there is usually something I want to ensure against. E.g. duplicate customers, orders, performances, etc... And I can be more confident in my results if I do it in the SQL rather than depend on the SSRS report to parse it correctly.
Thank you! That is very helpful. I'm going to keep working on my SQL and see if I can sort it out.