Hi,
I have a requirement where an excel report needs to be generated as part of a export functionality. This excel report should have Header, Body (to be extracted from database) and Footer contents.
I am using Export DSE to Excel smart service to generate the extract using Header and Body contents. I am having a tough time in populating the values for footer.
Sample of the expected excel report is provided below. Here, Generated By and Generated On along with its respective values (xxxxx and yyyyy) are the footer details that should be available in the excel report when it is generated. Please note that Generated By and Generated On is just an example that I have provided here. It can be anything based on client's requirement which needs to be populated for each excel report.
How can I achieve this requirement ?
Regards,
Deepak
Discussion posts and replies are publicly visible
Despite the other feature incompleteness inherent in the Export DSE to Excel smart service, I'd hazard saying that the "generated by / generated on" fields, and their respective values, should be relatively easy for you to populate here, just using the "Custom Cell Positions" / "Custom Cell Values" parameters of the smart service. You'd simply need to calculate the total length of the export DSE ahead of time and use that to generate your row numbers (x = "n + 5" and y = "n + 6" for example, where n = the totalcount of the DSE query), which you'd then use to generate your cell names (Ax and Ay for the wording, Bx and By for the values).
Note: You'll have a harder time with replicating the pre-formatted header row and pre-sized columns, sadly, since as I note in my old linked thread above, this smart service still doesn't accept a "base template" file (unlike the older "Export CDT to Excel" smart service, found in the Excel Tools plug-in, which is deprecated but still works fine). So you might need to consider how important it is for you to generate a nice-looking Excel export file, before deciding which Smart Service to use.
Hi Mike,
Appreciate the quick response. I had tried using the custom cell positions and custom cell values. We can use this to populate values that are above the starting cell.
Sorry, I didn't realize that constraint was there - I was hoping and assuming that as long as the designer takes care of positioning those cells after the totality of the data rows are accomodated, that it would just work.
Thus if you really must keep using the Export DSE to Excel smart service, I would suggest your main avenue of handling this would be to move those cells to the top of the export and move the data down another few rows to accomodate.
One work-around can be to add 2 extra records in the data source (may be a table) which has value for just those two columns (In your example may be it can be the DB columns represents Column B and C ) and rest are empty/null. Populate that dynamically with the values you want to print in the report.
I know it might not be the correct way of doing and it should be the last option.