Home Datapipelines
New Blog Posts: Merging Reports - Part 1 and Part 2

grouping by calculations, is this possible?

edited July 2009 in Datapipelines
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

Comments

  • edited July 2009

    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
This discussion has been closed.