Post reply

Total issue

  • 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.

  • July 12, 2012 at 2:53 am

    #1511920

    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.

  • July 12, 2012 at 3:05 am

    #1511925

    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

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

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