I have existing reporting that measures branch sales against prior year and budget for the previous business day and month-to-date. We recently acquired two additional branches and do not intend to load a specific budget into Phocas. Rather, budget is set to equal actual results. To do this currently, the report is extracted into Excel and the budget column is manually updated for the two locations to point to their Prior Day and MTD columns respectively.
In SQL, I would write a CASE statement that finds the new location and sets the values accordingly. I’m looking for a way to this in Phocas (custom mode) so the report doesn’t require manual intervention ahead of distribution.
You have budgets loaded for the original branch, and you want to add yesterday and mtd budgets (equal to the actual values) for the new branches. Am I understanding this correctly? If so, create two new calculated columns in Phocas designer. this will take a little massaging depending they type of your existing columns (integer, text, etc). You may need to CAST to whatever data type you are comparing, or use try_cast([column as xxx) if you have some blank values. You can use the same formula for both, just change out which columns you are using (previous or mtd).
CASE WHEN [branch]<>“original branch” THEN [previous day sales] ELSE [budget_column] END
Not exactly, I have existing budgets at the branch level loaded as a stream. The report is already designed via ‘custom mode’ to look at prior day and MTD vs budget. For the two new locations, I want to set budget equal to actual for the remainder of the year (I will load new branch budgets for 2020 that will include the new locations)
yea, I think you still have to do what you’re trying to do in the designer with calculated columns though. when you load your budgets for the new branches next year, you’ll just remove (in designer) the calculated columns, and remap the measures back to the original [budget] column (because there is data in that column now for the new branches).
The new columns you add now will end up showing the budgets for the existing branch, and the actual sales values for the new branches. Until next year when you load the budgets for all, delete the calculated columns and then remap the measures… I think I understand what you’re doing, am I missing something?