Rolling 12 months - in Cross tab not working in one report but works in another

[vinu_rnr]

hi All,

we have two crosstab reports meant for  rolling 12 months,  both report built using same query logic, but just differ in terms of dept filter.

In Both reports:
Rolling 12 months is fetched by a filter like:  [Full Date] >= _add_months ([Max_Date], -11)


Dep1_report:

[Department] = 1
[Full Date] >= _add_months ([Max_Date], -11)


Dep2_report
[Department] = 2
[Full Date] >= _add_months ([Max_Date], -11)



However when reports are executed, Dep1_report comes out with correct rolling 12 months whereas Dep2 report brings all months  present in DB.


as Dept2_Report is just a copy of Dept1_report differing only in its filter,  not able to understand the behavior!


Any leads where can i look to find issue!

[New_Guy]

Hi,
Try to filter for one month and let us know if it is working. Compare the generated sql of both the reports and see if there is any difference.
Create a seperate query with months only and test and check the results.

Good luck
New guy

[Lynn]

Two identical reports that differ only for a filter value is crying out for a parameter instead of a hard coded filter value. Take the report that is working properly and change the filter to [Department] = ?SelectDepartment? and try for both scenarios. You can always make report views with the prompt selection set so users don't even need to provide the prompt value.

[BigChris]

In terms of your original question though...have you got any suppression on the output from the first query?

[vinu_rnr]

All,
sorry for the delay, I'm out of office for whole week, will check and update by Monday eod.

hi Lynn,
off course report can have parameter instead of hard coded filter value :)
These are base requirements and we have been given heads-up that  one of them will be enhanced with additional filters later hence i had created two copies..

but will insert parameter in both and will see the behavior.


Regards,
Vinu_rnr