Question on Group Footer 2

[tnguyen315]

I have created a report showing all vaccine in group footer showing the sum of each kind of vaccine with parameters [BeginDate] and [EndDate] run by each month with total 26 vaccine in the list.

For instance the list for 01/01/10 to 01/31/10


Vaccine Total
HepA 15
HepB 25
Dtap 20
Dt 15
HIP 10
MMR 20
etc...to all 26 vaccine...

Is there anyway to show all the list of vaccine by order on the report each month we run the report even those that vaccine is not in database in that month but still want to show on the report with total = 0.

I got the report working to show in order however if in that month there is one vaccine not in the database then it's take off the list...

Sample: if during feb 2/1/10 --2/28/10 there is no vac HepA and Dtap then the result would be:


Vaccine Total

HepB 25
Dt 15
HIP 10
MMR 20
etc...to all 26 vaccine...

but I want to show something like:


Vaccine Total
HepA 0
HepB 25
Dtap 0
Dt 15
HIP 10
MMR 20
etc...to all 26 vaccine...


Is that possible?
Thanks for your help...

 

[briangriffin]

This may be as simple as changing the join from the vaccine table to left outer, instead of inner.

If you're not joining to a vaccine table, or if there is not one, you could create a command object that just returns all 26 vaccine names (and possibly the ID, if that is what is required to join to the results table). Then do a left outer join of the command object to your database table and you should be good to go.

Make sure the vaccine name field in your report is the one from the command object, not the results table.

You would also need to modify your total formula to accomodate the nulls being returned (and display "0" instead of null).

If this doesn't work, please describe the tables and joins currently being used.

 

[tnguyen315]

Thanks for your response.
I created some formulas called:
Record selection formula:

{table.IMM_DATE} >= dateadd("m",-2,{?Beg Date}) and
{table.IMM_DATE} <= {?End Date} and
{table.VACCINE_CODE} in ["ZOS", "HAB", "HepB", "HAV", "Var", "Tdap", "Td", "RTV2", "RTV", "PPCV", "PCV7", "MMR", "MCV4", "IPV", "FLUP", "FLUN", "FLU", "HPV", "HibB", "Hib", "HpBP", "HepA", "DTPP", "DTHI", "110", "DTaP", "DT"]


Formula fields:

@Total-Doses:
If ({talbe.IMM_DATE} >= {?Beg Date} and
{talbe.IMM_DATE} <= {?End Date}) and
({talbe.IMM_DATE}-{table1.DOB})/365.25 > 0 then 1
else 0


@PreviousTotalDoses:
If ({talbe.IMM_DATE} >= date(year(dateadd("m", -1, {?Beg Date})), month(dateadd("m", -1, {?Beg Date})), 01) and
{talbe.IMM_DATE} <= date(year({?End Date}), month({?End Date}), 01)-1) and
({talbe.IMM_DATE}-{talbe1.DOB})/365.25 > 0 then 1
else 0


@ImmuOrderSort:

if {table.VACCINE_CODE} = "DT" then "01-" else
if {table.VACCINE_CODE} = "DTaP" then "02-" else
if {table.VACCINE_CODE} = "110" then "03-" else
if {table.VACCINE_CODE} = "DTHI" then "04-" else
if {table.VACCINE_CODE} = "DTPP" then "05-" else
if {table.VACCINE_CODE} = "HepA" then "06-" else
if {table.VACCINE_CODE} = "HpBP" then "07-" else
if {table.VACCINE_CODE} = "Hib" then "08-" else
and so on to 26...

@ImmuPrintGroup:

if {table.VACCINE_CODE} = "DT" then "DT" else
if {table.VACCINE_CODE} = "DTaP" then "DTaP" else
if {table.VACCINE_CODE} = "110" then "DTaP-HepB-IPV (Pediarix)" else
if {table.VACCINE_CODE} = "DTHI" then "DTaP-IPV-Hib (Pentacel)" else
if {table.VACCINE_CODE} = "DTPP" then "DTaP-IPV (Kinrix)" else
if {table.VACCINE_CODE} = "HepA" then "Hep A ped/adolescent" else
if {table.VACCINE_CODE} = "HpBP" then "Hep B ped/adolescent" else
if {table.VACCINE_CODE} = "Hib" then "Hib" else
and so on to 26.

@ImmuRptGroup:

{@ImmOrderSort} + {@ImmPrintGroup}

On my report design:

Group Footer # 1: @ImmuRptGroup order Asc.

has 3 columns:

@ImmuPrintGroup Sum of @Total-Doses Sum of @PreviousTotalDoses

I tried to use Left Outer Join, Right Outer Join or even Full Outer Join...None is worked....


Any ideas?
Thanks for your help

 

[lbass]

Except that with this method, tnguyen could not use selection criteria on the database table or it would "undo" the join.

You could use a command as your datasource and use a union all to ensure there is a always a record for a particular vaccine:

select table.vaccine, table.date, table.qty
from table
where table.date >= {?Start} and
table.date < {?End} + 1
union all
select table.vaccine, null, null
from table

