If you are unable to create a new account, please email support@bspsoftware.com

 

Generated SQL shows GROUP BY clause but non of the report columns were GROUPED

Started by DiGzâ„¢747, 28 Oct 2008 05:43:06 AM

Previous topic - Next topic

DiGzâ„¢747

Hello Guys,

Can anyone help me out on this? I have a report that non of its data items are grouped.  But, when i view the Native SQL, a group by clause was specified.  This group by clause is causing the issue of missing data entries in my report.  Does it mean that the report really has the group by clause although from the layout non of the columns were grouped.  PLEASE DO REPLY ON YOUR FREETIME...

Regards,
DiGz

DanSev

There are a few possibilities. One is that there are 'Fact' items in the query. Due to 'auto' processing (rollup/sum/avg/ -- whatever default was set), of a numeric field, a group by clause is needed in the sql to process the statement, even if there are no formal groups set in the data items / data objects. If you have access to the model on which the report is based, you can see if there where determinates set, which will group the data 'behind the scenes' of the other studios (report/query..). You might want to contact the data modeller to find what logic was used making the section of the package/model you are using for a more complete picture.

Arsenal

Try turning off the "Auto Group and Summarize" property in Report Studio (Properties Pane of your Query)