CODEWITHSUNDEEP
sql-servervisual-studio-2012reporting-services
Chris Rivadeneira
Chris Rivadeneira

Reputation: 43

Parameter as Column Name in Reporting Service Project

I'm having trouble passing a parameter as a field name in a visual studio reporting service project. Here is my SQL Statement:

SELECT cast(SUM(@Month) as numeric(36,2))
FROM v_financials
WHERE GMOBJ_Object_Account > 0 AND GMOBJ_Object_Account < 100
AND
GMCO_Company LIKE '%' + @Company + '%'
AND
GBLT_Ledger_Type LIKE '%' + @LedgerType + '%'
AND
GMR001_Bill_Item_Code LIKE '%' + @BillItemCode + '%' 
AND
MCRP01_Division LIKE '%' + @Division + '%'
AND
GBFY_Fiscal_Year LIKE '%' + @FiscalYear + '%'

The @Month is the part that is giving me trouble. I keep getting an error that SUM cannot be applied to nvarchar. I don't understand why I am getting this error because when I pass the field name it runs properly, which is also a string.

Upvotes: 0

Views: 1886

Answers (3)

BlueMystic
BlueMystic

Reputation: 2287

This is how i solved the problem: the query is for an INFORMIX database:

SELECT * FROM vs_mant_expendedoras_01 
WHERE 
(CASE ? WHEN 'cardman' THEN cardman WHEN 'display' THEN display WHEN 'unit' THEN unit END)  = ?
AND fecha BETWEEN ? AND ?
ORDER BY date;

The Report parameter is 'text' kind with available values especified like this:

enter image description here Wich gives me a nice Combo picker as the above picture shows.

Upvotes: 0

Lamak
Lamak

Reputation: 70648

As I said on a comment, you can't use a variable directly as a column name on SQL (In this case, you can't do SUM(@Month) unless you want to sum the value of that variable). You can either use dynamic SQL or construct a CASE expression:

SELECT cast(SUM(CASE @Month WHEN 'January' THEN January
                WHEN 'February' THEN February.....) as numeric(36,2))
FROM v_financials
WHERE GMOBJ_Object_Account > 0 AND GMOBJ_Object_Account < 100
AND
GMCO_Company LIKE '%' + @Company + '%'
AND
GBLT_Ledger_Type LIKE '%' + @LedgerType + '%'
AND
GMR001_Bill_Item_Code LIKE '%' + @BillItemCode + '%' 
AND
MCRP01_Division LIKE '%' + @Division + '%'
AND
GBFY_Fiscal_Year LIKE '%' + @FiscalYear + '%'

I'm assuming the values for @Month and the column names (You need to finish the CASE for all the values).

Upvotes: 4

Janne Matikainen
Janne Matikainen

Reputation: 5121

Here is example on how you need to write your query and how you execute it

Specifying Column Name As A Parameter in SELECT statement?

Upvotes: 0

Related Questions