Join and Subreport Issue 1

[zemp]

CR 10
SQL Server 2000 SP4

I have a report on which I need to placew a subreport. I have a SQL statement that gets the information that I need.

SELECT  GR.GrpID, BE.BenfID, BE.BenfName, BE.BenfOrder, udCarrier.CarrName AS PresentCarrier, 
	GR.TeminateDate, udCarrier_1.CarrName AS PreviousCarrier, GR.EffectiveDate, 
	GR.RenewalDate, GR.PolicyNum, GR.StopLossLimit, GR.Manditory, 
	GR.Inclusions, GR.Exclusions, GR.SpecialAuthOnly, GR.Notes
FROM maGroupBenfLine as GR LEFT OUTER JOIN
	udCarrier udCarrier_1 ON GR.Pres_CarrID = udCarrier_1.CarrID LEFT OUTER JOIN
	udCarrier ON GR.Prev_CarrID = udCarrier.CarrID RIGHT OUTER JOIN
	ofBenefit AS BE ON GR.BenfID = BE.BenfID AND GR.GrpID = 53
ORDER BY BE.BenfOrder

However the problem is that I need to link the GrpID to the main report (Not an issue) so therefore I need to remove the "AND GR.GrpID = 53" part of the query and pass the group ID as a parameter. That is just not working.

Here is the senario. A client (GrpID) selects benefits offered by the compnay. The company has 20 different benefits and any client never selects all 20. The report must show all 20 benefits available and then which ones the client has selected. Therefore the subreport should always return 20 rows, some of which will have a lot of null values.

I have tried the SQL above as a view and I have tried adding the tables to the report.

I can't get the subreport to return more than the number of selected benefits. How can I acomplish what I need to. All suggestions are welcome.



zemp

 

[lbass]

Not sure what you want the final report to look like, but I think the main report should contain the client info. Link the subreport only on the client ID, but in the linking expert, uncheck "choose data based on this field" for the subreport. Place the subreport in a group section based on client ID.

Then in the subreport, set up a left join FROM the benefits table to the table containing the client info. Then create a formula to identify selected benefits per client:

if {table.clientID} = {?pm-table.clientID} then "Yes"

This will show all benefits for each client, with a "Yes" next to the ones they have selected.

-LB

 

[zemp]

Just to clarify. This is the result I get.

BenfID Benefit ClientSelected
-----------------------------------
4 Dental Yes
7 Vision Yes
10 LTD Yes

What I need is

BenfID Benefit ClientSelected
-----------------------------------
1 Drugs No or Null
2 Ext Heal null
3 Medical null
4 Dental Yes
5 Hospital null
6 Travel null
7 Vision Yes
8 Life null
9 AD & D null
10 LTD Yes
11 STD null
...

Your suggestions gave the same result as I have been getting. I am not sure if I placed your final formula in the correct place. I went to the menu 'Report - Selection formula - record' and placed the formula there.

I will try the formula in other places.


zemp

 

[lbass]

No, the point is that you cannot use record selection in the subreport. The formula is meant to be placed in the detail section of the subreport.

-LB

 

[zemp]

When the formula is placed in the suppress section of the details section of the subreport I just get the benefits selected by the client and any benefits that have not been yet selected by any client (GrpID=null). I have the following formula
'{maGroupBenfLine.GrpID}<>{?Pm-maGroup.GrpID}'

Removing the formula brings in hundreds of records, basicall all benefits selected by all clients.


zemp

 

[lbass]

No, it should not go in the suppression section either. Create the formula in the formula expert (field explorer->formula->new and then drag it into the details section of the subreport.

If you want to show ALL benefits for EACH customer, then yes, the subreport will bring in the records for all customers, but only display the "yes" for the particular customer. Show only those two fields--benefits and "yes" formula and then go to database->select distinct. Make sure the subreport is sorted by the benefits field asc, and {@yes} descending. Also select the benefits field->format field->common->check suppress if duplicated.

-LB

 

[zemp]

lbass: I'm sorry, I can't get it to work. I tried all your suggestions and still come up with only the benefits selected, and not all 20 of them, with the selected ones flagged. I probably was unable to follow or grasp your train of thought. I still learned more about Crystal, thank you very much for that.

However, while watching my son and daughter on the soccer pitch last night I had a thought about using variables. After some trial and error I did get the report to display the information I need in the format requested by my client.

Here is what I did.
1) I created a variable for each of the 20 Benefits and set it to default value and placed them in the report header section of the subreport.

whileprintingrecords;
numbervar Benf01;
Benf01 :=0

2) Using the view that returns only the selected benefits I created a formula to check the benefit and update the fore mentioned variables. Placed in the details section of the subreport. Below I only show the first 2 of 20 cases.

select {MA_Benefits.BenfOrder}
    case 1 : (whileprintingrecords;
             numbervar Benf01:=1;
             if Benf01=1 then true else false)
    case 2 : (whileprintingrecords;
             numbervar Benf02:=1;
             if Benf02=1 then true else false)
...

3) I created a formula to show the variable in 1).

whileprintingrecords;
numbervar Benf01;

4) Hard coded the benefits into the report footer section of the subreport and used the following formula to display a check box (checked or not) to show the selected benefits. The formula for the check box uses the formula to show the variable created in 3). The font of the formula in 4) is set to Wingdings (The formula below and the use of the check box I also got from this forum a while back, can't remember the thread or the poster to properly credit them).

if {@Show 01}=1 then 
    chr(254)
else 
    chr(168)

The end result is a list of all the benefits offered with a check box in front of each. The formulas will determine if the box is checked or unchecked. The benefits the client has selected are shown as checked the other benefits as unchecked.

Bottom line I got my client what they wanted.

zemp