Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSRS report with dynamic column headers Expand / Collapse
Author
Message
Posted Sunday, September 22, 2013 12:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:36 PM
Points: 189, Visits: 688
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.
Post #1497220
Posted Monday, September 23, 2013 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:31 AM
Points: 2, Visits: 19
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
Post #1497296
Posted Monday, September 23, 2013 4:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:36 PM
Points: 189, Visits: 688
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.

Post #1497315
Posted Monday, September 23, 2013 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 3:31 AM
Points: 2, Visits: 19
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
Post #1497322
Posted Monday, September 23, 2013 8:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497414
Posted Monday, September 23, 2013 9:48 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 3,087, Visits: 2,686
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.



Post #1497636
Posted Monday, September 23, 2013 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497639
Posted Tuesday, September 24, 2013 12:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 925, Visits: 5,876
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.
Post #1497663
Posted Friday, September 27, 2013 7:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:36 PM
Points: 189, Visits: 688
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.
Post #1499363
Posted Friday, October 4, 2013 3:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 3,087, Visits: 2,686
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).



Post #1501481
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse