Many Thanks, It Means a lot getting a valuable reply from you, I will take it from here.
Original Message:
Sent: Fri February 09, 2024 06:54 AM
From: Paul Mendelson
Subject: Export To Excel Button on cognos Report
You don't need to have the appbar visible to use the export report control from CogBox. It will appear as a regular button in the report, even if the report is held inside an iframe. The appbar is necessary to add the control.
From inside report studio click on the cogbox menu and then open Control Creator. Click on Copy next to Export Report to Excel. Then you can just paste into the report where desired.
At this point you're mostly done. You can modify the configuration to meet your needs:
This uses Font Awesome so the button will default to showing the Excel icon. You can also add custom CSS to the button, and even control the hover pseudoclass. Setting Interactive Export to true will allow the report to export any changes the user makes like sorting and adding subtotals to objects. When it's false it uses Mashup Services to run the report in the background.
Target Report will allow you to specify the target report, so it doesn't need to be the report the user is running. If you're running this from reporting you need to set a value here, otherwise the control will only work when the report is run from the portal.
That all being said, the control you're writing does something different. While this runs the entire report in Excel, you're attempting to export a specific dataset attached to the button.
A few comments:
- In the draw method you're making a static button with an id, then calling that with getElementById. This will limit the report to only having a single one of these buttons at a time. A better way might be like this:
DownloadReportToExcel.prototype.draw = function(oControlHost) {
// Customize the appearance if needed
var el = oControlHost.container, downloadButton = document.createElement('button');
downloadButton.innerHTML = oControlHost.configuration?.["Button Label"]??"Download Excel";
downloadButton.addEventListener('click', function() {
this.downloadExcel(oControlHost);
}.bind(this));
};
By doing it like this you can allow the report author to define the label in the configuration "Button Label":"Custom Label", defaulting to "Download Excel" if not provided. - In your getAllReportData function you're calling an obfuscated function to get the data. This will break whenever Cognos has any fixpack or update installed.
DownloadReportToExcel.prototype.getAllReportData = function(oControlHost) {
var allData = [];
console.log('oControlHost:', oControlHost);
// Use oControlHost directly to access methods and properties
var dataSet = oControlHost._191;
Instead, use the setData and then define the JSON onto the DownloadReportToExcel object directly:
DownloadReportToExcel.prototype.setData = function(oControlHost, oDataStore) {
this.data = oDataStore.json.rows.map(row=>row.map((cell,column)=>oDataStore.json.columns[column].values[cell]));
}
This makes an array of arrays of each value in the output.
- Finally the export to a file can actually be handled by Cognos directly. Instead of Excel, there's a dashboard feature that exports to CSV.
DownloadReportToExcel.prototype.downloadExcel = function(oControlHost) {
// Fetch all data from the report
parent.require(['dashboard-core/js/features/dashboard/csvExport/CsvExport'],csvClass=>{let csv = new csvClass; csv.export('Report Data',this.data)})
}
For some reason the require from inside reporting isn't able to grab the dashboard core features, but the parent frame is. Either way, this works. The browser will download a csv file titled 'Report Data', but like the button above you're able to allow the report author to set the label in the configuration.
You have a great start here, you're almost there.
------------------------------
Paul Mendelson
Original Message:
Sent: Thu February 08, 2024 01:25 PM
From: Loyapally Ranjith
Subject: Export To Excel Button on cognos Report
While Cognos reports offer various export options directly, I have specific criteria that necessitate the creation of a custom "Export to Excel" button within the report.
Although I have the option to use cogbox, embedding the report in an iframe causes the entire menu bar to be hidden. Consequently, I was tasked with implementing a button to handle the export functionality within the report.
Through research, I discovered the potential use of Custom Controls, allowing the external embedding of JavaScript files through extensions. I successfully wrote code by referencing examples from various sources to read the report and execute the export option. However, I find that a deeper understanding of the OControlHost object is necessary for further refinement.
Could you please provide some insights or guidance on shaping this implementation?
I have Simple list Report , That I'm trying to Export to Excel Format.
Here is the Code I'm Using in JS file.
define(function() {
"use strict";
function DownloadReportToExcel() {
}
DownloadReportToExcel.prototype.draw = function(oControlHost) {
// Customize the appearance if needed
var el = oControlHost.container;
el.innerHTML = '<button id="downloadButton">Download Excel</button>';
var downloadButton = document.getElementById('downloadButton');
downloadButton.addEventListener('click', function() {
this.downloadExcel(oControlHost);
}.bind(this));
};
DownloadReportToExcel.prototype.downloadExcel = function(oControlHost) {
// Fetch all data from the report
var allData = this.getAllReportData(oControlHost);
// Create a Blob with the Excel content
var blob = new Blob([this.generateExcelContent(allData)], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a link element to trigger the download
var link = document.createElement('a');
link.href = URL.createObjectURL(blob);
link.download = 'report.xlsx';
// Append the link to the document and trigger the download
document.body.appendChild(link);
link.click();
// Remove the link from the document
document.body.removeChild(link);
// Clean up
URL.revokeObjectURL(link.href);
};
DownloadReportToExcel.prototype.getAllReportData = function(oControlHost) {
var allData = [];
console.log('oControlHost:', oControlHost);
// Use oControlHost directly to access methods and properties
var dataSet = oControlHost._191;
// Check if dataSet is defined
if (dataSet) {
// Iterate through rows and columns to retrieve data
for (var iRow = 0; iRow < dataSet.rowCount; iRow++) {
var rowData = [];
for (var iCol = 0; iCol < dataSet.columnCount; iCol++) {
var cellValue = dataSet.getCell(iRow, iCol).getFormattedValue();
rowData.push(cellValue);
}
allData.push(rowData);
}
} else {
console.error('Data set not found in the oControlHost instance.');
}
return allData;
};
DownloadReportToExcel.prototype.generateExcelContent = function(data) {
// Implement the logic to generate Excel content
// For simplicity, you can use a library like SheetJS (xlsx) or manually create Excel content
var excelContent = '';
console.log('data:', data);
// Add headers
var headers = data[0];
excelContent += headers.join('\t') + '\n';
// Add data rows
for (var i = 1; i < data.length; i++) {
excelContent += data[i].join('\t') + '\n';
}
return excelContent;
};
DownloadReportToExcel.prototype.setData = function(oControlHost, oDataStore) {
// If data is needed, handle it here
};
DownloadReportToExcel.prototype.getParameters = function() {
// If parameters are needed, handle them here
return [];
};
DownloadReportToExcel.prototype.isInValidState = function() {
return true;
};
return DownloadReportToExcel;
});
------------------------------
Loyapally Ranjith
------------------------------