By using this website, you agree to our Terms of Use (click here)
Is it possible to allow a user to select multiple branches in Velixo Reports?
I know that you can put in a comma delimited list of branches, but I want a user to be able to easily check off the branches that they want to see on-the-fly.
Hi Gonzo,
Since we're in Excel with Velixo Reports, there are probably multiple ways to do this: using an Excel Add-In, using VBA code, etc.
But I prefer to use Power BI just because I like Power BI. Here's how I would do it with Power BI.
Since my demo data only has two branches, I'm going to use GL Account to illustrate this, but you would apply the same technique to Branches.
Let's say we have the following Velixo Reports formulas where the formula is shown to the right of the formula result like this:
You can see that the last formula combines all four GL Accounts with 40000,40010,49000,50000. But we want to make that 40000,40010,49000,50000 dynamic so that a user can randomly select the accounts that they want to be included.
I propose to use a slicer and the Power BI features in Excel 2016.
1. First we need to load the GL Accounts into the Power BI portion of Excel 2016. Go to Data -> Get Data -> From Other Source.
2. Put in the following URL, but replace http://localhost/acumatica with the URL for your Acumatica instance and remove /company if you only have one company. And click OK.
3. Choose Load -> Load To...
4. Select Only Create Connection and Add this data to the Data Model, then click OK
5. You should see it get loaded in on the right-hand side like this
6. Now we need to create a slicer which you can do with Insert -> Slicer on the ribbon. Then choose the Data Model tab and click Open.
7. Choose Query1 -> AccountCD and click OK
8. Now you should have a slicer in your spreadsheet containing a list of all of the Accounts like this:
9. Next create a Pivot Table by choosing Insert -> PivotTable on the ribbon, selecting Use this workbook's Data Model, and clicking OK
10. Right-click on Query1 and choose Add Measure...
11. Put AccountList in the Measure Name field, put =CONCATENATEX(Query1,[AccountCD],",") in the Formula field, and click OK.
12. Once you create the measure, you can delete the sheet with the Pivot Table because we don't need it anymore:
13. Now we need an Excel formula that can read the selected values in the slicer and put them in a comma-delimited list. We'll use an Excel formula and the measure that we just created to do it. Put this formula in a cell:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[AccountList]",Slicer_AccountCD)
14. Then select multiple Accounts by holding down Ctrl on your keyboard and clicking on each Account in the slicer. You should see the comma-delimited list of your selections appear in the cell that we just created.
15. Now we just need to take that "=CUBEVALUE("ThisWorkbookDataModel","[Measures].[AccountList]",Slicer_AccountCD)" formula and use it to replace the "40000,40010,49000,50000" part of our formula that we had in the very beginning:
Now, you can see how the slicer impacts the number that we had in the very beginning, including being able to dynamically select the four GL Accounts that cause the 48,552,406.21 number to get calculated.
Wow, I didn't realize that you could do that with Excel 2016. I think I'll give it a try. Thanks!
Online Members
