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.
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 9, 2020 at 2:08 pm
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.
June 9, 2020 at 5:09 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2020 at 11:33 am
This was removed by the editor as SPAM
August 5, 2020 at 11:41 am
This was removed by the editor as SPAM
August 12, 2020 at 9:48 pm
This was removed by the editor as SPAM
August 12, 2020 at 9:54 pm
This was removed by the editor as SPAM
August 13, 2020 at 3:54 am
I'd very much favor a nightly job... might want to investigate "Automate", which can make automating a nightly web-site download into a piece of cake... It's not free, but it can definitely do the job. Also, steer clear of the JSON files, as that just slows down the import process to deal with all the string processing needed to handle the JSON format, and that can be significant.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2020 at 5:12 pm
This was removed by the editor as SPAM
September 18, 2020 at 5:16 pm
This was removed by the editor as SPAM
September 27, 2020 at 5:21 pm
This was removed by the editor as SPAM
September 28, 2020 at 1:44 pm
This was removed by the editor as SPAM
October 14, 2020 at 2:22 pm
This was removed by the editor as SPAM
October 21, 2020 at 2:16 pm
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply