Report Design: Advanced Tag Function – Get Single Value & Execute Scalar

From AutoCount Resource Center
Jump to navigation Jump to search

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


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.


Adv tag1.png


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.


Adv tag2.png


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.


Adv tag3.png


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


Adv tag4.png


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


Adv tag5.png


6. Save your report and proceed to preview.


Adv tag6.png


Adv tag7.png


Adv tag8.png


Adv tag9.png


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.


Adv tag10.png


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


Adv tag11.png


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


Adv tag12.png


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


Adv tag13.png


6. Save and preview report.


Adv tag14.png


Adv tag15.png


Adv tag16.png


Adv tag17.png


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.


Adv tag18.png


8. Save and preview again.


Adv tag19.png



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




Go to menu

IconAcc20.PNG AutoCount Accounting 2.0


IconAcc188.PNG AutoCount Accounting 1.8 / 1.9
Wiki-Payroll.png AutoCount Payroll
Wiki-POS.PNG AutoCount POS
Wiki-AOTG.PNG AutoCount On The Go
Wiki-Accounting-Plugin.png AutoCount Accounting Plug-In Documentations