City, State, Zip/Postal Calculated Field autosizing...

rbuser

I have followed the steps in the "Learning ReportBuilder" pdf doc and have
created Avery 5160 mailing labels. However, the calculated field that
contains the City, State and Postal fields, does not autosize each included
field...

City = Jefferson
State = Maryland
Postal = 21755

The calc field was created with the expression: City+', '+State+' '+Postal

and we expect the label to print it as: Jefferson, Maryland 21755

...but it print previews it as: Jefferson , Maryland
21755

How do we get the component parts of the calc field to autosize within the
calc field?

Patrick

nardmoseley

This is not a ReportBuilder issue. The Learning ReportBuilder example does
not have the problem you describe. The example uses a SQL expression that is
executed by a database engine.

Two possible causes of this (might be others)

- the raw data field values stored in the database contain extra space
characters at the end of them
- the SQL engine has a bug



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

The workaround (fix) was to modify the SQL text and use the TRIM function
...

TRIM(City) + ', ' + TRIM(State) + ' ' + TRIM(Postal)

However, when the SQL is modified, the other functions on the Data tab are
disabled and no longer available, such as Search, Sort, etc.

This means that the user can no longer sort on the Postal Code field to
create a mailing list for a specific zip code.

Feature Request : Can the TRIM function be integrated into the SQL
construction process for Expression type calculated fields, where multiple
data fields need to be concatenated together?

Patrick

nardmoseley

- You can use the Query Designer to create Calculated fields that contain
any type of SQL Expression supported by your database engine. To do this,
access the Calc tab of the Query Designer. Add a calc field, and then set
the function to Expression. Type the expression string into the edit box. I
just tried a simple test and it worked great. I created a query on the
DBDemos customer table that contains a Calc expression like this:

Trim(Company) + Trim(City)

- if you manually edit the SQL text, then all of the options are disabled.
You are no longer using theSQL generation features of RB, but instead are
specifying the SQL text directly.


- your bigger problem is that your database contains field values that are
not trimmed. (Either that or there is an issue in which the data access
components are adding spaces). I recommend researching this and resolving
it. If the data is stored untrimmed, then I would fix the data and fix all
program code that adds/updates the data.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard,

Thanks for the info. I tried using the TRIM function in the syntax of the
Expression, but it had no affect on the result. But, when I placed the TRIM
directly into the SQL, I got the results that I was looking for.

Are you suggesting that the TRIM "should" work when used in the text of the
Expression?

Patrick

nardmoseley

- what database product are you using? and what data access components are
you using?

- the Calc expression that you specify is used in the SQL string that is
submitted to the database engine. Therefore, you can specify any expression
that is supported by your database engine.

- since you are not getting an error when you are using the Trim function,
that would indicate the database engine supports it. Consult the database
documentation for what expressions and functions are supported. A good way
to test, is to try the same SQL statement outside of RB. Try a test using an
ISQL type of utility provided by the database product, and try a test using
whatever Delphi data access components you are using.

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard,

Thanks. We use Advantage v8.1 ... along with their TDataSet descendants
(TAdsTable, TAdsQuery).

When we placed the Trim syntax into the Expression editor, it did not
concatenate the City, State and Postal fields. However, when we directly
edited the SQL (selecting Edit SQL from the popup RMB menu), and used the
Trim function the result was correct.

Should the content of the Expression editor appear "as is" within the
resultant SQL statement that is created by RB?

Patrick

nardmoseley

Position the mouse over the dataview tool window, then press ctrl + left
mouse button. This will display the exact SQL string that will be submitted
to Advantage.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com