Total issue

Question

Total issue

Conficker

SSCommitted

Points: 1567
More actions
July 12, 2012 at 2:24 am

#263689

Hello,
I have main report in SSRS which shows costs for each employee and subreport if employee has expenses it will display list of exepenses. The total for cost and expenses fine which are from two different reports, now there is a need for Grand Total means Costs + Expenses, i can use SUM() function for main report costs filed, problem to use expeses field in main report to calculate total costs.
Is there anyway i can use subreport field into main report ??
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

I would do this at the database level, have a linked table with the costs / expense information SUMmed using calculated columns and inserted into whenever an insert is made to the base tables (i.e using a trigger). You can then get a specific report JOINing this linked table back up to the employee data to get total costs.

Alternatively write a query to do this in the database engine, save as a stored procedure, then have your new report call the SP to get the value.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Conficker SSCommitted Points: 1567 More actions · Answer 2

Thanks derek for prompt reply.

It's good idea to create linked table, however i have few more reports like that to many linked tables.

The report is gone little bit bigger cause there are different groups, and each group level need to show costs and expenses. In SSRS an each level if i use sum() for costs then done however calculate expenses speparately in query its big job if there is no solution in SSRS.

Is there no solution in SSRS???

thanks