i have a business and we bought a linux based data management system from a third party.
There reports are limited. I would like some data pulled from the software in a relatively raw form so i can plug it into something super easy like excel and make a pivot table or something of the sort.
currently one of their reports gets all the data i want, but rearranges it in a manner i cant use or manipulate with a macro in excel.
They have suggested buying faircom ODBC and crystal reports. I bought msdn 2 years ago. I know i have crystal reports. do i have to buy faircom or is that in there too? how difficult will this be getting the data from a database that i dont know the structure of?
The software developers are looking for me to buy faircom odbc for 99 and then charge me to set up this query. I work well in msaccess and am good with sql on a novice level (low level intermediate ). I know i can do it, is it feasible time wise? am i getting overcharged... Please help! thanks
I have no idea, unfortunately. I'm trying to think if there is another area of this board or another than can help you and am coming up blank.
dumb question but how is the data pulled currently that makes it so unruly?
This is coming out in Crystal, Excel or what format?
If it's Crystal, and you are able to manipulate the report, I think I can help.
If it's already in Excel, it seems like a macro could do it but I'm not good enough with excel macros to tell you exactly how.
If it's some other form, well we'd have to see.
The way I'm imagining it is as a crystal report in which those three fields are the details, so all we would need to do is adjust the structure so they are displayed horizontally rather than vertically, then you could export to excel.
They may be grouped
all drugs cover by that insurance
all rx nums of scripts for that drug as covered by that insurance
which is more tricky although it make it easier to do it with a macro rather than crystal.
its actually coming out in a report format specific to the software. i was able to convince them to print this to file vs printer. i then have a text equivalent of this report which i can put into excel. then is the problem... i can convert text to columns but it gets messy w/the headers and the different "layers" of the report.
the crystal reports thing is a suggestion on their end. i personally like the ease of pivot tables. therefore, i'm guessing if i have the faircom odbc i can access the tables in the program and spit out record sets into something "import-able" into excel.
It's your call in the end. The ODBC may be easier but I sincerely believe that you could run this through a program/Excel with VB and have it processed. I don't know the code, you'll have to head over to the Excel forum armed with the kind of thing you posted above and they could tell you how but you basically would ignore the first line, grab everything before the space and store it as a variable.Go to the text after the first three >'s. You may have to figure out how to handle the drug name because that'll be pretty variable. Then continue that kind of process on down.
It kinda comes down to whether you want Expensive and Fast or Slow, Difficult and Cheap.