on using CrossTab

rbuser

Hello,

I have been digging through the CrossTab demos, and becoming more
confused, rather than less. I need to create a report which is similar
in concept to the mileage table on a map, where cities are listed for
both rows and columns, and the mileage between them is in the cell
where the row and column of the two cities of interest intersect.

The CrossTab is the obvious too, but I have not used it before, and
so far, it has me baffled. The natural form for my data is in two
datasets, but this does not seem to be the correct approach for the
CrossTab. In my tables I have:

tblCities
ID: integer
Name: string

tblDistance
ColCityID: Integer
RowCityID: Integer
Distance: Integer

I have tried a couple of simple experiments with a single dataset,
and I do not understand the CrossTab designer. But as I really need to
create things in code, I do not wish to spend a lot of time on the
designer anyway.

1: Do I need to provide a single dataset, in which the join of these
tables provides the result?

2: Since the number of records in each dataset is not known till the
report is called, how do I handle the naming and population of rows and
columns.

3: How to suppress the functions? I don't need Count, or Sum, or any of
those. Just the City names and the distance values.

In exploring the CrossTab demos, I am afraid the implementation is
perhaps a bit too cleverly compressed to be easily followed, at least
for me. It would be very nice, once I comprehend the concepts, but at
the moment seems a barrier to my understanding.

Thanks for your attention.

Bill Meyer

nardmoseley

Hi Bill,

As a start, try working through the RB Developers Guide tutorial
on creating a crosstab report.

Next, run the crosstab demos project, select an example and view it in the
cross tab designer. Make changes and then preview to see what happens.

The RB Help topics on TppCrosstab contain extensive reference info

The crosstab works by iterating over the data pipeline and building an
internal data matrix using the specified row, col, and value dimensions
(data fields). In a second step it renders the data matrix to the
page. The data pipeline should contain the data fields required for
row, col, and values.

I realize you want to do things dynamically, but creating an example using
the designer is what I recommend as an incremental step towards
understanding and building that type of solution.

1. For the dataset you need something like

FromCityName
ToCityName
Distance

2. The crosstab will iterate over the data rows and build a data matrix
containing the rows, cols, values.

3. There is no way to suppress the function, but your distance values
are unique. You can hide the row and column totals
by setting them to Visible False.



Best regards,

-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com