Crosstabs in Subreports

[Reebo99]

CR10, Adaptive Server Anywhere

Sample Data:

Date/Time Location
01/01/2004 08:50:54 M1
02/01/2004 09:54:15 A1
03/01/2004 12:30:54 M11
03/01/2004 07:58:25 A14
02/01/2004 12:50:54 M1
02/01/2004 09:24:45 A14

I need to display a rolling 6 month total per month, i.e Jan 2004 will show a count of all records between 01 Aug 2003 and 31 Jan 2004, Feb 2004 will show a count of all records 01 Sep 2003 and 29 Feb 2004, etc. etc. This then has to seperated by location.

The best way to do this would be via a CrossTab (and that's what the user wants). I've done the hard work, i.e. come up with the solution. The main report will be grouped by Date/time per month, then a subreport is placed in the group footer which will have a crosstab in it, with a link to 2 formulas which give the start and end dates foir the 6 month period based on the group month.

Easy I hear you cry... Except, I've finished the report, but the crosstabs don't seem to update from month to month, i.e. Jan 2004 crosstab is exactly the same as Feb 2004 and Mar 2004 and Apr 2004 etc. etc.

As a check, I've also added a simple count of records to the subreport to see if the figures were the same, they are not.

So, it seems that crosstabs within subreports, when the subreport is run more that once, do not refresh the display.

Now to the question...Anybody else seen this before? Do you reckon this is a CR10 problem or am I doing something blindingly obviously stupid?

Any help/comments greatly appreciated.




Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[lbass]

What are the two linking formulas and what are they linking to? Are they in the main report or subreport?

-LB

 

[Reebo99]

The 2 linking formulas (in the main report) are simply :

@FromDate
DateAdd("m",-5,DateTime(Year({action.action_date}),Month({action.action_date}),01,00,00,00));

@ToDate
DateAdd("m",1,DateTime(Year({action.action_date}),Month({action.action_date}),01,23,59,59))-1;

In then use the following selection formula in the subreport:

{action.action_date} >= {?Pm-@FromDate} and
{action.action_date} <= {?Pm-@ToDate}

I've double checked, and all the records which should appear in the subreport are found and display fine if I place a few fields in the subreport detail, but the crosstab does not update.

If you've ever used Visual Basic, it's like there needs to be a DoEvents command to refresh the crosstab view. Very Very Annoying!


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Reebo99]

Ok, I've found the problem, and I think it's an error with Crystal Reports.

The Row values in the Crosstab in the subreport was based on a formula :

@Common
ToText({?Pm-@ToDate},"MMM-yyyy")

This was to ensure all records within the Crosstab were on one row using a common field.

If I use any formula for the row value, then the crosstab doesn't work. If I base the row value on a common database field, the crosstab works fine.

To solve this I have based the row value on a field which all records have the same value(I've used a spare field within the database where all records have a Null value) then within the Group Options in the Crosstab row I've changed the changed the Group Name to the formula above.

Hooray, it all works.

Hope this resolution helps others in the future.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Reebo99]

Ok, I've found the problem, and I think it's an error with Crystal Reports.

The Row values in the Crosstab in the subreport was based on a formula :

@Common
ToText({?Pm-@ToDate},"MMM-yyyy")

This was to ensure all records within the Crosstab were on one row using a common field.

If I use any formula for the row value, then the crosstab doesn't work. If I base the row value on a common database field, the crosstab works fine.

To solve this I have based the row value on a field which all records have the same value(I've used a spare field within the database where all records have a Null value) then within the Group Options in the Crosstab row I've changed the Group Name to the formula above.

Hooray, it all works.

Hope this resolution helps others in the future.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.