how to create a matrix report ?

rbuser

Hi, I don't know how to start with a complex report, that I have to make
from 3 database tables.

+-----------+ +-------------+
| location | | cost-id |
+-----------+ +-------------+
| |
/|\ /|\
+--------------------------+
| matrix data |
+--------------------------+

the report should look like :


loc1 loc2
loc3 ..... loc n

cost1-id cost1-name maxtrixel-cost1-loc1 maxtrixel-cost1-loc2
maxtrixel1-cost-loc3 .... maxtrixel1-cost-locN
cost2-id cost2-name maxtrixel-cost2-loc1 maxtrixel-cost2-loc2
maxtrixel2-cost-loc3 .... maxtrixel2-cost-locN
cost3-id cost3-name maxtrixel-cost3-loc1 maxtrixel-cost3-loc2
maxtrixel3-cost-loc3 .... maxtrixel3-cost-locN
cost4-id cost4-name maxtrixel-cost4-loc1 maxtrixel-cost4-loc2
maxtrixel4-cost-loc3 .... maxtrixel4-cost-locN


How can I make such a report. Note that the colums are from a
database-table, and the number of locations is large enough to exceed one
printed page(50). The number of cost-id's is about 500.

One special point of attention for the pages that belong to eg. page 1.
for cost1-id at the end of the printed page a subtotal has to be shown,
presenting the sum of the previous columns.
At the start of next page, this amount should be shown in the first column.
The most right column presents a grand total.



Any ideas how to build such a report ?

Andre

nardmoseley

The following is an example of a spreadsheet style report that may help
you...

www.digital-metaphors.com/tips/SpreadsheetStyle.zip

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

rbuser

The spreadsheet style report is not exactly what i meant.

In the spreadsheet style report the columns are defined in a fixed manner.
Each column displays one field.

What I want is a variable number of colums to be retrieved from the location
table. After that i want to display in the matrixelements amounts of money
per cost-id (to be retrieved from the cost-id table) per location. These
amounts represent the sum of all costs made per cost-id per location. (The
costs are retrieved from the matrix-data table)

Per cost-id in the last column of the printed page a subtotal has to be
shown, presenting the sum of the previous columns. At the start of next
page, this transported amount should be shown in the first column. The most
right column presents a grand total.



Is it possible to dynamically define the colums and the data they have to
represent?

Do you have any ideas on how to create this kind of report in ReportBuilder?



Andr?

nardmoseley

I re-read your original post and think I understand a little better. You
have a matrix that might typically consist of 500 CostId rows and 50
Cost-Location columns. Something like this:

CostId1 Cost1-Loc1..........Cost1-Loc50
.
.
.
CostId500


The exact number of rows and columns can vary. Using the technique
demonstrated in the spreadsheet style report you could dynamically create
the required number of subreports to print any number of columns. Each
subreport will traverse all of the rows. At this point you would have
report that printed all of the matrix data.

The remaining issue is that the left and right most columns should display
beginning and ending subtotals for each page. One approach is to create a
temp table that contains the totals required for each page. Then link this
data to the matrix data and print it on the report. The subreports would
need data-aware controls that connect to this lookup total pipeline.


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