build a field on a one to many? 3

[jwa6]

Its been along time since I had to write a report in CR.

This is the sql I would use


Select
a.id,
a.name,
b.record_no
b.text
From
Table a,
Table b
Where
a.id = b.id
and b.record_no <5
order by b.record_no asc

for every 1 table a record I will pull up 5 table b records. I would then like to put the b.text field into a long report ( concatenate)

report field = b.text.1
report field = b.text.2
report field = b.text.3
report field = b.text.4
report field = b.text.5


then display

a.id a.name report field

thanks for any input folks!!

 

[synapsevampire]

Please at least post your software version.

It sounds like you want a Left Outer join from A to B. Since you know the SQL already, you migh also create a View on your database to handle this.

Anyway, concatenation would be accomplished by creating a group on the A.ID, and using the 3 formula method:

Group Header formula:
whileprintingrecords;
stringvar AllText:= ""

Details section formula:
whileprintingrecords;
stringvar AllText:= Alltext+{table.text}

Group Footer formula (for displaying):
whileprintingrecords;
stringvar AllText

Keep in mind that your example shows 5 fields not 5 records:

report field = b.text.1
report field = b.text.2
report field = b.text.3
report field = b.text.4
report field = b.text.5

I assumed that you made a mistake.

-k

 

[jwa6]

Please at least post your software version.

-- crystal reports 8.5 - but as you know crystal syntax is the same for CR7 on.

Group Header formula:
whileprintingrecords;
stringvar AllText:= ""

Details section formula:
whileprintingrecords;
stringvar AllText:= Alltext+{table.text}

Group Footer formula (for displaying):
whileprintingrecords;
stringvar AllText

-- I didnt make myself clear on what I need to do.
--The displaying and clearing of the work field at the group level on the report will not work.

6 db records will have to be read for every report detail line to be displayed --- one header db record and 5 detail db records.
--the way to build the detail 'report field' below is correct. What needs to happen is that on the db "header.id" change ( 5 records of the db.detail have been read and transferred at this point into the "report field")

the report detail line needs to be displayed on the report at this point. ( not at the group level)
Is the a detail level way to test for the db "header.id" change?


Keep in mind that your example shows 5 fields not 5 records:
-- that was my intention
report field = b.text.1
report field = b.text.2
report field = b.text.3
report field = b.text.4
report field = b.text.5

I assumed that you made a mistake.

--no mistake ...I didint really think I needed to display the records to get my point across? You seemed to understand the point didnt you? Whats the problem?

 

[solmskid]

Don't know if you resolved this or not...
If you want to display Id and ReportField as described,
the method suggested should work, just suppress the group header and the detail. The group footer should display the id and the concatenated field.

good luck...

 

[jwa6]

yes this helps...thanks

What is problomatic for me.... in a canned product there isnt a 'procedural programmical' method to display in the detail line IE read db records...store values from db in a array....form the detail ...present to a screen.
This method seems 'tricky'. By that I mean using the group level as the detail display. I need to start thinking out side the old rpg programming I did many moons ago.

very much appreciated!

jim

 

[synapsevampire]

This is wrong:

-- crystal reports 8.5 - but as you know crystal syntax is the same for CR7 on.

There are many features in CR 8 not in CR 7, and CR 9 not in CR 8.X, etc.

"what's the problem" is that you stated:

"for every 1 table a record I will pull up 5 table b records", but that isn't what you stated, and later you state that is what you meant, so either you don't know what a record or a field is, or you're just being difficult.

I suggest that you hire a consultant or task this to someone who understand databases and Crystal.

-k

 

[jwa6]

thanks for your suggestion. You do have some valid points.
Here is mine for you:

Get rid of the chip.

 

[synapsevampire]

No chip here, I just pointed out that I had to guess at your environment because you misunderstood basic database terminology, and later the aolution.

I should have mentioned that you needed to suppress the details and the group header, I had thought that stating display the group footer would mean the same to you, I often make leaps I should not.

Hope it worked out for you, and please try to post accurate technical information in the future.

-k

 

[jwa6]

No chip here, I just pointed out that I had to guess at your environment because you misunderstood basic database terminology, and later the aolution

-- you did...and that was my mistake for not being thoughtful enough or distracted.( as usual)

I have to admit I "hate" writing reports ( see burning resentment) as I see it as a dead end. Im too old to go out for a 'reporting job' at 50.
After looking at monster...the CR jobs I see posted pay 16 to 28k a year. I got too complacent Im afraid. Now Im the reports guy.

When I was on the as/400... the users did the reports they needed on the db. They used MS access. Now I work for govt.
Govt. has a much different mindset. This 'canned reporting' I view as a waste of my time and energy. I am here only for the health ins.

Im trying to rectify this fustration by night classes for vb.net. Hopefully this will ensure my marketabilty for a few more years anyway. I miss the control of programming. I also miss error msgs that give me some feedback.

I will try to take other users time more seriously. This forum has really been a very good resourse for this product.

 

[synapsevampire]

I wouldn't be so concerned about Crystal projects, amny here make > 100 hourly.

I think that the key is to have databases down, and it seems much more likely for .net work to go offshore than someone who understands business intelligence, try hitting the database and data warehousing side, it'll be hard for offshore firms to code or maintain that world because of it's sensitivy.

BTW, I'm not much ytounger than you, and already did my screen hanging era, I wouldn't consider .net these days. Database programming is much more flexible and interesting to me, almost every project needs it.

All of the contracts I see are usually 45-80 hourly on Dice/Hotjobs, but then I only work in larger cities.

Good luck with it.

-k

 

[jwa6]

what do you mean by DB programming? Oracle db work for instance?

thanks for your suggestions and thoughts

jim

 

[synapsevampire]

Right, the more backends that you've worked with, the better.

I would think that your backend work with AS/400 would be very useful, so perhaps if you start with that niche coupled with CR coding you'll find a more expansive, lucrative market.

-k

 

[jwa6]

INteresting...
There is a 16 credit program here put on by the tech college.
It is the 'oracle offical' standard classes format Oracles uses for the certification training but at a greatly reduced price and covering 18 weeks for each 1 of the 4 courses. Its worth pursuing( look into) ..maybe not for the limited madison area but the milwaukee area. Much bigger more business and believe it or not cheaper.

 

[synapsevampire]

I've received numerous offers from the Madison area in the past few months and they were willing to pay 50+ hourly for someone strong in Crystal.

There are always lowball headhunters, such as RHI (aka Robber Halfrate Inc) that try to undercut the market, but then they will eventually come looking for someone who knows what they're doing as $20-$30 hourly probably won't buy much.

I travel for most jobs, and if you have that luxury, you'll find that Crystal jobs for $50-80 are plentiful, and if you max the per diem, you should be pushing the equivalent to 150 - 200K per year.

-k

 

[jwa6]

your joking!

I havent seen anything in the madison area( wi)?

I am wanting to stay close( albiet very tired of winters)due to family etc. ( Milwaukee is 80 m) Dad first ya know. But ...I will look at other search engines than monster in the future.

jim

 

[synapsevampire]

Yeah, Monster has gone waaaaaaaaaaay downhill these days.

You might also try to work out a contract where you can do some telecommuting, it's generally available at most firms once you establish yourself (read: threaten that it's the only way to continue if they want to extend the contract).

-k

 

[jwa6]

hahahahahahahah

thanks!

 

[Naith]

I love a thread with a happy ending. For a moment there, I thought it was going to be crystals at dawn.

Good luck jwa.