CROSS TAB WITH % 1

[cristi22]

Hello all -

I am using the cross-tab report & need to add an additional field to calculate the % for totals

For example:

for "DART" the % = 20 (14 * 100 / 67)

Here is my report (sample)

[b]high[/b]

DART	MEDICAL	MEMORIAL UNIT 7	       Total
SINGLE ENROLLED	7   2	0  19  28
FAMILY ENROLLED	7   2	2  28  39
Total	        14  4	2  48  67
[COLOR=red] PERCENTAGE ??   20  [/color]

THANKS A LOT FOR YOUR HELP!!!

 

[lbass]

What CR version are you using?

-LB

 

[cristi22]

9.2

 

[cristi22]

i've been trying to figure it out with diffirent solutions...nothing works
Please help!!!

 

[lbass]

I'm not sure of the capacity of 9.0. In 11.0, you could do the following to get the percent of the grand total:

Create a formula:

{table.amt} % sum({table.amt})

Place this formula in the crosstab as a summary field, and choose "sum" as the summary. This should give you the correct percentages, but it will appear in each row as well as in the total row, so then you could either:

1-suppress the summary in the inner cells, or

2-create a separate crosstab with no row, only the column and summary. Then place this crosstab either in a separate section under the one in which your crosstab is located, or if you have a static number of rows, you could overlay the second crosstab so that it appears to be part of the first crosstab.

-LB

 

[cristi22]

Thank you very much for your help!
#1 worked,
Can I display the '%' sign
Thanks again!

 

[cristi22]

I have one more issue with this report.

I created an SP to sum records by employer, coverage.
In some cases the employer does not have any members with the standard/high coverage

The report have 2 groups (STANDARD & FAMILY)

How can I display the following record with 0 members under the STANDARD group with 0 members?

UMASS MEDICAL            FAMILY ENROLLED HIGH             2
UMASS MEDICAL            SINGLE ENROLLED HIGH              2
UMASS MEDICAL            NOT ENROLLED        NK                  5

Really appreciate your help!!!

 

[lbass]

If you need to display values that aren't in the database, then I think you'll have to create a manual crosstab, using either conditional formulas or running totals. If you want help with this, please supply your group (row) name, crosstab column field, and current summary field.

-LB

 

[cristi22]

LB - THANK YOU VERY MUCH FOR YOUR HELP !!!
here is my input data (the result from SP)

USR_EMPL_NAME         COVERAGE_2 COVERAGE_3 MEMBERS    
-----------------------------------------------
UMASS DART               FAMILY ENROLLED HIGH               7
UMASS DART               SINGLE ENROLLED HIGH               7
UMASS DART               FAMILY ENROLLED STANDARD           10
UMASS DART               NOT ENROLLED    NK                 65
UMASS MEDICAL            FAMILY ENROLLED HIGH               2
UMASS MEDICAL            SINGLE ENROLLED HIGH               2
UMASS MEDICAL            NOT ENROLLED    NK                 5
UMASS MEMORIAL           FAMILY ENROLLED HIGH               2
UMASS MEMORIAL           FAMILY ENROLLED STANDARD           5
UMASS MEMORIAL           NOT ENROLLED    NK                 107
UNIT 7                   SINGLE ENROLLED HIGH               19
UNIT 7                   FAMILY ENROLLED HIGH               28
UNIT 7                   FAMILY ENROLLED STANDARD           54
UNIT 7                   NOT ENROLLED    NK                 273

WHERE COVERAGE_2 is either HIGH/NK/STANDARD
COVERAGE_3 SINGLE / FAMILY / NOT ENROLLED

The report is grouped by (1) EMPLYOER_NAME and (2){@groping} =

 if {UMASS_COVERAGE_COMBINED.3} = "HIGH" OR {UMASS_COVERAGE_COMBINED.3} =  "NK" THEN
"HIGH"
ELSE {UMASS_COVERAGE_COMBINED.3}

Summary is on MEMBERS & % is on MEMBERS

{UMASS_COVERAGE_COMBINED.MEMBERS} % sum ({UMASS_COVERAGE_COMBINED.MEMBERS})

COLUMN: USR_EMPLOYER NAME
{@groping}

ROW: COVERAGE_COMBINED.2

SUM: MEMBERS
{@percent}


I REALLY APPRECIATE YOUR HELP!

 

[lbass]

First insert a group on {table.Coverage2}. Then create two formulas for each employer name, as in:

//{@UMASSDART-High}:
if {table.USR_EMPLOYER NAME} = "UMASS DART" and
{@grouping} = "High" then {UMASS_COVERAGE_COMBINED.MEMBERS}

