How to dynamically sort cached CR instance accessed via opendocument

[Former Member]

I am currently accessing cached CR instances via an opendocument call (from XC dashboard) that filters the records on display using &sf in the opendocument call to display only the records that match the criteria selected by a user. These instances are refreshed weekly and accessed hundreds of times throughout each week.

I have new requirements where the users want the records in the CR instance to be sorted differently depending upon what they have selected. I have been trying to do this with a formula based on the recordselection() and while this works fine if I am refreshing the report, it does not when I am filtering a cached instance because the recordselection() is not being evaluated at the proper time (I suspect). If I evaluate the recordselection() whileprintingrecords then I can get access to the selection filter value but I cannot group on the dependant formula - I get the error "Group specified on a non-recurring field".

Does anyone have any ideas on how I can accomplish this goal without forcing a refresh on the report (which is not an option based on volume / performance needs)? I am happy to provide more detail if needed.

Thanks in advance...

[Former Member · Accepted solution]

If you are using Crystal Reports 2008, go with the Sort Control else check out the Web elements functions.

Web elements Samples:

[http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/a038d221-c67d-2b10-5e98-ab17de9f443e]

[Former Member · Accepted solution]

Salah / Ashwin,

Thank-you both for the suggestion. I am using CR2008 so bind sort control is available for my usage however it does not solve my problem exactly. I really need to dynamically group and sort on the group... Here is some more detail for you to consider.

The CR I am accessing is grouped based on a customer ID. Each customer ID can have many detail rows (which are suppressed on the report always). Each detail row contains the band value for each trait which can be analyzed in the parent XC dashboard. When the CR instance is accessed via the opendocument call from XC, there is a selection filter applied so that only the detail rows that meet the criteria of the selection in XC are included for consideration in what is displayed in CR. What I have found is that the bind sort control can work within a group, but I need this to supercede the group sorting because otherwise I am just sorting the trait within customer and I could never get the list sorted in such a way that the customer with the largest or smallest value in a trait field is first because the grouping by customer ID always is the primary sort.

What I wish I could do, was set the group field to be set dynamically based on the recordselection sent from the XC dashboard. Then, if the request to filter the report came based on the sales trait, it would be grouped by sales number. If the request came based on customer age, it would be grouped / sorted by customer age. What I am finding here is that I cannot group by a formula that is evaluates the recordselection whileprintingrecords, yet I need the recordselection to be evaluated at that time in order to accurately pick up the selectionfilter passed in the opendocument call. Any ideas on this?

I am also toying with the idea of getting rid of the groups altogether and just displaying detail with identical rows suppressed. this will be a bit of a comprimise for our users because I don't know how I can show the total number (a sum) of records in the detail section. If I can get them to agree to that, I think that I can create a formula based on recordselection that I can bind sort control to -- Just tested and found out that I cannot sort on a formula that evaluates recordselection whileprinting records so this may be out the window too...

Of course, all this would be relatively easy if I could just referesh the report on demand when accessed but that is not a viable option due to a number of reasons. Any help / suggestions on this are greatly appreciated!!!

Edited by: Steve Fowler on Nov 2, 2009 4:07 PM

[todd_hanna · Accepted solution]

It's been a while since I've worked with openDoc - and specifically it's been a while since I've tried messing around with a saved instance of a report by using openDoc, so this may or may not work... but worth a test.

Can you set the grouping up to be based on a parameter (well, group on a formula that uses a parameter)

{@group}

if {?param} = 1 then {table.field1}

else if {?param} = 2 then {table.field2}

Once you have the parameterized group set up to take care of your grouping/sorting dynamically - try sending the parameter to it via openDoc....

Not sure if that will work, but it may be worth building a simple test report to give it a shot.

Todd

Edited by: Todd Hanna on Nov 3, 2009 1:30 AM

[Former Member · Accepted solution]

Hi Todd,

I am willing to give this a try but first a question... if I am not refreshing the report, would the report recognize/consider a parameter that I am passing via opendocument? I thought that a parameter value would only be considered on refresh?

On a side note, I have created a test version of the report with the groupings removed and a column that is dynamic based on the &sf value passed in the opendoc call. When I force this to be evaluated whileprintingrecords it works quite well however I have lost the ability to show group totals (because I no longer have groups) and unfortunately I cannot take care of this int eh SQL either because I need the atomic level detail in order for the filtering at view time to behave properly.

Steve

[Former Member]

HI Steve..

Which version of Crystal Report You are Using???

If it is 2008, You can use "Bind Sort Control" for dynamic Sorting..

Right click on field heading>Bind Sort Control>Select the sort field.

Regards,

Salah