By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Crosstab as subreport data source

P: n/a
Environment: Access 2k, win2k

Business Problem: I have to display returns on investments made over a
period of years, and show the amount of return earned on each investment by
year. For example, if you placed some money in 2001, you'd want to see the
returns on that for 2002, 2003, 2004 etc. (These are long-term investments,
so it's quite reasonable to track themin this manner - I wouldn't take this
approach aon an active portfolio..)

So, this strikes me as an ideal use of a crosstab report, with the time of
investment as rows, and the return years as columns. No problem, build a
report, write some bits to check how many columns, sort the columns (thank
you, Mr. Lebans - awesome sort implementation!), bind the detail and footer
fields at runtime, make it pretty. Except that I can't use it as a
subreport, even though there is no master-child linking. I get the "cannot
use crosstab as a subquery error"

I looked through the archives, and found some good suggestions, like setting
the data source property to an SQL string on report open, or of pre-setting
the values in the columns, none of which worked.

Am I beating my head against a wall here? I would really like to make the
crosstab work, as all the other solutions I can think of require some fairly
convoluted code that makes me more than a little leery.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Use the crosstab query to fill a temp table for the sub report.

"James Neumann" <jn******@dspd.mail.ipsoinc.net> wrote in message
news:i5*****************@newssvr32.news.prodigy.co m...
Environment: Access 2k, win2k

Business Problem: I have to display returns on investments made over a
period of years, and show the amount of return earned on each investment by year. For example, if you placed some money in 2001, you'd want to see the returns on that for 2002, 2003, 2004 etc. (These are long-term investments, so it's quite reasonable to track themin this manner - I wouldn't take this approach aon an active portfolio..)

So, this strikes me as an ideal use of a crosstab report, with the time of
investment as rows, and the return years as columns. No problem, build a
report, write some bits to check how many columns, sort the columns (thank
you, Mr. Lebans - awesome sort implementation!), bind the detail and footer fields at runtime, make it pretty. Except that I can't use it as a
subreport, even though there is no master-child linking. I get the "cannot use crosstab as a subquery error"

I looked through the archives, and found some good suggestions, like setting the data source property to an SQL string on report open, or of pre-setting the values in the columns, none of which worked.

Am I beating my head against a wall here? I would really like to make the
crosstab work, as all the other solutions I can think of require some fairly convoluted code that makes me more than a little leery.

Nov 12 '05 #2

P: n/a
raii,

Thank you - that did the trick. Can't believe I didn't think of that.
AAArrrrrrrggghhhhh....

I'd still like to make the crosstab work as a direct subreport source,
but that is going to be done on my own dime, not my customer's.

Please remove DAHT to get correct email. Please post any replies
directly to this newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.