AutoCount Accounting Report Designer: Tag Function (Advanced) – Get Single Value and Execute Scalar

Home » Report Designer » AutoCount Accounting Report Designer: Tag Function (Advanced) – Get Single Value and Execute Scalar
Report Designer No Comments

This guide / post will be discussing one of our latest additions of Tag Function into AutoCount Accounting Report Designer – @GetSingleValue and @ExecuteScalar. This function is only available 1.8.14.118 and above. Version before this will not work if you try to implement this Tag Function.

 

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.

tag1

 

The main purpose of this 2 Tag Function is to allow one to get the column / fields that are not available in the field list of Report Designer. Usually this will require scripting to be done in earlier version, but after adding 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 at 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 left it blank.

 

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

tag2

 

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

tag4

 

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

tag3

 

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

tag5

 

6. Save your report and proceed to preview.

tag6

tag7tag8

tag9

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.

 

@ExecuteScalar:

 

Format for Execute Scalar:

@ExecuteScalar:SQL Statement:String Format:Parameter

 

For Execute Scalar Tag Function, this will require and 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.

tag10

 

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

tag11

 

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

tag12

 

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

tag13

 

6. Save and preview report.

tag14

tag15

tag16

tag17

 

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

tag18

 

8. Save and preview again.

tag19

 

Summary:

Both of 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 Function will be Execute Scalar would require the person to have knowledge on 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 will be listed down here:

 

- Quantity

- PurchasePrice

- SalesPrice

- Cost

- Currency

- CurrencyRate

- MemberPoint

- Volumn

- Weight

 

* Please follow exactly as provided above, no spacing.

 

You can download this guide here: Tag Function (Advanced) – Get Single Value and Execute Scalar.