Search code examples
ssrs-2008

SQL SSRS aggregate fuctions

I am trying to figure out the aggregate functions in SQL SSRS to give me to sum of total sales for the given information by YEAR. I need to combine the year, the months within that year and provide the total sum of sales for that year. For example: for 2018 I need to combine month's 2-12 and provide the total sum, for 2019 combine 1-12 and provide total sum and so on.

enter image description here

I'm not sure where to begin on this one as I am new to SQL SSRS. Any help would be appreciated!

UPDATE:

Ideally I want this to be the end result:

id      Year  Price

102140 2019 ($XXXXX.XX)

102140 2018 ($XXXXX.XX)

102140 2017 ($XXXXX.XX)

And so on.

your query:

 Select  customer_id
 , year_ordered
 --, month_ordered
 --, extended_price
 --, SUM(extended_price) OVER (PARTITION BY year_ordered) AS year_total
 , SUM(extended_price) AS year_total
 From customer_order_history 

 Where customer_id = '101646'

 Group By 
 customer_id
 , year_ordered
 , extended_price
  --, month_ordered

Provides this:

enter image description here

multiple "years_ordered" because it is still using each month and that months SUM of price.

Solution

  • There are two approaches.

    1. Do this in your dataset query:

      SELECT Customer_id, year_ordered, SUM(extended_price) AS Price FROM myTable GROUP BY Customer_id, year_ordered

    This option is best when you will never need the month values themselves in the report (i.e. you don't intend to have a drill down to the month data)

    1. Do this in SSRS By default you will get a RowGroup called "Details" (look under the main design area and you will row groups and column groups). You can right-click this and add grouping for both customer_id and year_ordered. You can then change the extended_price textbox's value property to =SUM(Fields!extended_price.Value)