Rpt: crosstab(s) PLUS calculated fields

This probably requires coding a recordset? Do I have a choice?

Got a cross-tab worked up, based on a single table (probably should be on a query, and could I then use two tables?) that has 18 number fields.

I need to "insert" into the cross-tab at print time, the following:

1 field (for each of the 5 output columns) that totals the 4 fields above it.
1 field (for each of the 5 output columns) that shows the difference between a source field and the  calculated field.

2 more calculated fields, whose calculations are ratios. I think these also use some calculated data and some "source" data, according to different formulae, tho I'm not yet sure what those formulae are.

The only other way I can think of to do this is to break the one neat cross tab into two, and build an "intermediate" (because it physically has to go in the middle of the displayed "table" query that calculates the new numbers.  Will ONE additional query do it?


Marilyn Justman
Who is Participating?
paaskyConnect With a Mentor Commented:
You want already move this to PAQ and share points between us? You're too kind Marilyn! But it's okay too if you leave this question open and wait other experts to participate this too.

You can send a request to EE Community Support and they can help you to split points: http://www1.experts-exchange.com/Customer_Service/Experts_Exchange/

I'm willing to help you in this thread so feel free to send extra questions here so I can feel I've really deserved the points. ;o)

Hello mjustman,

Could you please post here your current crosstable and details how do you like to calculate new fields. I think this might not require any coding if we can calculate everything in one crosstable query...

meant crosstable query SQL and some sample data...
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

mjustmanAuthor Commented:
As requested, here's the SQL:

TRANSFORM Max(CRHistFinData.Value) AS MaxOfValue
SELECT CRHistFinData.ItemTitle
FROM CRHistFinData
WHERE (((Format([Date],"yyyy"))>1994) AND ((CRHistFinData.AlphaID)="00960"))
GROUP BY CRHistFinData.ItemTitle
PIVOT Format([Date],"yyyy");

Now let's see if I can grab the query results and get them in here!

OK: here's query's output saved as prn file:
                               CRHistFinData_Crosstab        5/10/2000

ItemTitle    1995      1996      1997
Accts rec   1911517   1680649   2327882
Capital S    250000    250000    250000
Cash         463568    605975    453429
Cash val      76713     81713     81713
Current d   2418134   1920718   2219407
Debenture         0         0         0
Def liab      92603    125031     73115
Dividends         0         0         0
Merchandi   1898386   1665484   1685638
Mortgages    424405    408905    391687
Net worth         0         0         0
Notes rec         0         0         0
Profits      248964    203667    186637
Reserves          0         0         0
Ret earni   2708724   2912391   3099028
Sales      18182360  18408536  16719170
U S Gov s         0         0         0
Withdrawa         0         0         0

This is passed to a report that looks exactly like this and is included as a subreport in a larger, named one.  (I also haven't figured out yet how to grab the ID that is used to join this to the larger report -- xtab queries don't seem to tolerate "extra" fields.)

But the goal here is to total the first 4 (substantive) rows of this AS the 5th row ("Total.."), then subtract the next row (Current debt) from that total and report the results immediately after the Current row, then figure two ratio rows ... (which, incidentally, are reported in percentages (not $ amts as all other #s represent) and report those following.  So order of output should be:

Cash ... (data)
Accts rec (data)
Notes rec (data)
Merchandise (data)
Cash val  (data)
->TOTAL ASSETS (calculated)
Current debt (data)
-> WORK CAPITAL (TOTAL - Current d, calc'd
->CURRENT RATIO (calc'd)
->QUICK RATIO (calc'd)
Debentures (data)
etc ... data to end

If you want the source table's structure, let me know.



p.s. there's no averaging or counting: I used max of each to grab the one and only piece of data for each label/year to make the table. So data you see IS what's in the "year" field.  Item No controls the order of Item title, which is the row heads.
This is much complicated than I though at first glance. I see you're working with GL application?

In GL systems I've been working with there has been differend code fields in account table which have been used to help calculating. I think table definitions would help now.

You like to build all data (and calculations) into worktable and print it contains to report or would it be possible to build several subreports which calculate certain data and put them all into one main report?
I prefer a "custom" crosstab convention like this:

SELECT CRHistFinData.ItemTitle, Max(IIf(Format([Date],"yyyy")=1995,[value],0)) AS [1995 Value], Max(IIf(Format([Date],"yyyy")=1996,[value],0)) AS [1996 Value], Max(IIf(Format([Date],"yyyy")=1997,[value],0)) AS [1997 Value]
FROM CRHistFinData
WHERE (((Format([Date],"yyyy"))>1994) AND ((CRHistFinData.AlphaID)="00960"))
GROUP BY CRHistFinData.ItemTitle;

This does the same thing as crosstabs except it is far more flexible, filling in dead space with 0s and so forth. In the standard crosstab, if there was no 1996 data it would generate only 2 columns and as such your reports would choke because they needed 3.
mjustmanAuthor Commented:
Thanks, guys.
The trickiest part of this problem has gone away.  The "other" programmer on this is planning to calculate the calculated fields at form time, so the query and report will get "hard" numbers. And yes, I probably MUST have a pre-query query.

I'm planning to break this one into two crosstabs, so the two lines with percentages in them (in the middle) can be simple queries and get formatted differently.

I'm going to try nealid's code, altho the new filip to this is that (it turns out) I need to report out the last 5 year's worth of data FOR THOSE YEARS THAT HAVE DATA, not a known set of years, and I still have to get that parameter (referring to the master-report's query) to replace "00960" for the AlphaID. (nealid: "date" is actually a field in the table/query.)

I think the only fair thing to do is to split the points between you for now, and re-asks the question if I need more specific help.  

Does either of you know HOW TO SPLIT POINTS????


mjustmanAuthor Commented:
This is one of the few places I can afford to be a Big Spender!

Problem is changing, as I said, and I haven't yet tested any suggestions.  Sure, I'll appreciate any further help, and will post further questions, but to keep the thread "honest", I think I should officially end it before we take the discussion "elsewhere".

Off to visit the Customer Service people!
Reviewing question.

Customer Service
mjustmanAuthor Commented:
I've decided you're right...I should save some points for future questions...so have asked CS to reduce this to 100 points, then split between you.
Hope that isn't TOO disappointing.
BTW: I've already posted a new question, regarding how to parameterize this xtab, which NOBODY's responded to yet.  That means it's a GOOD QUESTION.
I'm open to answers suggesting I attack the problem a whole different way...

Community Support has reduced points from 200 to 50
Hello everyone,

Reducing points to 50 allow for point decrease and split.

mjustman, you can now accept one of the comments in this thread as an answer. To award the other Expert, you can create a new question in this topic area with a title of 'For ExpertName -- 10341641' using that Experts username.

Remember, the Accept Comment as Answer button is in the header of the comment.

For your convenience, you can use this link to create the new question:

Customer Service
mjustmanAuthor Commented:
Nealjd - Go post  some comment at the question titled "nealid-Q10341641" (sorry for the misspelling) ... I owe you some points and I need a comment to "accept" in order to give them to you.

Nealjd and Paasky:
Believer came up with a nice, neat solution to my "xtab" problem by simply making my data into a multi-column report INSTEAD of an xtab. Don't know if you can discern enough details from the discussion there, but ask him or me if you have occasion to deal with a problem like this one!

Q was number: 103533590
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.