COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: vinu_rnr on 15 May 2017 11:58:39 AM

Title: Rolling 12 months - in Cross tab not working in one report but works in another
Post by: vinu_rnr on 15 May 2017 11:58:39 AM
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!
Title: Re: Rolling 12 months - in Cross tab not working in one report but works in another
Post by: New_Guy on 15 May 2017 01:06:07 PM
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
Title: Re: Rolling 12 months - in Cross tab not working in one report but works in another
Post by: Lynn on 16 May 2017 01:50:34 AM
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.
Title: Re: Rolling 12 months - in Cross tab not working in one report but works in another
Post by: BigChris on 17 May 2017 02:02:06 AM
In terms of your original question though...have you got any suppression on the output from the first query?
Title: Re: Rolling 12 months - in Cross tab not working in one report but works in another
Post by: vinu_rnr on 18 May 2017 04:41:40 PM
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