How to make the exported Excel and report paging settings different

Take the list as an example. The default number of rows is 20. If the number of rows exceeds 20, the list will be displayed by page. If we export an Excel file, the Excel file will also be divided into sheet and exported, with each sheet being 20 rows. When the page is displayed, 20 rows and one page can be displayed, but users often export all the data to a sheet. Some people often ask how to implement it. Two methods are listed here. The first is to implement two reports, and the second is to implement one report.

No matter which method is used, the idea is the same. Let's first introduce my implementation idea: the number of rows in the same list is the same as the condition for exporting the Excel sheet, this cannot be changed, but we can implement it using two lists. The display and export paging conditions are different. One list is used for report display, and the other list is used for export.

The first method is to export two reports using penetration drilling to excel.

Use the sales and marketing ZH (cube) in the Cognos sample as the package we demonstrated. Open reportstudio and create a simple report, report_for_html, which only contains a simple list so that the data is displayed on pages. As shown in:

 

In this case, save the report as a record. For example, the structure of report_for_excel is consistent, but the page of the List is set to 65535 (this limit can be left blank, but if no restriction is imposed, when the export data exceeds 65535, the exported Excel file cannot be opened because a single sheet is too large ).

 

Open the report_for_html report, drag a Text Project in the toolbox to the page, set the text content to "Export excel", select the text item, and click "trace definition, set the tracing definition to report_for_excel.

Set the report format to report_for_excel. Select Excel 2007 or another format. Open in a new window and click OK. The final effect is shown in.

 

The running effect is that the report can have pages, and the exported Excel file does not have pages, as shown in

 

Note: When a report has filtering conditions, the parameters must be passed in the trace definition.

 

The second method is implemented in a report. The idea is as follows: Use the condition style to determine the current output mode. If it is HTML, the displayed list is displayed. If it is Excel, the exported Excel list is displayed.

We also select the same list as the previous one and copy it. Assume that the list above is used for report display, and the list below is used for exporting excel, as shown in:

 

Enter the condition Resource Manager to create a new string variable and set the expression to reportoutput (). This function returns the name of the output format. Possible returned values include CSV, HTML, layoutdataxml, MHT, PDF, rawxml, singlexls, spreadsheetml, xls, XML, and xlwa. Create a new value in HTML. Here we demonstrate that only one value is created. You can create multiple values so that you can control more details.

Return to the page Resource Manager, set the style variable to the new "string 1" for the two lists, set the value to "html", and the box type of the List output Excel is none, when the value is other, the box type of the list displayed in HTML is none.

Run the report to display the data displayed by page. If you select the Excel file exported when exporting the Excel file, no sheet is displayed.

The operation demonstration video is put in the forum, as shown below

Http://www.cognoschina.net/club/thread-10061-1-1.html