Calculation in report studio

[Avisto.sbu]

Hello,

I know how to compute my fields in sql but don't know at all how to do this in report studio,( this computation should not be computed before in framework manager) :

(((select sum(turnover) from t1 where year ='2013')

-(select sum(turnover) from t1 where year ='2012')) / (select sum(turnover) from t1 where year ='2012')) *100

I don't know the syntax at all in queries, and I want a singleton to be returned
is there an equivalent to keyword 'where' in report studio?
is it possible to compute this in report studio?

The biggest problem for me is that I can't insert SQL queries directly in report studio because I need to use report parameters in my query

the
query should look like:

select
((((select SUM(ca_value) from sales_pax_dwh.salespax.fct_ca
where SUBSTRING(cast(ref_month as varchar) ,1,4)=?Year?)
-
(select SUM(ca_value) from sales_pax_dwh.salespax.fct_ca
where SUBSTRING(cast(ref_month as varchar) ,1,4)=?Year?-1))
/
(select SUM(ca_value) from sales_pax_dwh.salespax.fct_ca
where SUBSTRING(cast(ref_month as varchar) ,1,4)=?Year?-1)) *100)

And of course the parameter Year from report studio is not recongnized in sql

thanks very much in advance

[cognostechie]

From the toolbar, drag a calculated column in the query and use this kind of expression:

If ( extract(year,[Date]) = ?Year? ) then ( [turnover] ) else( 0 )   

Note: Replace whatever is inside square brackets with your actual query item.

Create another calculated column with this:

If ( extract(year,[Date]) = ?Year? - 1) then ( [turnover] ) else( 0 )

Create a 3rd calculated item as:

Column1 / Column2 * 100

Hide the 1st two columns if you don't need it in the report. To  hide, click on the column and from the Properties, set
the 'Box Type' property to None.