This is regarding excel output . I need an excel download when an icon is clicked on a dashboard. I am using xlsx output format for that and I am in 8207.28.13 version. The output is just gibberish instead of an excel download.

[Nihitha Reddy Bhimireddy]

This is regarding excel output . I need an excel download when an icon is clicked on a dashboard. I am using xlsx output format for that and I am in 8207.28.13 version. The output is just gibberish instead of an excel download.

[Guest]

Hi Nahitha

What happens if you just run a super simple request like

TABLE FILE CAR

PRINT COUNTRY

ON TABLE PCHOLD FORMAT XLSX

END

Lets make our repro of your problem really small like that.

It'd be fine to use your existing tables to mimic this. I'm just trying to get a small answerset and send it back to your browser in xlsx format.

If that doesn't work, we can start from there. No frames, no fancy HTML - just ON TABLE PCHOLD FORMAT XLSX and let it come back to your browser.

Hope all is well down in the caves of rosnet!

[Martin Yergeau]

This can be because something in the Excel file is not "valid". Could be something in the styling.

First of all, outside any HTML page or clicked link, can you have your Excel file displayed ?

This is the first thing to validate.

Second try holding your file with default format. Meaning do not put "FORMAT ALPHA".

Third try to comment all none necessary code : verything below the first -EXIT

At least you<ll know that the code is working up to there.

[Nihitha Reddy Bhimireddy]

Hello Toby

This is what I got by executing that sample code just by itself

[Martin Yergeau]

This is good, now try what I suggest below

[Nihitha Reddy Bhimireddy]

Hello Martin

[Martin Yergeau]

Change

ON TABLE HOLD FORMAT XLSX

by

ON TABLE PCHOLD FORMAT XLSX

HOLD mean : "keep result in memory for further use"

PCHOLD mean : "return result to browser"

[Guest]

Hi Nihitha

What Martin was asking you to change was where you're building your HOLD files named BEG and END...

I think he was suggesting you remove the words "FORMAT ALPHA" from the hold files.

I have a suggestion now from typing this response.

Change the END filename to something like HOLDEND... END is a reserved word that could trip you up.

I'm glad your basic XLSX functionality is working with the CAR file.

Also remember to use PCHOLD instead of HOLD to get the report to come back to your browser.

[Nihitha Reddy Bhimireddy]

Think I have implemented all of the above suggestions. To be noted the 74 lines is the number of records that I was expecting

[Martin Yergeau]

Lets keep it simple first

Remove (comment) every steps that you don't need to produce file HLD2

In other words comment steps where you hold file DELEMP, BEGFL & HLD1

Then comment all below lines from the TABLE FILE HLD2

Add -EXIT after the END to avoid running anything else.

Result should now be returned to screen in a HTML format

If properly displayed, then try back but only with following line uncommented

ON TABLE PCHOLD FORMAT XLSX

[Martin Yergeau]

I just notice that the TITLETEXT may be the issue.

Change : TYPE=REPORT, TITLETEXT=ENDFL Period Count,$

By : TYPE=REPORT, TITLETEXT='ENDFL Period Count',$

With quotes before ENDFL and after Count

[Nihitha Reddy Bhimireddy]

HTML

Excel

[Martin Yergeau]

Lets try another thing

At the beginning of your fex add the following

SET ASNAMES   = ON

SET FIELDNAME  = NOTRUNC

SET HOLDLIST   = PRINTONLY

Then try back as an Excel output

It is possible now that your fields E01, E02, E03 not anymore be recognized and should use the real field's name such as EmployeeID, EmployeeName, ...

[Nihitha Reddy Bhimireddy]

[Martin Yergeau]

Lets have another try

From the PRINT section, remove the line : LocationNumberName NOPRINT

You already have it in the BY section

Also, always be sure that any applied changes does work in HTML format output before testing as Excel

[Nihitha Reddy Bhimireddy]

Removed the NOPRINT line and checked in HTML format. It worked fine. As soon as the format has been changed to XLSX it's the same gibberish issue.

[Martin Yergeau]

Add the following at your last step and output in Excel

WHERE READLIMIT EQ 10;

[Nihitha Reddy Bhimireddy]

No change in the output. Do you think I need to change any settings?

[Martin Yergeau]

When I had that it was because of an invalid STYLING or data issue that contain something that Excel cannot manage (very rare).

As for the setting, could be anything and everything.

Open a case with Support

[Nihitha Reddy Bhimireddy]

Thank you @Martin Yergeau​  for all the suggestions and information. I really appreciate it.

[Martin Yergeau]

Sad that I haven't been able to solve the problem

GL

[Guest]

Hi Nihitha

Try making the WF reporting server do the rendering of your Excel file by going

To the Client Admin console

Then Client Settings

Set Excel Server URL to : Reporting Server JSCOM.

This will force the .xlsx to be created on the reporting server.

What you're showing makes me think that your browser believes this is a text file and it's trying to display it that way. that's why you see the contents of the excel file instead of it opening Excel outside of your browser like it did in your TABLE FILE CAR example.

Hope that helps.

[Nihitha Reddy Bhimireddy]

Hey @Toby Mills​ . It' already set to Reporting Server JSCOM

[Guest]

I think it might be helpful in troubleshooting to see that the XLSX file IS being delivered to your brower.

It's not actually total gibberish. That's what a xlsx file would look like if you looked at it with a text editor (or apparently your web browser).

So the PCHOLD part works as far as sending you the output. The catch is, that your browser doesn't realize it should be launching Excel to show you the output.

It puzzles me that your TABLE FILE CAR example works - the .xslx file is sent back to your browser - then your browser see's that it's an xlsx file (the file arrives with application/vnd.openxmlformats-officedocument.spreadsheetml.template in its headers). That's the signal to your PC should open up Excel to view the document.

But when you run how you're running now, that last step of knowing to open Excel isn't working.

A few things to check.

First lets check your mime.wfs settings by using the Client Admin Console again. Go down to Redirection Settings

Scroll down to see how .xslx is being processed. Here's how mine is set - if yours is different, you can try setting both things to yes like mine:

If that's not helpful, let me ask how you're editing your code. Are you working entirely from the web browser or are you also doing some of this in app studio or dev studio?

If you're using app or dev studio, try saving your report and then log on to the web browser to run it. Maybe that'll do something different.

Let me know what you're seeing.

[Guest]

One other thing that bugs me in your screenshots is the URL shown at the top of your window. Why does it want to run editor.bip? That's puzzling.

Please let us know if you make progress on this. We've gone pretty far down the debugging trail and not found the right ticket.

Please check your mime.wfs redirection settings as I showed in a post above.

editor.bip tries to open an excel file instead of it being redirected to open a file.... I'd say this sounds like a windows registry thing, but you are able to do a simple table file car example. Something about your application is not known to us (where it's run, or how it's being run).

I would suggest when you open a case, you try to get them to watch remotely as you make the problem occur.