Formula for manual crosstab in Crystal 1

[ebutter]

I am trying to create a manual crosstab report in Crystal 10 that uses two levels of groups on the vertical axis and has a fixed number of years on the horizontal axis. I am trying to write formulas that I point to to fill each column. My grouping and summaries work, but the formula doesn't. Here is what I have:

if {AssetYear.Value} = {Revision.planyear} then{AssetYear.GetEndingValue}. The next year would be: if {AssetYear.Value} = {Revision.planyear} + 1 then{AssetYear.GetEndingValue}. The If is supposed to be my "where" clause and GetEndingValue is the field.

Any help would be greatly appreciated!

 

[lbass]

It looks like the year values are coming from different tables. How are the tables linked? Can you explain a little about what you are trying to accomplish with your formulas pertaining to the years? The more usual formula for a manual crosstab using years as columns would look something like:

if year({table.date}) = 2005 then {table.amt}

Or,

if year({table.date}) = year(currentdate)-1 then {table.amt}//etc.

-LB

 

[ebutter]

The data structure is a little odd because we are reporting off an XML file. {Revision.PlanYear}is the base year--we don't know what the value is. {AssetYear.Value} is the column year or the year that we are trying to pull a value for. In pseudo sql: Select GetEndingValue Where Revision.PlanYear = AssetYear.Value for the first column. Then, Select GetEndingValue Where Revision.PlanYear + 1 = AssetYear.Value for the second column, etc. Each of these formulas has its own name and is being pointed at from the Details section.

I really appreciate your quick response!

 

[lbass]

So you are working from the equivalent of one table only? If you lay out your fields in the detail section like:

RevisionPlanYear AssetYearValue AssetYearGetEndingValue

...what do the results look like? And what are your group fields (maybe not relevant, but it can't hurt to know)?

-LB

 

[ebutter]

Thanks--let me give you a better description of what is happening. The structure is like this:

@Year1 @Year2 Etc.
Grp 1 = @AssetCategoryNames Sum of
Grp 2 = @AssetClass Sum of
Details = Description @GetEndingValue1 Etc.

We are reporting off an XML schema which is designed to mimic relational data-- with relationships established from Primary Keys to Foreign Keys. There are 3 "tables" used above that are related like this:

REVISION has many ASSETS has many ASSETYEARS

Revision.PlanYear is the base year. It is used in determining @Year1, @Year2, etc. It is also used in my @GetEndingValue Formulas to establish what Year I want data from:

if {AssetYear.Value} = {Revision.planyear} then{AssetYear.GetEndingValue}. The next year would be: if {AssetYear.Value} = {Revision.planyear} + 1 then{AssetYear.GetEndingValue}. The If is supposed to be my "where" clause and GetEndingValue is the field.

There is one AssetYear table for each year (60 in all)--hence the one to many relationship with the Asset table. Therefore, I want to pull a GetEndingValue value from the AssetYear table Where AssetYear.Value (eg 2005) = Revision.PlanYear + 1 (unique @GetEndingValue formula).

Does that help? We're using Crystal 10 with the most up-to-date xml driver which we got from Business Objects.

Thanks!

 

[lbass]

I think I understand what you are trying to do, but your approach should work, and since it isn't, I thought some sample data would help. In a new report, please lay out the three fields:

RevisionPlanYear AssetYearValue AssetYearGetEndingValue

...and report back. Also, how are you linking the three "tables", i.e., from what field to what field? It could be your links are interfering with what you are trying to do.

-LB

 

[ebutter]

Here's an example and some more info. I've just found out that we are not using the xml driver that I thought--we're upgrading this week. It is possible that this is involved in the problem because we have had issues with the driver I'm using. I'll let you know on this.

First, my "tables" are linked like this:
Revision.RevisionID to Asset.RevisionID
Asset.AssetID to AssetYear.AssetID

Second, here are the formulas that should limit my record selection-- pasted directly out of the Editor:

@GetEndingValue1
if {AssetYear.GetEndingValue}<> "" and tonumber({AssetYear.Value}) = tonumber({Revision.planyear}) then
tonumber({AssetYear.GetEndingValue})

@GetEndingValue2
if{AssetYear.GetEndingValue}<> "" and tonumber({AssetYear.Value}) = tonumber({Revision.planyear}) + 1 then
tonumber({AssetYear.GetEndingValue})

I would create one of the above for each year in the report.

Interestingly, the result of the above is that the first year works-- it is returning all of the correct asset values from the xml data for Revision.PlanYear = AssetYear.Value. In this case it is for year 2004. The second year is not working. It returns all 0's-- no values for the assets. If I change the second formula to hard code the year such as--

if{AssetYear.GetEndingValue}<> "" and tonumber({AssetYear.Value}) = 2005
tonumber({AssetYear.GetEndingValue})

-- I still get 0's! I've checked my data-- there is definitely values in there.

I'm sitting here thinking that this might indeed be a linking issue. All of my AssetYear "tables" (there are 60 of them) are linked to the same Asset table using the same ID. Do you think that Crystal doesn't know which table to look in? Maybe the AssetYear.Value check is not enough because its not a linked ID?

Thanks!

 

[lbass]

You make it sound as though the AssetYear tables all have the same table name. Not sure how that could be the case, but if it is, that probably is the issue with the formulas. You could try to use a Union All statement that combines the AssetYear Value and Get Ending Year Values from different Asset Years into one recordset--that might allow your formulas to work.

I still think you would learn about how your data is working if you were to lay out those fields in a new report in the detail section--you could see whether other asset years are currently represented.

-LB

 

[ebutter]

To LB,
You won't here from me for a couple days while I get the new driver installed and take your suggestion. And yes, in the XML structure all AssetYear "tables" are called the same thing, so this might be the issue.

Interestingly, the native crosstab does generate correct results, but the formatting constraints make it nearly impossible to meet requirements.

I will be in touch in a few days. Your help is tremendous!