Home› General
ReportBuilder 22.04 now available including Delphi 12.1 support!
New Blog Post: Helper Routines

on using CrossTab

rbuserrbuser
edited June 2016 in General
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

Comments

  • nardmoseleynardmoseley
    edited June 2016
    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.