Can I get one record to appear in 2 groups? 1

[azoe]

Crystal 11

I have 2 name fields(name1 and name2) and one dollar amount in each record. I want to group on name1 and multiply the dollar amount by a percent and I would like that record to appear again in another group so I can multiply the dollar amount by a different percent.

Then I will add the first figure to name1's total and add the 2nd figure to name2's total.

I tried to do that in my formula but it ignores the 2nd time I try to get the record. I'm guessing because it already sorted that record in name1's group.

I used a stored procedure with an ODBC connection to get the data.

The formula is below. I get a group for '2Refer70' but not for '2Render30'.

Thanks -

If
(isNull({dch_MonthlyPmtsWithSIMandDept;1.Rendering})
or
{@EvenSplitCalc1} = True)
Then '4Even Split'

Else If {@Refer70percentCondition} = True
Then '2Refer70'

Else If {@Render30percentCondition} = True
Then '2Render30'

Else If
{@RenderingCondition} = True
Then '1Rendering'

Else If {@ReferringCondition} = True
Then '3Referring'

 

[synapsevampire]

The short answer is no, you can't use a row twice. However you might use a subreport to perform something acceptable.

My approach would be to correct the data.

If you want the row twice in the recordset, then the SP should be placing the row in twice in the recordset.

If your database programmer is uncertain of how to do this, suggest that they use a UNION ALL to handle it.

so if the SP says:

select '2Refer70' type, field1, field2 from table where table.field = '2Refer70'
union all
select '2Render30' type, field1, field2 from table where table.field = '2Render30'

Now you can get the 2 rows and group accordingly.

If this fails, try posting 3example data and expected output (show what sections within CR where fields will be displayed).

-k

 

[azoe]

I chose to add the records to the sp with the Union All.

Is the next step the subreport - or was that a separate option?

I see the records I added are now duplicated in the 2Refer70 group so I'm not sure how to separate them out to the other group.

Thank you -

 

[synapsevampire]

A seperate option, now that you have the row in different groups it should be fine.

Why are they duped in 2Refer70?

that wasn't what I had suggested.

-k

 

[azoe]

I don't have fields in the database called 2Refer70.
The Rendering is called Rendering and the referring is referring so when I get the records again, they are in the dataset 2 times. I have my original select statement 2 times with the Union All in the middle and a more restrictive Where clause on the second one so I only get the extra records I need to do the calculations.

I knew how to do the Union but there must be something else I'm missing. Was I supposed to rename a field or something?

In the original query result I had:

Render Refer Amount Department Code
Name1 Name2 140.00 DeptA 115
Name2 Name3 50.00 DeptC 80

Now I have:

Render Refer Amount Department Code
Name1 Name2 140.00 DeptA 115
Name1 Name2 140.00 DeptA 115
Name2 Name3 50.00 DeptC 80
Name2 Name3 50.00 DeptC 80
Name3 Name4 110.00 DeptZ 99

Thanks

 

[synapsevampire]

Post your SQL.

Note that mine qualifies each row, check it more carefully:
select '2Refer70' type, field1, field2 from table where table.field = '2Refer70'
union all
select '2Render30' type, field1, field2 from table where table.field = '2Render30'

-k

 

[azoe]

Sorry - I see what you wrote but I didn't understand where to get the '2Refer70' type. That is a group name I put in Crystal (it does not exist in the database) and it is based on conditions such as the referring doctor works here (as opposed to an outside doc) and a certain list of procedure codes.

This is a prepackaged database so I can't add any fields.

Here's how it's grouped in Crystal:
Dr. Name
1Rendering100%
Department
records here (where rendering gets 100%)
2Refer70
Department
records here (where referring gets 70%)
3Render30
Department
records here (where Rendering gets 30%)
4Referring
Department
records here (where referring gets 100%)

Here's the sp:
ALTER Procedure dch_MonthlyPmtsWithSIMandDept
As

SELECT
v.visit_id as Visit,
pm.desc_30 as Rendering,
(
SELECT [desc_30]
FROM [Provider_mstr]
WHERE [Provider_ID] = [V].[Refer_Phys_ID]
) AS [Referring],

ChargeSIM.Service_Item_ID as SIM,
ChargeSIM.Service_Item_Desc_40 as SIMDesc,
t.type as Type,
--t.trans_id as TranID,
td.paid_amt as PaidAmt,
td.adj_amt as AdjRef,
--chargesim.department,
mlist.desc_30 as department,
t.closing_date as TranClosingDT,
--fm.desc_40 as facility,
p.claim_type as ClaimType,
p.payer_name as PayerName


