SQL queries of the existing schedule breakdown report

Posts about the simPRO API. Help/support/how-to etc.
Forum rules
  • Check the documentation first. Before posting questions, please check the documentation to ensure it doesn't already cover what you need.
  • No spam. All automated messages, advertisements, and links to competitor websites will be deleted immediately.
  • Post in relevant sub-forums only. Messages posted in the wrong topic area will be removed and placed in the correct sub-forum by moderators.
  • Respect other users. No flaming or abusing fellow forum members. Users who continue to post inflammatory, abusive comments will be deleted from the forum after two warnings are issued by moderators.
  • Harassment. No threats or harassment of other users will be tolerated. Any instance of threatening or harassing behavior is grounds for deletion from the forums.
  • Adult content. No profanity or pornography is allowed. Posts containing adult material will be deleted.
  • Bandwidth. All images and signatures must be 500 x 500 pixels or smaller. Posts containing over-sized images and signatures will be removed.
  • Illegal content. No re-posting of copyrighted materials or other illegal content is allowed. Any posts containing illegal content or copyrighted materials will be deleted.
Post Reply
Regine
Posts: 2
Joined: Tue Nov 07, 2023 10:45 am

SQL queries of the existing schedule breakdown report

Post by Regine » Tue Nov 07, 2023 10:58 am

Hi,

I found that the schedule breakdown report may not be displaying all rows when I download the report. As a workaround solution, we are downloading smaller chunks of the data and combine them by myself.

Therefore, I am looking for other ways to have the same report in SimPRO BI reporting. Is it possible for me to have the exact same SQL queries to be used in "BI Reporting" to generate the same report in live system?

Thanks,
Top
dmpchch
Posts: 36
Joined: Mon Aug 26, 2019 2:52 am

Re: SQL queries of the existing schedule breakdown report

Post by dmpchch » Fri Nov 10, 2023 2:16 am

You don't want to look at Live (now) data?

BI Reporting is based of a replicated database so you won't see today data.
My BI Reporting data is generally >24hrs old, I'm guessing this will depend on location
Top
Regine
Posts: 2
Joined: Tue Nov 07, 2023 10:45 am

Re: SQL queries of the existing schedule breakdown report

Post by Regine » Wed Nov 22, 2023 9:41 am

Thanks for your reply.
It's okay if the data is not up to today. I only need it to be up to end of month.
The issue of using Live data report is that - I found Live data is not displaying all the data I need, even after I press "All" button. I think it is due to the large volume of data in the report. Therefore, I am looking for a solution to obtain the complete data set.
Do you know if there is any method I can obtain the same data from BI reporting please?
Top
dmpchch
Posts: 36
Joined: Mon Aug 26, 2019 2:52 am

Re: SQL queries of the existing schedule breakdown report

Post by dmpchch » Wed Nov 29, 2023 2:01 am

So I tried this in BI Reporting and can replicate the Schedule Breakdown report but not how I thought I would.
We use multi company and in SimPRO can do each Company separately. I cannot do them separately in BI Reporting.

For BI Reporting I used SimPRO Schedule table and filtered Date on last month.
But to get a matching count I needed to select all 4 of our companies as well as 'All Companies'

Doing this any other way in BI reporting is missed the Activities

I can therefore only match it to SimPRO if in SimPRO I select all four companies.
So I feel the BI report is flawed.

It does work correctly when using the API
Top
Post Reply

Return to “General Discussion”