Wrong Sorting after Download

[apirker]

Hello!

I have a WEBI-Report with only 2 Columns (a date and a value)
The user can show the report online, and then download it
as a csv-file to use it with Excel.

Now the Problem:
After the user downloads the report, the sorting is wrong! (the sorting of date in WEBI is correct)
its like:

...
2001-05-31;-2.30
2001-06-04;4.12
2001-06-01;0.23
2001-06-02;3.45
...

Any ideas why this happens?
TIA
Anton Pirker

 

[blom0344]

Hello Apirker,

As comma seperated files do not work with sorted fields, this is quite understandable. The data at each line is regarded as a string, which automatically prevents any sorting on the date-part of the total string. Only after importing in Excel or Access etc. you can create a sort on the date-part again.
Considering this, is there any reason you want a sorted .csv file anyway?

 

[apirker]

following i noticed:
in WEBI there is no sorting on any column of my table.
if i run the report the date-column is sorted (i think
it gets the right order from the db)
but after download the "sorting" is another than in the
report.

why is it inpossible to export the data with the sorting?
is there another way to save a report as an excel-file?

our client wants only to open the downloaded file and
have all his sortings right... (he is not able to sort
it with excel.)

TIA
Anton

 

[blom0344]

Hello Apirker,

You have a few misconceptions about report and database functionality. There is no specific sorting within databases concerning records. If you create a report with BO on this data you start forcing sorts on the data you retrieve. But if you simply download the data that is delivered to you by the data provider , you do not use any report functionality, so if data looks sorted it is by accident. (Unless of course, you modify the SQL by applying a ascending or descending order on the date-field)
This you can do in the query panel.
An alternative would be (I suspect this works in Webintelligence too), to use edit --> copy all and let your user paste it into any kind of standard tool (wordpad, word)
This will not get you a .csv file, but if the acceptant not even uses Excel, it might serve him fine to have the contents of your BO-table exactly copied as is to his tool.

 

[apirker]

Hello blom!

first thanks for your help.

OK, i can only export the data of the data-provider, not the prepared data of my report.

I am working now for over a year with BO and get more and more depressed... There is an "Save as HTML" (where the report data will be exportet to html, not the plain data-provider-data)
but there is no chance to do the same with CSV? i think thats stupid....

modifying the sql would maybe a solution, but its not possible in WEBI.

the thing with the copy and paste is an idea, but: the customers are as stupid as bread

thats the truth: it would be much easyier to generate a csv file with a simple sql-quey in sql-plus and spool the output to a file....

(i dont like bo)
cheers
Anton

 

[blom0344]

Hello Anton,

Sorry, you don't like BO. I'm a devoted fan, but we use it mainly in its full client guise.
I've no real suggestions anymore , but it still puzzles me how your customers are able to work with something as advanced as a business intelligence tool , not smart enough to understand copy/paste and have no access to something as basic as excel (or quattro pro , lotus 1-2-3) It's sounds like a awful complex way of transfering data that's has a destination as a .csv file.
I hope another forum member comes up final a bright idea.

blom0344

 

[apirker]

this is a really complex customer.
i mean, the project-leader at customers side is really cool. but the users of the system are dumb. i think they would be able to sort a excel-sheet, they simply dont WANT to.... but the customer is king, so the poor developer has to jump if he says...

i will get a solution (at least i am believing it)

Thanks for your help!

 

[DrSmyth]

Apirker,

Hi, I don't use Webi so not sure if this would work, but it does work with full client BO. We too have some less than intelligent customers who want things on a plate.

Why not save the webi report as a text or HTML file (I do this overnight using using scheduled tasks on my PC and some VBA) and set up a link to this file either via MSAccess or Excel (Using MSQuery).

Then it's simply a matter of either providing your customers with the linked Excel file or just some kind of database report. I run the text files through a database, where i sort and format and then have several spreadsheets for clients to view with all the latest formated information.

As I said, I dunno if this would be suitable in your situation, but it might.....

Andy

 

[blom0344]

Andy, if you are going to revive VERY VERY OLD threads make sure you read through them.

Your solution (I call them refreshable sheets) would be very demanding on a population that does not know how to sort data within Excel

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

I disagree Ties Blom. Although i must admit that i didn't notice the date on the thread so am probably a little late.

The original thread refers to a single report. If one person in the know sets up the spreadsheet with the external data source linked to the refereshable data with a bit of vba to sort on open. Then all the end users have to do is open the spreadsheet and the data will be there and sorted.

I have an audience of about 150 users who are in the main absolutely clueless about excel. Yet they are all able to open a spreadsheet and view refreshed, formatted data, along with a few pivot tables that i have set up. I don't see whats demanding about this?

Perhaps i've missed the point somewhere, but I think that once these things are in place anybody can use them.

Andy

 

[blom0344]

Still, where does BO come in?

If you are heading the way you describe you can just as well create a directly refreshable sheet on the database itself. That is the way I work with refreshable sheets for our foreign agencies. With MS Query and proper connection you're done...

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Bo comes in to play with my limited SQL knowledge and the trickyness of combining a DB2 database's dates with microsoft products.

I've had a world of hurt using DB2 dates with any microsoft products (MSQuery, Access), for some reason they don't seem to like each other. Don't know if it's the thin client middleware, JET sql or DB2 databases combined with microsoft in general. But I can't get the two to combine with any great success when dates are involved

I also find it a quick and easy way of providing an easily readible data source. Perhaps I am going the long way round things, might be worth me taking another look at using a more direct data source for MSQuery.

 

[blom0344]

Well, about 80% of my BO documents and all my refreshable sheets run on DB2 datamarts, so if I can assist in any way, then let me now.

For me, the bottleneck seems to be MS Query itself. It does not allow very much , so the full potential of DB2 seems to require the use of pass-through queries from Access...



Ties Blom
Information analyst
tbl@shimano-eu.com