Technical Questions: Record Selection Help

Print Page | Close Window

Record Selection Help

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6438
Printed Date: 05 May 2024 at 11:30am

Topic: Record Selection Help

Posted By: FrnhtGLI
Subject: Record Selection Help
Date 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:

mailto:%7b@PERR%7d=Y - {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M'

in the Record Selection Formula Editor.

This seems to give me the D0 records I want

, but it only prints the D2 sections that have <DTLCODE>='M'

. I still need to print information from the D2 sections that have <DTLCODE>='D' though.

If I do something like:

mailto:%7b@PERR%7d=Y - {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M'

and {XMLNAME.D2_DTLCODE}='D'

then I get no information at all

Can someone PLEASE HELP?

Thanks in advance.

Replies:

Posted By: DBlank
Date 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.

( mailto:%7b@PERR%7d=Y - {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M')

or {XMLNAME.D2_DTLCODE}='D'

Posted By: FrnhtGLI
Date 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>

<D1>

</D1>

<D2>

<DTLCODE>D

</D2>

<D2>

<DTLCODE>D

</D2>

<D2>

<DTLCODE>M

</D2>

</D0>

but I may also have one that looks like this:

<D0>

<D1>

</D1>

<D2>

<DTLCODE>D

</D2>

<D2>

<DTLCODE>D

</D2>

</D0>

If the mailto:%7b@PERR - {@PERR } print option equals 'Y' then I do not want to include the second record set in the report. If mailto:%7b@PERR - {@PERR } print option equals 'N' then I want both record sets. I have a formula to include both record sets:

mailto:%7b@PERR%7d=N - {@PERR}='N'

and

{XMLNAME.D2_DTLCODE}={XMLNAME.D2_DTLCODE}

but I need something before the for when mailto:%7b@PERR%7d=Y - {@PERR}='Y' , then I will put an 'or' followed by the above code.

Any other help would be greatly appreciated.

Thanks in advance.

Posted By: DBlank
Date 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 mailto:%7b@PERR%7d=Y - {@PERR}='Y' then {XMLNAME.D2_DTLCODE}='M'

and {XMLNAME.D2_DTLCODE}='D'

else {XMLNAME.D2_DTLCODE}='D'

Posted By: FrnhtGLI
Date 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 all. Even if it's an If/Then. I'm not sure why it works out that way.

Posted By: DBlank
Date Posted: 22 May 2009 at 8:27am

Sorry was not paying attention. I really need more coffee

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 mailto:%7b@PERR - {@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 mailto:%7b@PERR%7d=Y - {@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.

Posted By: FrnhtGLI
Date 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.

mailto:%7b@PERR - {@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.

mailto:%7b@PERR - {@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:

mailto:%7b@PERR%7d=Y - {@PERR}='Y'

and

{XMLNAME.D0_MESSAGECODE}='Y'

mailto:%7b@PERR%7d=Y - {@PERR}='Y'

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.

Posted By: DBlank
Date Posted: 22 May 2009 at 10:06am

Just so i understand, is the " mailto:%7b@PERR - {@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?

Posted By: FrnhtGLI
Date Posted: 22 May 2009 at 10:32am

You are correct. Technically mailto:%7b@PERR - {@PERR } is suppsoed to be mailto:%7b@PRTOPT6 - {@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.

mailto:%7b@PERR - {@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.

Posted By: DBlank
Date 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...

Posted By: FrnhtGLI
Date Posted: 22 May 2009 at 12:13pm

I realize it should be a select criteria and not a print criteria but most of the time the users don't use the select criteria, so they use the print options to narrow down the choices

. Sound a little backwards? You would be right

The problem is we are really only creating templates for the information to be pulled into and cannot create Parameter fields as all the parameters are handled through the DataBase that we also create (My company that is). I have asked another Crystal developer that I work with who has been doing this longer and he seems a little stumped as well. I do appreciate your attempts though.

Posted By: DBlank
Date Posted: 22 May 2009 at 12:40pm

Just a thought, but you may be able to conditionally suppress data (rows)rather than exclude it using the "print option" formula as the condition.

May make any summarry data more difficult if you need that but could address your basic display issues.

Posted By: FrnhtGLI
Date Posted: 22 May 2009 at 12:47pm

I've also tried doing that as well, but I think the problem is since most of the information is in a Group Header and the D2 information is in the details section, the D2 information doesn't relate properly in suppressing the D0 information. Unless I'm just not doing something properly.

Do you think you could throw me a sample suppression formula?

Thanks.

Posted By: DBlank
Date Posted: 22 May 2009 at 1:05pm

I would have to see your set up to really help but here are some things to think about.

You can conditionally suppress fields or you can conditionally suppress sections (rows, headers, footers, etc). Sometimes you can do both by setting the section expert to suppress "blank sections" so when you suppress the fields if they all get suppressed that section also gets suppressed.

Also you may be running into issues about when to suppress the Gheader because it may contain rows that are changing based on the rest of the suppression. You can deal with this by using a formula field to "flag" a row a 1 or a 0 and then sum that on the group and conditionally suppres if the sum=0 (or >0).

For example you have 3 rows that may or may not be suppressed.

Create a formula to flag it based on your suppression critieria.

If fielda="this" and fieldb="that then 1 else 0 (generally a 1 inidcates not suppressed 0 indicates suppressed)

now the 3 rows have a 1 or a 0 on them. from there create a sum of that formula at your group level that you need to maybe suppress. Now your group header has 0 to 3.

You can conditionally suppress the header on that sum=0 (or >0 depending on your need). Sorry this is so abstract but I hope it helps give you some direction.

If you need help with it I would need to see some sample data in row format (I really have a hard time with XML codes) with the conditions that you want each part (row or headers) to be seen or not and I might be able to come up with a process for you.

Posted By: DBlank
Date Posted: 22 May 2009 at 1:11pm

Note that part of your row "flag" formula will include the user defined printing option.

Somthing like;

if mailto:%7b@PERR%7d=Y - {@PERR}='Y' and {XMLNAME.D2_DTLCODE}='M' then 1 else 0

Posted By: FrnhtGLI
Date Posted: 26 May 2009 at 11:48am

Okay, so I learned something today after speaking with the programmer who designed the XML, and it is a great lesson learned.

'A Print Option is always a Print Option except for when it's a Select Option.'

There apparently was a miscommunication over how the mailto:%7b@PERR - {@PERR } print option acts.

Normally, the user uses the Select Options to choose records to include on the report (this reduces the size of the XML). Then selectes Print Options to either A) Print Certain fields on the report or B) further narrow down the records on the report (this typically does not reduce the size of the XML and is something that I must handle through Crystal).

For this particular report, the Print Option mailto:%7b@PERR - {@PERR } actually acts as a Select Option in which it removes records from the XML, reducing it's size. I did not need to do anything for this since it is already handled. So I spent all day Friday and the majority of today trying to do something that didn't need to be done, and in all likelyhood, I wouldn't have even been able to do with the XML set up as it was anyways.

And while I will never get back the hours I spent trying to do some unnecessary task, I am extremely happy that my report runs properly and that I am finished with it.

Thanks for all the help DBLank.

Print Page | Close Window