Complex Quieries - Reporting Services

Greetings:

I have the following query which will get the column name of the table I am trying to work on ..

· SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'MyDataTable'

This query will give me the column name of “MyDataTable”. But I need to add some calculated field – i.e get the number of rows which are not null from “myDataTable” for a particular column.

I tried :

SELECT COLUMN_NAME,

myField = (Select Count(*) from myDataTable);

FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'MyDataTable'

The above one gives me the total number of rows in the “myDataTable”

myField should be base on the name of the column, so it should be something like

myField = (Select Count(COLUMN_NAME) from myDataTable);

But When I do that the COLUMN_NAME is not recognized during run-time.

Anybody that have some kind of idea for a work around please help.

Thanks,

Yared,

Dynamic query can be used.

http://msdn2.microsoft.com/en-us/library/ms157131.aspx