select a.InvoiceID,a.CustomerCode,a.CustName,a.dimension,
a.dimension2_,a.dimension3_ ,
a.SALES_AMOUNT,a.COST_AMOUNT,a.GROSS_PROFIT,a.MARGIN_PER
from (
select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT,
case
when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
else
sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PER

FROM salesdim

where invoicedate between @startdate and @enddate
and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_)a
where a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc

'where invoicedate between @startdate and @enddate
and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)'

how many records is that, and how many records are there in the view in total?

select a.InvoiceID, a.CustomerCode, a.CustName, a.dimension, a.dimension2_, a.dimension3_ , a.SALES_AMOUNT, a.COST_AMOUNT, a.GROSS_PROFIT, case when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100 when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100 when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0 else GROSS_PROFIT/LineAmountMST * 100 END MARGIN_PERfrom ( select InvoiceID, CustomerCode, CustName, dimension, dimension2_, dimension3_, sum(LineAmountMST) SALES_AMOUNT, SUM(linecostAmount) COST_AMOUNT, sum(LineAmountMST- linecostAmount) GROSS_PROFIT --case -- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100 -- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100 -- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0 -- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 --END MARGIN_PER FROM salesdim where invoicedate between @startdate and @enddate and itembuyergroupid = @itembuyergroupid and itemgroupid = @itemgroupid and salesoriginid = @salesoriginid and CustomerCode = @Cust and dimension = @Dept and dimension2_ = @CC and dimension3_ = @Purpose GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_ ) awhere a.MARGIN_PER between @marginFrom and @marginToorder by a.MARGIN_PER desc

and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

I understand @itembuyergroupid etc all come from variables/parameters passed to SSRS. Is it possible for you to tweak them to replace IN with exists and then check if that gives a better execution plan? Often, subquery with IN is inferior to exists in performance but not always. You may check this article for more details - http://blog.sqlauthority.com/2010/06/05/sql-server-convert-in-to-exists-performance-talk/

select * from (select a.InvoiceID, a.CustomerCode, a.CustName, a.dimension, a.dimension2_, a.dimension3_ , a.SALES_AMOUNT, a.COST_AMOUNT, a.GROSS_PROFIT, case when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100 when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100 when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0 else GROSS_PROFIT/LineAmountMST * 100 END MARGIN_PERfrom ( select InvoiceID, CustomerCode, CustName, dimension, dimension2_, dimension3_, sum(LineAmountMST) SALES_AMOUNT, SUM(linecostAmount) COST_AMOUNT, sum(LineAmountMST- linecostAmount) GROSS_PROFIT --case -- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100 -- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100 -- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0 -- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 --END MARGIN_PER FROM salesdim where invoicedate between @startdate and @enddate and itembuyergroupid = @itembuyergroupid and itemgroupid = @itemgroupid and salesoriginid = @salesoriginid and CustomerCode = @Cust and dimension = @Dept and dimension2_ = @CC and dimension3_ = @Purpose GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_ ) a ) twhere t.MARGIN_PER between @marginFrom and @marginToorder by t.MARGIN_PER desc

I assume salesdim is properly indexed. Have you checked the estimated execution plan to see if SQL Server thinks there is a missing index? Also, I have noticed in my SSRS reports, they all run SIGNIFICANTLY faster if the query processing is in a stored procedure and I just call the SP from the report.

@jimtpowers is right
Have a look at the estimated execution plan and see where the time is being spent.
Have a look at the fattest slice, and see if there may need to be indexes added.

You also have your summing going on inside your temptable.
You are therefore potentially throwing away some of the sums that you are doing.

What are these variables that you are using IN with?

and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

Are they arrays or temptables? If they are single values you should be using =, not IN

Look to do your summing outside of a temptable, and after the data is pinned to its smallest possible set
The code may give you the idea--although you will most likely need to debug it.

-- a table variable to hold the values while they get eliminated by the margin calculationDeclare @Vartable1 table (InvoiceID int IDENTITY(1,1) Primary Key, CustomerCode WhateverDataType,CustName WhateverDataType,dimension WhateverDataType,dimension2_ WhateverDataType,dimension3_ WhateverDataType,LineAmountMST WhateverDataType,linecostAmount WhateverDataType,lineamountmst WhateverDataType,linecostamount WhateverDataType--fill the table variableinsert into @Vartable1 (InvoiceID, CustomerCode,CustName,dimension,dimension2_,dimension3_,LineAmountMST,linecostAmount,lineamountmst,linecostamount)select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamountFROM salesdimwhere invoicedate between @startdate and @enddateand itembuyergroupid = @itembuyergroupidand itemgroupid = @itemgroupidand salesoriginid = @salesoriginidand CustomerCode = @Custand dimension = @Deptand dimension2_ = @CCand dimension3_ = @Purpose --now get the final summing done outside the table variable--and do the summing for MARGIN_PER inside a temptable, and eliminate the ones you don't need final summedselect InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,sum(LineAmountMST) SALES_AMOUNT,SUM(linecostAmount) COST_AMOUNT,sum(LineAmountMST- linecostAmount) GROSS_PROFIT, MARGIN_PERfrom (select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount,case when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0elsesum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PERfrom @Vartable1where MARGIN_PER between @marginFrom and @marginTo)GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_order by a.MARGIN_PER desc

Thank you all for your replies. I tried creating stored procedure and creating temp table and summing up outside. But i cant find any difference in performance. The view is not indexed.

@acperkins
Maybe.
We haven't asked what 'long' and 'slow' mean in this context :)

@AnnaJames77 hasn't said what looking at the estimated execution plan has told her.
If she has taken a couple different runs at the syntax, and gets the same performance, then the same underlying issue--perhaps a lack of an index--is underlying it all

Typically, having indexes on columns used in WHERE clauses can be helpful
itembuyergroupid
itemgroupid
salesoriginid
CustomerCode
dimension
dimension2_
dimension3_

Especially if there are text data types involved.
Look in the estimated execution plan for full table scans of the big table.
Do what you can to get those changed to an index scan

'where invoicedate between @startdate and @enddate

and itembuyergroupid in (@itembuyergroupid)

and itemgroupid in (@itemgroupid)

and salesoriginid in (@salesoriginid)

and CustomerCode in (@Cust)

and dimension in (@Dept)

and dimension2_ in (@CC)

and dimension3_ in (@Purpose)'

how many records is that, and how many records are there in the view in total?