Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to display records on the basis of previous record

Posted on 2011-03-16
Medium Priority
201 Views
I have data in my Oracle table as following:

TAT1    TAT2    TAT3     TAX1    TAX2      TAX3

VAT       -            -          100        0             0
CST      VAT      -           50         100          0
VAT      OTAX    CST     100         75           50

and I need to show on report as:

VAT    CST    OTAX

100      0          0
100      50        0
100      50        75
Sum: 300       100     75

Plz help me out.

0
Question by:boset
• 5
• 3
• 3

LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 35149640
SO TATn corresponds to TAXn

Try this basic idea.
Create 3 formulas
Name - VAT_Value
If {TAT1} = 'VAT' then
{TAX1}
Else if {TAT2} = 'VAT' then
{TAX2}
Else if {TAT3} = 'VAT' then
{TAX3}
Else
0

Similarly for the other 2

You can then use the formulas for the columns

mlmcc
0

Author Comment

ID: 35158398
Here the problem is TATn values are dynamic. So I cannot hardcode it with VAT or CST or anything.
May be a value that is not there in the database when the report was created, is present in the same when the report  runs.
0

LVL 101

Expert Comment

ID: 35159334
Are there only 3 fields?

Arer there more than 3 possible values?

mlmcc
0

Author Comment

ID: 35166345
only 3 values are possible.
0

LVL 101

Expert Comment

ID: 35166448
So are you saying VAT, CST, OST could be any values but only 3 values are allowed.

How do you plan to set the headers?

mlmcc
0

LVL 35

Expert Comment

ID: 35171424
I _think_ that the basic idea is that there are a number of possible tax codes (VAT, CST, OTAX, etc.) and one or more of those could be used in a single record, up to a maximum of 3, since there are only 3 sets of fields.

I was working on a solution using arrays, but I was just saving the tax codes and a total for each code.  If you also want to see the value for each code from each record, that would complicate things considerably.

Is there some kind of practical limit that you can set on how many different codes there could be in a single report, like there would never be more than 5 or 10 different codes?  If not, I don't see a really good way to do what you described at this point (using that data), but maybe mlmcc can come up with something.

James
0

Author Comment

ID: 35187651
you can take the limit as 3 in this case.
And I would also like to know how to get the total for each code. Since you said you have worked something. So it will be of great help if you can share it.

Thanks.
0

LVL 35

Accepted Solution

James0628 earned 1000 total points
ID: 35187864
If you have the individual values from each record, getting the totals should be easy.  What I had will only produce the totals and just doesn't seem useful, if you're going to be producing the values from each record.  What I came up with is not exactly short or simple, so I don't want to confuse things by posting it, since it's only a partial solution.

Looking back at the example output in your first post, with a column for each tax code, with the value from each record and then a total at the bottom, where did you want that on the report?  In the report footer or somewhere else?  Or is that all that there is going to be on the report?  I was assuming that there was going to be more to the report and you wanted to add that list somewhere.

If you can set a limit, like 3, on how many different tax codes could be in a single report run, then I think subreports might be the best way to do this in CR.  The idea is to have a subreport for each tax code value.  Not for specific values, like VAT and CST, since they could change.  Just one subreport for each possible value, so if there could be 3 tax codes in a report, that would be 3 subreports.  Each subreport would read the same data.  The first subreport would save the first tax code that it found and produce the values, and a total, for only that code.  The second subreport would save the second tax code that it found and produce the values for it, and the third subreport would report the third value.  It seems like that would work.  The key is that you know that you'll only have X number of different tax codes in a single report, and you have enough subreports to handle those codes.  FWIW, I would probably add an extra subreport that looked for any "extra" codes and produced an "Other" column, or maybe just a total, to alert you if the report ever includes more tax codes than you have subreports.

Assuming that the subreport idea works, you could create a subreport with a "tax code number" parameter that told it which code (first, second, etc.) to look for, and then just include multiple copies of that subreport, with different values linked to that parameter.  Personally, I think I'd prefer that to hard-coding the selection into each subreport, but other people might prefer the hard-coded approach.

James
0

LVL 101

Expert Comment

ID: 35190352
Could also do it with 2 arrays.  One for the tax type and the other for the total.

mlmcc
0

LVL 35

Expert Comment

ID: 35196851
mlmcc,

If I'm following you, that's what I did in the formulas that I came up with, but didn't post.  I've got one array with the tax codes, and one array with a total for each code.  But I was forgetting that the sample output in the OP also shows the value for each code from each record, which adds a whole other level to the problem.

James
0

LVL 101

Expert Comment

ID: 35473618
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompaâ€¦
Is your data getting by on basic protection measures? In todayâ€™s climate of debilitating malware and ransomwareâ€”like WannaCryâ€”that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.â€¦
###### Suggested Courses
Course of the Month12 days, 1 hour left to enroll