April 06, 2007, 05:49 AM
OmniQuestion about For-Matrix
I have datasource (data table) from 2 fields: cell_id and value.
I have to populate 200 rows into matrix of 20x10. (matrix should have static 20 rows and 10 static colunms).
I have done company profits raport with WF matrix where I had only static rows number (as many rows as account ids).
Question: is it possible to create matrix with more than 1 column.
it should look like:
cell[1;1]value, cell[1;2]value, ..., cell[1;10]value
cell[2;1]value, ...
....
cell[20;1]value, ..., cell[20;10]value
and problem is cells' ids are unique, they are named without any system (I cant parse cell id to two fields: x and y).
April 06, 2007, 08:51 AM
FrankDutchOmni
you can run a query where you first hold the data while you add a sequence number to it and in the next step you calculate a row and line number out of the sequence.
TABLE FILE XXX
PRINT X Y
COMPUTE SEQUENCE/I3=SEQUENCE+1;
COMPUTE ROW/I3=MOD(SEQUENCE/10)+1;
COMPUTE COLOM/I3=SEQUENCE-ROW*10;
(there are some smart functions that will give you both numbers, but I do not know them by hart)
ON TABLE HOLD
END
TABLE FILE HOLD
SUM X Y
BY ROW
ACROSS COLOM
END
Frank
April 17, 2007, 01:29 AM
Omnithank you Frank for the answer, unfortunatly it didnt help me much.
I had task to create pretty difficult report from table consisted from two fields: id, value. ID field had to be populated in 2 dimensions matrix (20x10 or so).
I have tried your suggestion, with some minor changes I got your example working, but for my task it wasnt good solution.
I chose other work-arround technique - first of all I created large SQL query with 10 UNION statements and many sub selects. In other words - I used SQL to create 2 dimensions matrix (table2) filled up with values from first table. This table2 is updated once per month on sql server side.
To retrieve data from table2, I used stored procedure WF report.
Another very tough task was to format well looking table header in WF report (html output). Unfortunatly I didnt find way how to merge header cells and make header to have few levels: first level of few merged cells, and second level (below first row) - detailed columns titles.