//{@UMASSDART-Std}:
if {table.USR_EMPLOYER NAME} = "UMASS DART" and
{@grouping} = "Standard" then {UMASS_COVERAGE_COMBINED.MEMBERS} //etc.

Then right click on each formula and insert a summary (sum) at the group level and at the grand total level. Then suppress the detail section. To get the percent of the grand total for the report footer, create separate formulas for each column like:

sum({@UMASS-DART}) % sum({UMASS_COVERAGE_COMBINED.MEMBERS})

To get the "%" sign, just click on the "%" icon in the toolbar.

-LB

 

[cristi22]

LB, THANK YOU VERY MUCH FOR YOUR HELP!

I am sorry, but I am a bit confused.
Perhaps, I was not clear with my question (I apologize)
There are no members with STANDARD coverage for UMASS DART only in this month. The formula should be dynamic.

UMASS MEDICAL FAMILY ENROLLED HIGH 2
UMASS MEDICAL SINGLE ENROLLED HIGH 2
UMASS MEDICAL NOT ENROLLED NK 5

Please let me know if my question is obscure.

Right now I have on the report:

[b]group HIGH:[/b]
UMASS DART UMASS MEDICAL  UMASS MEMORIAL UNIT 7 Total
HIGH  HIGH	HIGH	HIGH
FAMILY ENROLLED	7 2 2 29  40     6.81%
NOT ENROLLED   65 5 107	273 450  76.66%
SINGLE ENROLLED	7 2 0 18 27      4.60%
Sub Total   79	9	109	320	517  
Percentage:  13.46%	1.53%	18.57%	54.51%	

[b]group: STANDARD [/b]
UMASS DART UMASS MEMORIAL UNIT Total
STANDARD	STANDARD	STANDARD	
FAMILY ENROLLED	11  54	70	11.93%
Sub Total  11	5	54	70
Percentage: 1.87% 0.85%	9.20%

In this case on the STANDARD group I have to display an additional coloum for the UMASS MEDICAL employer with 0 members under the FAMILY & SINGLE coverage


I REALLY APPRECIATE YOUR HELP!

 

[lbass]

Please explain whether you are just wrapping the standard and high group instances for the purposes of this post or whether you intend for them to be displayed sequentially. Also you did not previously mention "month", so I don't have a clue how that fits in.

My solution is designed exactly to account for that zero value. If there is no record for UMass-standard, summing the corresponding formula will result in a zero. This is dynamic in that the formula will accommodate zero or some records. If you mean that you might have different employers at different times, or something other than "high" or "standard", then that is a feature of a manual crosstab that is not dynamic.

The other way of doing this is to "pivot" the display, and use groups on employers and {@grouping}, and use {table.coverage_combined2} as the column field, since there would be fewer formulas to create.

Did you try my suggestion?

-LB

 

[cristi22]

LB - Thank you very much for your help!

Yes, I tried your suggestion, but it only work for one particular period.
There were no members enrolled in STANDARD coverage for UMASS MEMORIAL in July & 5 new members could be enrolled in June.

Month is an input parameter from the SP, the records are being selected based on the month.

The employer could have different number of members from one period to another.

It should be something in the column field (if employer does not have any records display 0 ???)

REALLY APPRICIATE YOUR HELP!

 

[lbass]

I think you are not implementing this correctly. Please provide the formulas that you created in trying my suggestion.

-LB

 

[cristi22]

THANK YOU VERY MUCH FOR YOUR HELP!

umassdart-high:
if {UMASS_COVERAGE_COMBINED.USR_EMPL_NAME} = "UMASS DART" and
{@groping} = "HIGH" THEN {UMASS_COVERAGE_COMBINED.MEMBERS}

umassdart-std:
if {UMASS_COVERAGE_COMBINED.USR_EMPL_NAME} = "UMASS DART" and {@groping} = "STANDARD"
THEN {UMASS_COVERAGE_COMBINED.MEMBERS}

Is there a way in CR to do something like this:
USR_EMPLOYER is null then display 0 else display USR_EMPLOYER on the column level?

I am sorry I am completely lost

REALLY APPRICIATE YOUR HELP!

 

[lbass]

These formulas (plus the other 6 you didn't show) will display 0 if there are no members--the default for a conditional formula resulting in a number is an "else 0". You should be laying these eight formulas side by side in the detail section and then right clicking on each and inserting summaries on them at the group and report levels.

-LB

 

[cristi22]

I have to add the formula for each employer & get rid of the cross-tab

Thanks a lot!!!