Results 1 to 8 of 8
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: faircom/crystal reports

    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

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    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?

  3. #3
    Join Date
    May 2007
    Posts
    4
    i have a pharmacy so... its 47 pages... headers on each page... looks like this:

    [header]

    <insurance plan> (about 50 of them)
    <drug> (alot, to say the least)
    <rx numbers of this drug on this plan with reimbursement info>


    i want this:

    <ins plan> <drug> <rx info>

    its a report, i just need the recordset.
    i dont know if i should just find someone to make a super macro for me... seems like a big pain tho.

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    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.
    OR
    They may be grouped
    Insurance
    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.

  5. #5
    Join Date
    May 2007
    Posts
    4
    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.

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    So as I understand it you are presented with a .txt file that goes something like

    Report Header
    Page Header
    Column1Row1data
    Column2Row1data
    Column3Row1data
    Column1Row2data
    Column2Row2data
    .
    .
    .
    Column2RowMdata
    Column3RowMdata
    PageFooter
    PagerHeader
    Column1RowNdata
    Column2RowNdata
    .
    .
    .
    Column3RowZdata
    ReportFooter

    or something like that and you want to turn it into an excel spread sheet were all of row 1's data actually goes on row 1 and so on.
    Do I have that correct?

    If so, I do believe the cunning use of counters in Macros is the way to go.

  7. #7
    Join Date
    May 2007
    Posts
    4
    Here's example data:

    Pharmacy Name
    Report
    1/3/07

    Plan Name Average Paid Average Fee % Margin
    Empire 4.9345 1.00 90.03

    >>>Drug Name NDC Quantity Average Paid Average Fee
    >>>Furosemide 40 mg 1234-443-10 140 .056 1.00

    >>>>>>Rx Number Quantity AWP Paid Cost Fee >>>>>>423444 30 1.12 .04 0.03 1
    >>>>>>412983 60 1.12 .05 0.03 1
    ...

    >>>Drug Name NDC Quantity Average Paid Average Fee
    >>>Lipitor 10 mg 1234-443-10 280 1.67 1.00

    >>>>>>Rx Number Quantity AWP Paid Cost Fee
    >>>>>>403444 30 2.12 1.89 .96 1
    ...


    Plan Name Average Paid Average Fee % Margin
    Medicaid .455 1.00 90.03
    ...

    I want to see this:

    EMPIRE Furosemide 40 mg 1234-443-10 423444 30 1.12 .04 0.03 1
    EMPIRE Furosemide 40 mg 1234-443-10 412983 60 1.12 .05 0.03 1
    ...

    so i want the data from the RX number section with the drug name/ndc and the plan at the beginning.

    doable?

    should i just buy the odbc and run queries on the beast? memorial day is coming up and wife's working 10 hours, i can go in and screw around then!

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •