How many times did you design a complex report based in a user query?
of course you waste a lot of time in design.
Then someone tells you “Instead of this Field1 I need to see Field2 in that report!”.
First time this happend to me I thought “Well, 2 minutes”. I tried to change the user query, then updated the report… but It didn’t work. As far as I know, there is no way to change the datafields ofan existing report. This is a short explanation about how to face this via SQL
If you try this one, be carefull, and understand what you are doing. I don’t suppose SAP supports it, so be really careful.
When a report is created based on a user query, 2 tables are used.
*RDOC: which saves the documents headers
*RITM: which saves the elements of the documents
Suppose you have a report based on this SQL
SELECT T0.CardCode, T0.CardName, T0.GroupCode FROM OCRD T0 FOR BROWSE
But your boss comes and tells you that he wants to see the group name, not the group code.
First thing you must do is to identify your report in the RDOC table. You can use this SQL for it:
SELECT T0.DocCode FROM [dbo].[RDOC] T0 WHERE T0.DocName=N'Your table name' AND T0.TypeCode=N'USRP'
Now you that you know the DocCode (suppose USRP000X), you must make 2 changes.
- In the RDOC table, change the QString field to update the select SQL
UPDATE T0 SET QString='SELECT T0.CardCode, T0.CardName, T1.GroupName FROM OCRD T0 INNER JOIN OCRG T1 ON T0.GroupCode=T1.GroupCode FOR BROWSE' FROM [dbo].[RDOC] T0 WHERE T0.DocCode = N'USRP000X'
In the RITM table, search for the ItemId of the field you want to update.
In this case:
SELECT ItemId FROM RITM WHERE DocCode='USRP000X' and FileName='OCRD' and FieldNum='GroupCode'
Now you know the ItemId of the field (suppose XX). Use this data to change the FileName and FieldNum fields with the new values.
UPDATE T0 SET FileName=N'OCRD', FieldNum='GroupName' FROM [dbo].[RITM] T0 WHERE T0.DocCode=N'USRP000X' AND T0.ItemId='XX
And that’s all.
This is only an example of how to change the data of an existing Report via SQL. But I think it is enough to understand how Sap Business One manages userdefined Reports.
And everyone can decide on his own if and how to use this information.