Hi, My report should show the data from the current month to May of last year. For eg. If the current month is feb, the data to be shown in the report are feb2011data, jan2011data,....June2010data,May2010data. Currently my approach is to have static column headers having the columns as apr2011data,mar2011data,.....May2010data.The hide logic (along with the year name) is written in the code of the report, and called in the visibility expression of the column headers. The problem with the above approach is that I am unable to export the report to csv as columns with hide/show logic are ignored and exported csv files does not have all the columns, properly exported. Can anyone please suggest me a method to get the required columns with out hide/show logic. (might be to have my dataset containing all the headers as well as data...not sure of how to have them populated as column headers) Thanx

What if you have a T-SQL query that generates need the data(columns) for the given parameter , then I would use matrix control rather than tablixBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

You can try the same queries with matrix also with column grouping It will not take much time to do this , Bez Csv problem will not solved else.SUHAS R. KUDEKAR http://suhaskudekar.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Please try to add a column group to your table or matrix, and let the matrix do the grouping for you. For instance if your dataset is having having a date field named salesdate that is used in the grouping, then 1. Add a matrix to the report 2. Drag the row group field from dataset to the rows section in the matrix 3. Drag the salesdate field from dataset to the columns section in the matrix 4. Drag sales amount or any other values from dataset to the data section in the matrix 5. Click on the matrix 6. Right click the column group in the group pane (located in the bottom area of the designer) and select properties 7. Set the group on expression to =Int(Format(Fields!SalesDate.Value,"yyyyMM")) 8. Click on the sorting tab, and set the sorting expression also same =Int(Format(Fields!SalesDate.Value,"yyyyMM")), set the order to "Z to A" meaing descending (as per your requirement) 9. Right click the cell that displays the column header and the set the expression as Format(Fields!SalesDate.Value,"yyyy-MMM"). This would display your month as 2011-Jan, 2011-Feb etc. If you need the full month name, add an additional M to the format like Format(Fields!SalesDate.Value,"yyyy-MMMM"). Now run the report and observe the result. Based on your dataset, your columns will be populated. No worries of hiding columns and as such no issues when exporting to csv as well. Hope this helps. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi, plese use below code to solve problem by doing the "UNION ALL" OR you can use "CTE" to combile this below procedure results with my query results and after that you can create matrix by performing the steps explained by "Bilal Hani"...in report declare @year int DECLARE @dt datetime set @year =2011-1 set @dt=convert(varchar(4),@year)+ '0501' SET @dt = DATEADD([month], DATEDIFF([month], convert(varchar(4),@year) +'0101', @dt ), convert(varchar(4),@year) +'0101' ); WITH months as ( SELECT YEAR(@dt) AS yyyy, MONTH(@dt) AS mm, DATENAME("MM",@dt)as [Month], @dt AS dt UNION ALL SELECT YEAR(DATEADD([month], 1, dt)), MONTH(DATEADD([month], 1, dt)), datename("mm",DATEADD([month], 1, dt)), DATEADD([month], 1, dt) FROM months WHERE DATEADD([month], 1, dt) < GETDATE() ) SELECT yyyy,mm,[MONTH],dt FROM months Like this approach you will not miss the month if there is no dat for that specific month ...so continuity of the columns will remain ther... hope it will help you -------------------------------------------------------------------------------- Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

Hi, Thanks for the replies. But I am confused a bit. Query which I am using right now is SELECT p.Name,p.Number,p.Class,p.Section,p.Id,p.Team, Jandata,febdata,Mardata,Aprdata,Maydata,Jundata,Juldata,Augdata,Sepdata,Octdata,Novdata,Decdata FROM Table1 t,Table2 p Where t.pid = p.pid END (My table is updated with the latest month data - it contains last 12 month period data) In my Report, I wrote VB code to show the headers as Name,Number,Class,Section,Id,Team,Apr2011data,Mar2011data,Feb2011data,jan2011data,....June2010data,May2010data and the corresponding data as p.Name,p.Number,p.Class,p.Section,p.Id,p.Team,Aprdata,Mardata,Febdata,Jandata,......,Jundata,Maydata. Then I gave visibility expression based on the current month. If the current month is Feb, I hid the columns Apr2011data and Mar2011data, in the tablix control. Similarly if I am generating the report in December 2011, my columns look like Apr2012data,Mar2012data,Feb2012data,Jan2012data,Dec2011data,...May2011data Hid the columns ( Apr2012....Jan2012). Hope Its not confusing. Can anyone please give me an idea how my dataset should look like for the matrix control. Thanx

Hi, Thanks for the replies. But I am confused a bit. Query which I am using right now is SELECT p.Name,p.Number,p.Class,p.Section,p.Id,p.Team, Jandata,febdata,Mardata,Aprdata,Maydata,Jundata,Juldata,Augdata,Sepdata,Octdata,Novdata,Decdata FROM Table1 t,Table2 p Where t.pid = p.pid END (My table is updated with the latest month data - it contains last 12 month period data) In my Report, I wrote VB code to show the headers as Name,Number,Class,Section,Id,Team,Apr2011data,Mar2011data,Feb2011data,jan2011data,....June2010data,May2010data and the corresponding data as p.Name,p.Number,p.Class,p.Section,p.Id,p.Team,Aprdata,Mardata,Febdata,Jandata,......,Jundata,Maydata. Then I gave visibility expression based on the current month. If the current month is Feb, I hid the columns Apr2011data and Mar2011data, in the tablix control. Similarly if I am generating the report in December 2011, my columns look like Apr2012data,Mar2012data,Feb2012data,Jan2012data,Dec2011data,...May2011data Hid the columns ( Apr2012....Jan2012). Hope Its not confusing. Can anyone please give me an idea how my dataset should look like for the matrix control. Thanx

The version of SSRS is important to this question. I believe in 2008 (and R2) this was easier but pre, it was complicated. I wrote about it here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-column-names-and-fields-in-ssrs-Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!

The version of SSRS is important to this question. I believe in 2008 (and R2) this was easier but pre, it was complicated. I wrote about it here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-column-names-and-fields-in-ssrs-Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!

Hi, The links seems to be broken. Can you please post the correct link. Thanx

Hi, I am already doing what you said in your blog. Currently, in my report I am able to populate the names of the columns as dynamic. But I need the number of columns to be dynamic, since I want my report to populate the data from currentmonth to the month of may. I am using SSRS 2008 R2. Please suggest on this. Thanx

Hi nidabp, To hide the column you have to set its visibility at advanced mode. Firstly, please select Advanced Module like the below picture. Then there is a static in the Row Groups panel, click the static and press F4 button to open property window in your left side, find out Hidden property, type in your expression. If you have any question, please feel free to ask. Regards, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Challen, I have problem exporting the report to csv, if there are visibility expressions for columns. My original post is how to get rid of the hidden columns and popule only the required fields in the report.(please read my original question at the top of this page). Thanx.

Hi didabp, Sorry for misunderstanding your meaning. It seems that you wan to show all the columns after exporting to CSV even if it is hidden when view it on report Manager/Server, If so, I think you could make use of build-in field Globals!RenderFormat.Name, which is the new feature in SSRS 2008 R2. You need to design two matrix control. Similar thread, please see: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/a1e55d93-bafd-4a01-b832-c518c0367b1b If I misunderstand you, please let me know. Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.