% of Total in Formula 1

[shelby55]

Hi

I use CR9 on a Windows XP/SP 3 computer accessing an Access XP database.

I have a database with many physicians. I created a field indicating total physicians cases as % of total cases. I also created grand total summations for total cases shown as count(chartno) and total physicians which is distinctcount(docno).

In the Report Footer I also want to show total cases for all doctors with 1% or greater of total cases and what % of total cases that is. Same for physicians: how many have 1% or higher of total cases and what % of total physicians that is.

I know this sounds confusing so below is example:

For all physicians in the database, there are 1000 cases and 50 physicians.

There are 25 physicians (50% of total physicians) with 1% or higher of total cases. These physicians are responsible for 750 of the total cases (75%).

All assistance greatly appreciated.

Shelby

 

[lbass]

Create two running totals. For {#cases}, use a count of {table.chartno}, evaluate using a formula:

distinctcount({table.chartno},{table.physician}) % distinctcount({table.chartno}) > 1

Reset never. For {#physicians}, use a distinctcount of {table.physician}, and use the same for formula in the evaluation section. Reset never. Then create two formulas:

//{@percentcases}:
{#cases} % distinctcount({table.chartno})

//{@percentdocs}:
{#physicians} % distinctcount({table.physicians})

Place these in the report footer.

-LB

 

[shelby55]

Hi lbass!

Thanks so much for the post - it works great except for one thing: it doesn't take into account the number of decimal points so it is missing two doctors with 0.96% of cases which is included when rounding up to 1%. Can this be remedied?

I've given you a star anyway because my work with running totals has been quite limited and your solution will help so much with future reports!!

Shelby

 

[lbass]

Are you saying that you want these doctors included as if they were in the >= 1% group? Are you only rounding up to the nearest tenth? You can control the degree of rounding in the running total by changing the evaluation formula (and it should have had an >= sign to begin with) by using the round function, as in:

round(distinctcount({table.chartno},{table.physician}) % distinctcount({table.chartno}),1) >= 1

...which rounds to the nearest tenth. To round to the nearest whole number, use 0 instead of one for the second argument of the round function.

-LB

 

[shelby55]

Hi lbass

Just so you don't think I'm a lost cause, yes I knew to include the "=" sign in the equation. Also, chart number is count but physician is distinct count.

And your rounding formula worked (like I knew it would!). I knew rounding was what I wanted but was trying to round one part of the equation and not the whole thing which is why it wasn't working.

Thanks again!!

Shelby