Report Titles in MS-Excel 1

[Swathi37]

Hi All,

Is there a way to get the Report titles on to Excel from Impromptu Report?
If I export the imr report to Excel with format, only the detail report gets exported. I would like to have atleast a line describing the name of the report. Because my clients are interested only in Excel format. It is hard to view the report in Excel without the report title.

Please advice.
Thanks for your help.
Regards
Swathi

 

[griffindm]

Swathi,

In order to get the report titles into Excel, you have to create a dummy grouping in the Impromptu report, and put all of the normal page header information into that group header.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Swathi37]

Dave,
Thank you so much for your response. How do I invoke or get a dummy group header. I have tried getting this but in vain. Could you please elaborate on this? I would like to have the steps. This would be great help to me. I have crosstab query. Is it possible to get the group header for a crosstab?

Please let me know.
Thanks a lot
Swathi

 

[griffindm]

Swathi,

Create a calculated column, call it dummygrp and give it a value of 1. Then group on this column at the highest grouping level of the report. Thus all data is in this one group, the group header will only appear once, and the header information will be exported to Excel.

I don't believe this approach will not work for a crosstab, as there are no group header areas.


Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Swathi37]

Dave,

Thank you. It works. I tried creating a dummy groupheader, inserted the report title,but when i tried exporting this report to Excel with format, the report title is confined to just one cell i.e A1 and text gets truncated. Is there a way to format this?

Thanks for your help
Swathi

 

[spicysudhi]

Swathi,
while designing, increase the length of the text field size (grouped column) to the width of the report. this will force the excel to merge all the cells in that row.

Dave, adding a group like this is fine and we get the result, but i think creating groups will certainly downgrade the performance... isnt it.

regards,
sudhi

 

[drlex]

Swathi,
I think I may be reinterpreting sudhi's post...
Use properties of each item in the header (right click on each) to set the size and alignment of each to a report column. This should result in them appearing in separate cells in the excel report.

As a possible alternative, I use OLE automation of excel to do some formatting of reports run by macro and one can always use the ".MergeCells = True" on the selected header row so as to show all text without truncation.

Happy Friday, all
lex


"Time flies like an arrow, but fruit flies like a banana."

 

[Swathi37]

Thank you so much for all your replies.

Just curious about how to use a macro that inserts a row on A1 and then insert text and also to change the Sheet name from sheet1 to Report or something. How would you use OLE in Excel that does formatting?

When I publish a report in upfront in Excel format how would I add this macro in excel that performs the function of inserting a row and adding text? Lex could you please advice.

Thanks a lot.
Swathi

 

[drlex]

Swathi,

You could obviously record and retain a macro in Excel to perform the required functions, but I prefer to do the processing within the macro that generates the report.
To find the relevant commands and syntax, I record macros in Excel and then use Edit.

For a cognos script to use with Excel 2000 try this, which will open a sheet, add a row and a title in bold and then rename sheet1:

Sub Main ()

Dim objExcel as Object
Dim strfilename as string
strfilename = "C:\testsheet.xls"

Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = 1
objExcel.Application.Workbooks.Open strfilename
objExcel.Application.DisplayAlerts = False
objExcel.Rows("1:1&quot.Select
objExcel.Selection.Insert shift:=xldown
objExcel.Range("A1&quot.Select
objExcel.ActiveCell.FormulaR1C1 = "Customer results to date"
objExcel.Range("A1&quot.Select
objExcel.Selection.Font.Bold = True
objExcel.Sheets("Sheet1&quot.Select
objExcel.Sheets("Sheet1&quot.Name = "Customer results"
objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143
objExcel.Application.Workbooks(1).Close
objExcel.Application.Quit
End Sub

"Time flies like an arrow, but fruit flies like a banana."

 

[spicysudhi]

thanks lex,

with this macro u posted here, its creating the a sheet and adding text to the first cells. but how do u integrated this with ur impromptu report and how it works with iwr. in worst case the client without excel accessing this report could result in the complete failure of the report isnt it... bcoz excel.application fails.

sudhi

 

[drlex]

Sudhi,
I integrate it with my report by running it as part of the macro that runs and saves the report. Excel (and the rest of Office XP) is on my cognos server.

Since I don't have IWR, all users requiring static output get it as an attachment to an email or as a URL link in Upfront - either a pdf or an excel table.

If you have a client without excel, why would they want to save a report as excel formatted?

I guess if you need to keep it within Cognos, then Dave's answer of grouping, together with careful field placement, is probably the only way. (I've tried scripting to get the report title frame, but can't automatically select the relevant frame.) Maybe you need to publish two versions of the report to IWR - one to run if a user has excel and one if not - perhaps even linked to the user class....

lex

"Time flies like an arrow, but fruit flies like a banana."