In the main report, create a formula:

if isnull({command.qty}) then
0 else
{command.qty}

Use this formula as your summary field in the crosstab, with vaccine as the row, and date (on change of month) as the column field. On the customize style tab, check "suppress empty columns".

-LB

 

[tnguyen315]

Thanks lbass..however i'm so familar with the command object...then don't know how to use it.

 

[tnguyen315]

Is there any other way?
Thanks for your help

 

[tnguyen315]

Corrected: Thanks lbass..however i'm NOT so familar with the command object...then don't know how to use it.

 

[briangriffin]

Can you please provide all the table names used in the report?

 

[lbass]

You just enter the command in the "Add command" area above your table list in the database expert, and you would then use this as the datasource for your entire report--but you'd have to tailor it to your fields/tables, etc. You could just copy your current SQL query and paste it in and then add the union all and the second part of the union all statement.

Another alternative is to use running totals--one for each vaccine per month and place them in the report footer. You would do sum of qty, evaluate using a formula, e.g.,:

{table.vaccine} = "xxx" and
month({table.date}) = 1 and
year({table.date}) = 2010

Reset never.

-LB

 

[tnguyen315]

immune: list of vaccine and description
imfile: to get DOB
mpfile: main table show all doses administered during the month

 

[tnguyen315]

Thanks lbass, I'll try it...

 

[tnguyen315]

LBass: If I run for march 03/01/10 - 03/31/10 then this is my SQl query, can you help me to modify to create a command object?
Thanks in advance

SELECT "IMFILE"."IMM_DATE", "IMFILE"."VFC_CODE", "MPFILE"."DOB", "TBIMMUNE"."DESCRIPTION", "IMFILE"."VACCINE_CODE", "IMFILE"."PATIENT_NO"
FROM ("INSIGHT"."dbo"."IMFILE" "IMFILE" INNER JOIN "INSIGHT"."dbo"."MPFILE" "MPFILE" ON "IMFILE"."PATIENT_NO"="MPFILE"."PATIENT_NO") INNER JOIN "INSIGHT"."dbo"."TBIMMUNE" "TBIMMUNE" ON "IMFILE"."VACCINE_CODE"="TBIMMUNE"."VACCINE_CODE"
WHERE ("IMFILE"."IMM_DATE">={ts '2010-01-01 00:00:00'} AND "IMFILE"."IMM_DATE"<{ts '2010-04-01 00:00:00'}) AND ("IMFILE"."VACCINE_CODE"='110' OR "IMFILE"."VACCINE_CODE"='DT' OR "IMFILE"."VACCINE_CODE"='DTaP' OR "IMFILE"."VACCINE_CODE"='DTHI' OR "IMFILE"."VACCINE_CODE"='DTPP' OR "IMFILE"."VACCINE_CODE"='FLU' OR "IMFILE"."VACCINE_CODE"='FLUN' OR "IMFILE"."VACCINE_CODE"='FLUP' OR "IMFILE"."VACCINE_CODE"='HAB' OR "IMFILE"."VACCINE_CODE"='HAV' OR "IMFILE"."VACCINE_CODE"='HepA' OR "IMFILE"."VACCINE_CODE"='HepB' OR "IMFILE"."VACCINE_CODE"='Hib' OR "IMFILE"."VACCINE_CODE"='HibB' OR "IMFILE"."VACCINE_CODE"='HpBP' OR "IMFILE"."VACCINE_CODE"='HPV' OR "IMFILE"."VACCINE_CODE"='IPV' OR "IMFILE"."VACCINE_CODE"='MCV4' OR "IMFILE"."VACCINE_CODE"='MMR' OR "IMFILE"."VACCINE_CODE"='PCV7' OR "IMFILE"."VACCINE_CODE"='PPCV' OR "IMFILE"."VACCINE_CODE"='RTV' OR "IMFILE"."VACCINE_CODE"='RTV2' OR "IMFILE"."VACCINE_CODE"='Td' OR "IMFILE"."VACCINE_CODE"='Tdap' OR "IMFILE"."VACCINE_CODE"='Var' OR "IMFILE"."VACCINE_CODE"='ZOS')

 

[lbass]

Before going any further, if you go to database->database expert->your datasource, do you see "Add command" above your table list?

-LB

 

[tnguyen315]

Yes, I do

 

[lbass]

SELECT "IMFILE"."IMM_DATE",
"IMFILE"."VFC_CODE",
"MPFILE"."DOB",
"TBIMMUNE"."DESCRIPTION",
"IMFILE"."VACCINE_CODE",
"IMFILE"."PATIENT_NO"

FROM ("INSIGHT"."dbo"."IMFILE" "IMFILE"

INNER JOIN "INSIGHT"."dbo"."MPFILE" "MPFILE" ON "IMFILE"."PATIENT_NO"="MPFILE"."PATIENT_NO")

