Dear Developers

i need your Assistance,
i try to generate HTML Report with Accumliative value of Perecent.

the report look like the following:-

- Across date--------------------
- Day 1 |||| Day 2 |
Name |Total | firstvalue | %| secoundValue |%
--------------------------------------
Divi | 100 | 20 |20%|80 |100%
Elin | 100 | 70 |70%|30 |100%

Note that the Accumilitive apllied on the % only not for the real value.Also if i have more than 2 Real Value let Say FirstValue,SecoundValue,ThirdValue....etc

i generate the same report using Excel template and it is OK becuase i use Excel functionality .


but can we do it with HTML

I Appreciate Your Assistance

Regards
Qalqili

I think its easier to do this by using a HOLD file to compute the percentages. Something like this..
table file data
sum value and compute cvalue=cvalue + value ; pct.cvalue WITHIN name
by name by date
on table HOLD
end
table file hold
sum value and cvalue across date by name
end

Hello Qalqili,


The first step is you need to do is to make the total for each person for the periode you want to display in the report. Make sure the result is sorted by person and by date (in ascending order so the oldest date comes first). Put the result in a hold file.
In the second step you need to join the hold file to the file that contains the values per day for each person. After the files are joined you can calculate the % of the value against the total value.
To do this you need a Computed field to cumulate the values per person per day.
The first day of the period, the computed field will contain value1 , for second day it will contain value1+ value2,...., the last day it will contain the sum of all values for that person for that period. To do this you can compute the field like this:

Example:
COMPUTE TOTFIELD/D12.2= IF NAME NE LAST NAME THEN VALUE ELSE (TOTFIELD + VALUE);

Since you have the total value per person in each record per day and per person it is very easy to calculate the cumulative %:
Example:
COMPUTE PERC/D5.1%=((TOTFIELD/total value)*100)
Both fields are calculated per day and per person and put the result in a new hold file.

In the last step the only thing you need to do is the correct sorting.

TABLE FILE holdfile
PRINT
VALUE AS 'Whatever'
PERC AS %
BY NAME
BY TOTALVALUE_FIELD (this needs to be a BY-field since you only wants to see this once per line)
ACROSS DATE_FIELD
END

Test every step and check if your sorting is right, if your results are correct before you take the next step. For this solution I presumed you have only one value per person per day. If you have multiple values for the same day for the same person you will need to use the max.-prefix on the total value field in step 2 in order to make the calculations correct!!!!!

I'll hope this will help you.

Kind regards,

Dirk JJI

Hello Qalqili,

I made a little mistake.

In the last fex
this line :
PERS AS % should be PERC AS '%'

I forgot the single quotes. Sorry for that.

Kind regards,

Dirk JJI

Dear GeraldCohen/JJI

i will apply your technique and return back to you.

Dear JJI/Gerald Cohen

i try your code it gives me some different from expected result.

but i modified some code and it is working fine with me.

here is the code:-
TABLE FILE SQLOUT
SUM
C_Counter AS 'Volum'
COMPUTE P2/D12.2% = ( D_Counter / C_Counter ) * 100; NOPRINT
AS 'Total %'
D_Counter NOPRINT
BY V1SND AS 'Sender'
BY V1RCV NOPRINT AS 'Receiver'
SUM
D_Counter AS '#'
COMPUTE SS/A2 = LAST V1SND; NOPRINT
COMPUTE Per/D12.2% = ( D_Counter / C_Counter ) * 100; NOPRINT AS '%'
COMPUTE Temp1/D12.2% = IF V1SND NE SS THEN 0.00 ELSE LAST Per; NOPRINT
COMPUTE Temp2/D12.2% = IF V1SND NE SS THEN 0.00 ELSE Per + Temp1; NOPRINT
COMPUTE Final/D12.2% = IF Temp1 EQ 0.00 AND Temp2 EQ 0.00 THEN ( Per + Temp2 ) ELSE ( IF LAST Temp2 EQ 0.00 AND LAST Temp1 EQ 0.00 THEN Per + Temp1 ELSE Per + LAST Final );
AS '%'
BY V1SND AS 'Sender'
BY V1RCV NOPRINT AS 'Receiver'
ACROSS HOUR1 AS 'Term of Hours'

ThanX very much for your Assistance

in 1 step
DEFINE FILE CAR
2SEATS/I8S=IF SEATS EQ 2 THEN SALES ELSE 0;
4SEATS/I8S=IF SEATS EQ 4 THEN SALES ELSE 0;
5SEATS/I8S=IF SEATS EQ 5 THEN SALES ELSE 0;
END
TABLE FILE CAR
SUM SALES NOPRINT BY CAR
SUM 2SEATS
AND COMPUTE SHARE2/D5S%=100*C2/C1;
4SEATS
AND COMPUTE SHARE4/D5S%=100*(C2+C4)/C1;
5SEATS
AND COMPUTE SHARE5/D5S%=100*(C2+C4+C6)/C1;
BY CAR

END