Help with creating User defined Function

English
#1

Or looking at How to create a user defined function - #7 by hjek perhaps I do not need a User defined Function?

Here is the problem I am trying to solve. I have a table with columns Delivery_Days (character) and Total (integer). I can accumulate Total in the header of a report with Function/Total/Accumulation/Specified Route.

How would I accumulate Total only for those rows in which Delivery_Days did not equal ‘MWF’?

As you can probably tell I am very inexperienced with databases but I have years of programming background including Visual Basic. I would be very comfortable writing a macro if you got me pointed in the right direction.

I have read over Chapter 6 Reports - User Defined Functions but it is not “clicking” for me.

#2

Please upload your ODF type sample file here.

#3

I’m sorry. I don’t have a clue how to do that, or even if I have “my ODF type sample file.” If I knew how to do that, I might well know how to insert a user-defined function.

Isn’t there any – you know – documentation?

#4

ODF: Open Document Format is the native file format of the LibreOffice. .odt for the Writer, .ods for the Calc… etc…
.
Ahhh, I see now, your problem is related to the Base, but not to the Calc…

#5

Sure, actually there is so much you need to find the right spots.
For uploading files here there is a button, available. An older picture is shown in the guide for this site, but on my mobile the upload button moved to the lower right of the edit-window. Not mentioned in the guide is: Not all types of files are allowed, so .odb is typically rejected.

https://wiki.documentfoundation.org/Ask/Guide_-_How_to_use_the_Ask_site/en

For your real problem, you already linked the documentation, but forget about the first link. A user-defined function in Calc (actually a macro) has the same name, but is completly different from user-defined functions in the Report-builder for Base.
.

1 Like
#6

I use mostly SQL for this problems, so some variant of
SELECT SUM("Total") WHERE "Delivery_Days"<>'MWF'

SQL will also allow grouping and quite complex conditions, depending on your database.

But this would be done before the data enters the report.
I never used the user-defined-functions in reports

#7

@Wanderer, thank you! I am not a SQL expert but I am willing to give it a try. Here is the underlying query as it exists today.

SELECT "Route" AS "Route", "Delivery_Order" AS "Order", "First" AS "First", "Last" AS "Last", "Address" AS "Address", "Phone" AS "Phone", "Contact" AS "Contact", "Contact-Relationship" AS "Relation", "Contact_Phone" AS "Phone", "Notes" AS "Notes", "Collect_Payment" AS "Collect", "BanqMC_MealsRequired" AS "BanqMC", "COA_Meal" AS "COA", "Total" AS "Total", "ID", "Birth_Year", "Lunch_Count", "Delivery_Days", "Total" AS "Total_TuTh" FROM "Clients" WHERE "Route" = UPPER ( :Please_Enter_Route_Letter ) ORDER BY "Order" ASC

I just now added that "Total" AS "Total_TuTh" . How do I qualify that – just that sum – with something like WHERE "Delivery_Days" <> 'MWF'?

#8

FilterData_FB.odb (53.2 KB)

#9

Nice. What is the relevance to my question?

Isn’t there something somewhere that explains how to create a simple function in a Base Report: IF [Delivery_Days> <> ‘MWF’ then accumulate [Total]

#10

Better solution than working with functions in a report is to create all values with the database. I only use views in a report and calculate all I need in the views, because the reports will work very slow and some calculations will never work there.
Upload your example here, not only some SQL-code.

#11

@SonomaCharles,
because your using a single table it’s probably best to define a function in report builder.
.
here is an image of the report builder function you need:

Nav_and_Properties
Nav_and_Properties710×209 21.3 KB

.
you can copy from here:

[SUM_Tue_Thu]+if([Delivery_Days]<>"MWF";[Total];0)
if([Delivery_Days]<>"MWF";[Total];0)

this link may help

1 Like
#12

I’ll replace the link in yesterday’s posting with an improved sample.
There is a record set “Data” with table data (person, category, date, value, primary key).
There is a query returning another record set with aggregations (sum, count, min, max, average). Both record sets have nothing to do with each other. They have different count of columns and different data types.
In a form, you can link many different record sets for editing or display.
In a report, you can display data and add additional calculated values to the header or footer.
Query “qReport” merges the data tables and adds a calculated “VAT” column multiplying each amount “V” with 0.16.
From this query the report wizard generated a simple tabular report and I added a footer with 2 controls adding up the amounts and the VAT.

There are 2 forms. “FilterData” is an advanced sample demonstrating how to leverage forms and subforms in order to display filtered record sets together with aggregations (sum, count, min, max etc.) based on the same filter criteria.

The other form “Persons with Subform” is simple. You select a person and get the records from the data table related to the selected person.
It started with the form wizard (main form “Persons”, subform “Data”). Since that wizard is not good enough (only one form with one subform, no list boxes), I had to add a list box for the category column in the subform and a second subform showing the aggregated values for the selected person.

#13

@cpb Thank you, thank you, thank you.

I have created the Function exactly as you show. Then I have defined my report field with Type User defined Function and Function {SUM_Tu_Th].

But no joy. It prints as a blank field on the report. Any thoughts?

I thought perhaps the problem was that the field is in the report header and perhaps the User Function had not been calculated at that point? So just as a test I added the same field to the Footer – and similarly it is blank. Fields are formatted as 1234.57, the default. No conditional print – most of the General Properties are defaulted.

REALLY appreciate your help!

#14

@Villeroy, thanks.
I am pursuing the User defined Function approach for now but if I can’t make that work I will come back to the SQL,

#15

@SonomaCharles,
I do not have your database so carefully check your syntax, here is an example:
Delivery.odb (8.9 KB)

#16

@cpj, thank you so much for your efforts.

I spotted one difference right away – you specified Pre Evaluation Yes. I changed that but still no joy. I believe I have done exactly as you did:

image

image

Prints as blank (not zero, blank).

Do you see what I am missing?

#17

like I said “check your syntax”:
if([Delivery_Days]<>“MWF”;[Total];0)

#18

I have no idea how to debug this stuff. It it were C++ (my primary language) I would stick printf()'s all over the place until I figured out what was going wrong. No idea how to debug this.

#19

Yes!!! Second set of eyes.

There are no error messages I guess. If you get the syntax wrong it just “don’t work.”

Thank you, thank you, thank you.

One more total to condition, and I’ve got this. Our clients will be very grateful.

#20

Sorry, I did not think of “user defined function” in the context of a Base report. This term is confusing. Most people here understand the term in the context of Calc, where it describes a snippet of Basic code callable by a spreadsheet formula. This is also what the link in your initial posting points to. Therefore, I tried to deflect the topic from “user defined functions” because it seemed to be unrelated to Base.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.