Question: How to apply Get single value or execute scalar into report?

Version: 1.8 / 1.9 / 2.0

Advanced Tag function is only available in version 1.8.14.118 or later. This function will not work in earlier versions even you try to implement it.

In order to fully utilize this 2 Tag Function, knowledge of AutoCount’s Database table and column name is required. You can download and install the SQL Management Studio to browse through all the table and column available in AutoCount’s Database.

The main purpose of this 2 Tag Function is to allow to get the column / fields that are not available in the field list of Report Designer. Usually this will require scripting in earlier versions, but with this improvement, there is no need for scripting anymore as the knowledge of Table Name and Column Name is sufficient.

Let’s start with an example: A text type UDF is created for Debtor named SCode and this field is not available in our Debtor Statement Report. So, this example will show how to extract this UDF field out and display it in our Debtor Statement Report.

GetSingleValue

Format for Get Single Value:

@GetSingleValue:Table Name:Column Name:Condition:String Format:Parameter

From the format above, the table name, column name and the condition column will follow the exact names from the database. String format will be any C# supported format such as “n2″, “n3″ and also AutoCount’s decimal formats. The parameter will be using the column name which you can find at our Report Designer Field List. For the format path, if the column field is text, we can just leave it blank.

1. Create a text type UDF at AutoCount Management Studio by the name of SCode, caption Short Code.

2. Login AutoCount Accounting, key in some data for our Debtor Maintenance’s UDF – SCode (Short Code) and proceed to Report Designer for Debtor Statement.

3. Add in a new blank label somewhere at the Header of the report so that result could be displayed after pulling of data.

4. Now here is where the Tag Function comes in, since we want the SCode UDF From Debtor Maintenance, we will need to know the table and column name for Debtor Maintenance in the database. In order to fulfill the format of Get Single Value, we will need to know the UDF SCode column name, Debtor Code column name and Debtor table name. For this case it will be UDF_SCode (Column Name), AccNo (Column Name) and Debtor (Table name).

5. Fill in the table and column name according to the Get Single Value format into that particular label’s Tag property. Eg: @GetSingleValue:Debtor:UDF_SCode:AccNo=?::AccNo

6. Save your report and proceed to preview.

ExecuteScalar

After completing Get Single Value, another example will be done through Execute Scalar. This time a new decimal UDF – Number will be created for Debtor and the next example will show how to pull it by playing around with the String Format as well.

Format for Execute Scalar:

@ExecuteScalar:SQL Statement:String Format:Parameter

For Execute Scalar Tag Function, this will require additional knowledge to use it – SQL Query. Similar to Get Single Value, the string format rules will be the same and the Parameter Column Naming should follow the Report Designer column’s name.

1. Create a new decimal UDF – Number for Debtor at AutoCount Management Studio.

2. Again key some data for this field at Debtor Maintenance.

3. Now add in another new Label at Debtor Statement Report Designer.

4. So now we will need to build a SQL Query to be filled in our Execute Scalar Tag Function Format later. For this case it will be as like: Select UDF_Number From Debtor Where AccNo = ?. Also, assuming that we only want 2 decimal digits for this field even though the UDF we had created has 6 decimal points, so “n2″ will be our string format.

5. Fill in the label’s Tag value under Property that is used to display this field according to the format of Execute Scalar. Eg: @ExecuteScalar:Select UDF_Number From Debtor Where AccNo=?:n2:AccNo

6. Save and preview report.

7. Let’s play around with the string format, this time we will like to follow AutoCount Decimal Setting of Currency Rate – 4 decimals. Change the “n2″ to “CurrencyRate”. Attach image to show the current Currency Rate Decimal.

8. Save and preview again.

Summary:

Both these Tag Functions well serve the same purpose which is to pull fields or columns which could be not available in the Report Designer (usually UDF). The difference between these 2 Tag Functions will be Execute Scalar would require the person to have knowledge of SQL Query while Get Single Value does not. However, the advantage of Execute Scalar is by building your own SQL Query, joining up multiple tables to extract data is possible while it is not on Get Single Value. Personally, Execute Scalar is recommended if SQL knowledge is not a problem for you.

And finally, a list of AutoCount Decimal with the wordings that you can use for the String Format of Tag Function is listed below:

- Quantity

- PurchasePrice

- SalesPrice

- Cost

- Currency

- CurrencyRate

- MemberPoint

- Volumn

- Weight

Note: Please follow the format exactly as provided above, no spacing.

By: JS 150706, KM 180309, P180322