Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Problem with Drill down
pruebasEx
Member Posts: 51
I have a field in a table that sum another (I put the calcformula in the property of calcformula of the field). Then I show in a form.
In my database it works, but in the database of the customer not. The objects are the same in bouth case (form with same filter, same calcformula...etc)
In my database.
Customer
In customer when you click on drill down of this field, you can see the values but, drill down field is still zero ( 0 ).
I repeat, the objects of two databases are THE SAME.
Thanks.
In my database it works, but in the database of the customer not. The objects are the same in bouth case (form with same filter, same calcformula...etc)
In my database.
Customer
In customer when you click on drill down of this field, you can see the values but, drill down field is still zero ( 0 ).
I repeat, the objects of two databases are THE SAME.
Thanks.
0
Best Answer
-
pruebasEx
Member Posts: 51
I do not know if anyone said anything similar, but the solution to this problem was to remove the key that was being used by the SUMINDEX.
1-Disable de check of the Key.
2- Compile
3- Enable check again.
4- Compile.
When I try to use a new variable with the value of the bad SUMINDEX, I got an error that seems to be a bug or an inconsistence with SQL. The error was about the option NOEXPAND from SQL query.0
Answers
Export the object. Then remove the SIFT key that is used (or all if you don't know). And then import the object again.
If I remember correctly, the problem might popup if you run a script that deletes the 0-value records in the SIFT-table.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
It's a 2009 R2 versión....
Versión 6.0 R2 (6.00.32012)
The properties of Myamount are
Data Type -> Decimal
Enabled -> YES
Init Value -> Undefined
Field Class -> FlowField
CalcFormula -> Sum("Ticket Header"."Total Amount" WHERE (H=Field(Time Filter), Date=(Date Filter), Code = Field(Code), store=Filter()No)))
...
...
...
I repeat, In my database It works, In the customer not. I realized they are the same objects, same code....So I dont understand....
In the matrix I have a CALCFIELDS of variable MyAmount (MyAmount has a calcformula set)
Is it definitely a problem with the CALCFIELDS or is the problem somewhere else in the code.
Did you try CALCFIELDS directly on the table, in a page with SourceTable = that table? What does the default drilldown page show (Property DrillDownPageID, no Code in the OnDrillDown trigger)?
Did you try to delete metadata for all objects involved and recompile them (table beeing summed, table containing the FlowField, Pages, Codeunits, ...). I have seen cases where a recompile did not correct an issiue, without manually deleting object metadata beforehand.
Do you use the same build in both databases? If not, try the other one.
Don't rely on others presenting you with a solution. Narrow down the real cause. Report back what you have done, what your results were, and what you concluded from it.
I have tried all the things you propose, CALCFIELDS directly on the table, see if is default drilldown, or even the DrillDownTrigger.
But the only thing I cant try at the moment is delete metadata (how can i do that? I am connected to the customer so, I cant delete any object.)
I think is a problema with the CALCFORMULA/FLOWFIELD but...I cant test anything....or find anything strange...
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Both have same filters. Exactly. And they are same object. I import on the customer database the objects of my database.
Have you checked the collation on customer's database ? Is it case sensitive? Is is the same as at yours?
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
How can I check that?
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
This is important stuff, because either the sum is incorrect, or the selected records beieng summed are incorrect, or the filter is incorrect ...
By the way, did you check the SQL Table's and Views integrity? Consult the DBA in charge, if you don't know how, or don't have access.
On 2009 Classic you may also try File > Database > Information > Table > select the target table of the flowfield, then Check (you may skip relations) and Optimize.
Don't you have a test database at the customer's site? Is the failing code life? The customer should grant you time to fix it.
You may try to export the objects as text, import them from text and then compile them. This might make the deletion of metadata redundant.
Also try restarting the service. Many strange things have disappeared this way.
Meta data tables are
2000000071 Object Metadata
For Pages there are many more, but i think that's irrelevant here.
Did you investigate the executed query on SQL used to execute the CALCFIELDS?
You can test by changing little bits, e.g. adding or deleting flowfilters. You can also delete and or (re-)create sumindex fields.
None of these goes with some interruption for other users.
Ok Thanks.
Same collation. Accent Sensitive (YES) and Case Sensitive (NO) in both
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
No...triggers are empty too...
Try to edit keys on the table with the source data (the table referred in the flowfield definition), find all the indexes which could be used by your flowfield, and untick MaintainSIFTIndex on every one of them. This will force NAV to calculate values by summing up the actual records from the table, rather than summing up records from associated indexed views.
It may be a stupid question but have you tried to copy the data from the drill down details form and sum them up in Excel to ensure they don't add up to 0?
Your screenshots doesn't show the headers on the drill down form - double check that what you are displaying on the drill down is what you actually summing up in your flowfield definition.
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Firstly what is the code that calculates the numbers
Then you need to put the data in Excel and add it up and show that.
You are just hiding too much information to be able to get good help. From the screen shots these could be two completely different pieces of information.
We have to remember that In my database everything works ok, and the objects are the same...so obviouly yes, what I'm displaying on drill down is what I'm summing up
(and the same for the data sum is not Zero.)
Thanks for your help!
Ok I'll try to update the post!
Thanks!
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Yes of course!.
I only assume that I have same objects with same code, same object properties, same formulas etc... Thats why I thought that...
As I've mentioned in my previous post the SQL server is usually the very very very last thing to blame for data errors or inconsistencies in NAV. Having said that there were some problems with indexed views (the technology used behind NAV SIFT from NAV 5 SP1 onwards) on the SQL Server. There was a bug in some SQL versions (2008, 2008R2 and 2012) which caused incorrect result an indexed view was queried. You can read the details here
The KB article mentions the NOEXPAND query hint, but this hint is used by NAV when querying indexed views. On the other hand the error was manifesting itself only when the base tables have been updated - so straight after loading an object with a key having a SIFT defined on it, or after disabling and re-enabling the SIFT index (both operations aer rebuild underlying indexed views) the returned results would be OK.
The article also mentions a problem with indexed view based on two tables - which is never the case in NAV - but it does not contain information if indexed views based on a single table have been affected. My guess is that single table based indexed views were probably not affected - therefore NAV was not affected by this bug.
Anyway - the problem is quite old and your customer should have its SQL Server patched long time ago (in case of SQL2012 the Service Pack 2 included the fix, but before SP2 was released the fix was available in Cumulative Updates)
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
I up this again to check if it's possible test this.
How to know if it's a SIFT KEY??
Where is the SIFT table?
If I understood correctly, steps are...
1- Export
2- Remove SIFT keys
3-Import??
4-Script 0-values??
Run SELECT @ VERSION in SSMS (use two @ chars, no space between @ and VERSION, Mibuso doesn't display two @ signs for some reason), this will give you your SQL server version and build number. Compare that with the list of SQL Server versions listed in the KB article, and you will know if your NAV installation is potentially affected or not.
If it is not don't bother checking results by querying SQL views. Checking results against VSIFT views is tricky, especially when you don't have the experience. You need to know which one to use, and you need get your predicates correct. They need to be exact equivalent to filters set in NAV to get the same results. They need to be exact equivalent to what NAV sets, not to what you think NAV sets on the record var.
If you still insist on checking against values stored in SQL views then the SELECT SUM(SUM$MyVarible) FROM MyTable$VSIFT$1 ... is pretty much it, if your table is called MyTable, the field used to add up values is called MyVarible, and it is defined on the second key on your table. Then you need to add your filters. Time='1754-01-01 12:00:00.0' is an equivalent of SETRANGE(Time, 120000T) in C/AL, Date>='2016-12-01 00:00:00.0' AND Date<='2016-12-02 00:00:00.0' is equivalent of SETRANGE(Date, 01122016D, 02122016D), and so on. The Cod and Shop field filters are pretty straight forward.
By the way - you've written in your previous post that you are on version 2009 R2 build 321012 (which is 2009 R2 RTM version), therefore what kriki said about possible SIFT data corruption does not apply to your installation.
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
URL FIXED
I search for the SQL version of the customer and compare to our version.
Our version SQL
Customer's version
How it's possible our SQL works if it's older than customer's version??
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Ok. So, SQL discarded...?
I update the post with a bit of information.