I've been trying to create a report that compares budgeted sales and actual sales for any given period. What I'm doing is creating a table using the Table Builder for the Item Budget Entry table and one for the Item Ledger Entries table (So two separate tables). In both of these tables I want to have dimension data (Salesperson and Area in my case). In NAV these tables have a field called Dimension Set ID, which links to the Dimension Set Entry table.
I was able to link the dimensions by copying the Dimension Set Entry table into Excel and make a primary key from that table (=B4&", "C4). I would like to be able to do this in the Table Builder so it could automatically populate every time I refresh the report, but I cannot figure out how to link the dimensions to these tables in the Table Builder because of the composite key from the Dimension Set Entry table. Is there a way to reference a composite key from another table using the Table Builder?
Also, I was able to create a dummy report where I combined the two tables into one. In this composite table, I had two fields for Salesperson (Budget and Actual), and two fields for Area (Budget and Actual). When I go to analyze this in a pivot table, however, I can't figure out how to show Budgeted Sales Amount per Salesperson AND Actual Sales Amount per Salesperson at the same time (1 click of a slicer). Instead, I have to click the Budgeted Salesperson slicer that I want, look at the Budgeted Sales column, and then clear that filter, click on the Actual Salesperson slicer, look at the Actual Sales column. I would really like to be able to show both at once.
This is a big mountain to climb, but if anyone has advice I'd appreciate it.
Date
Votes
3 comments
-
Jet Reports Historic Posts Hello.
Can you send us an example of what you have so far? Even if its just a mock up with example data. Its just hard to follow when its all in text.
Thanks,
Jason -
Jet Reports Historic Posts Regarding your pivot table question - the only want to filter by the salesperson and show both values (budgeted and actual), is to use a single column for your salesperson name (see below)
SALESPERSON | BUDGET AMT | ACTUAL AMT
RICKY R | 15,000 |
RICKY R | | 8,000
if you were to pivot this table, on the salesperson name, you could have Budget and Actual columns side by side in the table.
You'll need to build a helper column or something so that you can get the salesperson's name into a a single column (from both the budgeted table and the actual table). Unless you can link one of the values (budget or actual) to the salesperson name from the other table? -
Jet Reports Historic Posts I actually tried creating the same report using Enterprise instead of Essentials, and I was able to link the dimensions to both fact tables (Item Budget Entry and Item Ledger Entries). Also, I found out that if you specify relationships between the 'dimension tables' and the 'fact tables', you can slice multiple fact tables by having the slicer come directly from the related dimension table (In this case, Dimension Set Entry). Further issues come up when you deal with the composite nature of Dimension Set Entry, but that's a post for the Enterprise forum.
Please sign in to leave a comment.