grouping by calculations, is this possible?
I have a table with a primary key PK, some fields F1, F2 and G1
I have a user defined aggregate function in SQL Server UDF(int) which takes
PK as an argument and it returns a string (it calculates the string by
concatenating some strings in a second table, based on PK)
The data I need to fetch consists of G1, UDF(PK), Sum(F1) and Sum(F2). The
data would be grouped by G1 and UDF(PK).
I added Sum(F1), and Sum(F2) as calculations in the data calculations tab,
and the report builder correctly added the grouping by G1 to the sql.
However, I also tried to add the UDF(PK) as a data caclulation (expression),
which fails when checking SQL, because the query also needs grouping by
UDF(PK).
If I take the generated sql and manually add the extra group by clause, it
will run fine and return what i want.
The problem is that I cannot find a way to add this calculated UDF to the
grouping set. Is this a known limitation, is there any workaround?
Manually grouping at the client level and doing the calculations using
Report builder would take forever, so i'm trying to avoid that at all cost.
Also exposing the UDF as a column in a view gives me some headaches, so I'm
trying to avoid this too.
Much appreciated
I have a user defined aggregate function in SQL Server UDF(int) which takes
PK as an argument and it returns a string (it calculates the string by
concatenating some strings in a second table, based on PK)
The data I need to fetch consists of G1, UDF(PK), Sum(F1) and Sum(F2). The
data would be grouped by G1 and UDF(PK).
I added Sum(F1), and Sum(F2) as calculations in the data calculations tab,
and the report builder correctly added the grouping by G1 to the sql.
However, I also tried to add the UDF(PK) as a data caclulation (expression),
which fails when checking SQL, because the query also needs grouping by
UDF(PK).
If I take the generated sql and manually add the extra group by clause, it
will run fine and return what i want.
The problem is that I cannot find a way to add this calculated UDF to the
grouping set. Is this a known limitation, is there any workaround?
Manually grouping at the client level and doing the calculations using
Report builder would take forever, so i'm trying to avoid that at all cost.
Also exposing the UDF as a column in a view gives me some headaches, so I'm
trying to avoid this too.
Much appreciated
This discussion has been closed.
Comments
This is possible in RB 11.05.
One of the new features is the ability to group on non-aggregrate calc
expressions.
--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com