Different output/format when export in xls vs xlsx

<div>I am creating a report using crosstab, group per month with "MONTH(dtime)" in store procs (It only shows number of month). Then I use map properties to map my month 'data["mtime"] equal to 1, display value = January'.</div>
<div> </div>
<div>When I export in old version excel (xls), it shows my map month name (January, February,...) but in new version excel (xlsx), it can't read my map month (it shows month number 1,2,...)</div>
<div> </div>
<div>How do I make it so that even in xlsx, it will still show January, February,... using map.</div>
<div> </div>
<div>Another example: Format Number, Percent. In xlsx, it won't show the % only show the raw data (e.g: 0.5366), in xls, it will show 53.67%</div>
<div> </div>
<div>Anyone please help me, it's urgent.</div>
<div>Thank you very much.</div>

Find more posts tagged with

Comments

JFreeman

<p>Which version of BIRT are you using?<br>
Which XLSX emitter are you using?</p>
<p> </p>
<p>You could create a computed column instead and use a switch statement to change the number into a string for the month instead of using the mapping. This may work around the issue.</p>

DarkSilver

<p>I am using "Version: Kepler Service Release 1, Build id: 20130919-0819", I have also tried "birtviewer version 4.3.2" with the same result. </p>
<p> </p>
<p>Where do I find the <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">XLSX emitter?</span></p>
<p>I can only find "uk.co.spudsoft.birt.emitters.excel_4.3.1.v201308301349" in eclipse/plugins/uk.co.spudsoft.birt.emitters.excel_4.3.1.v201308301349/plugin.xml</p>
<p> </p>
<p>Is it correct? I will attached the "plugin.xml".</p>
<p> </p>
<p>What is "<span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">computed column", is it "Data Set" in palatte? </span></p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Can you let me know what are the code used for "</span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">switch statement to change the number into a string for the month"?</span></p>
<p> </p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Do you have any ideas about the %? I use a data set/ data binding, I want to know the % of my call so in (fx) I do it like call/grand total.</span></p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">and use </span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Format Number, Percent. Btw, It's all inside Cross Tab, I try to export my other report that has % but inside a table, I am able to see the %. Is it the cause?</span></p>
<p> </p>
<p><span>Thank you for your help.</span></p>
<div> </div>

JFreeman

<p>The spudsoft emitter includes an XLSX export option which is the default emitter for XLSX in the OS Designer.</p>
<p> </p>
<p>A computed column is configured within the Data Set through the Edit Data Set context.</p>
<p> </p>
<p>A switch statement in javascript is done like the following:</p>
<pre class="_prettyXprint _lang-js">
switch(row["MonthInt"]){
case 1:
"Jan.";
break;
case 2:
"Feb.";
break;
}
</pre>
<p>I would take a look at the w3schools page for more details on a switch statement in javascript.</p>
<p> </p>
<p>Regarding the %, could you attach a sample report that we can run to replicate the issue?</p>

DarkSilver

<p>I have created a sample report with the same way on how I did on my actual report.</p>
<p>Please take a look and let me know if there are some setting that can solve it.</p>
<p>I thought it's cause of the new version, but I tried to export to docx and pptx, it shows the month mapping and the % like what I want.</p>
<p> </p>
<p>Attached are my report and xls,xlsx files when I export it out.</p>
<p>Btw, where should I put in the "<span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">switch statement"? </span>is it on the report > script > initialize?</p>
<p> </p>
<p>Thank you for your help.</p>

JFreeman

<p>Alright, looking over the design you should not need to use a switch statement for the Month. In the Data Cube you can specify the format for Month to be (MMM) which will take care of converting the int into a string for the month. You will need to remove and re-add Month to the cross tab for the new formatting to take effect.</p>
<p> </p>
<p>Regarding the loss of formatting, this appears to be due to have 2+ aggregations in a single cell in the cross tab when using the spudsoft emitter. If you remove the percentage aggregations so that each cell only has 1 aggregate the formatting will appear properly in the XLSX output.</p>
<p> </p>
<p>The reason you do not see it in the XLS output provided is because that was not generated with the spudsoft XLS emitter. If you generate the report to XLS using SPUDSOFT_XLS output option you will get the same result as XLSX because XLSX is also using the spudsoft emitter.</p>
<p> </p>
<p>It may be possible to get around this by adding a grid or table into the cells with 2 aggregations in them and then dropping the aggregations into an individual cell in the grid or table.</p>
<p> </p>
<p>Otherwise, you may need to contact the creator of the spudsoft emitter for more details on why this occurs.</p>

DarkSilver

<div>I have tried to re-add the month data > format datetime > custom > MMMM but the Month format is still not taking effect.</div>
<div> </div>
<div>The % is able to show by using the grid, only left some problem which is it's not generating the month total so the column is not the same, it forward one column. </div>
<div> </div>
<div>"The reason you do not see it in the XLS output provided is because that was not generated with the spudsoft XLS emitter."</div>
<div>Then what is my XLS generate with now? Is it possible to let XLSX generate with the same (emitter?) with the currect XLS?</div>
<div> </div>
<div>Thank you again for for help.</div>

JFreeman

<p>The format for the month needs to be added in the Data Cube Builder versus in the format date/time on the data element inside the crosstab.</p>
<p style="margin-left:40px;"> </p>
<p style="margin-left:40px;">- Select to Edit the cube which will launch the Data Cube Builder dialogue.</p>
<p style="margin-left:40px;">- Select Groups and Summaries from the menu on the left then Month from the Groups and Summaries window on the right.</p>
<p style="margin-left:40px;">- With Month highlighted select the Edit button.</p>
<p style="margin-left:40px;">- This will open the Edit Data Level dialogue which has a drop down for Format which should be changed to your desired format.</p>
<p style="margin-left:40px;">- After making this change you will need to re-add the month element to the crosstab for the new date format to take effect.</p>
<p style="margin-left:40px;"> </p>
<p>The XLS generated was created with the non spudsoft excel emitter. It can be found in the ./eclipse/plugins directory and the jar is named "org.eclipse.birt.report.engine.emitter.prototype.excel_4.3.1.v201309131458.jar". To my knowledge this emitter cannot export to XLSX only XLS.</p>

DarkSilver

<p>Thank you very much again for all of your help.</p>
<p> </p>
<p>There is only one final problem left, can you take a look and let me know how to solve it. I have tried it myself but still can't.</p>
<p>When I export it to XLSX, the group total is missing, in result, all of the data after total forward by one column. What I want is the report like in XLS.</p>
<p> </p>
<p>Please help me. I will attached the reports.</p>
<p> </p>

DarkSilver

<p>It's also the same with mapping, is there any way to show mapping in XLSX?</p>
<p>for e.g: 1 - Chocolate, 2 - Strawberry...</p>
<p> </p>
<p>P.S: Please let me know in the future, when there is XLSX emitter that will show the same result with my XLS emitter now. It's so difficult other ways, to not get the result that I want. Btw, the PPT (old power point) can't open crosstab? it can't read.</p>