Need to Modify the Query Subject / Query Item Names in Reports

[jeeva]

Hi,

we need to modify all the existed reports with the new query subjects or query items names. how can we modify these names in the reports. Is it possible by using SDK. please suggest me.

Thanks,
Thanay

[Michael75]

Sounds like a job for the DRU: http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg24021248

[jeeva]

Sounds like a job for the DRU: http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg24021248

Thanks for the reply.

DRU is fine. but it looks like do update one by one. instead of one by one, can we modify all reports in content store in single shot.

[Michael75]

I think you're basing your reaction on this bit of the doc I linked to (highlighting is mine):

The Dynamic ReportSpec Updater (DRU) does this by traversing the IBM Cognos BI content path, retrieving the XML specifications one by one and applying the search and replace attributes identified within a user defined transformation file.

I think this could have been phrased a little better. My understanding is that it is a global search and replace tool which analyses and updates any reports specs in the content store which correspond to the rules you've defined. This is maybe better explained i the following article:

https://www-304.ibm.com/connections/blogs/basupportlink/entry/the_dynamic_reportspec_updater_dru7?lang=en_us

[chris.chapman]

Hi Thanay,

BSP Software's MetaManager can do exactly what you are looking for. The Find/Replace module would allow you to scan each report in the Content Store and perform a find and replace operation against those reports. More information can be found in the link below.

http://www.bspsoftware.com/products/metamanager/Update/

If you have any other questions please reach out to us and we would be happy to set up a demo or trial for you.

Good luck!

Chris

[TheBrenda]

this finds the text. I will also copy over some code for replacing which can easily be incorporated into the proc

Code Select Expand


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cm_SearchForTextInReports]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cm_SearchForTextInReports]
GO


CREATE PROCEDURE [dbo].[cm_SearchForTextInReports]

/***************************************************************

cm_SearchForTextInReports - search the Cognos Content
Store database for Reports and Queries that contain the Search Text.

Parameters
==========
1) Search Text
2) Show Preview = 0 to not show the Preview column. Only advantage to
   not showing the preview column is that you will only get one record
   returned per report or query. That is because a report might have
   the Search Text multiple times and a 1 will return a row for each
   time the SearchText was found.

EXAMPLE
=======
These will both Return a Preview
exec cm_SearchForTextInReports '[GL Type]', 1
exec cm_SearchForTextInReports '[Account Attributes].[GL Type Code]'

This will not return a Preview
Exec cm_SearchForTextInReports '[Demand Deposits / Savings].[Account Attributes].[GL Type Code]', 0

ERRORS
======
Below error means that your SearchText is too common and it was found
too many times.

Msg 28102, Level 16, State 1, Procedure cm_SearchForTextInReports, Line 17
Batch execution is terminated because of debugger request.

****************************************************************/

@pSearchText varchar(512),
@pShowPreview int = 1       -- 1 Shows the Preview columnm, one record will be returned for each match of the @SearchText
                             -- 0 does not show the Preview column, one record will be returned for each report that matches the @SearchText

AS 

BEGIN 

SET NOCOUNT ON

IF @pSearchText is NULL or LTRIM(RTRIM(@pSearchText)) = ''
Return
ELSE
Set @pSearchText = LTRIM(RTRIM(@pSearchText))

IF @pShowPreview is NULL SET @pShowPreview = 1

IF RIGHT(DB_Name(), 2) <> 'cm'
Begin
Print 'Execute Stored Procedure fm_SearchForTextInReports in the context of BankXXcm Database.'
Return
End

Declare @SearchTextLIKE varchar(512)
Declare @PreviewTextLeadSize int = 50
Declare @PreviewTextTrailSize int = 30

Declare @Results Table (OwnerName varchar(128), ClassID varchar(25), ReportName varchar(128), DirectoryPath varchar(512),
CMID int, PCMID int, ReportDefinition varchar(max))

-- Escape LIKE wildcards then add %
Set @SearchTextLIKE = Replace(@pSearchText, '[', '[[]')
Set @SearchTextLIKE = Replace(@SearchTextLIKE, '_', '[_]')
Set @SearchTextLIKE = Replace(@SearchTextLIKE, '%', '[%]')
Set @SearchTextLIKE = '%' + @SearchTextLIKE + '%'
     
    -- complicated query on the Cognos Content Store to find reports and queries (10 and 37)
