How to refernce the field in the cross-tab report

Hello,

I'm trying to refer to the field in the cross-tab report using GridRowColumnValue function.

The field name in formulas is DevelopmentTracking_SELECT.Completed, so I'm trying to refer like this:

GridRowColumnValue("DevelopmentTracking_SELECT.Completed")

I'm getting:

"This is not the name of a grid group for the field being formatted."

I've tried different ways - gave the name of the field, for instance, or used the name form the Cross-tab expert - Max of DevelopmentTracking_SELECT.Completed, or simply use "Completed" - nothing worked.

I see the possible cause for it. DevelopmentTracking_SELECT.Completed is not a row or column name, in the Cross-tab expert this is a Summarized field. In this case the GridRowColumnValue function probably won't work. How to refer to the field like this correctly?

Thank you.
ehitekAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I'm reading this now that you are saying that in each summary cell you have two fields:

DevelopmentTracking_SELECT.Anticipated field
DevelopmentTracking_SELECT.Completed

(What summary statistic are you using on these fields?)

Assuming they are in this order then I think you would do:

If Not IsNull(GridValueAt (CurrentRowIndex,CurrentColumnIndex ,1 )) and CurrentFieldValue >= CurrentDate Then
           crGreen
Else If CurrentFieldValue < CurrentDate And GridValueAt (CurrentRowIndex,CurrentColumnIndex ,1 )=False Then
          crRed
else
          crnocolor
0
 
peter57rConnect With a Mentor Commented:
Which version of CR are you using?  The ability to address parts of a crosstab is very version specific.
0
 
ehitekAuthor Commented:
I'm using CR 2008.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
peter57rConnect With a Mentor Commented:
OK - we can be optimistic then.

Can you describe what you are trying to do, please, that requires the reference.
0
 
ehitekAuthor Commented:
OK, I need this conditional formatting:

If Not IsNull({DevelopmentTracking_SELECT.Anticipated}) Then
If CurrentFieldValue >= CurrentDate Then
crGreen
Else If CurrentFieldValue < CurrentDate And {DevelopmentTracking_SELECT.Completed}=False Then
crRed

The CurrentFieldValue is the value of the DevelopmentTracking_SELECT.Anticipated field.

The first part:

If Not IsNull({DevelopmentTracking_SELECT.Anticipated}) Then
If CurrentFieldValue >= CurrentDate Then
crGreen

works just fine.

I need this part to work:

Else If CurrentFieldValue < CurrentDate And {DevelopmentTracking_SELECT.Completed}=False Then
crRed

DevelopmentTracking_SELECT.Completed is a boolean field that actually shouldn't be on the report at all. But since this is a crosstab report which is located in the Header section I had to add it to the summarized section together with DevelopmentTracking_SELECT.Anticipated field, which is the field that I'm formatting. I suppressed the DevelopmentTracking_SELECT.Completed field to make it invisible.

So my guess is if we can figure out how to reference the field DevelopmentTracking_SELECT.Completed correctly then everything should work.

Thank you.
0
 
ehitekAuthor Commented:
I'm afraid this may not be possible. This is what I've got in the CR forum:

It is not possible to get all the field values if you don't place the row or column in the crosstab. To check this if you place the field in report header you can only see the first values but not all. But if you place the crosstab in report header and insert the column then you can see all the values. So you can access the field values by inserting a column or row in croostab.
0
 
ehitekAuthor Commented:
I summarize by Max. I don't need any summaries at all, just had to use something.

Your formula seems to be correct. I was getting an error ("Field is required here), so I removed the portion that checks for Null (this is not really needed here), so it looks like this now:

If CurrentFieldValue >= CurrentDate Then
           crGreen
Else If CurrentFieldValue < CurrentDate And GridValueAt (CurrentRowIndex,CurrentColumnIndex ,1 )=False Then
          crRed
else
          crBlack

However, now I'm getting an error when try to open the report:

'If CurrentFieldValue >= CurrentDate Then'
A number, currency amount, boolean, date, time, date-time, or string is expected here.

It seems that it doesn't like crGreen here. Although the report designer had accepted this formula.
0
 
peter57rConnect With a Mentor Commented:
I can't see why you are getting the error.

Which format feature are you using?  I tested mine using the cell background.

It might be worth just making sure there are no spurious characters in the spaces by deleting/backspacing and then tab-ing to create the indents.

If your logic is correct as shown then you don't need the date test in the Else If line.
0
 
ehitekAuthor Commented:
I'm changing the font color for the field.

I keep trying, I'll let you know. I also think that this shoould work.
0
 
ehitekAuthor Commented:
It's strange. I've tried to simplify your statement like this:

If CurrentFieldValue < CurrentDate And GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1)=False Then
          crRed
Else
          crBlack

and I'm still getting:

A number, currency amount, boolean, date, time, date-time, or string is expected here. Error in File C:\DOCUME~1\pafonin\LOCALS~1\Temp\rptGameSchedule {C495A7D0-EC46-4081-A11F-29D57AE66B37}.rpt: Error in formula . 'If CurrentFieldValue < CurrentDate And GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1)=False Then ' A number, currency amount, boolean, date, time, date-time, or string is expected here.
0
 
ehitekAuthor Commented:
This might have something to do with the Nulls - some dates are nulls. I'm trying to figure out how to handle this.
0
 
peter57rConnect With a Mentor Commented:
Nulls are almost certainly the problem.
Have you tried using File>ReportOptions and setting Null fields to default values?
0
 
ehitekAuthor Commented:
Yes, in fact, I've also done it in the formula window.

This doesn't seem to be an issue. I've also tried to replace all nulls with the default date value in my stored procedure - it didn't change anything.
0
 
ehitekAuthor Commented:
It doesn't like this portion:

"And GridValueAt(CurrentRowIndex,CurrentColumnIndex, 1)=False"

If I remove it - everything works.

0
 
ehitekAuthor Commented:
OK, with the help of the CR support this finally worked:

The Completed field must be put before Anticipated field in the Crosstab wizard.

Completed (boolean) field - Supress formula:

WhilePrintingRecords;
BooleanVar i;
i:=CurrentFieldValue;
True

Anticipated (date) field - Format font formula:

WhilePrintingRecords;
BooleanVar i;
If CurrentFieldValue >= CurrentDate Then
crGreen
Else If CurrentFieldValue < CurrentDate And i=False Then
crRed
Else
crBlack

Thank you very much for your help, I've learned a lot from it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.