INNER JOIN "INSIGHT"."dbo"."TBIMMUNE" "TBIMMUNE" ON "IMFILE"."VACCINE_CODE"="TBIMMUNE"."VACCINE_CODE"

WHERE (
"IMFILE"."IMM_DATE">= {?StartDate} AND
"IMFILE"."IMM_DATE"< {?EndDate}
) AND ("IMFILE"."VACCINE_CODE"='110' OR "IMFILE"."VACCINE_CODE"='DT' OR "IMFILE"."VACCINE_CODE"='DTaP' OR "IMFILE"."VACCINE_CODE"='DTHI' OR "IMFILE"."VACCINE_CODE"='DTPP' OR "IMFILE"."VACCINE_CODE"='FLU' OR "IMFILE"."VACCINE_CODE"='FLUN' OR "IMFILE"."VACCINE_CODE"='FLUP' OR "IMFILE"."VACCINE_CODE"='HAB' OR "IMFILE"."VACCINE_CODE"='HAV' OR "IMFILE"."VACCINE_CODE"='HepA' OR "IMFILE"."VACCINE_CODE"='HepB' OR "IMFILE"."VACCINE_CODE"='Hib' OR "IMFILE"."VACCINE_CODE"='HibB' OR "IMFILE"."VACCINE_CODE"='HpBP' OR "IMFILE"."VACCINE_CODE"='HPV' OR "IMFILE"."VACCINE_CODE"='IPV' OR "IMFILE"."VACCINE_CODE"='MCV4' OR "IMFILE"."VACCINE_CODE"='MMR' OR "IMFILE"."VACCINE_CODE"='PCV7' OR "IMFILE"."VACCINE_CODE"='PPCV' OR "IMFILE"."VACCINE_CODE"='RTV' OR "IMFILE"."VACCINE_CODE"='RTV2' OR "IMFILE"."VACCINE_CODE"='Td' OR "IMFILE"."VACCINE_CODE"='Tdap' OR "IMFILE"."VACCINE_CODE"='Var' OR "IMFILE"."VACCINE_CODE"='ZOS')

Union All

SELECT null,
null,
null,
null,
"IMFILE"."VACCINE_CODE",
null

FROM "INSIGHT"."dbo"."IMFILE" "IMFILE"

WHERE ("IMFILE"."VACCINE_CODE"='110' OR "IMFILE"."VACCINE_CODE"='DT' OR "IMFILE"."VACCINE_CODE"='DTaP' OR "IMFILE"."VACCINE_CODE"='DTHI' OR "IMFILE"."VACCINE_CODE"='DTPP' OR "IMFILE"."VACCINE_CODE"='FLU' OR "IMFILE"."VACCINE_CODE"='FLUN' OR "IMFILE"."VACCINE_CODE"='FLUP' OR "IMFILE"."VACCINE_CODE"='HAB' OR "IMFILE"."VACCINE_CODE"='HAV' OR "IMFILE"."VACCINE_CODE"='HepA' OR "IMFILE"."VACCINE_CODE"='HepB' OR "IMFILE"."VACCINE_CODE"='Hib' OR "IMFILE"."VACCINE_CODE"='HibB' OR "IMFILE"."VACCINE_CODE"='HpBP' OR "IMFILE"."VACCINE_CODE"='HPV' OR "IMFILE"."VACCINE_CODE"='IPV' OR "IMFILE"."VACCINE_CODE"='MCV4' OR "IMFILE"."VACCINE_CODE"='MMR' OR "IMFILE"."VACCINE_CODE"='PCV7' OR "IMFILE"."VACCINE_CODE"='PPCV' OR "IMFILE"."VACCINE_CODE"='RTV' OR "IMFILE"."VACCINE_CODE"='RTV2' OR "IMFILE"."VACCINE_CODE"='Td' OR "IMFILE"."VACCINE_CODE"='Tdap' OR "IMFILE"."VACCINE_CODE"='Var' OR "IMFILE"."VACCINE_CODE"='ZOS')

Add this into the command area and on the right, create two parameters: StartDate and EndDate. Make them date parameters, but remove the default dates at the bottom.

If you enter them just as I've shown, then the command will work just as entered above. You should be starting a new report using this as your only datasource (no other tables).

-LB

 

[tnguyen315]

Thank you very much for you help LBass, I'm very appreciated for what you've done for me.

I have one more question before adding a command object...can I add this command object in the same report without deleting all join tables? or I need to create a brand new report? Thanks

 

[lbass]

See the last line of my last post--yes--this should be your sole datasource--new report. Or, you could start a new report and then insert it as a subreport in the report footer of your existing report and then you are just using it for the crosstab.

-LB

 

[tnguyen315]

Sorry LB, I haven't read to end...I'm so excite about the code and inserted into the command without error.
Again, thank you very much for your help.
You derset a star.

 

[fisheromacse]

LOL....Linda deserves way more than 1 star!

Even if i haven't benefited from this particular thread, her ability to see into other people's problems (mine included) and to share the solutions in such a kind and educational way is admirable!