Developer forum

Forum » CMS - Standard features » Custom reports missing data

Custom reports missing data

E-mail notifications
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Reply
Posted on 07/01/2022 22:49:56

Hello,

I don't spend a lot of time in the reports so I could be missing something silly, but when I attempt to create a custom report for the last 12 months, it only shows data for January 3rd and 7th.

 

However, if I check the database, there's plenty of sessions in the TrackingSession_202X tables.

The settings are straight forward, and I experimented with a few things.

 

This is on 9.9.2.

Is this a known bug, something that I'm doing wrong, or something odd in the data?

The site is customerportal.kentww with a .com at the end (a bit obscure on purpose). The report is under Marketing -> Reports -> Portal -> Kentww Visits.

Thanks,

Scott

 


Replies

 
Oleg Rodionov Dynamicweb Employee
Oleg Rodionov
Posted on 10/01/2022 02:35:04
Reply

Hi Scott,

Good catch! I can see the issue on solution based on last DW9.12.7 as well. New task 6270 has been submitted to investigate and fix the bug. Thanks for finding.

BR, Oleg QA 

 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Posted on 10/01/2022 22:44:02
Reply

Hi Oleg. Glad that it can be reproduced on a current solution. Thanks for submitting the bug.

 
Alexey Tanchenko Dynamicweb Employee
Alexey Tanchenko
Posted on 18/01/2022 09:17:56
Reply
This post has been marked as an answer

Hi Scott,

The behavior you described is a performance improvement feature.
In case when you want to see the reports for a specific period only, you can go to Settings > Web and HTTP > Tracking and set the period length in the bottom of the page by changing the "Table time interval" setting.
This setting is type of "Set it once and never change" :-)

When it is set to value different than "None", the reports system creates a new table in database to store the data of each period separately. It makes the table size smaller and work reports faster. But yes, it have a limitation - you cannot see the data from previous periods. All our standard reports works with the current period only. The "old" tables are still in database and you can access them for example using your own code - we have API to create custom reports.

If the setting is set to "None", all the data stores in one table.
A possible way to aviod performance decreasing in this case is to set auto-cleanup for "Tracking*" tables (which does not have "_<date>" postfix in the name) in Settings > Logs - "Database logs" section. But in this case you lose all the "old" data forever.

 

BR, Alexey

Votes for this answer: 1
 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Posted on 18/01/2022 19:45:52
Reply

Hi Alexey,

Thanks for the explanation. That makes sense as the cause.

However, wouldn't it be best for the reports to support the new strucure? It would have to join across all available tables, but I would assume that all reports should be backwards compatible with either database method. Then it's not up to me to create custom reports, or revert to the old database method.

Best Regards,

Scott

 

 

 
Nicolai Pedersen
Posted on 18/01/2022 23:44:33
Reply

Hi Scott

The entire point of using seperate tables is to limit the amount of data queried to make reports faster and not really slow... Joining the tables would kind of mis the point -- so you can just revert to just use one table. The alternative would be to choose table on each report.

BR Nicolai

 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Posted on 19/01/2022 05:37:36
Reply

Hi Nicolai,

Ok, good to know the intent. I've seen reporting systems that keep full details for the last 30 days and summarized information prior to then, giving them a multi-table structure like this, so I assumed it was for that purpose.

I have two additional questions and a feature request then.

  • If the built-in reports don't use the older tables, what is their purpose apart from building our own reporting?
  • You mention to choose table on each report. How do I do that? In the custom report section, I don't see a way to select a particular table. 

Would it make sense to have an option to span all statistics tables? That way the defaults could be focused on performance and just be the current stats table, but we could opt-in to more months if we want. 

For this project we'll likely set it with the time interval of Year. However, if I understand it correctly, this will have a stepped date which means that when it rolls over, we'll start with no history, and the history will grow throughout the year until it resets back to no history again for the cycle to continue. 

Thanks,

Scott

 
Nicolai Pedersen
Posted on 19/01/2022 11:39:57
Reply
This post has been marked as an answer

This feature was solely made to get rid of old data in simple matter. If you do not want to get rid of the data, do not use that feature...

If you do not want to change tables every year, change this setting:

Combining tables will have the same problem as running on one table, so just use one table....

Then use the table cleanup script to only have X days in that table. Or setup a job on the SQL server.

You cannot change table currently - it was an alternative proposal for the report view that it would be possible to choose table in the top.

It does not make sense to span all tables - just use one table instead. Change your setting and merge the 2 tables and you are done. You want all the data, then keep all the data.

Votes for this answer: 1
 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Posted on 20/01/2022 00:00:06
Reply

Hi Nicolai,

Ok, fair enough. Thanks for the clarification on the purpose, and your recommendation. It's certainly each enough to turn it off and implement our own cleanup at a timeframe that works for us, so that sounds like good advice.

Thanks,

Scott

 

You must be logged in to post in the forum