Crosstab

rbuser

I am having troubling building off a report I have created where I want to
use the last record of a column group. What crosstab event would be best to
achieve this or is it not possible to do.

Current report something like this...
Org A Org B
-----------------------------
Value A 22 202
Value B 33 404

the data for this is like

Org Value A Value B Month
============================
Org A 20 30 2
Org A 2 3 3
Org B 200 400 2
Org B 2 4 3

But what I would like to do is have Value B only report the last value in
the data (in this case month 3 value only) while Value A is still a sum of
both record values.

Org A Org B
-----------------------------
Value A 22 33
Value B 3 4



Thanks,
Ian

nicocizik

Hi Ian,

ReportBuilder simply traverses the data you give it. You should be able to
filter the output by adding a search condition to your query accessing this
data. Something like the following...

select Org, ValueA, ValueB, Month
from MyTable
where (Org = A) or (Org = B and Month = 3)

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

This query did not help me. I did have an error in what the result cross tab
should be from below:

Org A Org B
-----------------------------
Value A 22 202
Value B 3 4

Value A is the sum of the each org's record which the cross tab does fine.

Value B is not a sum of the two records for Org A, not 30 + 3, but just 3
(the last record for that org which is month 3). Same for Org B. Now the
crosstab does min, max, avg., sum, count but is there a way I can just get
the last record's value B for that org and put it in. Are there any events I
can use on the calc tab to set the value as it traverses the data?

Thanks,

Ian

nicocizik

Hi Ian,

Sorry, I misread your initial post. You can manually edit a calculated
value in a crosstab using the OnGetValueText Event. Take a look at demo 127
located in the \RBuilder\Demos\Crosstabs\... directory for an example of
this.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I have looked at the demo 127 and still can not produce the results. I still
can not get the data value of the last record of the column. The event does
not seem to traverse the data. For instance

procedure CrossTab1OnGetValueText(aElement: TppElement; aColumn: Integer;
aRow: Integer; const aDisplayFormat: String; aValue: Variant; var aText:
String);
begin

if aRow = 3 then
aText := plOrders['Amountpaid'];

end;

Results in sum of amount paid for all the states having the same amount paid
and it is equal to the first record in the data which is 784.9. The result I
am looking for would have BC with a sum of amount paid of 17,917 and HI at
$1,400

State(C)
Data (c) BC (C) HI (C) GrandTotal (TC)
------------------------------------------------------------------
Count of tax rate (c) 5 (V) 23 (V) 28 (V)
Sum of Amount Paid (C) 784.9 784.9 272684.10

Note: I set the Search on the data to reduce the result above and sorted it
by state (for a total of 28 records)


Thanks,
Ian

nicocizik

Ian,

The crosstab component does not function the same way the report does. It
traverses your data independantly and builds a matrix based on that data on
it's own before creating drawcommands that display everything. This is the
reason you are getting the wrong record value when directly accessing the
datapipeline. If you need to manually enter a datavalue into a crosstab
cell, you will also need to manually retrieve the correct record based on
the cell being printed. This is something the crosstab component was not
designed to handle automatically.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com