Need to Display records Group By in SSRS

user_8682362 0
Viewed 79

In SSRS 2008, I am developing the report that should display the records based on condition: It should give me the amt_total based on gift_type (Here, amt_total and gift_type are columns of the table). This is the query I am using.

SELECT   o110113.gift_batch_no, 
     o110113.gift_type, 
     (o110113.gift_date),
     o110113.feed_doc_code, 
SUM (o110113.amt_total)
FROM GIFT_CARD o110113
   WHERE (o110113.gift_type IN
             ('RR', 'RB', 'CR', 'RM', 'RW', 'CW', 'RJ', 'RO', 'RK', 'CI')
     )
GROUP BY o110113.gift_batch_no,
     o110113.gift_type,
     (o110113.gift_date),
     o110113.feed_doc_code
ORDER BY o110113.gift_batch_no ASC, o110113.gift_type ASC

And the report I am trying to generate in SSRS 2008 should look like this.

Clikck this to see the Image of the Report that I am trying to develop

I am trying to use the SSRS expression

 =Sum(Fields!SUM_O110113_AMT_TOTAL_.Value,"GIFT_TYPE")

It is throwing me the error saying:

Please click this to see an Error I am getting in SSRS

Kindly provide the Solution

This the report design I have developed

[click to see the Image of report]

Thanks Arun

ssrs-2012 ssrs-2008 sql
2 Answers

That error has to do with the scope of your sum. without seeing the data sets and assuming it is not a nested aggregate operation based on the image. you are trying to reference the column name "GIFT_TYPE" when it should actually be the name of the record set, or the appropriate scope(data region/group).

here is how scope works from the provided link below

The value of scope must be a string constant andcannot be an expression. For outer aggregates or aggregates that do not specify other aggregates, scope must refer to the current scope or a containing scope. For aggregates of aggregates, nested aggregates can specify a child scope.

you will want to see this link for more information

user_83462660

Here you need to do group by of your Tablix. First you need to add Row group on gift_batch_no so that will dispaly based on gift batches. Another row group your have to create on Gift type and in that second group type you need to set SUM of total amount it will work.

Let me know if you have any other query

user_23396740
Related