How to display records on the basis of previous record

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.

Thanks in advance.

bosetAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
James0628Connect With a Mentor Commented:
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
 
mlmccConnect With a Mentor Commented:
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
 
bosetAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Are there only 3 fields?

Arer there more than 3 possible values?

mlmcc
0
 
bosetAuthor Commented:
only 3 values are possible.
0
 
mlmccCommented:
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
 
James0628Commented:
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
 
bosetAuthor Commented:
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
 
mlmccCommented:
Could also do it with 2 arrays.  One for the tax type and the other for the total.

mlmcc
0
 
James0628Commented:
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
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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.