Populate Table Headers from db dataset table column names

Hello,

How can I populate the table headers in my report, from the db dataset table column names.  I am calling the dataset through a stored procedure.  But cannot find a way to dynamically set the column headers on the table based on db results - Is this porssible?
MosquitoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shannon_LowderCommented:
Absolutely it's possible.  I have a report that adds columns for each additional month. Check out my code below...It shows how I add columns to the report dynamically.

Basically, I build a loop that iterates through all the columns I need to add, and alter the table to add them using dynamic sql.  Then I populate the values in that same loop, to save processing time.  

It's not amazingly fast, on 1 million raw rows, it takes almost 15 seconds to render the outputTable.  But it works, and keeps the code in the stored procedure, protecting it from the application developers.

Let me know if you need any further help on this question.
---
Shannon Lowder
Database Engineer
--these three columns are the "base data columns"
CREATE TABLE outputTable (
	  col1 NVARCHAR(6)
	, col2 NVARCHAR(21)
	, col3 NVARCHAR(10)
)
--populate the "base data"
INSERT  INTO outputTable  ( AU, OU, line )
SELECT DISTINCT
    Col1
  , col2
  , col3 
From rawDataTable

--since I have to make a new column for every month/year combinations, I need to capture the starting month, starting year, ending month, and ending year.
DECLARE @minMonth INT
DECLARE @minYear INT
DECLARE @maxMonth INT
DECLARE @maxYear INT

--I fill those columns from the raw table.


        WHILE @currentYear <= @maxYear 
            BEGIN
                WHILE @currentMonth <= 12
                    BEGIN
		
                        SET @sql = 'alter table outputTable add ['
                            + RIGHT('0' + CONVERT(VARCHAR(2), @currentMonth),
                                    2) + CONVERT(CHAR(4), @currentYear)
                            + '] decimal(9,2) '
                        IF @currentYear < @maxYear 
                            BEGIN
			--print @sql	
                                EXEC ( @sql
                                    )
			--insert the neccessary data here
                                SET @sql = 'update extracts.gprOutput set ['
                                    + RIGHT('0'
                                            + CONVERT(VARCHAR(2), @currentMonth),
                                            2) + CONVERT(CHAR(4), @currentYear)
                                    + '] = raw.Amount '
                                    + ' from outputTable '
                                    + ' inner join rawDataTable raw'
                                    + '  on outputTable.AU = raw.AU '
                                    + '  and outputTable.OU = raw.OU '
                                    + '  and gproutput.line = raw.line '
                                    + '  and raw.monthBucket = ' + RIGHT('0' + CONVERT(VARCHAR(2), @currentMonth), 2)
                                    + CONVERT(CHAR(4), @currentYear)
                                EXEC ( @sql
                                    )	
                            END
                        ELSE 
                            BEGIN 
                                IF @currentMonth <= @maxMonth 
                                    BEGIN
				--print @sql
                                        EXEC ( @sql
                                            )
				--insert the neccessary data here
                                        SET @sql = 'update outputTable set ['
                                            + RIGHT('0'
                                                    + CONVERT(VARCHAR(2), @currentMonth),
                                                    2)
                                            + CONVERT(CHAR(4), @currentYear)
                                            + '] = raw.Amount '
                                            + ' from outputTable '
                                            + ' inner join rawdatatable raw'
                                            + '  on outputTable.AU = raw.AU '
                                            + '  and outputTable.OU = raw.OU '
                                            + '  and outputTable.line = raw.line '
                                            + '  and raw.monthBucket = '
                                            + RIGHT('0'
                                                    + CONVERT(VARCHAR(2), @currentMonth),
                                                    2)
                                            + CONVERT(CHAR(4), @currentYear)
                                        EXEC ( @sql
                                            )
                                    END
                            END		
                        SET @currentMonth = @currentMonth + 1
                    END
	
                IF @currentMonth > 12 
                    BEGIN
                        SET @currentMonth = 1
                        SET @currentYear = @currentYear + 1
                    END		
            END 

Open in new window

0
Shannon_LowderCommented:
I just re-read the question, were you asking how to create column names from the dataset, or how to read the existing column names from Crystal reports or SQL reporting services?
---
Shannon Lowder
Database Engineer
0
MosquitoeAuthor Commented:
How to create column names from the dataset in reporting services..Right now I call a sp from the report and that gives me the data - but I cannot figure out how to set the column headers in the table on the report to be dynamically displayed - rather than setting their text value in the report itself...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Megan BrooksSQL Server ConsultantCommented:
The dataset field names are constants, so you would normally just use the name itself, a literal string, as the column heading in a table.

If you have a dynamic query that might place different fields into a column depending on (for example) parameters then you might need for the column heading to be dynamic as well. In that case you could possibly derive the column name as an expression (using Choose() or Switch() or IIf()) from the parameter(s).

The column heading can also be obtained from a dataset, though you will have to come up  with the query behind that dataset, which might even be a metadata query if you want the heading to be an actual table name. You can reference any dataset in the report definition from a table heading column using an expression of the form

=First(Fields!<fieldname>.Value, "DatasetName")

When referencing a dataset other than the one assigned to the table, you will have to use an aggregate function (First(), Last(), etc.) and specify the dataset name as the scope. You can also reference outer groups, if the heading is a lower-level one with enclosing groups.
0
planoczCommented:
For the header titles just add a textbox for each column and type in the name that you want to call your column.
0
MosquitoeAuthor Commented:
Well, that certainly was the easiest suggestion.  However - I have already got those in place and the issue here is that the report is going to have to be bilingual, with the translated values for the columns being stored in the database
0
planoczCommented:
You can set the report up to check for different lanuages. No need to have different database data.
0
MosquitoeAuthor Commented:
..That would be an ideal solution - How can I do this?
0
Megan BrooksSQL Server ConsultantCommented:
The heading cells are text boxes that take expressions, just like the detail cells. Provide an expression for the heading cell that tests the language, such as
=Switch(User.Language = "lang1", "Heading1", User.Language = "lang2", Heading2", true, "HeadingDefault")
The language codes are strings like "en-US" (for US English). You can add
=User.Language
to a cell in your report to see the current language code.
Switch() checks each of the odd-numbered parameters from left to right until it finds one that is true, then returns the even-numbered parameter that follows. Just substitute your language codes into it (lang1, lang2, ...) and the headings that you want for each language (Heading 1, Heading 2...). You should include a default case at the end, with "HeadingDefault" being the column heading in the most common language being used.
0
MosquitoeAuthor Commented:

I have attempted to use this, but it is not accepting it...:


=SWITCH(User.Language=``English``,``COUNTRY NAME``,User.Language=``French``,``Payee``, true,``COUNTRY NAME``)
0
Megan BrooksSQL Server ConsultantCommented:
You will need to use the language codes, which are things like "en-US" (English, United States). I suggested just displaying the current code in the report (temporarily; you can remove this once you have what you need) using the expression I provided above. That way you will have the correct capitalization. You can find a completel list of language codes here:
http://msdn.microsoft.com/en-us/library/ms533052(VS.85).aspx
These are all lower case, but you can force the case to lower. You don't need the code for your most common language--just use the final default case in the SWITCH for that. There may be a number of regional variations for a language, but you can can avoid having to deal with that by using just the 1st 2 characters of the code.
With all those changes, your Switch expression above should look like:
=SWITCH(LCase(Left(User!Language, 2)) = "fr","Payee"`, true,"COUNTRY NAME")
This checks for any French language code and defaults to English. Are there any other languages you need to check for?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.