Can I use the QBF selection criteria from a form to limit rows in a report?

Can I use the QBF selection criteria from a form to limit rows in a report?

I like the automatic QBF facility that comes with all default Power Object applications. Is there any way to pass the selection criteria to a report?

Yes, there is, but it may not be intuitively obvious. In trying to use a form to specify selection conditions for a report through its QBF facility, you run into two obstacles. The first obstacle is that you do not have any way of getting to the selection clause created by use of the QBF facility in a form. The second obstacle is the limited way available to impose selection criteria on a report.

To overcome the first obstacle, you will need to use some code and possibly the help of a temporary database table. Although it is true that you cannot get access to the WHERE clause created by the QBF activity, you do have access to all the rows that were returned as a result of the QBF selection criteria. You will have to create a list of values that uniquely identify the rows in the recordset for the form.

The unique identifiers could be either the unique key value for the row or, if you are using an Oracle database, the ROWID for the row, which not only uniquely identifies the row, but is a very fast way to access rows. If you are going to use a list of ROWIDs, you will have to create a temporary table in the database with a single column to hold the ROWIDs. You will first make sure that you have all the rows in the database by using the FetchAllRows() method for the recordset. You will then delete all the rows in the temporary table with an EXEC SQL statement.

After clearing the table, you will walk through the recordset and write the ROWID to the temporary table. In the example below, I have used an EXEC SQL statement to accomplish the insert. When you have completed writing all the values, you COMMIT the inserts to the database.For this example, I am using a form called frmMain and a temporary table called TEMP_ROWID that contains a column called TEMP_ROWID. The code for this is:

        DIM nCount As Integer        DIM nPointer As Integer        DIM nROWID As Integer        DIM sSQL As String        frmMain.GetRecordset().FetchAllRows()        EXEC SQL AT MLDATA delete from TEMP_ROWID        nCount = frmMain.GetRecordset().GetRowCount()        nPointer = 1        DO WHILE nPointer <= nCount                nROWID = frmMain.GetRecordset().GetColVal("ROWID")                sSQL = "insert into TEMP_ROWID (" & nROWID & ")"                EXEC SQL AT MLDATA :sSQL                nPointer = nPointer + 1        LOOP        EXEC SQL AT MLDATA commit

You have all your ROWIDs safely in their temporary table. Your final step is to call the report and set the limitation on the rows for the report. The only way to impose a selection condition on a report is through the DefaultCondition of the report. Set the DefaultCondition of the report to "ROWID = (SELECT TEMP_ROWID FROM TEMP_ROWID)" . The subSELECT will return the ROWIDs from the temporary table and limit the rows in the report to the same rows from the original table.You could accomplish the same effect by building a string of all the ROWIDs and setting the DefaultCondition for the report to "ROWID IN (" concatenated with the string of ROWID values and terminated with ")". This method might be a little more efficient, since it would not require a query to the database to fetch the ROWIDs, but I would be worried that you might have too many values and exceed the size limit for the DefaultCondition property.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes