I’m pretty new to both Epicor and Phocas, but very excited about the functionality Phocas has. Loving this software already.
I’m trying to create some visualizations for our sales team and I need to compare sales numbers by region. I had to add the property “region” to the dimension Sales Territory manually as it wasn’t there initially.
As you can see, I’m getting multiple of each region, where I wanted to see just four entries, one for each region.
I can include screenshots of the database design too, if that helps. I suspect something is joined wrong somewhere but I haven’t been successful in figuring it out.
Thanks for your reply. I’ve been unsuccessful in creating a separate dimension for Region Code (but hope someone can point out what I’m missing).
The two streams I’m working with are built off of table f_OrderDtl, which does not have a region field. At this point, I can bring the table d_Territory into this stream (which has Region Code and Region) but d_Territory does not have the field CompanyCustNum, which is what Phocas is using to join the tables together.
I’m thinking there has to be somebody out there using Epicor data and Phocas who has accessed Region Code but I’m just stumped.
Does your f_OrderDtl table have any location identifier that can be mapped to your regions or territories? You may need to create a location cross reference.
We use an Epicor ERP product call Eclipse. Not sure how similar or different it is to your system, but we have several levels we like to aggregate to, including region. To do this, we created a cross reference in Excel that maps all our location levels into a simple hierarchy - in our case, we have individual branch locations (lowest level) that roll up to markets, and markets roll up to regions. The hierarchy is setup as a dimension and we map our streams to that dimension when the location is relevant.
Ahhh that’s very good to know. Is your Excel cross-reference table external to your ERP system? Or do you maintain it from within Eclipse?
To be honest, I’m not super familiar with Epicor or Eclipse. I like your idea - even if that’s not my long term solution I think it will definitely work for now.
Might be worth a call or email to your support desk, they can see the tables and data and will be able to come up with a solution for you. I am one of those people that need to be able to see the data in front of me to come up with an answer.
I like @Mike 's idea. You could just create an Excel workbook and upload it into your Database Designer, then work off of there for cross-referencing. The only issue with that is that you need to update it and re-upload in the future if any of your locations or regions change, unless you have an instance of Sync that points to the Excel file in a folder on your computer/server, in which case you just need to update the workbook.
The cross-reference is external. Our locations don’t change often, so I update only when there is a change to regional alignments or other factors like an acquisition. I bring it in thru Sync and use it in any database where location is relevant.
