Percent Calculations

[Mark D Eppley]

Hi all

Is there a way to get a percent in a displayed group

Example grouped by date

Date Revenue Cost Margin

12/01/2009 100.00 90.00 10.00
12/02/2009 150.00 125.00 25.00

I would like to to calculate the percent margin on every group subtotal and and grand total

[Mikkel Jensen]

Hi Mark

The best way to solve this is by calculating an extra column.

If you have the Dynamic AI Demo available you will find an example of this in the report group

Report Types / 20. Calculated Extra Column:

Calculated Extra Column

Screen shot 2009-12-08 at 11.37.25 AM.png (135.17 KiB) Viewed 19719 times

The Extra Column is defined on the List tab in design of the report:

Extra Column Definition

Screen shot 2009-12-08 at 12.18.35 PM.png (74.15 KiB) Viewed 19719 times

In your case the Total (Formula) should be defined as:

[[Margin]]/[[Revenue]]*100

After you have typed the formula click List tab, and you will see the new column appear as a Total.

Please be aware that the formula definition is case sensitive and can only be used on row-grouped reports.

The advantage of this type of calculation is that it's calculated on both details and group totals.

[Bob Cergol]

This is probably an SQL question, but I thought I would post it here anyway since the example for creating a calculated column to compute a percentage does not provide for the case where the divisor is zero. I am getting a "divide by zero" error in a similar calculated column because sometimes the source data is zero.

Is there a simple solution?

BTW, I have just begun exploring the power of DAI ... it is simply incredible! I don't understand why I haven't heard more "hype" about this product from Data Access or on the DAW forums. Also the speed is phenomenal. I had no idea just how fast a SQL database could respond across a relatively slow WAN link.

Bob

[Bob Cergol]

I just saw an example of using the CASE command in another post here, so that's one solution I can use to resolve the divide by zero problem.
Bob

[Mikkel Jensen]

Hi Bob

Thanks a lot for sharing your thoughts about the power of Dynamic AI!

Regarding the use of extra columns and divide by zero:

Did you use the Total (Formula style) of +Columns?

When using this type, Dynamic AI should return '0' instead of an error as shown in the below example, where I removed the Condition set on [Version] and grouped by version as first row grouper.

Divide by zero in a Total (Formual) type of +Column

Clipboard01.jpg (42.7 KiB) Viewed 19700 times

As you see the result is '0' where [Hrs] are null or 0.

If you still got trouble with this, you are more than welcome to send me screenshots showing the issues.

Also be aware that the main difference between a 'SQL' type and the 'Total' type is the time of the calculation. The 'SQL' type adds the sql statement to the original select and executes it, meaning that the average is calculated on transaction level. The 'Total' type is calculated after 'main report' preparation which gives you correct average calculation on the group levels as well.

Mikkel

[Mark D Eppley]

Thanks for the help. Worked out perfectly!!

[Bob Cergol]

Mikkel,

I hadn't gotten as far as trying the different types of computed fields available within a report definition and I had used the default of SQL numeric. Your tip also answers another thing I hadn't figured out -- getting percentages on control break totals in a report.

Thanks!
Bob

[Bob Cergol]

Mikkel,

I just tried creating a "+total formula" field but am getting an unexpected error.

I copied this formula from an existing calculated report field that works fine, of type "number(sql)":
[PRICE] - ([SELL_MATL] + [SELL_PAPER] + [SELL_PURCH])
to a new +total formula field and edited it to this:
([PRICE] - ([SELL_MATL] + [SELL_PAPER] + [SELL_PURCH])) / [PRICE] * 100

When applying DAI tells me it can't open the report because:
5: Invalid procedure call or argument
+Columns:
[PRICE] - 0
Please check that the definition is valid and that fieldnames used are in the data source.

??? Its the same formula except I've added a divide by a field that exists and is used elsewhere.

Bob

[admin]

Hi Bob,

I think the notation is
([[PRICE]] - ([[SELL_MATL]] + [[SELL_PAPER]] + [[SELL_PURCH]])) / [[PRICE]] * 100

I have not verified your formula, but the double brackets are needed.

Best regards

Bo

[Bob Cergol]

Bo,

