graph % of a running total 1

[fisheromacse]

I attempting to retrieve data from support v outcome surveys and chart them but must be missing something, so, any assistance would be greatly appreciated.

Below is a lengthy description of the errant or hopefully relevant information. If there is any other information that may be of use, please ask.

I have a report with several groups.
Group Header 1.Wide Area,
Group Header 2.Local Area,
Group Header 3.Block,
Group Header 4.Building,
Group Header 5.Floor,
Details Secton


there should always be 26 records per wing, 4 of them should always be "Personal Info" and are of no interest. I cannot filter these records out in the Select statement as the information is needed for display in the report, but not for these calculations. However, I would like the report to be accurate even if items on the evals are skipped.

I have no problem showing the sum of {@Outcomes} on a chart using this (formulas below):
Chart Info:
Chart Type: Bar
Data: On Change Of: {@ItemName}
Show Value: Sum of {@Outcomes}

But, I need to determine and graph the % of outcomes met ({@Outcomes}=1) for each of the 22 relevant items for display in the Report Footer.

I have all the values I need in Group Footer sections, but am stuck graphing them.

I evaluate a detail record using this formula:
//{@Outcomes}
if isnull({Table1.ItmA}) or trim({Table1.ItmA})="" or {Table1.CatName}="Personal Info"
then 0
else
IF {MCLdetail.ItmA} = "Y" then 1
else 0


The percentage:
//{@OutcomesPCT}
(Sum ({@Outcomes}, {Table1.WideArea})/{#RT_Q)*100


The running total:
//{#RT_Q}
Field to Summarize: {Table1.DTS}
Type of Summary: Distinct Count
Evaluate: Use a Formula: {Table1.CatName}<>"Personal Info"
Reset: On Change of Group 1 Wide Area


//{@ItemName}
IF {Table1.CatName}="Personal Info"
then ""
else {Table1.ItemName}

 

[lbass]

Replace your running total with a conditional formula:

//{@DTS}:
if {Table1.CatName}<>"Personal Info" then
{Table1.DTS} else
{@null}

...where {@null} is a new formula that you open and save without entering anything. If DTS is not a string, you need to wrap {@null} in tonumber() or date()--whatever the datatype is.

Then create a formula like this:

Sum ({@Outcomes}, {Table1.WideArea})% distinctcount({@DTS},{table1.Widearea})

This percent formula should be available for charting.

-LB

 

[fisheromacse]

thanks LBass!

i will give it a go tonight.

it always seems so simple when you give an answer.
i seem to have a talent for making the simple things more complex.

 

[fisheromacse]

LB,
I am not sure what i am doing wrong,but when i attempt to chart, using the method above, i only get the % results for the final item in the list.
If i chart just the raw #'s it displays all 22 desired items.
the formulas are exactly as you specified.

Sum ({@Outcomes}, {SDetails.WideArea})% distinctcount({@DTS},{SDetails.Widearea})

//{@DTS}:
if {SDetails.CatName}<>"Personal Info" then
{SDetails.DTS} else
{@null}


//{@null}
//created then saved empty


I created a 2nd report similar to the 1st but with much less to display only the charted %'s and as long as i keep the chart in ({SDetails.CatNamem}, it does display the % for the items in each CatName together. I would like it to display the items from all CatNames on the same chart.

I do not have the report in front of me, so if you need more info/raw data/etc, i will have to do that this weekend.


Thanks much.

 

[lbass]

I guess I thought you were evaluating per wide area. Where does the item fit in? There isn't a group on this--is it just in the detail section? Can you explain what the percentage is supposed to be (in words)?

-LB

 

[fisheromacse]

You are correct that i want to evaluate per wide area.
i created a group on {SDetails.ItemName} (Group 6) and one on {@ItemName} (Group7), which is the items name if it does not belong to the {SDetails.CatName} "Personal Info" If the {SDetails.CatName} is "Personal Info" i have it to return {@Null}

{@ItemName}
if {SDetails.CatName}<>"Personal Info" then
{SDetails.ItemName} else
{@null}

Now i want to show the % of outcomes met per wide area, displaying them by item name (using the {@ItemName}).

I will be away from the computer for about 24 hours....gotta give eyes a break....40oz of MtnDew and 1/2 pot of coffee following 8 hrs of sleep in the past 72 hours is catching up with me.

This week, as fast as I can add items to my task list, someone else adds two!!!

At this point, i am sorely tempted to start a report from scratch that will just display the charts and tell the client that is how it needs to be...2 reports, one for a textual summary and one for graphical.

Like i have said before, i am amazingly talented at taking the simple/obvious and making a mess of it!

Of course, as soon as i/we get this working, the client will want some arcane change.

 

[lbass]

I'm not following this. Where are you placing the chart? If you want to show the percent of outcomes per item name per wide area in the report footer, then you need two on change of fields (wide area and item name), and the outcome percent has to use the item name group for both group arguments.

Or you can place the chart in the wide area group section and just use item name as the on change of field, but again, the item name should be used for the group arguments.

-LB

 

[fisheromacse]

lb,
sorry for the long delay getting back to this.

re-reading what i posted, i can understand the confusion i caused.

I have talked with the instigator of this report and think they are ok with 2 reports. one to summarize the data and one for the charts, and as such, your provided solution is working out nicely.