Hi folks,
For those you you that write SSRRS reports pulling data from Tessitura db stored procedures ... where do you do your calculations? In the Stored Procedure that gives you the datasetor in the Report
I like having most of my numbers counted in the Stored Procedure as it's easier to juggle in my head and plan. But in debugging an old report that is displaying differently in iOS and Android or Windows I'm working with a different style of build where a fair ammount is calculated in the Report (mainly column math).
Interested to hear your preferences.
Heath
Hi Heath,
I hope this finds you well!
I prefer to perform many calculations in the SP rather than the SSRS report. I haven't found any difference in the speed of processing and find it useful when debugging my SP.
It's also a time saver if you have a couple of reports based on the same SP.
Martin
Likewise...
I prefer to have the calculations in the DB especially around upgrade time as the upgrade scripts can usually pick up on where affected tables/columns have been referenced.
Perhaps this also goes back to my loathing of Infomaker and where it was SO much easier to check the SQL if it was in SPs rather than in the PowerBuilder reports 8-)
Hey Heath Wilder,
I also tend to do as much as possible in the procedure as I can. I agree that doing the work in the procedure also dates back to the InfoMaker days for me as well. Also, as a database person in general, I think we all tend to want to default to doing things in our "natural habitat", as it were.
That said, as a SLIGHT counterpoint, when it does come to column math/aggregations, I HAVE found it useful to leave some up to the report as well. Some things are just annoying to do in SQL. Like getting a percent out of a bunch of INT columns. Not impossible by any stretch; just annoying, and, if it is intended as a fairly straight-forward result in the report, why not let it do the work for you?
But it is all a matter of need. I think column math/aggregations in the report is an easy do when it is a straight forward thing. E.g. all rows will ALWAYS show, or you are always percenting (new word?) on two columns in the same row. But if you are building a complex report with tons of hidden rows, weird references and complex summations... while I have always found that there IS a solution in the report writer in one shape or another, it almost always just seems so much easier to me to do the work in SQL.
Hope that whole project is going well!
John A. Moskal II
Heath Wilder said:I like having most of my numbers counted in the Stored Procedure as it's easier to juggle in my head and plan.
Agreed. Always in the sproc for the above reasons, as well as troubleshooting. The final output report for formatting only.
Thanks all. I really agree. It was column math that was a capacity % that is failing on iOS. Even then I'd prefer to build it in sproc
Martin Keen (Past Member) will likely know the work I'm looking through ;)