Crystal Reports and VS2003 - loop through dataset

I have a crystal report where I am passing the values through a dataset from a vb.net form.

My problem is I do not know how to display results when I have more than one value retruned.   An example would be I have 3 rows retruned in the dataset.   The Name, Address,SSN are the same on each line but the Diagnosis is different on each line.

I need to test and see if the Diagnosis in any of these 3 lines equals 96150 or 96152 if it does I can display an x.

I added a formula filed to the report but I will not get a match as I am not sure how to loop through the dataset in crystal report.

********example, I need to add looping so I can test all 3 lines in the dataset
if {tblLkpMHCPDiagnosis.DiagnosisID} = '96150' then
'X'

else

''

Thank you
running32Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajesh DalmiaCommented:
add a Group of DiagnosisID. Put your formula in the group header.


rdonline1
running32Author Commented:
Sorry I'm new to all this.  If I group the DiagnosisID  I will still get 3 rows as the DiagnosisID are different.  The form is layed out as below.   I need to put an X next to either one all or all of them depending on what results are returned so If I get back 96150, 96152 and 96153 in the Diagnosis filed I need to put and X next to each one.   Does this make sense, sorry for being so slow?  Thanks


    96150    H & B Assement   (Neg)
    96152    H & B Asssement (Pos)
    96153    Family Visit with patient
    96154    Family visit without patient  
Rajesh DalmiaCommented:
you have to write one formula as


If {DiagnosisID}  IN (96150,96151,96152) Then
"X"
else
""

then put this formula in your report. If will print X where DiagnosisID will be in a range of 96150 and 96152



rdonline1
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

running32Author Commented:
I agree, thanks, but my problem is that it will read what is in the first row of the dataset but not what is in the 2nd row.

How can I get the formula to loop through the rows in the dataset and test.  Thank you for sticking with me on this one. :-)

thanks
Rajesh DalmiaCommented:
CR can not read all values at the begining. Unless you loop through all the details it can't pick the values. You can put one Initilize formula at the report header and put another formula in the detail section which will check the values, depending on the checking do some variable updation and at the report footer print the variable or whatever you want.

Wen the CR is printing the 1st record, it can't read the 2 row value unless it loops through that record


rdonline1
frodomanCommented:
Hi running 32.  What rdonline just wrote is somewhat accurate but there is a way to do it.  Generally what you would do is create a subreport that reads through all of the records and at the end of them displays the X (or whatever) so that you'll actually have your subreport loopoing through all of the appropriate records and causing the display on your main report.

My question to you is where do you want to display this X?  Perhaps if you can type a quick illustration of what you want the report to look like?  With that I can give you more specific instructions if you need them.

frodoman
running32Author Commented:
Thank guys.

I start with a dataset that gives me the name, address and Diagnosis of a person.  I tied the dataset to the report and added the database fields to the report for name etc.  My problem is that when I add the {tblLkpMHCPDiagnosis.Diagnosis} field to the report it displays the first record.  What I need to do is test {tblLkpMHCPDiagnosis.Diagnosis} to see if it is equal to 96150 etc and put an x in the box.

Hope I am making sense.
Thanks


The form is set up

Name
Address
City                               State                    Zip
Income Rating

Diagnosis  1.
                2.
                3.


*** is where I would put the X if one of the rows returned matched.

   Health and Behavior Services
   ***     96150    H & B Assement   (Neg)
   ***     96152    H & B Asssement (Pos)
   ***     96153    Family Visit with patient
   ***     96154    Family visit without patient

frodomanCommented:
So on your report now, you have all of the diagnosis codes appearing in the Details section, correct?  Does the report contain grouping so that each patient / visit / whatever is grouped together - if not it will need to.

Create a formula in your group header:
 //@initformula
shared numberVar IsFound := 0;

Create a formula in your details section:
//@updateformula
if {tblLkpMHCPDiagnosis.Diagnosis} = 96150 or {tblLkpMHCPDiagnosis.Diagnosis} = 96151 or {tblLkpMHCPDiagnosis.Diagnosis}  = 96152 then
   shared numberVar IsFound := 1;

Create a formula in your group footer section:
if shared numberVar IsFound = 1 then
   'X'
else
   ' ';


That's all you need to simply display an X after reading all of the lines.  If you need to display the X above the group footer, then you'll need to get into a subreport but I'm avoiding that if it isn't required because it is much less efficient.

frodoman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
running32Author Commented:
On on my report I have just one diagnosis code appearing, which is the first record.  It is not displaying the 2nd Diagnosis id.  Only the 317 diagnosis is showing up.  I don't know how to display the 90853 as it is in the 2nd row of the dataset.   Thanks

my dataset looks like
Fname   LName   Street                City         Zip        Diagnosis
test       Person   123 test street    Test         11234   317
test       Person   123 test street    Test         11234   90853
frodomanCommented:
How is your report set up?  What section of the report do you have Diagnosis field in?
running32Author Commented:
I have the Diagnosis filed in my details section.
running32Author Commented:
I put the //@updateformula in the detail section next to the box 96150.  It shows 0.00 in the box not an X.  

Thanks again for your help.
frodomanCommented:
Sorry, I forgot that you should suppress the formula so it isn't visible.  The idea is that the formula in the group footer would actually display the X.
running32Author Commented:
the field is a varchar 12 in the sql database.  It is never get a match so  shared numberVar IsFound := 1;

the results show 96150 but it wants me to put a string '96150' in the query.

thanks
frodomanCommented:
You can convert from string to number or vice-versa using cdbl or cstr functions.
running32Author Commented:
Do you mean in cyrstal?
running32Author Commented:
I guess you did sorry, I'm loosing my mind over this problem.
running32Author Commented:
Thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.