Formula Containing Cross Tab Values

[baycolor]

Hi,

I'm using CR XI and XI Server with a Teradata database.

ISSUE 1
I just created my first cross tab report using the cross tab wizard.

I need to be able to create custom formulas based upon data in the cross tab output (column summary and row summary and detail intersection data) so I can do other calculations on the data in my cross tab.

From what I understand cross tab output is created during CR's second pass and my cross tab output is currently in the report header so my formulas will need to be placed in a lower section which is fine.

I got this idea after reading the following in a Ken Hamady newsletter: "What I learned this week is that you can fire variables in the conditional formatting formulas of every cell in a cross-tab. These formulas give you access to the values in the cross-tab, using the "CurrentFieldValue" function. That means that you can take the values inside a cross-tab and pass them to formulas later in the report. The example given in the article showed that you could recreate a running total in a cross-tab located in the group header, and display the running total before the details of the group. This is something that isn't normally possible in a report."

I've looked at a bunch of posts out here but I'm having trouble getting the cross tab values represented in a formula.

I've done the following in a Custom Formula:

NumberVar X;
X := GridRowColumnValue("Class")

This function can only be used in field formatting fields.

So I went into the DisplayText are of one of my cross tab output fields and put the following:

StringVar x;
x := ToText(GridRowColumnValue("Class"));
x;

QUESTION 1
No errors and I didn't even use CurrentFieldValue but even if I did how am I able to use the value "x" in a custom formula?

ISSUE 2
The reason I'm creating a cross tab is that I'm dealing with dynamic data and I'm doing multiple summaries. Example:

                  class1   class2   class3 etc...
US
   New York
       employee     1      0        1
       employee     0      1        0
CA 
   Los Angeles
       employee     1      1        1
       employee     0      0        0
etc...
1 = class completed, 0 = class not completed

Ultimately I want my output to be in Excel and I haven't had much luck dropping the cross tab output to Excel in a format that I would call useable. So I as thinking if I had a custom formula that contained all the cross tab output and I hid the cross tab I could display my custom formulas on the output (easier to control the output in Excel when working with my own fields).

QUESTION 2
The cross tab is dynamic and the report will be automated so I can't create custom formulas on the fly. How would I handle something like this - or do I have to go back to fiddling with CR's cross tab Excel export?

Thanks in advance.

 

[lbass]

I really think you shouldn't take this approach. You would have to write too many formulas, and the more columns and rows you have, the more complex this is.

The example formulas you show don't really do anything. If you wanted to do capture the US summary, you would select the row total->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar USvar;
if gridrowcolumnvalue("table.country") = "US" then
USvar := currentfieldvalue;
false //this just allows the current value to display

Then in the main report, in a lower section, you could reference that variable, as in:

whileprintingrecords;
numbervar USvar;

For values at inner group levels, you would need to specify each specific gridrowcolumnvalue (three factors for summaries at the employee level).

-LB

 

[baycolor]

That solution worked. I can see the values in the lower sections. Thanks!

I agree my method/line of thinking here is not good.

Do you have any recommendations for working around the following:

1)
Exporting a cross tab to Excel (not data only) looks terrible. I get empty columns between page breaks and I get small unused columns bewteen columns on a page.

2)
Exporting a cross tab to Excel (data only) resolve some of this but then I lose any slick formatting I want to do (highlight cells based upon conditions etc).

I'm attempting to make my cross tab output to Excel presentable and I played around trying to do this for a good 2 hours and didn't have a lot of luck.

Any suggestions?

Thanks.

 

[lbass]

The data-only export isn't too bad except row labels appear at the bottom of each section for outer rows(groups). If you are not displaying subtotals for particular rows (groups), you could concatenate the the three row fields in a formula and then use only that as a row field. You will then get repeating values per row.

I don't generally export crosstabs (or anything) to Excel, but I'm guessing you might have to settle for adding the highlighting within Excel.

If you could create a manual crosstab, the export would be more in line with what you are looking for. Are the courses you are evaluating relatively constant? You would need to change/add only one formula per change in course.

-LB