; with base_query as
(
select classid, cmobjects.cmid, cmobjects.pcmid, cmobjnames.name as reportname
from cmobjects , cmobjnames
where cmobjects.cmid = cmobjnames.cmid
and classid in (10, 37)
-- and cmobjnames.mapdlocaleid = 24
union
select classid, cmobjects.cmid as cmid, cmobjects.pcmid, cmobjnames.name as reportname
from cmobjects , cmobjnames
where cmobjects.cmid = cmobjnames.cmid
and  cmobjects.cmid in (select distinct cmobjects.pcmid
from cmobjects)
-- and cmobjnames.mapdlocaleid = 24
),

-- complicated recursive query to find the report and query path
ctebuildpath as
(
select base_query.cmid,
base_query.pcmid,
base_query.reportname,
1 as Hlevel,classid,
cast(base_query.reportname as varchar(500))as directory_path
from base_query where reportname = 'Public Folders' or reportname = 'My Folders'
union all
select recur.cmid,
recur.pcmid,
recur.reportname,
cte.Hlevel+1 as Hlevel,recur.classid,
cast(cte.directory_path + '>' +  recur.reportname as varchar(500))as directory_path
from base_query as recur,
ctebuildpath as cte
where cte.cmid = recur.pcmid
),

-- query to find the objects that match the @SearchTextLike
report_spec as
(
select cast(spec as  varchar(max)) as spec, c.cmid --,
--charindex(']/package[@name=', cast(spec as  varchar(max)))+17 as startposition,
--charindex(']/model[@name=', cast(spec as  varchar(max)))-18-charindex(']/package[@name=', cast(spec as  varchar(max))) as endposition
from cmobjprops7 c inner join ctebuildpath b
on c.cmid = b.cmid
where spec is not null
and SPEC like @SearchTextLIKE
),

-- query to find the Owner of the reports and queries
    owner_name as
    (
    select a.name, b.cmid
from cmobjprops33 a,  cmobjnames b, cmrefnoord2 c, cmobjects d, cmclasses e, ctebuildpath cte
where b.cmid = c.cmid
and a.cmid = c.refcmid
and b.cmid = d.cmid
and d.classid = e.classid
and (e.name = 'report' or e.NAME = 'query')
and cte.CMID = b.cmid
)

-- Insert the results of the 4 cte queries into @Results temp table
INSERT into @Results
SELECT ISNULL(Owner_name.name, 'Unknown') as 'Owner Name', Case classid When 10 Then 'Report' Else 'Query' End, 
   reportname as [Report Name], directory_path as [Directory Path],
   cte.cmid as CMID,  cte.pcmid as PCMID, spec as [ReportDefinition Report Definition]
FROM ctebuildpath cte inner join report_spec rep
on cte.cmid = rep.cmid
full outer join owner_name
on cte.CMID = owner_name.cmid
WHERE cte.classid in (10, 37)

