Hi,
My goal is to create burst reports and copy them into specific folders defined by the burstkey.
I know that by using stuff like CM.OUTPUTLOCATION, you can specify a system folder location to store your reports. But I want to keep these reports in the CRN datastore. Besides, the name of the report created this way is a mess and they are all in the same folder. I would still need to rename them and copy them elsewhere based on the xml that goes with them.
I could also use SDK, but we don't have a license and I don't have a clue on what would be needed to achieve my goal this way.
What I have in mind is this:
I managed to find where the reports were stored in the CRN datastore.
We use Oracle 9.2.0.4, the CRN datastore iis named 'CRN' and here is roughly the sql that gives the burst reports in PDF having a burstkey beginning by 'FS':
SELECT
BURST.CMID "Report_Key",
BURST.BURSTKEY "Burst_Key",
NA.NAME "Report_Name",
DA.DATAPROP "PDF_Report"
from
CRN.CMOBJPROPS6 BURST,
CRN.CMDATA DA,
CRN.CMOBJNAMES_BASE NA,
CRN.CMOBJECTS OB1,
CRN.CMOBJECTS OB2
Where
BURST.BURSTKEY like 'FS%'
and DA.CONTENTTYPE = 'application/pdf'
and DA.CMID = BURST.CMID
and BURST.CMID = OB2.CMID
and OB2.PCMID = OB1.CMID
and OB1.PCMID = NA.CMID
This gives me the name of the master report, the reports themselves in a BLOB format and the burstkey that will be used to choose the folder in which I'll put my PDFs.
This SQL might not be 100% clean but it is a good start.
Now, I don't know the SQL command to copy a BLOB field into the system file using "Report_Name"||'.PDF' as a name. And as you can see it is the only thing left that I need to be able to copy burst reports into the correct file folder with a correct name.
So if you wondered where were the burst reports in the CRN schema, now you know and if you can help me copy blob to the system file, well please do so ;-)
My goal is to create burst reports and copy them into specific folders defined by the burstkey.
I know that by using stuff like CM.OUTPUTLOCATION, you can specify a system folder location to store your reports. But I want to keep these reports in the CRN datastore. Besides, the name of the report created this way is a mess and they are all in the same folder. I would still need to rename them and copy them elsewhere based on the xml that goes with them.
I could also use SDK, but we don't have a license and I don't have a clue on what would be needed to achieve my goal this way.
What I have in mind is this:
I managed to find where the reports were stored in the CRN datastore.
We use Oracle 9.2.0.4, the CRN datastore iis named 'CRN' and here is roughly the sql that gives the burst reports in PDF having a burstkey beginning by 'FS':
SELECT
BURST.CMID "Report_Key",
BURST.BURSTKEY "Burst_Key",
NA.NAME "Report_Name",
DA.DATAPROP "PDF_Report"
from
CRN.CMOBJPROPS6 BURST,
CRN.CMDATA DA,
CRN.CMOBJNAMES_BASE NA,
CRN.CMOBJECTS OB1,
CRN.CMOBJECTS OB2
Where
BURST.BURSTKEY like 'FS%'
and DA.CONTENTTYPE = 'application/pdf'
and DA.CMID = BURST.CMID
and BURST.CMID = OB2.CMID
and OB2.PCMID = OB1.CMID
and OB1.PCMID = NA.CMID
This gives me the name of the master report, the reports themselves in a BLOB format and the burstkey that will be used to choose the folder in which I'll put my PDFs.
This SQL might not be 100% clean but it is a good start.
Now, I don't know the SQL command to copy a BLOB field into the system file using "Report_Name"||'.PDF' as a name. And as you can see it is the only thing left that I need to be able to copy burst reports into the correct file folder with a correct name.
So if you wondered where were the burst reports in the CRN schema, now you know and if you can help me copy blob to the system file, well please do so ;-)