Solved

How to display records on the basis of previous record

Posted on 2011-03-16
13
199 Views
Last Modified: 2012-06-27
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.

0
Comment
Question by:boset
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
13 Comments
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 250 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

by:boset
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

by:mlmcc
ID: 35159334
Are there only 3 fields?

Arer there more than 3 possible values?

mlmcc
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

Author Comment

by:boset
ID: 35166345
only 3 values are possible.
0
 
LVL 101

Expert Comment

by:mlmcc
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

by:James0628
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

by:boset
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

by:
James0628 earned 250 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

by:mlmcc
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

by:James0628
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

by:mlmcc
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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