SSRS report with dynamic column headers

Question

SSRS report with dynamic column headers

sql_novice_2007

Hall of Fame

Points: 3307
More actions
September 22, 2013 at 12:24 pm

#298131

Hi,
I have a report which runs for last 12 months data. Since this is going to be last 12 months the column headers change every month. How can we implement this with dynamic column headers in the dataset?
Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

akhileshguha05 SSC Enthusiast Points: 112 More actions · Answer 1

Hi,

as per your discription u 'r requirement is like when ever Row data changed column name also has to be changed. IF my understanding is correct u have to create one more Row detail row at the end of table of your Report and write below Expression

=Fields(ReportItems!Textbox1.Value).Value in header part.

OR

in your Query you have to insert every column name as a First value in data set.

take that First values in Header using below expression:--

First(Field.value,"dataset")

please Let me know if anything!!

Regards,

Akhil

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 2

Its not just one additional column. The report data comes from the stored procedure which accepts a date as an input parameter and the result will have 12 columns with last 12 months of data.

So it could be any date. Say if we enter 9/23/2013 the result set will have column headers

Sep2012,Oct2012,Nov2012,Dec2012,Jan2013,Feb2013,Mar2013,Apr2013,May2013,Jun2013,Jul2013,Aug2013.

if we enter 01/01/2013 the result column headers will be

Jan2012,Feb2012,Mar2012,Apr2012,May2012,Jun2012,Jul2012,Aug2012,Sep2012,Oct2012,Nov2012,Dec2012.

Based on this requirement how can I keep the column headers dynamic in the result set. This report can run for the dates starting from 01/01/2004 to any future date since this report is going to run in the future as well.

Thanks.

akhileshguha05 SSC Enthusiast Points: 112 More actions · Answer 3

Hi,

As per Your requirement you have to create column group based on your Year fileld from Data set.

please refer below Link for matrix report

http://technet.microsoft.com/en-us/library/ms157334(v=sql.100).aspx

Still you have any thing plz paste your sample Query than i "ll give you step by step Solution !!

Regards,

Akhil

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

akhileshguha05 (9/23/2013)
Hi,
as per your discription u 'r requirement is like when ever Row data changed column name also has to be changed. IF my understanding is correct u have to create one more Row detail row at the end of table of your Report and write below Expression
=Fields(ReportItems!Textbox1.Value).Value in header part.
OR
in your Query you have to insert every column name as a First value in data set.
take that First values in Header using below expression:--
First(Field.value,"dataset")
please Let me know if anything!!
Regards,
Akhil

You're saying that SSRS won't display the column names of a given result set produced by a stored proc? I'm no SSRS Ninja but I find that very difficult to believe.

All,

Can anyone help clarify this please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

happycat59 One Orange Chip Points: 29389 More actions · Answer 5

As akhileshguha05 suggests in his last post - use a matrix report. The column headings will be the values found in the dataset (e.g. the months of this year) and will change depending on what data is present in the dataset. This is really quite easy to do. The only issue you may encounter is the order of the columns. You will need to supply a field (or a calculation) that can be used to sort the columns. Otherwise, you will end up with unsorted or alphabetically sorted data.

If you want to write you own and use a table instead, you will need to do something similar to akhileshguha05's first answer. This is more work (as far as I am concerned) but it will give the desired result.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

happycat59 (9/23/2013)
As akhileshguha05 suggests in his last post - use a matrix report. The column headings will be the values found in the dataset (e.g. the months of this year) and will change depending on what data is present in the dataset. This is really quite easy to do. The only issue you may encounter is the order of the columns. You will need to supply a field (or a calculation) that can be used to sort the columns. Otherwise, you will end up with unsorted or alphabetically sorted data.
If you want to write you own and use a table instead, you will need to do something similar to akhileshguha05's first answer. This is more work (as far as I am concerned) but it will give the desired result.

Keeping in mind that what I know about SSRS just left the room, is there any type of report that will use the headers from a query result set automatically as the column headers for the report without using a Matrix? From what I understand, a Matrix is the equivalent to a CROSSTAB or PIVOT in T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 7

Yes, a Matrix is essentially a crosstab (well, except you don't have to do the PIVOT in T-SQL... SSRS does the pivot part for you).

So, yes, you could choose a date (or two) and have the crosstab built for you. You specify which are Rows, Columns and which are Values, and the matrix/tablix does the rest.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 8

Hi,

I was able to implement this with pivot and matrix report. I have all the row groups in the beginning and column groups in the end in the report. But I need couple of row groups to be at the end of the report. I changed that order in the data set but it's not showing up in the report. Is there a way I can drag them to the end.

I have report headers like the following.

country,State,city,total,avg,jan2012,feb2012,mar2012,apr2012,may2012...dec2012.

in the above headers jan2012 to dec2012 comes from column group and the others from row group.

Now I want the display to be like the following

country,State,city,jan2012,feb2012,mar2012,apr2012,may2012...dec2012,avg,total

How can I do this?

Thanks.

happycat59 One Orange Chip Points: 29389 More actions · Answer 9

sql_novice_2007 (9/27/2013)
Hi,
I was able to implement this with pivot and matrix report. I have all the row groups in the beginning and column groups in the end in the report. But I need couple of row groups to be at the end of the report. I changed that order in the data set but it's not showing up in the report. Is there a way I can drag them to the end.
I have report headers like the following.
country,State,city,total,avg,jan2012,feb2012,mar2012,apr2012,may2012...dec2012.
in the above headers jan2012 to dec2012 comes from column group and the others from row group.
Now I want the display to be like the following
country,State,city,jan2012,feb2012,mar2012,apr2012,may2012...dec2012,avg,total
How can I do this?
Thanks.

To do this, you will need to explicitly handle the order of the columns in the report - the group column group in the matrix has a field to order the heading. Add an expression to this field that puts the columns in the order you are after. Personally, I include a column in my data source that deals with this (i.e. I have a column for the label and a separate column for the column order in the dataset).

sakshisharma94 SSC Rookie Points: 39 More actions · Answer 10

I am facing the same type of problem. Can you please upload a \solution for the same or write down the steps with example.

androidaruna SSC Rookie Points: 37 More actions · Answer 11

Is this still a problem? One simple requirement I have done is

code.ReturnMonthHeader(-11,Parameters!BatchDate.Value)

code.ReturnMonthHeader(-10,Parameters!BatchDate.Value)

etc

in Report Design Code, you can write a script that returns the month name or any other logic you want.

sql Sarah SSCrazy Points: 2081 More actions · Answer 12

sakshisharma94 (3/5/2014)
I am facing the same type of problem. Can you please upload a \solution for the same or write down the steps with example.

To do this add a matrix to the report design. Go to the properties of the tablix and choose the dataset.

Right click on the column heading, go to Group Properties.

There is a field that says Group on. Click the fx button on the right.

Here is an example expression:

=MonthName(Month(Fields!DATE_RECEIVED.Value)) + " " + CStr(Year(Fields!DATE_RECEIVED.Value))

To add other fields, you can click and drag from the data set on the Report Data tab. You can also right click on the Column Header and choose Insert Column and one of the options.

Sarah

aaron.reese SSChampion Points: 13498 More actions · Answer 13

Another Solution:

Add a calculated column to the results set: Split the date into the month name and year and convert the monthname to a zero-led 2 digit number and append this to the year (this will require lots of typecasting, appending and string splitting) so that you can convert dec2014, jan2015 into 201412,201501. You can then use this as the sort criteria of the columns, but still display the placeholder with the proper month name.

The lesson from this is always always return your raw data in separate fields and with logically sortable results.;-)