Dynamic Column in Report Builder 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Column in Report Builder 2

Author

Topic

colekata
Starting Member

12 Posts

Posted - 2011-03-14 : 07:27:45

I am supposed to bring in a column out of many columns depending upon the user's input.

For example, if a user inputs 1 into the parameter box then Column1 will show up into the query result.

I know how to use parameters in Report Builder but that won't work.

This won't work either as the Dataset is not recognizing the output as a valid column:

DECLARE @SQL varchar(4000)

SET @SQL = 'SELECT ' + @variable1 + ' FROM Table1'

EXEC(@SQL)

Could you please help? Kindly put an example query into your answer. Really appreciate your time.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 07:44:22

You should return both columns in the dataset but add an expression on the visibility-property of the columns that returns true/false based on the parameters.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

colekata
Starting Member

12 Posts

Posted - 2011-03-14 : 10:19:51

Thanks Lumbago

I would request you to give me a bit more detail as I am a newbie...

Perhaps a sample SQL (if reqd) would also be more helpful.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-14 : 11:21:48

You cannot design a report whose datasource is from a dynamic sql

Madhivanan

Failing to plan is Planning to fail

colekata
Starting Member

12 Posts

Posted - 2011-03-14 : 13:57:10

Thank you Madhivanan

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 04:05:51

As I said you need to select BOTH columns in the data set, using a standard sql select query (no dynamic stuff). Then in the grid that displays the data you select one of the columns:

Then you enter an expression in the "Visibility -> Hidden" property:

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

colekata
Starting Member

12 Posts

Posted - 2011-03-15 : 04:26:52

That was really really helpful. Thanking you won't be enough.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 04:51:43

quote:
Originally posted by colekata

That was really really helpful. Thanking you won't be enough.

A virtual beer would though! Cheers

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

colekata
Starting Member

12 Posts

Posted - 2011-03-15 : 14:58:32

If you are in London, we can ave real beer together!

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 15:27:53

quote:
Originally posted by colekata

If you are in London, we can ave real beer together!

Wouldn't that be an Ole~ or Ale in either case....

Don't tell me....Budweiser

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

aleximas
Starting Member

11 Posts

Posted - 2011-03-15 : 22:27:05

Thank you Madhivanan.
unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources