Hy guys and girls!

I have a fex that makes a union of two queries. One of them counts credits in a bank, the other one sums the amount of money granted on the same credits.

Each one has a column named DATO wich carries the "Quantity: " value on the first case and the "Amount: " value on the second case (always). So I make a BY DATO and my data gets displayed something like this:

Bank1   Quantity  5
        Amount    10,000
Bank2   Quantity  15
        Amount    210,400

So now, my problem are the totals.

I need to make some sort of column-total for Quantities and another one for Amounts.

Any ideas? Thanks!

This message has been edited. Last edited by: <José Andrés Vargas Aguilar>,

How about carrying 2 NOPRINT variables with the values for QUANTITY and AMOUNT respectively. You can then print them both in a SUBFOOT.

I don't use OVER much so I don't know if it would give you TOTALs in 2 lines similar to the detail data but you might try to use that one instead. If it worked though, then you would't even need to make a union of files but just keep 2 independent fields for QUANTITY and AMOUNT and print one OVER the other.

This message has been edited. Last edited by: njsden,

I actually have the same doubt about OVER. Anyway, I can't use it here because it disarranges some calculated across-columns.

To have a better idea of what I'm looking for copy this and paste it into excel or so:

   
		0 - 0	1-6	7-30	31 - 90	91 - 180	181 - 270	271 - 365	366 - 9999	TOTAL	% CONCENT.	MORA 7 DIAS	MORA 30 DIAS
TRIMESTRE	DATO												
													
													
 De Trimestres Previos	Cantidad:	8,115.00	209	871	898	148	91	84	203	10,619.00	55.86%	21.61%	13.53%
	Saldo:	190,187,206.23	2,946,765.62	22,421,809.29	23,893,309.76	3,623,401.24	3,510,086.83	2,189,130.40	3,695,251.95	252,466,961.32	43.22%	23.50%	14.74%
2009 - Trimestre 2	Cantidad:	1,006.00	24	72	53	7	6	4	1	1,173.00	6.17%	12.19%	6.05%
	Saldo:	33,755,979.91	732,082.91	3,785,663.51	2,459,554.92	810,180.74	450,416.39	416,523.13	14,786.17	42,425,187.68	7.26%	18.71%	9.79%
2009 - Trimestre 3	Cantidad:	1,171.00	32	110	33	10	3	3	0	1,362.00	7.16%	11.67%	3.67%
	Saldo:	49,171,064.45	2,684,564.47	5,546,778.00	1,242,758.65	331,468.83	53,638.75	108,738.39	0	59,139,011.54	10.13%	12.32%	2.94%
2009 - Trimestre 4	Cantidad:	1,340.00	37	78	48	8	4	0	0	1,515.00	7.97%	9.11%	4.09%
	Saldo:	47,193,866.84	1,573,731.82	2,793,218.74	4,525,771.48	274,900.73	251,039.25	0	0	56,612,528.86	9.69%	13.86%	9.01%
2010 - Trimestre 1	Cantidad:	1,656.00	42	72	41	9	0	0	0	1,820.00	9.57%	6.70%	2.80%
	Saldo:	60,984,913.38	1,142,921.12	6,261,965.58	2,749,007.22	132,901.90	0	0	0	71,271,709.20	12.20%	12.83%	4.42%
2010 - Trimestre 2	Cantidad:	1,851.00	36	65	13	0	0	0	0	1,965.00	10.34%	3.97%	0.66%
	Saldo:	71,871,467.34	1,503,856.78	3,650,730.63	273,374.12	0	0	0	0	77,299,428.87	13.23%	5.08%	0.35%
2010 - Trimestre 3	Cantidad:	552	3	1	0	0	0	0	0	556	2.92%	0.18%	0.00%
	Saldo:	24,577,502.91	269,500.00	20,000.00	0	0	0	0	0	24,867,002.91	4.26%	0.08%	0.00%

The tabs in between should allow you to see it. Currently that's my output and I only need to add the Totals. These should be something like this:

	Total Cantidad:	15,691.00	383.00	1,269.00	1,086.00	182.00	104.00	91.00	204.00	19,010.00	99.99%	65.43%	30.80%
	Total Saldo:	477,742,001.06	10,853,422.72	44,480,165.75	35,143,776.15	5,172,853.44	4,265,181.22	2,714,391.92	3,710,038.12	584,081,830.38	99.99%	86.38%	41.25%

Thanks Neftaly.

Check it out again I corrected my previous post.

FYI, the previous, previous post has the labels in spanish.

Here is some of my code that may help you find an answer:

TABLE FILE SQLOUT

SUM
     MONTOS NOPRINT
     MORA7 NOPRINT
     MORA30 NOPRINT
     SALDOS NOPRINT
     SALDO7 NOPRINT
     SALDO30 NOPRINT
     CANTS NOPRINT
     CANT7 NOPRINT
     CANT30 NOPRINT

SUM
     MONTOS NOPRINT
     MORA7 NOPRINT
     MORA30 NOPRINT
     SALDOS NOPRINT
     SALDO7 NOPRINT
     SALDO30 NOPRINT
     CANTS NOPRINT
     CANT7 NOPRINT
     CANT30 NOPRINT

BY &wf_agrupa
BY DATO

SUM
     MONTOS AS ''

BY &wf_agrupa
BY DATO

ACROSS MORAMIN NOPRINT
ACROSS DIA AS 'Dias de Mora'
   COMPUTE COL0/D20.2 = C10; AS 'TOTAL'
   COMPUTE COL1/D20.2% = IF DATO EQ 'Saldo: ' THEN 100*C13/C4 ELSE 100*C16/C7; AS '% CONCENT.'
   COMPUTE COL2/D20.2% = IF DATO EQ 'Saldo: ' THEN 100*C14/C13 ELSE 100*C17/C16; AS 'MORA 7 DIAS'
   COMPUTE COL3/D20.2% = IF DATO EQ 'Saldo: ' THEN 100*C15/C13 ELSE 100*C18/C16; AS 'MORA 30 DIAS'

Yes, the labels are in spanish and yes, I'm aware that some columns (like C11 and C12) are not being used. Please ignore that for a while.

As you can see all I need is a total for "Cantidad: " (i.e. quantities) and another one for "Saldo: " (i.e amounts).

Thanks!

Instead of BY DATO, can you use FOR DATO and select the records you want?

Well José Andrés, you had missed a very important piece of the puzzle when you first posted the question as the technique I described is not really "ACROSS-friendly".

For what you're trying to achieve, I'd probably just produce a small "report" with totals only appending it to the HOLD file. Then I'd run a report out of the final HOLD.

This message has been edited. Last edited by: njsden,

Actually I´m currently doing some tests with FOR DATO (I think that would be FML. Is it?) but I´m not familiar with the syntax.

This is what I have:

FOR DATO
'Cantidad: ' AS 'Cantidad: ' LABEL R1 OVER
'Saldo: ' AS 'Saldo: ' LABEL R2

But I'm having some issues with the two previous BY DATO.

You're right Neftaly, sorry.

I'm still unfamiliarized with how HOLD files work.

Would you care posting an appropiate example?

Thanks!

