SSRS 2016 and older, with APIs

  • Rod at work

    SSC-Dedicated

    Points: 33376

    I am working on converting an old MS Access report to SSRS, making some much needed improvements along the way. This is for our pharmacy. I've got to incorporate what's known as a National Drug Code (NDC) value into the report, as a flag. I won't go into the details, as its not pertinent to my question. What is pertinent is I've got to reference what is known as the NDC Database, located here. Looking at that link I see that the US Food & Drug Administration has provided various REST APIs. I'm thinking that might be the best way to retrieve the data I need, when generating the report.

    However, I've never called an API before in an SSRS report. I don't even know if it's possible. I've search for it using SSRS that would use an API. One website I came across was Microsoft's documentation site Develop with the REST APIs for Reporting Services. This isn't encouraging to me, as it talks about SSRS 2017 and newer. The newest SSRS we've got here is SSRS 2016.

    So, I'm wondering a few things. First, am I correct, that SSRS 2016 won't call APIs that someone else, such as the US Food & Drug Administrations provides?

    If the answer to question one is yes, then I thought maybe I could write a separate app, that retrieves the needed information from the NDC Database, puts it "somewhere", then have the SSRS report incorporate it somehow. Does that make sense? Has anyone done that before?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    I would probably look at downloading the text file and uploading the data into table(s) in a reference database.  I could then join to the table(s) to get the values needed for the report.

    This can be automated - but it gets a bit tricky.  You have to create a process that checks the web site for a new file - downloads the zip archive, unzips the file(s) and then loads the table.  It can be done using SSIS but I don't like what has to be built to download the files from a website.

    If it is possible to download as needed - then it is much easier as all you do is manually download the zip archive and place that in a specified inbound folder and the SSIS package can then pick it up, un-archive the files - and process them into the final tables.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Rod at work

    SSC-Dedicated

    Points: 33376

    According to the NDC Database website, they update their database daily. So, if I take your approach, we could have a job do it daily. Probably overnight.

    I experimented a bit with that yesterday. Doing a bulk download would require downloading multiple JSON files, which would then need to be loaded into a database. Yesterday I downloaded one, in a .zip file. I unzipped it and tried to open it in Notepad++. Notepad++ failed to open it, telling me that the file was too big to load. I've never had that happen before, with Notepad++. It was a large .json file; about half a GB. Since they're several (less than 100), that's represents a lot of data to pull down every night.

    Ultimately, I think being able to call the API would be the best. But we're at the wrong version of SSRS. I don't see how I could insert a call to some software module into SSRS 2016. Hey, something like what you've suggested is probably the way I'll have to go.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    Not sure why you are messing with the JSON files - they provide the files in text or Excel format.  From this page: https://www.fda.gov/drugs/drug-approvals-and-databases/national-drug-code-directory

    Unless your report is limited to a very few items - calling out to the API would most likely take a long time, as it would have to call that API for every row in the results.  I would not look at doing something like this in a report unless it was done as a 'link' to a separate report and rendered only when that link is clicked and setup for a single 'lookup' to the NDC data for that specific item.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply