Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Record Selection Help Post Reply Post New Topic
Page  of 2 Next >>
Author Message
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Topic: Record Selection Help
    Posted: 22 May 2009 at 7:03am
I should warn in advanced that I have been working with Crystal for a few months and am, at best, a novice formula creator.
 
I'm having trouble creating a formula to select all the information that I want and here is the explanation:
 
The dataset is coming from an XML. Each recordset has D0, D1 and D2 sections. D0 detail section contains account information. D1 detail section contains meter information. D2 detail section contains billing information as well as errors and warnings associated with the meter.
 
The D2s are either Billing Information sections or Errors and Warnings sections. They are differentiated by a field <DTLCODE>. If <DTLCODE>=D it is a Billing Information D2 (Detail). If <DTLCODE>=M it is a Errors and Warnings D2 (Messages).
 
PROBLEM:
 
Not all accounts have Message D2s associated with them. If a Print Option 'Print Errors and Messages' is checked, then I do not want to include those accounts (D0) that do not have a Message D2 (<DTLCODE>=M).
 
I tried something along the lines of:
 {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M'
in the Record Selection Formula Editor.
 
This seems to give me the D0 records I want Smile, but it only prints the D2 sections that have <DTLCODE>='M'Cry. I still need to print information from the D2 sections that have <DTLCODE>='D' though.
 
If I do something like:
 {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M'
 and {XMLNAME.D2_DTLCODE}='D'
then I get no information at allAngry.
 
Can someone PLEASE HELP?
 
Thanks in advance.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2009 at 7:28am
NOt sure based on you description if this will give you all the records you need but I can fix the stement you tried...you need an "or" statement and to parenth the other statement to include any record where dtlcode=d.
 ({@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M')
 or {XMLNAME.D2_DTLCODE}='D'
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 22 May 2009 at 7:41am
Thank you for your reply.
 
If I use this formula I get all D0 records from the XML. I only want D0 records that have a D2 with <DTLCODE>='M'.
 
For a better visual of how the record sets from the XML look:
 
<D0>
<field1>
<field2>
<D1>
<fielda>
<fieldb>
</D1>
<D2>
<DTLCODE>D
<fieldz>
</D2>
<D2>
<DTLCODE>D
<fieldz>
</D2>
<D2>
<DTLCODE>M
<fieldz>
</D2>
</D0>
 
but I may also have one that looks like this:
 
<D0>
<field1>
<field2>
<D1>
<fielda>
<fieldb>
</D1>
<D2>
<DTLCODE>D
<fieldz>
</D2>
<D2>
<DTLCODE>D
<fieldz>
</D2>
</D0>
 
If the {@PERR} print option equals 'Y' then I do not want to include the second record set in the report. If {@PERR} print option equals 'N' then I want both record sets. I have a formula to include both record sets:
 
 and
 {XMLNAME.D2_DTLCODE}={XMLNAME.D2_DTLCODE}
but I need something before the for when {@PERR}='Y', then I will put an 'or' followed by the above code.
 
Any other help would be greatly appreciated.
 
Thanks in advance.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2009 at 7:56am
Sorry, having trouble tracking but essentially I think you can just use an if then statement in you select to address this...You will have to tweak it as I can't totally track your process yet (coffee has not kicked in yet this morning) but maybe this will get you on the right track:
if  {@PERR}='Y' then {XMLNAME.D2_DTLCODE}='M'
 and {XMLNAME.D2_DTLCODE}='D'
else {XMLNAME.D2_DTLCODE}='D'
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 22 May 2009 at 8:08am

Thanks for your reply.

Yeah it's almost lunch time over here so I'm already deep into it.

As I wrote before, a statement with {XMLNAME.D2_DTLCODE} equal to 'M' and 'D' gives me no data at allCry. Even if it's an If/Then. I'm not sure why it works out that way.



Edited by FrnhtGLI - 22 May 2009 at 8:09am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2009 at 8:27am
Sorry was not paying attention. I really need more coffee Dead
That of course will not worker because it can never be both hence no returns...
Basically what I was trying to do was allow you to change it around using the if-then on your {@PERR} formula as the toggle.
if it is "n" then select based on one set of criteria, if it "y" then another set criteria. I just can't seem to wrap my head aound what the 2 different total criteria are basedon Y or N return but maybe something maybe like:
 if {@PERR}='Y' then {XMLNAME.D2_DTLCODE}='M'
 else {XMLNAME.D2_DTLCODE}='D'
 
If that is not it can you tell me exactly what your criteria are using for both Y and N options?
Thanks.
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 22 May 2009 at 8:59am
That yeilds the same result as before, I get the D0, D1 and Message D2 information to print, but the Detail D2 information does not.
 
Maybe if I give you some background on the layout it will help:
 
Most of the information is in a Group Header section that is grouped on a Meter # field found in the D1 section. The information in the section includes fields from the D0, D1 and Detail D2 (DTLCODE=D) sections.
 
Details A section contains fields from Detail D2 sections.
 
Details B section contains a formula that totals an amount field in the Detail D2 sections.
 
Details C section contains the messages from the Message D2 (DTLCODE=M) sections.
 
{@PERR} would be 'Y' if the customer only wants to print accounts/meters that have an Error or Warning associated with it. The errors and warnings are stored in the Message D2.
 
{@PERR} would be 'N' if the customer wants to print all accounts/meters regardless of if they have any errors/warnings or not.
 
I thought that if there was a field located in the D0 in the XML that was a Y if the account/meter had a message associated with it and a N if it didn't, it would be SOOOOOO easy and the formula would be:
 
and
{XMLNAME.D0_MESSAGECODE}='Y'
or
and
{XMLNAME.D0_MESSAGECODE}={XMLNAME.D0_MESSAGECODE}
But the programmer has informed me that the messages are not created until after the D1 is evaluated, so there can be no field without creating extreme amounts of work for the programmer.
 
I'm still wondering about this though and am keeping it on the back burner incase all else fails.
 
Sorry if it is a bit confusing, I am not the most eloquent of speakers.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2009 at 10:06am

Just so i understand, is the "{@PERR}" suppose to be a user selection criteria at report run time so they choose if they want to see all or only records?

or is this something that is generated by and stored in the DB?
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 22 May 2009 at 10:32am

You are correct. Technically {@PERR} is suppsoed to be {@PRTOPT6} which is Print Option 6. The way our reports work is, we have Select Options, Sort Options and Print Option. The user selects the records to include and how they want to sort them then choose how (or what) to print.

{@PERR} is a formula I created that is blank and basically just used to pull the value of that field from the XML into the report.

So yes, it is used by the user at report run time to choose what information they want to print.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2009 at 11:31am
Given my lack of knowledge using XML I may have to defer this Hilfy or Lockwelle, but since this is a user defined option at runtime it should be able to be used as a select criteria. I just do not know how it is being passed into the report. How I usually end up handling this is creating a parameter in Crystal report that allows the user to define some function and then use that parameter in the select statement. Not sure how to pass it from XML though...
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.