Monthly aggregation for DB2 V7 in Universe

[russula]

Hi ALL,

it looks like big problem. I have database in DB2 V7 (old one) and I need to count records on the monthly base. The field in the table is date, so I have to perform some calculation to make just only month. But when I create Universe with grouping by this month, it doesn't work because DB2 V7 doesn't allow use formula in GROUP BY statement.
For example, this one doesn't work:

GROUP BY
month(table.REC_DATE_ENTERED)

If I create query manually, I do SELECT from SELECT to solve this problem. But I can't force Universe to do the same way. All my reports are based on monthly or weekly aggregating. Is there any solution for it?
I have only SELECT rights for DB, can't create additional fields in the table or additional table, even can't create DB link to any external data sources.

We have BO XI R2.

Thanks in advance.

 

[skrandel]

well....There is a parameter you can set for the universe. I believe it's GROUPBYCOL. Check into it. You can add it to your universe parameters, GROUPBYCOL=YES, or modify the PRM file for DB2.

Try searching

http://busobj.forumtopics.com

for GROUPBYCOL and you'll see 37 articles about it. HO has many DB2 customers, so you know there is an easy answer.



Steve Krandel
Intuit

 

[russula]

Thank you, skrandel. The link you gave was the first where I checked. I posted even my question where. But nobody answered until now.

I tried GROUPBYCOL=YES, that caused GROUP BY 1 while creating query. And error during running. From what I understood, it works with SAS, but not with DB2.

 

[skrandel]

Have you called tech support? Business Objects has thousands of DB2 customers. Someone has solved this problem.

My guess it that this is due to the old version of DB2.

Steve Krandel
Intuit