Link to home
Start Free TrialLog in
Avatar of czarubah

asked on

Rename column header based on table

i have a table that has generic filed names like Sales_01, sales_02,...sales_nn. the end user can define the names for those fields in order to report the data...thus I have a table with fields "Number" and "Description".

when I write a query that selects the fields sales_XX, i want to rename the filed to the corresponding row "Description" based on the XX.

select sum(sales_01) as "???", sum(sales_02) as "????" from ....

Since the name and number of descriptions could vary from user to user I cannot hard code the query. I need a way to get the names of the columns by a query that resolved the XX to a Description.
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

I don't quite see where you are going with this plan.
A variable field name is not something that can be used in a report.
Avatar of czarubah


Let me complete my example.

user 1 has sales_01=Food, Sales _02=Beer, Sales_03=USA_today, sales_04=coupons
user 2 has:  sales_01=Food, Sales _02=Beer, Sales_03=Toy for tots.

the database stores the sales as specified by the sales_XX (of course there are fields for the store and the date) and thus the owner would like to see a report that shows Food, Beer, USA_today, toy for Tots and coupons....
Each store has a configuration file that gives names to the sales _XX which is different from each other. I know there has to be way to create a relationship of sales_xx to configuration file based on the store.  so that store 2 sales_03 is toys while store 1 sales_03 is USA today...the final report would have 5 columns...thus variable number of columns with user defined headers.
i know I can get the column names from the user_tables, but I already know the generic names, what i need is a process that converts the generic name to user specific name.
Not within the query and be able to use the recordset in Crystal.

One thing you can do is to use a UFL to read the configuration file too get the column names and association.  You can then use them as the column headers in the report with formulas.

I have the column names and association in a table...config (store, number, description).

the sales table (store, date,sales_01,..sales_nn) and I know that number is the last two characters of the sales columns...what I haven't done is a formula that relates the number to the column....

I can say sales(description) = sum(sales_&text(number)) for number=1 to nn. I am not sure that this works in SQL or how to do it inside Crystal reports...
How many records do you have in the config table ?

If less than 1000 then you could try using a  subreport in the report header to bring back all of the records from that table and populate 2 arrays

array1 = Store&number
array2 = description

you can then use these in your main report to lookup the description for the relevant field
GJParker The config table has a maximum of 21 records. I do not know how to apply your suggestion.

the data as specified above is in a table that looks like this (Store, Date, sales_01, sales_02,...sales_21)...

In the majority of cases only a few fields are in use...i've been trying to create the filed name sales_XX as a text, which i can do by using this formula named "field_name" if code <10 then "Sales_0"&code else "sales_" & code...this works in the sense that i can see the field names sales_XX and its associated description.

Now what i want to is get the sum ("field_name") of course when I try this i get an error that says either field name unknown or it highlights the field and says it needs a value field.

I can convert the formula to text, but I'm not sure that will work either.
Something like Sum ("field_name") is simply not going to work.  You can't use variable field names.

 Going back to your first post, let's say that you were going to change the query somehow, so that for one user, it might be

select sum(sales_01) as abc, sum(sales_02) as def from table1

 and for another user it might be

select sum(sales_01) as ghi, sum(sales_02) as jkl, sum(sales_03) as mno from table1

 I'm not sure if you were intending to have a different number of columns too, but I went ahead and included that.

 There are two basic problems with that:
 1) The field names can't change.  Let's say that you create the report using the first query.  If you tried to run it with the second query, CR wouldn't be able to find the abc and def fields and wouldn't run the report.  If you were running the report from the CR designer, it would ask you to remap the old fields to the new ones (eg. abc is now ghi).

 2) The number of columns/fields needs to be consistent too.  Ignoring the name issue for the moment, if you created the report using the first query and then tried to run it using the second query, you would only have two fields on the report and CR might simply ignore the third field (mno), or it might tell you that the datasource has changed.  If you created the report with the second query and then tried to run it using the first query, CR would complain because the third field (mno) was missing.

 So, you can't do what you seemed to be trying to do.

 What you can do is just use the generic names (Sales_01, Sales_02, etc.).  Put each of those fields on the report, do summaries on them, etc.  And then, in one way or another, get the column names for the user from your other table and use those for the headings for those fields.

 If the basic report structure is always the same (Sales_01, Sales_02, Sales_03, etc.), then that may not be too difficult.  If you want to show different columns for different users, put the columns in different orders, etc., then it becomes more complicated, but it may still be doable.  It depends on exactly what you want to do.

 Is the report going to be run for one user at a time, or for multiple users (so the column headings or whatever may be changing in the middle of the report)?

YOu can do this with a "parallel" formula

Use formulas like this

If {codeFeild} < 10 then
Else if {codeFeild} < 20 then

If {codeFeild} < 10 then
Else if {codeFeild} < 20 then

You can create formulas for each column as needed.

The formulas can be summarized as needed.

That's an intersting Michael, but I think it would require further changes to the data structure.

Froom myunderstanding of the way that the OP has expressed it so far, he wants different fields to be present depending on circumstances.
To create formulas along the lines you have suggested would require all fields to be present all the time, otherwise the formulas would crash.
He could use SQL with enough NULLs on the end to satisfy the formulas.

Also if the field isn't there the report will have problems

Thanks for the contribution.
James- I'm not trying to change the structure of the table that has the data and thus i have a report that gives me the sum(sales_01) and the you mentioned and that is what I'm trying to do is select sales_01 as "abc" where "abc" is defined in another table. I'm trying to do this with CR because there aren't SQL tools available at the sites.

mlmcc- I'm not sure how to interpret your formulas...but here is an example of what i have
config file                          Sales table
code Description                store date sales_01 sales_02 ....sales_05...sales_21
1 Food                                 6556 11/1/12 1521.12 0 0 0 5.00 ....0
2 Beer                                 6556  11/2/12  2411.12 0 0 0 1.00 ....0
5 Shamrocks.

thus a sales report would show (and for this purpose assume it is the sum)
store    sales_01   sales_02   sales_03....Sales_05...Sales_21
6556     3933.24              0               0            6.00...

what I'm trying to solve here is the report header. I want to "change" sales_01 into Food and sales_05 into Shamrocks, as well as all the others that are defined because I know that code in the config file is the index to the sales column
store    Food              Beer  sales_03....  Shamrock...Sales_21
6556     3933.24              0             0              6.00...

the only other issue is that the config may not have all 21 codes defined and thus in that sense my report should only have columns that are defined...i thought about creating an array of "totals" using code as the index. that gives me the relationship I need, my problem then becomes creating a report based on the array.
The database table always has 21 columns for sales?
The sales columns are populated with values so configfile code=x is in sales_xx

Are those assumptions correct?

Avatar of GJParker
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mlmcc -Your assumption is correct. the caveat is that the config may or may not have 21 rows general I now that 4 rows will be defined because the restaurants always sell food, beer, wine and beer...the rest is locally defined.

GJParker...I'm looking at your samples, will get back to you soon.
GJParker. thanks, your sample report is what I was looking for. I will accept your answer as a solution.