Solved

How to display records on the basis of previous record

Posted on 2011-03-16
13
194 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
  • 5
  • 3
  • 3
13 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Are there only 3 fields?

Arer there more than 3 possible values?

mlmcc
0
 

Author Comment

by:boset
Comment Utility
only 3 values are possible.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
James0628 earned 250 total points
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Could also do it with 2 arrays.  One for the tax type and the other for the total.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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 100

Expert Comment

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 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