Hi All
I continuously run up against this problem: I'm attempting to build a customer sales report showing our three dimensions (Sales Person; sales type and sales area). When I link the default dimension table to the customer table so as to see all three I get the following message
"Jet can not find any relationship between the 'Default Dimension' table and Advanced Dimensions. there must be at least one advanced dimension entry for a table in order for Jet to find the relationship. Either there are none or Jet does not support Advanced dimensions for this table"
Should I give up? Or is there a clever trick I'm missing?
cheers
Matthew
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Matthew,
You shouldn't have to link the Customer table to the Default Dimension table in order to return of filter by dimensions on the Customer table. You can just specify the dimension names as fields directly in the Customer table and that should work. If you are still having problems doing that, can you show us the functions you're using or upload the report?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes
That's what I thought but when I use the fields from the customer table I get the same data returned for both SALESTYPE and SALESAREA. I've attached a copy of the design mode of the report
Thanks for the help.
Matthew -
Jet Reports Historic Posts Hi Matthew,
I've attached a small test report for you. It retrieves the values of the 2 dimension both from the Customer table (letting Jet make the link and get the value from the Default Dimension) and directly from the Default Dimension. The results for the first set of values should be the same as the results for the second set of values. Can you run this report and confirm whether this is the case?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes
Yep. That's what it does. I've attached it so you can see what the result was..
Matthew -
Jet Reports Historic Posts Matthew,
You need to actually run the report by clicking Jet -> Report in order to get the real results. What you sent me is in Design mode and results in design mode are not guaranteed to be correct. If you run the report by going to Jet -> Report, what do the results look like?
Regards,
Hughes -
Jet Reports Historic Posts Sorry Hughes
Stupid mistake. I'm trying to do too many things simultaneously here.
No, the results are different. Columns E & F from the customer table are identical to each other. Columns G & H from default dimensions are different from each other but also, weirdly in some circumstances column H is different from column E. (see attached)
This is clearly not right but I'm also seeing the same in NAV so I've logged it as an issue with our developer. This is sort of a side issue however. The information I'm after for my report is is columns G & H and I can't seem to get Jet to pull it through. Where am I going wrong?
M :( -
Jet Reports Historic Posts Hi Matthew,
So my suspicion here is that what we are seeing here is a result of one or both of these being Global Dimensions. If a dimension is a global dimension and the table you're trying to pull it from has the Global Dimension 1 Code and Global Dimension 2 Code fields (as the Customer table does), then Jet would use the data from the Global Dimension 1 Code and Global Dimension 2 Code fields rather than the data in the Default Dimension table because it's faster. Theoretically, the data should be the same in both cases, but it appears that in this case it's out of sync for some reason. Your NAV developer can probably figure out why it's out of sync and fix it for you.
As far as your report, in order to get the data from the Default Dimension table, first you'll need to add the No. field to the report. Then you should click the "Add Formula" button and then type a formula very similar to mine in the same report only with a reference to the No. field something like this:NL(,"Default Dimension","Dimension Value Code","Table ID",18,"No.",[@[No.]],"Dimension Code","SALESTYPE")
Does that help?
Regards,
Hughes
Please sign in to leave a comment.