Not a problem. This is what I could come up with:

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
FILEDEF HRESULTS DISK hresults.ftm (APPEND

-* Get quantities
DEFINE FILE CAR
GRP_SEQ/I2    = 1;
ITEM_SEQ/I2   = 1;
ITEM_LBL/A20  = 'Quantity';
DATO/D20      = SALES;
END
TABLE FILE CAR
SUM DATO
ACROSS CAR
BY COUNTRY
BY GRP_SEQ
BY ITEM_SEQ
BY ITEM_LBL
ON TABLE HOLD AS HRESULTS
END

-* Get amounts
DEFINE FILE CAR
GRP_SEQ/I2    = 1;
ITEM_SEQ/I2   = 2;
ITEM_LBL/A20  = 'Amount';
DATO/D20      = RETAIL_COST;
END
TABLE FILE CAR
SUM DATO
ACROSS CAR
BY COUNTRY
BY GRP_SEQ
BY ITEM_SEQ
BY ITEM_LBL
ON TABLE HOLD AS HRESULTS
END

-* Calculate total quantity
DEFINE FILE CAR
CTRY/A10      = 'Total';
GRP_SEQ/I2    = 2;
ITEM_SEQ/I2   = 1;
ITEM_LBL/A20  = 'Quantity';
DATO/D20      = SALES;
END
TABLE FILE CAR
SUM DATO
ACROSS CAR
BY CTRY AS 'COUNTRY'
BY GRP_SEQ
BY ITEM_SEQ
BY ITEM_LBL
ON TABLE HOLD AS HRESULTS
END

-* Calculate total amount
DEFINE FILE CAR
CTRY/A10      = 'Total';
GRP_SEQ/I2    = 2;
ITEM_SEQ/I2   = 2;
ITEM_LBL/A20  = 'Amount';
DATO/D20      = RETAIL_COST;
END
TABLE FILE CAR
SUM DATO
ACROSS CAR
BY CTRY AS 'COUNTRY'
BY GRP_SEQ
BY ITEM_SEQ
BY ITEM_LBL
ON TABLE HOLD AS HRESULTS
END

-* Produce report
TABLE FILE HRESULTS
PRINT *
BY GRP_SEQ NOPRINT
BY COUNTRY NOPRINT
BY ITEM_SEQ NOPRINT
ON TABLE SET STYLE *
TYPE=DATA, STYLE=BOLD, WHEN=GRP_SEQ EQ 2, $
ENDSTYLE
END

It's not pretty but it will hopefully illustrate the idea. You will need to tweak it in order to print the exact fields you want as opposed to resorting to PRINT * as I did in my example. Using ACROSS when creating HOLD files does not make life any easier but some Dialog Manager magic will probably give you a hand there.

Regards,
Neftali.

"Well ilustrated your idea is" would Yoda say.
Jajaja.

Thanks! I'll try it and tell you after.

Best regards.

Hy again. Your idea should work.

Following this solution I'd make my normal fex and save it in a HOLD. Afterwards I'd make the same query again, totalize it and save it on the same HOLD.

But as is usual there are some business rules that makes this a bit more difficult. On my parameters screen the user can pick one of several groupers which (is that word well writen?) then turn into that BY &wf_agrupa you can see on the code I printed here some posts ago.

Is there a way to make reference to the columns of the HOLD file?

I'd need to substitute the value of the first column (the one this "dynamic" BY grouper) in all records of the SQLOUT recordset for a generic value so it would allow me to make grouping on my totalizer script.

It would be ideal If WF were able to make references to the HOLD dataset like if it were an array. Something like:

DEFINE FILE MYHOLD
HOLD(1) = 'new value';
END

That would be superb (or anything similar).

Thanks!

Best regards, wf newbie.

This message has been edited. Last edited by: <José Andrés Vargas Aguilar>,

I think you are making this much harder than it needs to be. Have you checked out RECAP?

In response to referencing the columns of your HOLD file, you can join to it (if it isn't indexed it needs to be in the same sort order as the host file). ?FF HOLD (or whatever you have named the file) will give you a list of all available columns in the file, their aliases and formats.

How can I use RECAP there?

Hy again, is it possible to make something like:
ON DATO SUBTOTAL
but that only gets printed at the end of the report?

Tks!

Well, to wrap-up this thread.

Neftali, your suggestion works perfectly but on our infrastructure it does only for "little" recordsets. On this particular pase the maximum verb statements on Webfoucs gets overflowed.

Anyway it's a pretty clever idea, thanks.

I also tried to do it using the Report Composer but again, the queried recordset is too big.

Ultimately I delivered the report having Amounts a Quantities as columns and not as rows.

Thanks for the support.