SQL to remove community reports

[Bruce]

Hi all,

One of the tasks we've recently performed manually is remove hundreds
of system reports from community reports by deselecting the “Share as
Community Report” checkbox within each report. It was quite a manual
task. Would any of you know how to automate this? Perhaps know the
correct database table to reference and SQL query to run?

Thanks.

[LarryN]

I have the same need and, like you, eagerly await suggestions from
the group.

[Rosenow, Nancy]

We created a temp table to hold the name of all report to be community reports and then used that to update the system_flag on the rep_template_hdr table to indicate system or community report.

-- create a PreUpdate data audit for the rep_template_hdr table.
select * from rep_template_hdr

-- count the system reports
select count(*) from rep_template_hdr where system_flag = 1;

-- count the community reports
select count(*) from rep_template_hdr where system_flag = 2;

-- Select all community reports (and save as spreadsheet)
select * from rep_template_hdr where system_flag = 2 ;

-- Now update all of the community reports so that they are system reports
update rep_template_hdr set system_flag = 1 where system_flag = 2;

-- Now change reports that are on the temp table back to community reports
update rep_template_hdr a set a.system_flag = 2 where exists
(select * from bch_community_rpts b where rtrim(ltrim(b.bchrepname)) = rtrim(a.rep_name));
commit;

-- create a PostUpdate data audit for the rep_template_hdr table.
select * from rep_template_hdr

delete from bch_community_rpts;
commit;

Nancy Rosenow
PIMS / IBM Tririga Technical Lead.
IBM Tririga Certified Application Developer.
IT Services - Application Outsourcing
Accenture Business Services for Utilities
Telephone:  604-623-4552 (74552)
Nancy....@absu.accenture.com
This message is for the designated recipients only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

--
You received this message because you are subscribed to the Google Groups "triDeveloper" group.
To post to this group, send email to tridev...@googlegroups.com.
To unsubscribe from this group, send email to trideveloper...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trideveloper?hl=en.

[Ken Janes]

Cool stuff Nancy (good to hear from you)


\ken

> trideveloper...@googlegroups.com.For more options,

> visit this group at http://groups.google.com/group/trideveloper?hl=en.
>
> --
> You received this message because you are subscribed to the
> Google Groups "triDeveloper" group.
> To post to this group, send email to tridev...@googlegroups.com.
> To unsubscribe from this group, send email to

> trideveloper...@googlegroups.com.For more options,

[Bruce]

Thanks, Nancy, great stuff!

On May 16, 3:25 pm, "Rosenow, Nancy"

> Nancy.Rose...@absu.accenture.com