Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.