I am trying to create a report for our own inspectors to visit site and inspect against signing and guarding items and also Permit compliance checks. I have created a report with:-
Twor Works Ref
Tphs Notice Regime
Tphs Loc Description
Str Descr
Str Locality
Str Town
Highway Authority
Tphs Act Start Date
Tphs Act End Date
Twor External Ref
Tphs Contractor Name
Tphs Descripton
Tphs Reinst Cat
Tphs Fway Closure
Which works fine, however when i try to add Tnot Condition Text it starts to duplicate all the lines, I've tried different joins but can't get it to work. any ideas? thanks Ali
Hi Ali,
I suspect you query will be returning duplicate rows as the condition text is stored on a notice, and there could be multiple notices per works with the data.
Please could you email me the query you're running or share the report with the atlas user?
Lee Jackson | Product Manager
hiya Lee,
I have shared the report with the atlas user, its called Test - wip
thanks
ali
I've had a look at the report and i think it is returning multiple lines for some works because you're asking it to collect data from notices, so you see a record for each notice. If you remove the TNOT_SENT_RECIEVED and TNOT_CONDITION_TEXT columns and conditions, you should then only see one record for each WIP phase/works.
Unfortunately as condition text is stored on notices, you would need the report to return the notices on each phase in order to see the data. As the condition text can be changed during a phase, an inspector may actually benefit from seeing the information on each notice as this will show any changes and they can then asses the permit compliance accordingly. To make this information clearer to the inspector, i would recommend adding the TNOT_NOTICE_SEQ and TNOT_NOTICE_TYPE columns to the report, then sort the data on TNOT_WORKS_REF and TNOT_NOTICE_SEQ.
Please could you try this and let me know if it helps?
If your still not happy with the data, please provide some more detail on the business process which is driving this requirement and the information needed. I think i have a good idea of what you need and what this report will be used for, but so I don’t miss anything, please describe the report you want in detail by listing the fields you think the report must have and the business processes which it will be used for.
Thanks,
Lee
Answer Verified By: Alison Pyott
Thanks Lee, this did reoslve the problem, but due to the amount of works and number of variation notices ect, it has been agreed that this would take far too much admin time to be able to produce on a regual basis for our inspectors.
At least i know now tho!