design column report

rbuser

I am trying to design a weekly invoice report. A report with 7 columns,
sunday to saturday, listing all the invoices for each day of the week like
the following

sun mon tue wed thu fri sat
100 105 121 156 164 170 179
101 106 122 157 169 172 185
102 127

How should I do it?

Thank you

digitalmetaphors

You can use the cross tabs component to try to mimic this type of behavior.
I tried creating such a report by creating a calculated field from the date
field and populating it with the day of the week. That field became the
column dimension for the crosstab. The question was what to use for the row
dimension. The basic idea seems to be that you would have to modify the
data, possibly in an intermediate table, to order it correctly. Here's an
approach which should work or at least get you on the right track. After
calculating the day of week field write the data back to a temp table you
need to find a way to order it so that it is in sets of sun-sat (so that
data look like so: sun, mon, tue, wen, thu, fri, sat, mon, tu ...) It under
that ordering it also needs to be ordered by the order number. Once you have
that ordering create another calculated column which assign a distinct
integer to every group of 7. At this point you data should look like this:

1 sun 100
1 mon 105
1 tue 121
1 wen 156
1 thu 164
1 fri 170
1 sat 179
1 sun 101
2 mon 106
2 tue 122
2 wen 157
etc...

This integer field will be a row dimension, the day field a column
dimension, and the order number the value. Lastly disable the sum fields on
the crosstab. The biggest challenge here would be to get the data to look
like this but it should be possible with a temp table.

--
Cheers,

Alexander Kramnik
Digital Metaphors