Solved

Rpt: crosstab(s) PLUS calculated fields

Posted on 2000-05-09
13
322 Views
Last Modified: 2012-05-04
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?

Thanks.

Marilyn Justman
0
Comment
Question by:mjustman
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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...

Regards,
paasky
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
meant crosstable query SQL and some sample data...
0
 

Author Comment

by:mjustman
Comment Utility
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.

Thanks.

Marilyn

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.
mj
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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?
0
 

Expert Comment

by:nealjd
Comment Utility
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.
0
 

Author Comment

by:mjustman
Comment Utility
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????

Thanks.

Marilyn
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Accepted Solution

by:
paasky earned 50 total points
Comment Utility
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)

Regards,
paasky
0
 

Author Comment

by:mjustman
Comment Utility
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!
0
 
LVL 3

Expert Comment

by:darinw
Comment Utility
Reviewing question.

darinw
Customer Service
0
 

Author Comment

by:mjustman
Comment Utility
Guys:
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...

Marilyn
0
 
LVL 3

Expert Comment

by:darinw
Comment Utility
Community Support has reduced points from 200 to 50
0
 
LVL 3

Expert Comment

by:darinw
Comment Utility
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:
http://www.experts-exchange.com/bin/NewQForm?ta=91

darinw
Customer Service
0
 

Author Comment

by:mjustman
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now