I need to provide facility in reports wherein the users can filter the result by certain dimension values (not the global dimensions).
Lets take the example of ‘Amount’ in Item Ledger Entry. User should be able to get the break up of amount for different values of a particular dimension.
Any suggestions on how to achieve this.
Look carefully at the need, and see if you can rethink it. Basically as you have asked now, you would need to add Global Dimension 3 to all the tables. This is not a big job, but its something that has a lot of performance implications, so should only be done if there is no other solution.
When I’ve had similar requests, I take the approach of building a work table that combines the desired data fields. In this case, the work table would be fields from Item Ledger Entry and Ledger Entry Dimension. Create fields in the work table for the specific dimensions desired. Use a report object to populate the work table before printing the records in that work table.
Sam Bartley
Service Dynamics, Inc.
In item ledger entry table, i have created a flowfield with lookup to LedgerEntrydimension table and then filter the ledger record with this field in my report. i am getting correct data, but is this the optimal way.
This question can not be answered as it stands, since it really depends on your data and the output you need.
If you have the need to analyze a handfull of item ledger entries, and have 3 or 4 dimensions on each, and you run the report once per monthm then this may be the best solution. On the other hand, if you have 10 million entries and 12 dimensions, and you print out a daily summarized report of this data, then it probably wont work.
You need to add up the advantages of creating a proper data structure, vs the cost of running a report based on the wrong keys and sorting because of the data model not matching the requirement. On top of that, it also depends a lot on Native vs SQL.