"Crosstab" report question

[mirirom]

hi all,

i have a report that is bound to a crosstab query. the column header in the crosstab query contains values (mmm yy) that periodically change, or rather, are continuously added. i'm wondering if there's a way to create a report based on this query yet reflects the changes in the columns. as it is now, once i create the report, the columns (and bound values) are fixed. e.g say the first date value is Jan-78 and the last is Jan-01. the report is only valid IF and only if the corresponding values ($ amounts) exist within this timeframe. if a user enters a new $amount for a newDate, the report obviously does not reflect the change.

idealy, the report should be calculating how many months there are and Then create columns for all of them...

been thinking about this too long /sigh. any help would be greatly appreciated.

mirirom

 

[seaport]

You may try to publish all data in this crosstab query into an excel file, programmingly. I cannot think any other way out.

Seaport

 

[mirirom]

okies, guess that'll do for now.

i'm just curious though because, if a wizard can create a report on the first time through (obviously running some sort of code), then this sort of action must be possible. i've created a few toy reports that list each of the applicable month values in a tabular fashion (going down) for each project record; my logic tells me that there's some way to derive the minValue & maxValue for months over the dynaset in the report, (actually, i've done this by creating an ADODB recordset in VB) and then format them into columns or some kind of group option.

 

[mustangirl]

Did you ever figure out how to do this because I am having the same problem.
Thanks
Shawndra

 

[mirirom]

Shawndra,

don't know if you're still checking the thread, hopefully this'll find you. i found some great advice on how to solve this problem; can be found in a new thread i created recently. check out the link below and scroll down a ways to see the URL for a GREAT .mdb from MDSN that shows/tells exactly how to do this.

hope this helps

mirirom

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/703/qid/139142

 

[Databaseguy]

See the Solutions sample database. It has a Dynamic report that adds and deletes columns as the X-tab it is based on changes..... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[JoeMiller]

I don't know if this will help AT ALL!! but I had a similar problem with crosstab reports. My problem was that I always wanted to show the latest month in the furthest right column of a report. The problem is that crosstabs always sort Jan, Feb, Etc...

I wrote a fancy little module that takes a MMM formatted crosstab and then creates a select query based on it placing the months in the appropriate column. If you'd like it I can email it to you, it may provide you some ideas. It's not a solution but may open you up to another approach.

Anyone else interested can email me as well.
Joe Miller
joe.miller@flotech.net

 

[mirirom]

hi Joe,

sure, i'm always open to newer & better ideas; would love to see your code. feel free to email it to me at:
benh@steffian.com

thanks

Ben Huntley