Group By clause not generated

building reports webintelligence
1

Hi There,

We are using SQL Server 2005 as the front end database. When we use the aggregation function STDEV() in a measure, we donot get a group by clause in the sql generated by WebI for this measure.

The WebI Sql generation treats STDEV() as a single row function.

However, if other aggregated measures (e.g. SUM, MAX) are included in the query panel along with the STDEV() measure, it simply includes STDEV() in the Group By clause which is wrong.

SELECT
dbo.DIM_A.CUST_NB,
STDEV(dbo.FACT_f.NUMERIC_VAL_1),
MAX(dbo.FACT_f.NUMERIC_VAL_2)
FROM
dbo.FACT_f INNER JOIN dbo.DIM_A ON (dbo.FACT_f.CUST_SK=dbo.DIM_A.CUST_SK)
GROUP BY
dbo.DIM_A.CUST_NB,
STDEV(dbo.FACT_f.NUMERIC_VAL_1)

Could anyone tell me how the sql can be correctly generated for STDEV() ? Is this a bug?

sams_ju (BOB member since 2009-05-12)

2

Welcome to B:bob:B!

This old topic may help:

Please use search before posting a new question because very likely a same or a similar question has been asked already :wink:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi Marek,

I checked the link. It’s gr8. I tried to search first but couldn’t retrieve the link (probably because I didn’t select All topics).

But would just want to make sure if this is a .prm file in the odbc directory (probably odbc.prm). I donot have admin access to the system. So I need to mention the specific file in the directory when requesting the administrator.

Thanks a bunch for your help!!!

sams_ju (BOB member since 2009-05-12)

4

Yes, it’s either odbc.prm or sqlsrv.prm - depending on what kind of connection you use to connect to the database, either odbc or oledb.

So it’s good to get access to the whole folder
…\dataAccess\connectionServer
where you can try changing the prm files and then test whether it helped.

Marek Chladny :slovakia: (BOB member since 2003-11-27)