Crosstab Report with "dynamic" columns 1

[mirirom]

hi there,

i'm wondering if anyone out there has had experience with creating a crosstab report that has no "set number" of columns. in my case, i'm trying to bind a report to a crosstab query which asks a user for both a start and end date; the resulting columns consist of the months in the date range. for example, someone asks for values between Jan-00 and Sep-01, project info is listed as rows, while each month has its' own column heading (with totals, et al).

the query works fine. however, the only way i've been able to bind a report to it, is to create an unbound report then select the query as its' data source in properties. at this point, i'm prompted to enter date values which then produces a list of available fields. quite frustrating since this defeats the whole "dynamic" idea.

is there a way to do this? any advice/suggestions would be appreciated. =)

mirirom

 

[VogonPoet]

I have a report that does just that. Here's how I approached it.

- I did not create a crosstab query. I have each of the months as iif statments sum(iif([date]>=#9/1/2000# and [date]<#10/1/2000#,[value],0)) as fld1. It involves some code to create the Sql string each time. Also, I always create the same number of output fields each time. If the number of fields is fewer than my set number, the last few fields are populated entirely by zeros.
- I named the fields as fld1, fld2, etc. this way the field names are constant and the report knows what it is looking for.
- When setting up the report, I created controls for the maximum number of fields the report could have and hide any extra controls/columns on the right.
- I have code to generate the column headings.

If you want to see the code and report; let me know and I'll email you a copy.

Michael
mhodes@earthlink.net

 

[mirirom]

Michael,

thanks for your offer, and yes(!), i'd like to nab a copy of your code. i'm actually in the process of creating a report with a set number of month fields populated by corresponding data from a recordset. what blows my mind is that both the Access wizards and the Excel Pivot Table tools seem to generate this info rather quickly, whereas &quot;coding&quot; each of my set fields will definitely introduce more lag to my users (pulling over a network, backended at that).

thanks again for your code. my email is benh@steffian.com

mirirom

 

[Databaseguy]

The Solutions sample database has an example of a dynamic crosstab.

I'd recommend looking into it. The maintenance on a Dynamic query is non existent.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[mirirom]

heya Tyrone,

thanks for your advice, however, i'm not sure what you mean by &quot;Solutions&quot;. are you reffering to MSSoluations on MSDN or Northwind or...? please define. i'd really like to check this out.

thanks again

mirirom

 

[Databaseguy]

Should be c:\Program Files\Microsoft Office\Office\Samples\Solutions.MDB

If it's not there, re-install and select all sample databases...

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[mirirom]

aha, that would explain it. thanks again. nice resume/services on your site by the way.

mirirom

 

[Databaseguy]

Thx. I've gotta re-vamp it. I just threw it up there quickly to get some exposure. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[mirirom]

hmmph,

not sure what version of Access you may be reffering to, but i've looked on our CD (Office2000 Pro) and &quot;Solutions.mdb&quot; can't be found. yet, all the other sample databases are.

if the file isn't too large, any chance you could forward a copy of if to me? i'd greatly appreciate it. tx.

mirirom
benh@steffian.com

 

[mirirom]

another update. found the .mdb (included with a few other databases as well) on the MSDN site. it can be downloaded from:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/html/mdbdownload.asp

mirirom

 

[Databaseguy]

Tried to E-mail it to you, but the Verizon on line E-mail server is powered by a ruber band.....

Let me know if the example helped. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[mirirom]

heya Tyrone,

no worries about the email. i downloaded Solutions9.mdb direct from MSDN (see above). and yes, this is helping me A LOT! wish i had this a long time ago.

thanks again for the pointer; pun inteded... =)

mirirom