building report based on crosstab

[jommyjet]

I'm trying to build a report with a crosstab definition that can accomodate extra fields as the data source grows. I'm creating a monthly report that will grow next month. Has anyone built something similar to this? I bet there's a statement like insertfield that would allow me to do that when a field does not already exist. thanks

 

[CosmoKramer]

....extra fields as the data source grows

Do you mean new fields will be added, or new field VALUES?

 

[jommyjet]

extra fields will be created thropugh the crosstab. Say a datasource now includes records for march, april, and may, but beginning in June a fourth field will be created by the crosstab. thanks

 

[CosmoKramer]

You mean to say a new column will be created because data for a new month will be present, right??

You could use the In clause as part of your PIVOT statement to do two things:

[ul][li]Define values for columns so that columns will appear even when there is no data for them[/li] [li]Define the order of the columns[/li][/ul]
Something like this:

PIVOT YourMonthField In ('Jan', 'Feb', 'Mar', 'Apr'....etc...);

 

[jommyjet]

thanks. I'll try that

 

[GingerR]

if it will work for you, the easiest thing to do is to take what you know will be the max fields (jan, feb, mar, apr, may, etc) and put all twelve of them across the report. the ones with data will get filled in, the others will not until they have some data. how you can make sure these fields always appear in your crosstab query is to fill in the ColumnHeadings property with "Jan", "Feb", etc..so the col headings will always appear regardless of if they have data or not. this is the equiv of cosmo's IN statement. the problem of course will be if you want to see, say, 12 rolling months, not in the same year. if that is the case, then more crazy programming will be involved. but if you can live with jan-dec and it will only be one calendar year at a time, then you're set.