Field titles issues in the report

[kate8]

Hi,
I have a uncommon issue in a report. I hope I can explain it clear.
I am using version 2008, the report layout is like a form, such as:
Department:Accounting Project Manager: John Boeck
There are about 20 fields. The title, like Department is not the field name. Right now they are all hard-coding. But we have a table, let me call it D, in database which stores all the tables, fields and descriptions. The title in my report is the description in that table. Since the descriptions are changed very often. I want to use that description in my report.Once they change the descriptions for any fields, I don't have to make the changes to the report(after report is completed, it will be in client site). The table D has no connection to any tables in my report.
May I create something from that table and use them as the titles for my report?
I have a dump way: I create subreport for each specific field,set condition to table name = the table that file is in, field name=filed name, then put out the Description. The problem is that I have about 20 fields, then I have to create 20 subreports.the report will connect to the same table 20 times. Obviously it is not the right way to do it.
But could it be a way?

Thank you so much for your patient and very appreciate for any ideas!!!

 

[Turkbear]

Hi,
Maybe you could use a SQL command to create a distinct fieldname and description list and link the field name in that command to the field name in your report's table - that will allow you to place the corresponding description as the label for that field.

If you have the rights to change the database, creating a view in the database with that would be even better.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

You could create an Excel spreadsheet with the data and linking field, then attach it to the existing table. (Use SEARCH to find examples of how this is done in detail.)

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[kate8]

Turkbear and Madawc,

Thank you so much for the respond. I have been busy meeting deadline for other projects, haven't got chance to try them yet.
For Madawc's suggestion: create an Excel spreadsheet might not work for this case. Because the database is in client site, when they make changes, the excel file must be recreated. That is why I need create something from database directly. whenever they change the description, the titles in my report will change.
Turkbear, for SQL command, I am not sure if I understand this correctly "link the field name in that command to the field name in your report's table" -- I cannot link field name in that command to the field name in my report. Because in the tables that my report generate from have no a field called field name. For example, in info table, 1st field called table_name, value1: em, value2:acc; 2nd field called field_name, value1:Fname, value2:Lname,3rd field called description,value1:Employee First Name.
But in the report tables, there is no fields can connect to this info table. Since I know which field comes from which table and the name of the field, when I create subreport for
a field title, I just select the description under that table name and field name as condition.

I hope I am not making it more confusing.

Again, thank a lot to both of you!!!

 

[lbass]

Using the name field as an example, please identify the {table.field} that populates the report, i.e., the field that you need the description for.

-LB

 

[kate8]

Hi lbass:

Thanks a lot for your attention!!
Ok, for example, my report shows like this:

Employee's Name: John Boeck

"John Boeck" is from table em, field name: emName. When I put out the data in my report, it is em.emName. Now I hard-code "Employee's Name", otherwise it will show "emName". Em table has fields like emName, emAdd, emPhone… etc
In the database, there is a table, called tableInfo which stores all tables and fields info. It has fields such as table_name, field_name, description. For this example, it looks like:
table_name, field_name, description
em emName Employee's Name
em jobTitle Job Title
I can get “Employee’s Name” by “select description from tableInfo where table_name=’em’ and fieldname=’emName’” in a non linked subreport.
But I cannot connect table em to table tableInfo. They don’t have fields in common. What I want to do is create something like emNameDec (I don’t if it is possible or not) with value “Employee’s Name” from that tableInfo and use it in front of em.emName. Whenever the client changes the description, the report can always have the match title in the fields.
Is it possible to do something like this?
Thank you to all!!

 

[lbass]

Okay, I think you can handle it this way. Insert a subreport in the report header that uses tableInfo. In the sub, add the fields: table, field, and description into the detail section. Then add a formula to the detail section:

whileprintingrecords;
shared stringvar array x;
shared stringvar array y;
numbervar i := i + 1;
shared numbervar j := count({tableinfo.field});
if i <= j then(
redim preserve x[j];
redim preserve y[j];
x := "{"+{tableinfo.table}+"."+{tableinfo.field}+"}";
y := {tableinfo.description}
);

In the main report use a formula like this for EACH title, substituting the relevant main report field for {em.emName}:

whileprintingrecords;
shared stringvar array x;
shared stringvar array y;
numbervar i;
shared numbervar j;
stringvar k := "";
for i := 1 to j do(
if "{em.emName}" = x then
k := y
);
k

The subreport should automatically update as changes are made in the description field, and you shouldn't have to change any of these formulas after implementation--unless the table and field names change.

-LB

 

[kate8]

Hi lbass,

Thank you so much. This is great!!
But there is issue. I don't want to show that tableInfo subreport in my main report. When I suppress that Report Header which has the tableInfo subreport, the formula in main report become blank. Is there anything I did wrong?

 

[kate8]

Hi,

I did the suppress the detail in the subreport, then it is working!

There is another issue. If I want the fields in subreport also have this kind of title(I have 3 subreports in this report), how can I do it? do I have to insert the tableInfo subreport into that subreport, then create formulars like what I did in the main report?

Thank you!!!

 

[lbass]

You can't use subreports within subreports, but...

You can reference the same shared variables within the subreports, as long as the subreport containing the tableInfo table is in an earlier section, e.g., if it is in RH_a and the others are in RH_b.


-LB

 

[kate8]

Hi lbass,
The subreports do not contain the tableInfo table, but I added it in without link. Like always, I got "Your current link configuration contain multiple starting points, please be advised that this is generaly not supported", but all the fields work fine.
I am wondering (I also read the thread about no link table) weather it could cause big issues. I have most part of my field titles now, if adding into subreport will cause prblem, then I will leave that part alone.

Very appreciate your help, I can also use this idea in my other reports!!
Have a nice weekend!!

 

[lbass]

Why do you need to do that? I wasn't suggesting that. You can reference the shared variables from the tableInfo subreport in the other subreports as long as they are in a lower section. In other words, you can use the same method that I suggested for your main report in the other subreports.

-LB

 

[kate8]

Hi Lbass,
Thank you so much!!! I misunderstood what you meant. I don't need add the tableInfo, just reference the shared variables. It is working fine.
Thanks a lot!!

 

[kate8]

Hi Lbass,

Something odd happened. Some of the descriptions do not show all. For example, a description:"Employee Start Date", only show Employee, when I show it in the subreport, it show all but in 3 rows. I set the Fomular to can grow. Not all words are splited into different rows, like "Employee End Date", "Employee End" in one row, "Date" in another row. Then this one shows "Employee End" in the main report.

I don't think it is the string size issue. Do you think it could be a database issue? How can I fix it?

Thanks a lot!!!

 

[lbass]

I wouldn't set the formula in the sub to "can grow". If you want the title to wrap, set the formula in the main report to can grow.

-LB

 

[kate8]

Hi lbass,
I set what you said. But the titles still are splited. I checked them in database, and realize that "Employee End Date" between "Employee" and "End" is not a space(the space is biger than regular space), I used Replce() to replace the space with character, but nothing was replaced between "Employee" and "End",but did replace between "End" and "Date". I think it is from "return". Looks like it was done on purpose or the data was imported from a file.

Maybe it need to be solved in database.

Thank you so much for all you help!!!

 

[lbass]

If there are characters you could check not only for returns: chr(13)

...but also for a line feed: chr(10)

or a tab: chr(9)

-LB

 

[kate8]

lbass,

You are SUPPER!!!
Between "Employee End Date", there is one chr(13), one chr(10), after replace both with space. the title display in one line!!

Again, thanks a lot!!!