One Report Does It All With Alpha Anywhere or Alpha Five
This is a technique that can save you a lot of time and effort when creating Alpha Anywhere reports. No longer is there a need to create a report for every single type of scenario. We are going to show you how to create versatile reports capable of being used in many different scenarios.
This video will get you more details:
Check our Custom Software Development Services.
Complete control of Alpha Five report while using MS SQL database:
It is completed with minimal code using Alpha Anywhere, in which controlling the report is primarily done via MS SQL Stored Procedure. You may use other databases such as MySQL, Oracle etc... The report’s data is created on the fly based on the selected values in the parameters’ form.
Hide and Show groups in Alpha Five report’s engine:
It is required to create the same number groups in the Alpha Anywhere report as the same number of controls where you select data in the parameter’s form. See video above for more details.
This way it allows us to cover all scenarios and assign fields on the fly to these groups based on some logic in your MS SQL stored procedure code. For instance: if no field is assigned to group 2 then its value should be NULL. With this technique you have total flexibility to assign fields dynamically to any group.
Assign dynamic fields to Alpha Anywhere report:
In the below example you can see how to assign fields to groups based on different conditions. The IIF() statements are actually part of MS SQL code and it shows you that based on different conditions, fields are dynamically assigned to multiple groups. This kind of capability provides the user tremendous amount of flexibility.
SELECT CustomerFK, @BudgetYear AS BudgetYearParam , IIF(EaDisplay > 0, EaDisplay,EaAcctDescription) AS Grp1 , IIF(EaOrigAppr IS NOT NULL,EaOrigAppr, (EaCurrAppr + EaPyWApp)) AS Grp2 FROM Accounts
In the below screen shot there are 7 groups. Your individual logic may use only Group1 and Group7 to dynamically assign fields. That means that the rest of the groups, Group 2 through Group7 will read the value as NULL and will not be shown in the report.
See below an example of the report’s body:
Manage empty spaces in groups in Alpha Anywhere report:
All fields in each group should be wrapped with a Conditional Object so each space will be eliminated when the group’s value is NULL. Remember, if no field is assigned to a particular group then its value should be NULL in its data column.
In the Condition Expression of the Conditional Object we need to add the following code for each group. Seg4<>"" so the label for each NULL group will not be shown. Seg6 is for Group 6 in my scenario. You may enter any name that works for you for the group’s label.
Go Back to Our: Main Blog
Written by: Doron Farber - The Farber Consulting Group, Inc