?
Solved

How to refernce the field in the cross-tab report

Posted on 2009-02-17
15
Medium Priority
?
2,919 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:ehitek
  • 10
  • 5
15 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 750 total points
ID: 23663378
Which version of CR are you using?  The ability to address parts of a crosstab is very version specific.
0
 

Author Comment

by:ehitek
ID: 23663422
I'm using CR 2008.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 750 total points
ID: 23663548
OK - we can be optimistic then.

Can you describe what you are trying to do, please, that requires the reference.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ehitek
ID: 23663679
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
 

Author Comment

by:ehitek
ID: 23665845
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
 
LVL 77

Accepted Solution

by:
peter57r earned 750 total points
ID: 23667853
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
 

Author Comment

by:ehitek
ID: 23671687
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 750 total points
ID: 23671948
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
 

Author Comment

by:ehitek
ID: 23672282
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
 

Author Comment

by:ehitek
ID: 23672440
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
 

Author Comment

by:ehitek
ID: 23672558
This might have something to do with the Nulls - some dates are nulls. I'm trying to figure out how to handle this.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 750 total points
ID: 23672737
Nulls are almost certainly the problem.
Have you tried using File>ReportOptions and setting Null fields to default values?
0
 

Author Comment

by:ehitek
ID: 23672807
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
 

Author Comment

by:ehitek
ID: 23672919
It doesn't like this portion:

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

If I remove it - everything works.

0
 

Author Comment

by:ehitek
ID: 23674803
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question