That's interesting. Adding the double brackets eliminates the error, and the column displays, but it doesn't seem to be calculating -- and I know the math is correct.

Formula for EST_VA_AMT = [PRICE] - ([SELL_MATL] + [SELL_PAPER] + [SELL_PURCH])
Formula for Est_VA_PCT = ([[PRICE]] - ([[SELL_MATL]] + [[SELL_PAPER]] + [[SELL_PURCH]])) / [[PRICE]] * 100

I also tried changing the formula to just: [[PRICE]] - ([[SELL_MATL]] + [[SELL_PAPER]] + [[SELL_PURCH]])
Still all zeros when it should be showing the same value as the Est_VA_Amt column.

Then I tried simply: [[PRICE]] -- still all zeros
Then I tried: [PRICE] -- all zeros, but no complaints about the field name without the extra brackets.

So it seems to not be seeing the data at all as a "+total column". Yet it sees the data as a "numeric SQL" formula column.

Bob

[Mikkel Jensen]

Hi Bob

You need to include all columns that is used in the calculation on the report. If you don't want to show them, use the hide feature set on the List Options tab.

Also be aware that the field names are case sensitive.

Mikkel

[Carsten Sørensen]

Hi Bob,

One thing that is maybe not too clear in our explanations is the difference between Total and SQL columns and the meaning of the brackets.

Just to clarify:

The [[ brackets ]] indicates a Total column to the formula editor and will make Dynamic AI search the Total Columns for a match of the column name within the [[ ]] brackets. As Mikkel is mentioning - the columns used in the Total formula's must be part of the Totals on the report (as Dynamic AI is performing the calculation on top of the SQL resultset). The other types (the SQL types) actually becomes an extension to the SQL statement sent to the database and therefore can address any columns available through SQL.

In your case using the [] syntax of SQL columns - which is valid and absolutely ok - it might be a bit confusing with the additional [[ ]] brackets. In case you do [Price] I believe you get results as a SQL Number column as that is a valid SQL column in your case.

We have in the past concluded that we need both options (both the Total option as well as the SQL extension option) in order to cover all (or as many as possible) needs. As you saw with the total break calculations the Total type columns actually serves a role that is not possible with SQL on the underlying resultset.

FYI - we are currently prototyping a promising point-and-click formula editor - it's pretty cool actually. I believe we can ease the formula editing in a future release soon!

Best regards,
Carsten

[Bob Cergol]

Mikkel / Carsten,

Thanks. I understand now. It makes perfect sense.

There seems to be a feature for everything I can imagine! I remember when I first discovered Dataflex and started by reading the manual. I encountered so many things in it that to me signified a rich product with mature roots in the real business application development world. I am getting the same impression and more from exploring the personal edition of Dynamic AI and today reading the release44 sever administration document. (I will reread that section in the current 6.3 document.) I sent a special email to our management here that DAI would support creating a "branded" customer portal to each customer given access to their information in our system -- amazing bit of work you guys have done!

Bob

[Carsten Sørensen]

Hi Bob,

Just wanted to add - that one reason that you might find logical similarities to the Dataflex approach to business and live in general is that I was raised by Dataflex 2.3b. The manual following 2.3b was my most important source of logic for a long time - a while ago

Actually I developed some systems for the "Printing industry" as a Eurosoft partner here in Denmark. What I especially liked at that time was the fact that I could "paint" a Form (which was the way I understood business transactions at that time) - and that was so quickly turned into a real professional multi-user system with DataFlex. It was visual and easy to explain to customers and included all what was needed for business logic etc.

Funny how we still face the same sort of challenges.

Best regards,
Carsten

[Bob Cergol]

Very interesting to hear Carsten.

I settled on Dataflex (starting with the last Pascal version they published before they switched to Borland C) because it seemed the only game in town for real-world multi-user applications on a PC network. Also it was the only thing I found that I didn't have to be a rocket scientist to write a transaction processing application supporting updates to the same record at the same time by multiple users (order cost totals from shop employees) Back then my current employer was doing pretty much the same thing with the system as they are today -- except they could do it on a 10 mhz CPU with 1 MB of memory and a 10MB hard disk and 4.77 mhz. pc's with 128KB of memory running on a 600 kilobaud network!!!

Back then things like Dyanmic AI were unimagineable!