FROM

trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join visits v on t.source_id = v.visit_id
left outer join payer_mstr p on t.payer_id = p.payer_id

left outer join (
SELECT [Charges].[Charge_ID],
[Charges].[amt],
[Charges].[closing_date],
[Charges].[Rendering_ID],
[Charges].[Service_Item_ID],
Charges.Service_Item_Desc_40,
(
SELECT TOP 1 [Department] /*get Department the first time for any SIM - otherwise it is repeated for price changes */
FROM [Service_Item_mstr] AS [Services] /*subselect to get SIM only once - in there several times because price changes*/
WHERE [Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
) AS [Department]
FROM [dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_alias
left outer join facility_mstr fm on v.facility_id = fm.facility_id


where
t.closing_date between '20050402' and '20050502'
and t.type <> 'A'
--and (pm.desc_30 like @ProviderDescription)


Union ALL --I'm adding this 5-23-05 to get the render 30% records again because in Crystal you can only use
--each record once. I want to use one to calc 70% for referring and one to calc 30% for rendering. srb

SELECT
v.visit_id as Visit,
pm.desc_30 as Rendering,
(
SELECT [desc_30]
FROM [Provider_mstr]
WHERE [Provider_ID] = [V].[Refer_Phys_ID]
) AS [Referring],

ChargeSIM.Service_Item_ID as SIM,
ChargeSIM.Service_Item_Desc_40 as SIMDesc,
t.type as Type,
--t.trans_id as TranID,
td.paid_amt as PaidAmt,
td.adj_amt as AdjRef,
--chargesim.department,
mlist.desc_30 as department,
t.closing_date as TranClosingDT,
--fm.desc_40 as facility,
p.claim_type as ClaimType,
p.payer_name as PayerName


FROM

trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join visits v on t.source_id = v.visit_id
left outer join payer_mstr p on t.payer_id = p.payer_id

left outer join (
SELECT [Charges].[Charge_ID],
[Charges].[amt],
[Charges].[closing_date],
[Charges].[Rendering_ID],
[Charges].[Service_Item_ID],
Charges.Service_Item_Desc_40,
(
SELECT TOP 1 [Department] /*get Department the first time for any SIM - otherwise it is repeated for price changes */
FROM [Service_Item_mstr] AS [Services] /*subselect to get SIM only once - in there several times because price changes*/
WHERE [Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
) AS [Department]
FROM [dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_alias
left outer join facility_mstr fm on v.facility_id = fm.facility_id


where
t.closing_date between '20050402' and '20050502'
and t.type <> 'A'
and ChargeSIM.Service_Item_ID in ('93307', '93000', '93224', '95810', '95806', '95805', '95811', '94240', '94060', '94010')
and V.Refer_Phys_ID is not null and v.refer_phys_id in ('PH14K14SJT6J', 'PH14K14TQHOW', 'PH14K14V0S7Q', 'PH14K222MZC1', 'PH14K33MGV8O', 'PH14K33N1710', 'PH14K33NF901', 'PH14K33NMZ7Z', 'PH14K33O8FPY', 'PH14K341CH4K', 'PH14K3468F6T', 'PH14K346MHAU', 'PH14K347KY51', 'PH14K348BY2R', 'PH14K348NTQW', 'PH14K349B8H8', 'PH14K349N55K', 'PH14K34A8H0Z', 'PH14K34AJOAV', 'PH14K34B482B', 'PH14K34G1M24', 'PH14K34GF378', 'PH14M93W94IT', 'PH14M93X3WGE', 'PH14M93XKS4S', 'PH14M93WMVK1', 'PH14P92CJX9Z', 'PH14M93WNQ4P', 'PH150P3F9UQX', 'PH14P92CBQEI', 'PH14M93WMCFP', 'PH14M93WPHEZ', 'PH14M93WQ18V', 'PH14M93WJZKA', 'PH14M93YP6E2', 'PH14M93XKA3Q', 'PH14M93WGRN3', 'PH14M93WEJN3', 'PH14M93WF5BX', 'PH150P3FH5JC')
and pm.desc_30 is not null and pm.desc_30 in ('40 doctor names listed here')


Thank you -

 

[synapsevampire]

Why is your SP duplicating this:

pm.desc_30 as Rendering,

That wasn't my example, and note the Where clause for each side of the union demonstrated that each would qualify rows returned.

If you understand what I posted, than you should be rewriting your SQL, not posting it in it's current state.

Each side of the query will pull rows that meet the criteria for each grouping. This does NOT add fields to the database, but it does supply a new field to crystal which shows Rendering as the field name, so if you group on this, you have the rows that you want.

-k

 

[azoe]

So the specific Where clause I added to include only the records I want I should reverse that on the other side of the union to exclude them. I guess that is what you are saying.

I can do that. But I still need one record two times. In Crystal one will get grouped with 2Refer70 because I want to do a calculation on the referring physician. I need that same record again in the Render30 group so I can do a separate calculation on the rendering physician.

But when Crystal sees that I want to group one of our rendering physicians together it takes both those records.

You say "each side of the query will pull rows that meet the criteria for each grouping" but because I have to make sure the referring is one of ours it grabs all the records where the referring is one of ours. But I really only wanted one in that group and I wanted the second one in the rendering group.

I think the problem is that I'm not making myself clear as to what I want but I'm not sure how to do that.

I need this in one group:

record# render refer amt dept code
1 Name1 Name2 140.00 DeptA 110
So I can multiply 140.00 by .7 for Name2

I need the same record in another group:
record# render refer amt dept code
1 Name1 Name2 140.00 DeptA 110
So I can multiply 140.00 by .3 for Name1

No matter how I pull the records the way I'm grouping (that formula is repeated below) it pulls both those records into whichever of these two groups I list first in the formula: 2Refer70 or 2Render30


If
(isNull({dch_MonthlyPmtsWithSIMandDept;1.Rendering})
or
{@EvenSplitCalc1} = True)
Then '4Even Split'

Else If {@Refer70percentCondition} = True
Then '2Refer70'

Else If {@Render30percentCondition} = True
Then '2Render30'

Else If
{@RenderingCondition} = True
Then '1Rendering'

Else If {@ReferringCondition} = True
Then '3Referring'

Thanks -

 

[synapsevampire]

you're right, it's unclear.

First "two groups I list first in the formula: 2Refer70 or 2Render30" are not GROUPS!

These are now a single field in the SP which has duplicates identified by the TYPE field (in my example), so group on this field.

To clarify requiremnents. don't post what you don't want, not what you already have, post tchnical information, as in:

Example data (show fields in the database as you want them)
Expected output (as a result of the example data)

When describing the example data, don't state that a row will appear in 2 different groups, that's not how SQL nor Crystal works, state that you want a duplicate of that row to appear in each group, and state the fields that are the groups.

I think that you're trying to wrap your head around this PRIOR to trying what is suggested, and you are embellishing (inocrrectly) instead of following the prinicpals shown.

Try it again, and keep in mind that you're going to 1st group by the field we create, and that the way to resolve is to create duplicate rows in the recordset.

As for your grouping formula in Crystal, trash it, that should be the logic used by the SP.

-k

 

[azoe]

You said "don't state that a row will appear in 2 different groups, that's not how SQL nor Crystal works, state that you WANT a duplicate of that row to appear in each group."

I used the word NEED, isn't that the same?

"I need this in one group"
"I need the same record in another group"

I know it is hard to have patience with someone less experienced. I am trying though. I did re-do my sp.

I will try again. I'm not sure I know how to "create" a field in SQL but I'll look it up.

 

[synapsevampire]

Exactly as I gave the example for creating a field:

select 'Some data' Afield, field1 from table

Will return a field called Afield containing the text 'some data'

This is how you create the group by within the UNION query.

Again, look at my example and note the single quotes. Don't assume or change anything, except for your field names and criteria, follow the syntax I used, it's generic to most databases.

-k

-k

 

[azoe]

Ok, I see how to create the field.

So is it ok if one side of the union gets all the records and I create a field that populates with a 70 (so I remember those are the ones that need to be multiplied by .7), and then the other side of the union gets the second set of records and I populate a field there with 30 (multiplying by .3)?

Then you are saying I put a group by on each side of the union? I'm just trying to be sure because I already have so many groups in my report I guess I'll have to take out.
Thanks

 

[synapsevampire]

Sure, it sounds like you're on it now!

As for groups in your report, I have no way of knowing what you have in there, but yes, you're MUCH better served to perform grouping on the server, and you can do so within each select statement, and even do so afterwards on the whole returned data set if need be.

-k