Selecting column in formula based on column name

[bwalvoord]

I'm using CR9 to create a budget report based on an excel spreadsheet which looks like the following:

Item# Jan-05 C Jan-05 D Feb-05 C Feb-05 D
------- -------- ---------- --------- ----------
11111 500 $15,000 450 $13,500
22222 100 $1,000 200 $2,000
33333 20 $ 20 10 $ 10

The above spreadsheet includes a budget for the next four years and the C and D represent cases and dollars respectively. My report must have month to date and year to date totals along with prior year month and prior year (YTD). What I would like to do is drive the report based on a parameter month/year entered. This is no problem. What I cannot figure out how to do is evaluate only the columns I need based on the name of the columns. I can recreate the column Names by creating a variable which = "{" + @MonthPicked + " D}" but this of course returns a string and not the value of the column. Is there a function or some other means of identifying a column at runtime?

 

[dgillz]

Create formulas for the values too, using the same logic, based on the values of 2 parameter fields, one for year and one for budget revision.

If {?Year}=2005 and {BudgetRevision}='C' then {Fy2005BudgetCFieldName} else

If {?Year}=2005 and {BudgetRevision}='D' then {Fy2005BudgetDFieldName} else if...

Continue this logic as long as it takes to cover every possibility.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[bwalvoord]

The issue is not whether I'm trying to pull the C or D number, it's that there are three years and therefore 36 if then's to loop through for every formula. I was really hoping there was a way to pick only the twelve columns I need.

 

[lbass]

I think you would have an easier time if you used a union all statement to convert the C columns into one column and the D columns into one column, adding a date column that you can use in calculations. You would go to database->database expert->add command and add a query like the following to use as your datasource:

SELECT `table`.`Item No`, `table`.`Jan 05 C`,`table`.`Jan 05 D`, cdate('2005-01-01') as Datex
FROM `table` `table`
UNION ALL
SELECT `table`.`Item No`,`table`.`Feb 05 C`, `table`.`FEB 05 D`,cdate('2005-02-01') as Datex
FROM `table` `table`
UNION ALL
SELECT `table`.`Item No`,`table`.`Mar 05 C`, `table`.`Mar 05 D`,cdate('2005-03-01') as Datex
FROM `table` `table`

//etc.

Then you could use a record selection formula based on {command.datex} to establish the report range, and creating summaries for month and year to date becomes simple:

//{@YearToDate}:
if {command.datex} in date({?Year},01,01) to
date({?Year),{?Month}, 01) then {table.Jan 05 D} //which now contains all amounts

//{@Month}:
if {command.datex} = date({?Year),{?Month}, 01) then {table.Jan 05 D}

//{@LastYearToDate}:
if {command.datex} in date({?Year}-1,01,01) to
date({?Year)-1,{?Month}, 01) then {table.Jan 05 D}

//{@LastYearMonth}:
if {command.datex} = date({?Year)-1,{?Month}, 01) then {table.Jan 05 D}

Then you would insert summaries on these and suppress the detail section.

-LB

 

[bwalvoord]

Thanks for the tip. I have never used commands before. FYI, here is the syntax for Excel:

Select Budget$.[Account], Budget$.[CustomerID], Budget$.[Item #], Budget$.[Jan-05 C] AS Cases, Budget$.[Jan-05 D] AS Dollars, CDATE('1/1/2005') AS BudgetDate
From [Budget$]

 

[lbass]

When I use a command with Excel, the syntax appears as in my post. It might depend upon your connectivity. Glad you found something that works.

-LB