The query of SSRS report takes long time .

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

Try this:

Open in new window