strange report outcome from crosstab query

[mirirom]

i have a report based on a dynamic crosstab query. the query "values" show a total sum of related data under the appropriate month. for example, the first row of data (not the column headers) shows a studio name, a related contact, then the total $amount for all its' related projects through [startMonth] and [endMonth]. conceivably, studio N could have 10 projects with different $amounts for all off the months between the parameters. fine. the query works without error, showing only 1 row of data per studio.

now, in the report version, for some reason it shows multiple rows for the last studio. for example if studio Y has 10 project records in underlying table, the report shows 10 rows, all identical. again, the query shows only one row - correctly. i was thinking that perhaps there were small differences between these 10 records hence showing 10 listings. however, to test, i deleted these records and ran the report again. same thing. the studio now sitting in the last row of the query behaves the same way in the report; rows of identical info.

anyone else ever seen this? would love to hear how you went about troubleshooting it. thanks=)

mirirom

 

[mirirom]

visual example of what's happening:

QUERY OUTPUT (months are dynamic parameters)

Studio Contact SrtMonth Months EndMonth
Jan-01 mm-01 Dec -01
=============================================
B Max $10 $20 $30
MMA Bob $30 $ 1 $15
GEN Sal $25 $50 $ 0
---------------------------------------------

the values shown under the months are the sums of all the related dollar values for that particular month in that particular studio. as stated above, studio B could have 8000 projects which total $10 for the month of Jan-01.

REPORT OUTPUT

Studio Contact Jan-01 mm-01 Dec-01
- - - - - - - - - - - - - - - - - - - - - - -
B Max $10 $20 $30
MMA Bob $30 $ 1 $15
GEN Sal $25 $50 $ 0
GEN Sal $25 $50 $ 0
GEN Sal $25 $50 $ 0
GEN Sal $25 $50 $ 0
GEN Sal $25 $50 $ 0
----- ----- ------
Totals $165 $270 $45