Grouping Based On Parameters

[Guest_imported]

Hi,
I've created a report in Crystal 9 that has 3 group headers called Group1,Group2 & Group3. I have three Parameters setup that ask the user for Group1,2 & 3. I then use a formula to choose the appropriate field from the database for each level of grouping. I then use the Group Expert to choose the 3 formulas as the 3 grouping levels .
This works fine & allows the user to choose the groupings and group order that they require. Therefore one report allows for multiple listing with various groupings.

The problem is that is doesn't convert the grouping levels into the SQL statment that would normally get the server to do the work with regard to Grouping & summing. All this is being done on the workstation and takes much longer that if I hardcode the Group fields into the reprort.

I could get arounf this if the SQL Expression builder allowed me to use paramters , but it doesn;t seem to.
Has anyone any ideas on how to build a Dynamicd Grouping reprort that generates the proper SQL code to improve performance ? Is this clear ? Propbably not. Thanks.

 

[skuhlman]

I've been running similar reports using Stored Procedures. I pass the grouping parameter(s) to my stored procedure. Then the SP builds the SELECT statement and executes it. Here's a rough idea, but not complete. If you need further clerification, ask:


declare @MyParam nvarchar(50)
set @MyParam = 'Zip Code'

declare @MyField nvarchar(50)
set @MyField = 'lname'
if @MyParam = 'First Name'
set @MyField = 'fname'
if @MyParam = 'Zip Code'
set @MyField = 'zip'
if @MyParam = 'City'
set @MyField = 'city'

declare @FinalSQL nvarchar(4000)
set @FinalSQL = 'SELECT '+@MyField+' as PrimarySort, '
set @FinalSQL = @FinalSQL+'fname, '
set @FinalSQL = @FinalSQL+'lname, '
set @FinalSQL = @FinalSQL+'add1, '
set @FinalSQL = @FinalSQL+'add2, '
set @FinalSQL = @FinalSQL+'city, '
set @FinalSQL = @FinalSQL+'state, '
set @FinalSQL = @FinalSQL+'zip '
set @FinalSQL = @FinalSQL+'FROM MyTable '
set @FinalSQL = @FinalSQL+'order by '+@MyField+' '

exec (@FinalSQL)

 

[synapsevampire]

Note that skuhlman's SP won't optimize very well, dynamic SQL can't.

And groupings are significantly more complex to allow for in dynamic SQL than just a sort by.

In CR 8.5 you have an option to perform group bys on server, perhaps that exists also in CR 9?

I believe that there's a recent posting on CR 9 here, wherein they mentioned that you can use parms in the SQL.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Guest_imported]

Thanks skuhlman for the tips, I'll give it a go.

synapsevampire , CR9 does support grouping on server and that option is switched on, but it only seems to work if I choose a specific field for the grouping. It removes the Group By commands from the generated SQL if I use formulas. Therfore I have to use SQL expressions but the expression editor doesn't seem to allow me to insert paramaters or variables ! Catch 22.

I also seen other threads mentioning the parms in SQL for CR9, but can't find out how to implement.

Anyone else got experience of this ?

Thanks,
crystal_clear_not

 

[synapsevampire]

I'm disappointed to read that CR 9 doesn't allow you to use parms in dynamic SQL... I can't use it yet because all of my clients are using Crystal Enterprise.

The best performance might result from creating all of the required reports (6 versions) in separate suppressed sections and letting the parm drive which is unsuppressed.

I've built similar reports to what you're alluding to, and used a similar approach to skuhlman, but I've also just gone ahead and did the tedious work as described above to optimize performance, which seemed like your main concern.

Remember, that SP results in little more than pass through SQL.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[skuhlman]

You can also use the same syntax mentioned in my post above and build the group by clause into the @FinalSQL statement. I figured that you'd be able to come up with that on your own and it requires agrigate functions on all fields which aren't grouped. If you want to do it that way and need further info, just ask.