Reports - date/time parameters not pulling information

[lomixture]

Hi all,

I am back in work after an extended period of leave, and have come back to multiple (broken?) updates within our instance, some of which have gone unnnoticed for months.

At present, I am trying to work out how to fix a report which should run on a date prompt but no matter what combination of options I choose the report is pulling blank each time. I've taken a screenshot below of how it is built, is there anything glaringly obvious I've done wrong as every single column is empty when run!

[James Ainsworth]

Hi @lokent

Welcome back.  

Have you tried running the report without any date prompts?  This would help pinpoint if the issue is with one of the other filters or the date selection.  

[lomixture]

James,

You're right! I deleted the date filter and it's still pulling empty. How odd! 

We have one report that is working brilliantly, so I'm thinking I may just make copies and change the date fields.

Thanks for helping me work through that :)

[James Ainsworth]

Happy to help.  With the Date options removed, you'll just need to look a the remaining options.  As they are all AND statements any request would need to match all the statements.  My guess is that it is that last statement to do with asset types.  Not sure how you are doing the join to the table storing the asset types.  Should these be assets rather than asset types?

[lomixture]

James,

I'm not sure on the assets point, I'll take a screenshot of where they're currently sitting in the system and see what we've got them built as... my confusion comes from the fact all these reports worked perfectly until March, and then seem to have broken. I was told there was an update in Service Manager around that time but can't find anything that would link this issue to that.

Lauren

[lomixture]

 

They are all sitting within Asset Management here, so we could assign them to the job for reporting purposes. I believe this was done when we first built in 2017/18 as we didn't have the supplier set up atm.

Lauren

[lomixture]

21 hours ago, lokent said:

 

They are all sitting within Asset Management here, so we could assign them to the job for reporting purposes. I believe this was done when we first built in 2017/18 as we didn't have the supplier set up atm.

Lauren

@James Ainsworth apologies, forgot to tag you in the response

[Victor]

@lokent how are you joining "Asset Types" data (table) with the "Requests" data (table)?

[lomixture]

 

Forgive me, I rarely built reports previously and so I think this is what you're asking for? Doesn't appear to be any joins

[Victor]

@lokent 

3 minutes ago, lokent said:

I rarely built reports previously

Ok, no worries. Download the report definition file and send it to me, e.g. post it here. If there are any concerns about private data (although is a report configuration) then you can send the file via PM.

[lomixture]

@Victor sent via DM!

[lomixture]

All - further internal investigations have been conducted (I feel like Poirot) and I've noticed the following today:

These two reports, were both run today. Exactly the same information with the following differences:

34989 - did NOT include a 'only search status.onhold' filter, and covered a 3 month period. Report was blank.

34988 - did include an 'only search status.onhold' filter, and covered the time period from April 2021 - July 2022. Report pulled information, hurray!

 

As you can see, the Process Time was double for the seemingly shorter report (albeit potentially with far more information due to all status types being pulled). Am I right in thinking the later report is failing because its taking too long to process i.e. over 15 seconds? I'm sure I was told the process time was lengthened to 30 seconds but perhaps not.
 

If this is the case, is there anything we can do about it? I know we currently have over 60k records on our system and are due a data cleanse...