Print Page | Close Window

Assistance in creation of report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9614
Printed Date: 05 May 2024 at 2:14am


Topic: Assistance in creation of report
Posted By: jordanofmn
Subject: Assistance in creation of report
Date Posted: 06 Apr 2010 at 7:37am
I have a ctree database I am working with in the health care field. I am trying to create a report based on a condition, which I do not want to appear on the report (ie. use the presence of the condition as a filter), and then return values from other tables to the report. Possible? Thankx


Replies:
Posted By: kevlray
Date Posted: 06 Apr 2010 at 9:07am
Yes you should be able to go into the select expert and filter on a condition.

Posted By: jordanofmn
Date Posted: 06 Apr 2010 at 9:27am

I am able to find the value in the select expert, but what I would like to do is to have other selected values returned to the report if the first option is true, but I can not figure out the right formula 

If {tableA.field} = "XXX" then {tableB.field} and {tableC.field) 

Posted By: kevlray
Date Posted: 06 Apr 2010 at 10:51am

I think I understand what you are trying to accomplish, but unless the tables are linked, there would be no way to determine what row(s) to display from tableb and tablec.   If they are linked then the normal record selection should filter the records properly.

Posted By: jordanofmn
Date Posted: 07 Apr 2010 at 6:18am
The tables can be linked by and id field. I think my issue comes down to the 'and' / 'or' arguements. I am trying to select only records that have a certian condition, and then return a number of other records based on the condition. When I use the select expert it is returning all records if only one of the varibles is present, not just the key variable. The If...then..else statement does not work for me, as it wants a boolean value.
The select expert does not have a 'last date' option is there a way to set up a filter to select the last dated record.
Thanks in advance of any insight,
Cheers 

Posted By: kevlray
Date Posted: 07 Apr 2010 at 6:36am
Unfortunately I am not understanding entirely what you are trying to accomplish.  As far as the 'last date' issue.  Sometimes I can group by the date, and not show the group header or details (just showing the group footer).   I do not know if this will work for you.

Posted By: jordanofmn
Date Posted: 08 Apr 2010 at 6:03am
Sorry for the confusion. What I am trying to do is to return a set of data with a certian condition met, and with that condition present, a sub set of data returned on the report. For example, out of the the database I want to pick up only the clients that have had a bought a red car, AND for only those clients return the type of radio, engine size, number of doors....
I can not get the IF part to work.
Again, thanks in advance

Posted By: DBlank
Date Posted: 08 Apr 2010 at 6:38am
You do not need the IF. You just choose what fields to display (e.g. type of radio, engine size, number of doors.... ) by placing them on the report canvas. Just because your query returns the field does not mean you have to display it. Same goes for using the field in the select statement. YOu can use it to filter without displaying it.

Posted By: DBlank
Date Posted: 08 Apr 2010 at 6:39am
using your example you just say in the select statement
Table.car="Red"
then display the clients and the engine size, type of radio etc.

Posted By: jordanofmn
Date Posted: 08 Apr 2010 at 7:06am
But when I do a select on "RED" I get all records return, but when I try and do a select on "RED" and "SAT Radio" I get no records returned. But If select "SAT Radio" I get record returned. I guess I am trying to do 'anding' and it isn't working

Posted By: DBlank
Date Posted: 08 Apr 2010 at 7:20am

Rereading all of this you are likely running into a problem with the MAX date on your main table.

Can you give more exact examples of your data your links and and your conditions?

My guess is you have something like multiple rows of a diagnosis with different dates and you need to link this table using a the most current diagnosis. This gets tricky to do in crystal. If you give more concrete examples (change the actual data if you need to for security issues) someone will ilkely e able to better answer your question.

Posted By: jordanofmn
Date Posted: 08 Apr 2010 at 7:46am

For Example - What I am attempting to do is pull all patients diagnosed with Diabetes and then return the latest blood work test results; XXX, ZZZ. All of these are kept in the same table, so my sorting expert is catching all of the patients that have an XXX result, but is not diagnosed Diabetic.


Posted By: kevlray
Date Posted: 08 Apr 2010 at 8:01am
Maybe what you need is to be able to select 'one of' (or one of the other possibilities).  That makes the selection more like an 'OR' instead of a 'AND'.

Posted By: jordanofmn
Date Posted: 08 Apr 2010 at 8:17am

When I select 'one of' I get all results, regaurdless of being diagnosed as Diabetic. What i need is 'Is Exactly like' AND 'one of', I think. My head is starting to hurt!!!

Posted By: DBlank
Date Posted: 08 Apr 2010 at 8:24am

Guessing on your structure here but inner join the patient table to the diagnosis table and the outer join the diagnosis table to the blood work table.

In the select expert make diagnosis=Diabetes (or ICD code if you use it)
In the report group on patient ID
Add an extra group header (B)
sort descending on blood work date
place the desired result fields for display on the group header B
these should be from the most recent blood work for that patient
 
They will be NULL if the patient has no blood work reported

Posted By: jordanofmn
Date Posted: 12 Apr 2010 at 5:59am

Am I able to create a sub set of data (ie All Diabetics) and then do a sub report pulling all info based that subset?

Posted By: DBlank
Date Posted: 13 Apr 2010 at 3:45am
Theoretically yes you could do a mian report that limits your pateint set to only those with diabetes and then run a sub report per patient to get the blood work data.


Print Page | Close Window