Reports and grouping

[pacsinte]

Hello,

I have a table like this:

patid diag fee
----------------------
1 A 10
2 B 15
2 A 10
3 C 20
3 A 10
3 D 25
4 B 15

And i want my report to look like this:

ID Diag and Fee Total
----------------------------------------
1 A 10
10
----------------------------------------
2 B A 25
15 10
----------------------------------------
3 C A D 55
20 10 25
----------------------------------------
4 B 15
15
----------------------------------------

I'm grouping by patid but i still can't figure it out how to display horizontally the members of a group. A patid can have multiple diag, each diag having a corresponding fee.

Thanks,
Bobby

 

[craigsboyd]

look up cross-tab query in the help file


Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[jimstarr]

Hi Bobby,

I'd create a cursor containing 1 record per patient. The following code assumes a max of 3 diagnoses per patient.

USE mytable && in order by patid

CREATE CURSOR mycursor (id1 c(2), diag1 c(1), diag2 c(1), diag3 c(1), fee1 N(3), fee2 N(3), fee3 N(3))

previd = '99'

SELECT mytable
SCAN
IF patid != previd
SELECT mycursor
APPEND BLANK
i = 1
previd = mytable.patid
REPLACE id1 WITH mytable.patid
ENDIF
diagx = 'diag' + ALLTRIM(STR(i))
feex = 'fee' + ALLTRIM(STR(i))
REPLACE mycursor.&diagx WITH ;
mytable.diag, mycursor.&feex WITH mytable.fee
i = i + 1
ENDSCAN

Then the reporting is easy. Just use a lot of "Print when's" to avoid trying to print empty fields.

Jim

 

[Mike Lewis]

Bobby,

I agree with Slighthaze. The cross-tab wizard (from Tools / Wizards / All Wizards) will make light work of this. Just be sure to de-select 'Display null values' in the final step.

The wizard will generate a query program which you can run to create a cursor that can be used as the data source of the report. The report itself should then be trivial.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[pacsinte]

Many thanks guys,

Indeed the cross-tab seems to solve my problem though there is another question i want to ask you.
This is a daily report and there are days when i do not know what values i will use. There is no fixed limit and could be quite a lot of values in a single day.
For example A B C D
and the next day B C F R T Z Y W
I can construct the cursor dynamically by calling the query, but how im i supposed to use it in the report?
Also, cross-tab works fine for 3 fields but i have another 10 fields which shoud appear only once, as patid does (date, reason and so on). From what i have seen, i can only use 3 fields for the cross tab. I could join the cursor with another one which has the rest of the data by using patid. But i still cannot figure it out how to insert at runtime those new values for diag and fee in report horizontally along with the grouped members.

Thanks,
Bobby

 

[craigsboyd]

You will probably need to look up "CREATE REPORT Quick Report" in the VFP helpfile. This should give you some idea of how you can dynamically create a report based on a varying source table. It won't be very pretty, but it will be functional for your needs (and the users' needs) I think.

The second portion of your question regarding the 10 other fields does not make sense to me, could you explain it another way or maybe in more detail so I could understand? Thanks.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[Mike Lewis]

Bobby,

Are you saying that you need a way of creating a report, but you don't know at design time how many fields will appear in the cursor (and therefore how many columns the report is to contain)?

If so, let me know and I will try to find some of my code that will meet that requirement.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[R17]

hi mike,

i have the same problem as bobby,

i don't know at design time how many fields will appear in my report.

pls help.

 

[pacsinte]

Hi,

I am sorry i wasn't quite clear so i will try to expose the whole problem.
I have a table like the one shown below; this table represents a single day and is the result of a join. Diag values can vary greatly from one day to another, for example A B C D and the next day B C F R T Z Y W.
Every diag value has a correponding fee value (1-to-1 if i can say so).

Patid Name Date Reason Code Diag Fee
------------------------------------------------------------
1 John Doe 01/01/2000 yyy nnn A 10
2 Paul N. 01/01/2000 yyy nnn B 15
2 Paul N. 01/01/2000 yyy nnn C 20
3 Jessy P. 01/01/2000 yyy nnn A 10
4 John M. 01/01/2000 yyy nnn F 25
4 John M. 01/01/2000 yyy nnn G 30
4 John M. 01/01/2000 yyy nnn A 10
.......

Some days contain other patids and in other days the same and others patids (1,2,3,4, 11, 20) may have different diagnosis sets (A,T,X...). A patid may have a lot of diags in a specific day.
The report should look like this (for this single day from the previous example):

Name Date Reason Code Billing Total
-----------------------------------------------------------
John Doe 01/01/2000 yyy nnn A 10
10
-----------------------------------------------------------
Paul N. 01/01/2000 yyy nnn B C 35
15 20
-----------------------------------------------------------
Jessy P. 01/01/2000 yyy nnn A 10
10
-----------------------------------------------------------
John M. 01/01/2000 yyy nnn A F G 65
10 25 30
-----------------------------------------------------------

I can make a cross-tab cursor using a query with patid, diag and fee (only 3 fields i can use) and will result something like this:

Patid A B C F G
-------------------------
1 10
2 15 20
3 10
4 10 25 30

I cannot figure it out how am i supposed to use this cursor to generate the report showed previously dynamically, with
The next day, the cursor will look differntly (different no of columns and column names caused by different diags used that day).
I hope I was able to express myself clearly.

Thank you very much,
Bobby

 

[pacsinte]

The report wasn't formatted correctly. This is how it should appear. Each fee under the corresponding diag. Same for the cursor.

Name Date Reason Code Billing Total
---------------------------------------------------------------
John Doe 01/01/2000 yyy nnn A 10
10
---------------------------------------------------------------
Paul N. 01/01/2000 yyy nnn B C 35
15 20
---------------------------------------------------------------
Jessy P. 01/01/2000 yyy nnn A 10
10
---------------------------------------------------------------
John M. 01/01/2000 yyy nnn A F G 65
10 25 30
---------------------------------------------------------------