I have a query that includes a logical field from a table. For the life of me, I can’t include a text string in my resulting report when the logical table field is true. Can someone walk me through the process. Thanks, Erik
And the query is:
And the text string is:
I prefer to do this in SQL before the report-generator works
SELECT "logical_table_field",
CASE
WHEN "logical_table_field"=TRUE THEN 'text string'
ELSE ''
END
AS inserted_string
FROM "table"
Obviously =TRUE should not be necessary.
Wanderer,
Thanks. Do I literally enter the string “inserted_string” after the AS?
I assume then, that when using this SQL, it is independent of the Query that generated the report? Erik
It doesn’t matter. Try Mickey_Mouse and see where it shows or omit the AS inserted_string
No, the case-statement should be part of that Query.
It is because of this that I asked above: how are you trying to insert the text string into the Report (?)
The text string should be a field of Report’s data source.
Or it can be a TextBox in the row:
[InsertedText] = “Your text”
As stated above, “inserted_string” is the alias (= a name) to identify the column that contains the text.
"text “string” is from the data source = @Wanderer’s query.
Can I apply a condition to ibkt show a label when a logical field in the table is true, otherwise don’t show the label?
Ignore the “ibkt” - my question should have read:
Can I apply a condition to show a label (added to my report) when a logical field in the table is true, otherwise don’t show the label?
The easiest way is to utilize conditional formating!
Now I’m interested: How do you integrate this suggestion in the generation of the document by the report-generator of Base??
Sky,
I still am struggling with this. I want to toggle a label in a report to on when the value of a logical field (turbocharged) is true, and not show the label when it is false. I have included the turbocharged field in my query. I tried a variation of what you suggested, =IF([turbocharged] IS NULL;"";“TURBOCHARGED”), and the label doesn’t show when turbocharged is true or false. Thanks, Erik
Sky,
Subsequently, I realized that for a label the conditional logic probably should provide a true or false result, so I tried =IF([turbocharged] IS NULL;false;true) and this did not work either. Erik
Some examples from German Base Handbuch (Reports):
IF([boolschesFeld];"ja";"nein")
IF([boolschesFeld];"☒";IF(ISBLANK([boolschesFeld]);"-";"□"))
IS NULL doesn’t exist as a function in ReportBuilder. See Base Guide 7.3 - Reports
- Best solution: to include in source query
(second column - ‘TURBOCHARGED’) - Label > Conditional Print Expression
[turbocharged] = TRUE()
Rem: I’m using the boolean “PAID” column of @Villeroy’s Teams_Finance DB for the “turbocharged” query field:
Might be this one of November 25: Teams_Finance_3.odb (27.0 KB)
SELECT "tblTeams"."TeamName" AS "Team", "PAYMENTS"."Y" AS "Year", "M"."N" AS "Month",
CASE WHEN "PAYMENTS"."PAID" = TRUE THEN 'PAID' ELSE 'UNPAID' END AS "State"
FROM "PAYMENTS", "tblTeams", "M"
WHERE "PAYMENTS"."TID" = "tblTeams"."ID" AND "PAYMENTS"."M" = "M"."ID"
SELECT
"PAID",
CASE WHEN "PAID" = TRUE THEN 'TURBOCHARGED' ELSE '' END AS "turbocharged"
FROM "PAYMENTS"