Sum for year within Tablix

  • Hi

    I have a report that I am trying to do that is filtered by what period the user enters. What I would like to show on the tablix is the total for the year to date even though it has been filtered by period to show current billing, is there a way I can do this by a sum command or something similar

    Thanks
    Chris

  • Rather than filtering the data at SP level, or Tablix, filter the Row Group. Add the Totals row to your tablix first, then in your Row Groups Pane put the filter on the Details_Group. The Total will still have the full total for the data that was returned to the report, where as the tablix will only display those you filtered to.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Hi Thom

    Thanks for the reply, I would have done that but one of the items in the data is a total pulled from the server and it multiples this total when filtered like this so I thought the only way around it was to do this calculation on the tablix itself. Sorry im still new to this and self trained so I dont always know the best answer to it.

    Is there a way I can do the above without totalling a vital piece of information that must show as per  how it should be pulled in?

    Thanks
    Chris

  • It sounds like your describing that you have totals already in your data as well as the non-aggragated data itself. Are you having to display those sub-totals as well? I'd suggest that, if so, you do your grouping and sub-totaling in SSRS, not in your dataset. Then you would have to issue of double counted data.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • The total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level

  • cmw 66135 - Tuesday, November 14, 2017 10:04 AM

    The total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level

    I don't really understand what you're saying here.

    Perhaps you could provide some sample data (including data you don't want to include) and your expected results in SSRS.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Hi Thom

    This is below what I am trying to achieve with my report. This is a spreadsheet that we currently update manually but I am trying to automate a report and make it, idiot proof for the people who will us it.

    This is what I have done so far, I feel im on the right track but as I say I am still very new to this and self taught so I dont always know if I have explained things correctly

  • That dataset doesn't look anything like what you originally posted, hw does it relate? Also, those screenshots are tiny (I very much doubt you have a screen that is only 271 pixels tall!).

    Have a look at the link in my signature on how to post sample data. If you can provide some consumable sample data, along with what you want your SSRS report to look like, I'll be more than happy to try to guide you through.

    Cheers.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Looking at the detail you have posted it is clear that this is a law firm, and I should think that the data you are looking for is already calculated within the PMS system as this would be a normal reporting requirement, so not entirely sure why you need a manual form of entry onto a spreadsheet; also the data you have posted above is not obfuscated and can lead to the easy identification of the individuals and the law firm you represent, please take this as a piece of advice on being careful of what you post in future.
    You may wish to edit your post!!

    ...

  • Point taken Happygeek. Thanks for the advice.

  • You have removed all reference to data and that seems to have stopped any advice. Depending on your PMS system you should find that there are objects; maybe a separate database, maybe a table, or even columns that have a suffix something like '_YTD' and '_PTD' these are where you will find the data you are seeking without having to do manual or otherwise calculations.
    Alternatively in the FE table you may find that the data already exists - I don't know what PMS you have -  as this would normally be available through the Time Recording application. If it is not then you should find it in an archive table associated with time recording.
    I Hope this helps, the report you are seeking is pretty standard and achievable.

    ...

  • In your data set definition > 
    Something like this will help get you going : This just to illustrate the concept. I am using an older version of the adventure works database here . 


    /* assume that prior month is selected as the period */
    SELECT    s.customerID, sum(s.totalDue) as totalSalesAmount,
            ca.totalSalesAmount as totalSalesAmountYTD
    FROM    Sales.SalesOrderHeader as s
            CROSS APPLY
            (
            SELECT    sum(totalDue) as totalSalesAmount
            FROM    Sales.SalesOrderHeader
            WHERE    
                    customerID = @customer and
                    year(orderDate) = 2007 /* can do like year(getdate()) */
            
            ) as ca
            
    WHERE    
            customerID = @customer and
            month(orderDate) = 10 and
            year(orderDate) = 2007
    GROUP BY s.customerID, ca.totalSalesAmount        
    ;

    ----------------------------------------------------

  • Thanks MMartin. Sadly that went right over my head. Sorry im very green at SQL reporting 🙁

  • I have had a thought though is there a way i can create a dynamic calculated field that in my head would look something like =sumif(and("for each fee earner change",Where year id = perameter,Where financial period doesn't equal perameter) as an expression? also for the financial period not to include numbers greater than if someone selects and earlier period number?

    Thanks
    Chris

  • cmw 66135 - Friday, November 17, 2017 8:25 AM

    Thanks MMartin. Sadly that went right over my head. Sorry im very green at SQL reporting 🙁

    No problem. I created some test data to better illustrate my point 

    create table #table (    customerID int,
                            salesAmt decimal(10,2),
                            orderDate date
                        )    
                        
    insert into #table(customerID,    salesAmt,    orderDate)
    values
    (15652,20,'2017-01-01'),
    (15652,20,'2017-02-01'),
    (15652,20,'2017-03-01'),
    (15652,20,'2017-04-01'),
    (15652,20,'2017-05-01'),
    (15652,20,'2017-06-01'),
    (15652,20,'2017-07-01'),
    (15652,20,'2017-08-01'),
    (15652,20,'2017-09-01'),
    (15652,50,'2017-10-01'),

    (15675,20,'2017-01-01'),
    (15675,20,'2017-02-01'),
    (15675,20,'2017-03-01'),
    (15675,20,'2017-04-01'),
    (15675,20,'2017-05-01'),
    (15675,20,'2017-06-01'),
    (15675,20,'2017-07-01'),
    (15675,20,'2017-08-01'),
    (15675,20,'2017-09-01'),
    (15675,70,'2017-10-01'),

    (27059,20,'2017-01-01'),
    (27059,20,'2017-02-01'),
    (27059,20,'2017-03-01'),
    (27059,20,'2017-04-01'),
    (27059,20,'2017-05-01'),
    (27059,20,'2017-06-01'),
    (27059,20,'2017-07-01'),
    (27059,20,'2017-08-01'),
    (27059,20,'2017-09-01'),
    (27059,80,'2017-10-01')

    SELECT t.customerID, sum(t.salesAmt) as totalSalesAmountLastMonth,
       ca.totalSalesAmount as totalSalesAmountYTD
    FROM  #table as t
       CROSS APPLY
       (
                SELECT customerID,
                        sum(salesAmt) as totalSalesAmount
                FROM  #table
                WHERE 
                        year(orderDate) = year(getdate())
                GROUP BY customerID
       ) as ca
       
    WHERE 
       year(orderDate) = year(getdate()) and
       month(orderDate) = month(getdate())-1 and
       ca.customerID = t.customerID
       
    GROUP
         BY t.customerID, ca.totalSalesAmount  

    There is no need here to create anything within SSRS as the YTD field is here and ready to be pulled.

    ----------------------------------------------------

Viewing 15 posts - 1 through 14 (of 14 total)

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