Print 'Number of Reports Matching ''' + @pSearchText + ''': ' + Cast(@@RowCount as varchar)

IF @pShowPreview = 0
Select OwnerName, ClassID, ReportName, DirectoryPath, ReportDefinition, CMID, PCMID
From @Results
Order by DirectoryPath, ReportName
Else
BEGIN
-- Now use a recursive cte to find each of the matches within the ReportDefinition report definition and give a preview
;with T(OwnerName, ClassID, ReportName, DirectoryPath, CMID, PCMID, ReportDefinition, start, pos) as (
select OwnerName, ClassID, ReportName, DirectoryPath, CMID, PCMID, ReportDefinition, cast(1 as int), charindex(@pSearchText, ReportDefinition)
from @Results
union all
select OwnerName, ClassID, ReportName, DirectoryPath, CMID, PCMID, ReportDefinition, cast(pos + 1 as int), charindex(@pSearchText, ReportDefinition, pos + 1)
from t
where pos > 0
)

SELECT OwnerName, ClassID, ReportName, DirectoryPath,
   Replace(Replace(SubString(ReportDefinition, CharIndex(@pSearchText, ReportDefinition, pos) - @PreviewTextLeadSize,
   @PreviewTextLeadSize + Len(@pSearchText) + @PreviewTextTrailSize), char(13) + char(10), ''),
   @pSearchText, '***' + @pSearchText + '***') as Preview,
   ReportDefinition, CMID, PCMID
FROM T
WHERE Pos > 0
Order by DirectoryPath, ReportName
END

END

GO


[TheBrenda]

this has some update logic in it. it would be simple to incorporate this into the above sproc. we just did not do it because you have to be very controlled about what is changed. so we ran the sproc above and then selected out what we wanted changed and entered that into the @CMIDTable below. This can and will change all your reports if your OldText is too common. and the reports could be changed in a way that makes them invalid. like changing all 'report' to 'report123'.  you think you are only chaning one report title, but 'report' is all over the report definition xml.

Code Select Expand


/*


BACKUP content store first.

this script requires table @CMIDTable to have the information required to make the changes.

Script will search the reports/queries specified in the input, and replace and instances of old expressions with new
expressions.

There is an output display that contains an internal CMID report ID, Report Name, and Changed/Not Changed indicator.
The report name will match up to the report names on the Excel file. Any report that shows Not Changed should be
researched.

*/


Declare @id_num as Int
Declare @OldField as NVarChar (MAX)
Declare @OldFieldLIKE as NVarChar (MAX)
Declare @NewField as NVarChar (MAX)
Declare @NewFieldLIKE as NVarChar (MAX)
Declare @Report as NVarChar (100)
Declare @CMID as Int

-- Misc Variable
Declare @SearchField as VarChar(300)
Declare @NumCMIDs as Integer;
Declare @NumFieldsProcessed as Int
Set @NumFieldsProcessed = 0
Declare @change Int

-- Final Results Table
--Declare @Results Table (objtype varchar(50), oldField varchar (100), newField varchar(100), oldpath varchar(300),
--newpath varchar(300), reportname VarChar(100), directorypath VarChar(200), cmid integer,  pcmid integer,
--XML varchar(max));

Declare @Reporting Table (CMID INT, ChangeInd VARCHAR(1), Report NVarChar(100))

-- Table of Old Field Names
Declare @CMIDTable Table (id_num int IDENTITY(1,1),  CMID INT, OldField NVARCHAR(MAX), NewField NVARCHAR(MAX), Report NVARCHAR(100));

Insert @CMIDTable(CMID, OldField, NewFIeld, Report) values(1866,'[Demand Deposits / Savings].[Current Measures].[Accrued Interest]','[Demand Deposits / Savings].[Current Measures].[Accrued Interest (Int Cyc)]','CALL REPORT-SAVINGS 250,000 AND LESS')


Select @NumCMIDs = Count(*) FROM @CMIDTable;

-- Loop through the CMIDtable
While exists (Select * From @CMIDTable)
    Begin
   
    -- Pull off the Report/Query
    Select @id_num = [id_num],
           @CMID = [CMID],
   @OldField = [OldField],
   @NewField = [NewField],
   @Report = [Report]
   FROM @CMIDTable;

set @OldFieldLIKE = '%' + Replace(@OldField, N'[', N'[[]') + '%'
set @NewFieldLIKE = '%' + Replace(@NewField, N'[', N'[[]') + '%'


IF EXISTS (select *
from cmobjprops7
where spec like @OldFieldLIKE and cmid = @cmid)
BEGIN
Set @change = 1
    Update cmobjprops7
Set spec = Cast(Replace(Cast(Spec As nvarchar(max)), @OldField, @NewField) As ntext)
where cmid = @cmid;
END
Else
BEGIN
Set @change = 0
END

Insert into @Reporting (CMID, ChangeInd, Report) Values (@CMID, @change, @Report)

    -- Delete the selected Field row
Delete from @CMIDTable where id_num = @id_num

-- Loop govenor. stop when NumOldFields is reached
Set @NumFieldsProcessed = @NumFieldsProcessed + 1
If @NumFieldsProcessed > @NumCMIDs
Begin
Print 'Error -  Field Name: ' + @SearchField
Return
End

End  -- WHILE

Select Report, CMID,
  (Case WHEN ChangeInd = 1 THEN 'Changed'
  ELSE 'Not Changed' END) as Changed
From @Reporting
Group by CMID, Report, ChangeInd
Order by Report, CMID, ChangeInd

GO