Using AI to Build Diversion Reports

What to Ask Your IT Team

Power BI Desktop is free to download and run on a Windows computer. That does not mean you can install it. Most hospital devices block software that is not in the approved catalog, and sharing a finished report through the Power BI service requires a license that your organization may or may not already hold. The request to IT goes faster when you ask specific questions and ask to join things that already exist.

Six Questions for the Ticket

1. Is Power BI Desktop in our approved software catalog? If yes, ask how to request it: a self-service software portal, a ticket, or a manager approval. If no, ask what the review process is to add it, and submit that request. Installation follows the review.
2. Do I already have a Power BI license? Many health systems hold a Microsoft 365 agreement that includes Power BI Pro for every user. If so, publishing a report costs nothing extra. If not, ask what a single Pro license costs and who approves it.
3. Is there an existing Power BI workspace for pharmacy, compliance, or quality? Joining a workspace that already passed security review is far easier than creating a new one. Ask who administers it.
4. What is the approved way to get data into a report? The options are usually a read-only database account, a scheduled export dropped into a secure network folder, or an on-premises data gateway that already exists. Ask which one IT prefers. Each is a known pattern for them.
5. Which AI assistant is approved for staff use, and for what data classification? Many organizations license an enterprise assistant that is covered by their Microsoft or other vendor agreement. You need to know which tool is approved and whether it is cleared for internal data, confidential data, or neither.
6. Who signs off on a new report that contains workforce data? Dispensing data names staff. Privacy, compliance, or HR may need to approve the audience before the report is shared. Finding this person early saves weeks later.

How Power BI Is Licensed

The licensing questions above make more sense with the product tiers laid out. Power BI is several products sold under one name, and IT will answer differently depending on which one you ask for.

Power BI Desktop

The free Windows application where reports are built. It needs no license. In some organizations the Microsoft Store version installs without administrator rights; in others the Store is blocked. Ask before trying either route.

Power BI Pro

A per-user license that allows publishing to the Power BI service and viewing shared reports there. In the standard setup, both the publisher and every viewer need Pro. It is bundled in some Microsoft 365 enterprise plans, which is why question 2 matters.

Premium Per User

A higher per-user tier with larger datasets and more frequent refresh. Rarely needed for a diversion report.

Premium or Fabric capacity

Organization-level capacity that IT buys. Reports in a workspace on a large enough capacity can be viewed by staff who hold only the free license. If your organization has it, your viewers may need no license at all. Ask.

Report Server

An on-premises server edition that some hospitals already run for existing reports. If IT names it, it is a valid publishing target.

Two facts reduce the licensing problem for a first project. A saved report file can be opened by anyone who has Power BI Desktop, with no license. Power BI Desktop can also export a report to PDF, which can be distributed through the same channels as any other confidential document. A first report can be built, reviewed, and used before any license question is settled.

A Request You Can Paste

I am building a controlled substance surveillance report for the diversion prevention program. I would like to use Power BI Desktop on my work device to build it, read data from a read-only extract, and publish to an existing pharmacy or compliance workspace if one exists.

Could you tell me:
1. whether Power BI Desktop is in the approved catalog and how to request it,
2. whether my account already has a Power BI Pro license,
3. whether a pharmacy or compliance workspace already exists and who administers it,
4. the approved way to receive a recurring data extract (read-only account, secure folder drop, or gateway), and
5. which AI assistant is approved for staff use and what data classification it is cleared for.

The report will contain staff dispensing activity and no patient identifiers. I will not use any AI tool with real data.

When IT Says No

A refusal usually applies to one specific request: a new install, a new workspace, or a new data connection. Most of the work can still be done inside tools that are already on your device.

Paths That Need No New Software

• Excel. Power Query, the data-shaping tool inside Power BI, is also inside Excel on Windows. Pivot tables, conditional formatting, and standard charts cover most of what a compliance report needs. An AI assistant writes Excel formulas and Power Query steps as readily as it writes DAX. Start the project in Excel and move to Power BI later if you get approval.
• The vendor's own reporting console. Dispensing cabinets, the EMR, and diversion surveillance platforms all ship with report builders and scheduled exports. The AI can help you design the export, choose columns, and interpret the output, even when it cannot touch the tool.
• A shared analytics workstation or virtual desktop. Many organizations keep a few machines where Power BI is already installed. Ask whether you can book time on one.
• An analyst builds the connection, you build the report. Ask the analytics team to set up the extract or data model once. You own the visuals and the definitions. This divides the work along the lines each side is good at.

The Access Ladder

Each level below is a complete way to deliver a report. Start at the level you have today and move up as approvals arrive. Nothing built at a lower level is wasted, because the measure definitions, the synthetic test set, and the validation log carry forward unchanged.

Level 0: Excel only

Power Query loads the extract, a pivot table or formulas compute the metrics, and standard charts display them. Deliver as a PDF or a protected workbook in an approved folder. Refresh by opening the file and clicking refresh.

Level 1: Desktop, no publishing

Build the full report in Power BI Desktop. Deliver as a PDF export, or send the report file to a reviewer who also has Desktop. Refresh is manual.

Level 2: Published to an existing workspace

Viewers open the report in a browser or the mobile app. Refresh is still manual unless a gateway exists, so you republish after each new extract.

Level 3: Scheduled refresh and row-level security

The gateway refreshes the data on a schedule and each manager sees only their unit. IT owns this level with you, and it is a second project.

A Smaller Ask

IT responds to bounded requests. Propose a pilot: one report, one unit, one data source, read-only, 60 days, with a named sponsor in pharmacy or compliance. A pilot is easier to approve than an open-ended program, and a working pilot is the strongest case for the full request.

Understanding IT Concerns from a Clinician's Perspective

IT and security teams are accountable for a data breach in the way pharmacy is accountable for a diversion. When they object, they are describing a risk they own. The vocabulary below translates the common objections.

Not in the catalog

Software has to pass a security review before anyone can install it. The answer is to request the review, not to find another download.

Data egress or exfiltration

Data leaving the hospital network. Pasting rows into a website counts. Their specific fear is patient data inside a consumer AI tool, which is also the one thing this page tells you never to do.

No BAA

A Business Associate Agreement is the contract that lets a vendor hold protected health information on your behalf. Consumer AI tools do not have one. Some enterprise tiers do. Without a BAA, no PHI may touch the tool, and your process should be designed so that none ever needs to.

Read-only service account

A login that can read a database and change nothing. This is the easy yes. Asking for it signals that you understand the risk.

Gateway

The connector that lets a published report refresh from a database inside the hospital network. If one already exists, your scheduled refresh is a configuration task. If not, it is a project that IT has to own.

Row-level security

A rule that lets each viewer see only their own unit or role. It is how a report that names staff can be shared with unit managers without exposing the whole organization.

Data classification

Labels such as public, internal, confidential, and restricted. Dispensing data is confidential or restricted. The label decides which tools and storage are allowed to hold it.

Shadow IT

Tools and data flows that IT does not know about. A spreadsheet of staff dispensing on a shared drive nobody governs is the example they are thinking of. A registered workspace with a named owner is the alternative.

Endpoint or attack surface

The device itself. Installers downloaded from the open internet are a common way malware enters. The catalog version of the same software has been checked.

Minimum necessary

The HIPAA principle that a report should contain only the data needed for its purpose. If the report works without patient identifiers, it should not contain them.

Frame the conversation around the controls they already trust: approved software, approved storage, read-only access, a named owner, and a defined audience. When the request fits those controls, the objections usually fall away.

Getting the Right Data

Almost every number a diversion report needs is already being recorded. The work is choosing one source, asking for a clean recurring extract, and resisting the urge to join everything on the first attempt.

Where the Data Already Lives

• Automated dispensing cabinets record every removal, waste, return, override, count, and discrepancy, with the user, the drug, the quantity, the location, and a timestamp. The vendor console exports these as spreadsheets and can usually email a scheduled export.
• The EMR holds the medication administration record, orders, barcode scanning events, pain scores, and patient movement. Its reporting tools or reporting database can produce a scheduled extract. Barcode scan compliance comes from this source alone.
• Diversion surveillance software, if your organization has it, already reconciles dispensing against administration and scores risk. It exports its own tables and case lists.
• Scheduling and time-and-attendance systems tell you who was on shift, which turns an off-hours transaction from a guess into a fact.
• HR supplies role, unit, hire date, and termination date, which define peer groups and catch access after separation.

The data landscape section of the Power BI guide lists the key fields to request from each source. The Analytics Dashboard Blueprint defines the metrics those fields feed.

Start with One Source and One Question

Override rate needs only the cabinet log. Barcode scan compliance needs only the EMR. Waste documentation lag needs only the waste log. Each of these is a complete, useful first report from a single table. The dispense-to-administration gap needs two sources joined on a shared key, and the join is where most first projects stall. Treat it as the second project.

What to Ask for in the Extract

• One row per event, with a stable event identifier.
• Fixed column names that will not change between runs.
• Timestamps in one time zone and one format, with the date and time together.
• Identifiers rather than names where the source offers them: employee ID instead of employee name, a patient token instead of a medical record number.
• Ninety days of history to start, delivered as a plain CSV or spreadsheet.
• A data dictionary: one line per column saying what it means and what values it can hold. If none exists, ask the system owner to walk you through the columns and write it yourself. You will paste this dictionary to the AI instead of the data.

What AI to Use, by Capability

The right choice is whichever approved tool has the capabilities below. Brand matters less than the clarity of your description of the data. The prompt library describes the tool categories; this section describes what to look for inside any of them.

Capabilities You Need

• Code generation with explanation. The tool must write Power Query steps, DAX measures, Excel formulas, and SQL, and explain what each line does when asked. A measure you cannot explain is a measure you cannot validate.
• Conversational iteration. You will paste an error, get a fix, paste the next error. The tool must hold the thread of a session and remember the column list you gave it at the start.
• Reads a document you paste. Your data dictionary and project brief go in as text. The tool should work from them without needing a file of real rows.
• Reads a screenshot. Optional but useful. A cropped screenshot of an error dialog or a formula bar is faster than retyping it. Never screenshot a visual that shows real data.
• Approved by your organization for internal data. Your prompts will contain column names, metric definitions, and synthetic rows. None of that is patient data, but it is internal. Use a tool your organization has cleared for that classification.
• Clear data-handling terms. Look for a statement of whether prompts are retained, whether they are used to train the model, and who in your organization can see them. Enterprise tiers typically let an administrator control these settings. Consumer tiers often do not.

What You Do Not Need

• A healthcare-specific or diversion-specific model. The task is writing formulas and explaining them. General assistants do this well.
• An assistant embedded inside Power BI. It is convenient when available, but a separate chat window plus copy and paste works for the entire build.
• A BAA, if you follow the process on this page. The process is designed so that no protected health information ever reaches the AI. A BAA is a safety net for mistakes, not a license to upload extracts.
• Autonomous agents, plug-ins, or anything that connects the AI directly to your data sources. For a first build, keep the AI on one side and the data on the other.

Designing an AI-Assisted Build Project

The AI will produce whatever you describe. Projects fail when the description is vague. Write a one-page brief before the first prompt, and paste that brief at the top of every working session so the assistant has the same context you do.

The Brief

Report name: Override Rate by Nurse, Medical-Surgical Units
Question it answers: Which nurses remove controlled substances by override more often than their peers, and is the unit's override rate trending down?
Audience: Diversion prevention coordinator (weekly), unit managers (monthly).
Decision it informs: Which users to review in the weekly surveillance meeting.
Data source: Dispensing cabinet transaction export, one row per transaction, 90 days of history, refreshed weekly.
Grain: One row per cabinet transaction.
Columns (data dictionary attached): transaction_id, employee_id, unit, medication, quantity, transaction_type, override_flag, transaction_datetime.
Metrics, in words:
  - Override count = number of removals where override_flag is true.
  - Override rate = override count divided by all removals, as a percentage.
  - Peer average = override rate of all users in the same unit.
Visuals: one card for unit override rate, one bar chart of override rate by employee_id with a peer-average line, one line chart of weekly override rate.
Filters: date range, unit.
Done means: the three visuals match a manual count on three sampled users, and the coordinator has signed the validation log.
Owner: [your name and role]. Reviewer: [pharmacist or analyst].

Scope Rules That Keep the Project Finishable

• One report, one data source, three to six visuals, ninety days of history.
• Every metric defined in a sentence before any formula is written. The sentence is what you validate against.
• Sketch the page on paper first: which tile goes where, what the title says, what the filter does. Describe that sketch to the AI.
• Decide the audience and the storage location before you build. A report nobody is allowed to see is not finished.
• Name a reviewer who did not build it.

Expectations and Time Estimates

The estimates below assume a first-time builder working on the project a few hours a week, with an AI assistant available throughout. They are planning ranges, not promises. Calendar time is dominated by approvals and data access, which you cannot shorten by working harder.

What Takes Longer Than Expected

• Shaping the data. Fixing column types, splitting a combined date and time field, removing duplicate rows, and handling blanks takes most of the build time. This is normal.
• Definitions change after the first chart. When stakeholders see the number, they refine the question. Budget one revision cycle.
• Publishing is its own project. Scheduled refresh, sharing, and row-level security involve IT and are separate from building the visuals. Plan them as a second phase.
• Checking the AI's output. The assistant will sometimes produce a formula that runs without error and computes the wrong thing. The validation section below exists for this reason. Set aside time for it in every session.

What to Tell Your Sponsor

A single-source report built by a clinician with AI help is a one-quarter project from first request to validated, shared report. A multi-source reconciliation report is a two-quarter project and benefits from a data analyst for the joins. Neither requires a budget line beyond licenses your organization may already own.

Building the Report in a Structured Way

AI assistants are reliable at syntax, boilerplate, explanation, translating between languages, and diagnosing error messages. They are unreliable about your data, your definitions, and the current location of menu items in a tool that changes monthly. The sequence below puts the AI on the tasks it is good at and keeps you on the ones it is not.

1. Load the extract and fix the types. Open the file in Power Query. Paste your column list to the AI and ask for the steps to set each column to the right type, split the timestamp if needed, and remove exact duplicate rows. Apply the steps. Do not change any values.
2. Add a date table. Ask the AI for a date table that covers your extract. The Power BI guide has a tested recipe you can paste in directly.
3. Write one measure at a time. Give the AI the definition sentence from your brief and the exact column names. Ask for the measure and an explanation of each function it used. Read the explanation. If it does not match your sentence, say so and ask again.
4. Test the measure before the next one. Put it in a table visual next to the raw counts. Check it against a hand count for one user and one week. Only then move on.
5. Build one visual at a time. Describe it in words: the chart type, what is on each axis, what the legend shows, what the filter does. Ask the AI which visual type fits and how to configure it.
6. Paste errors verbatim, after reading them. Error text is the most useful thing you can give the assistant. Read it first to make sure it contains no data values, then paste it.
7. Keep a build log. For each step, record the prompt, what came back, and whether it worked. This becomes your documentation and your evidence for the reviewer.
8. Ask for a critique. When the report works, paste your measure list and ask the AI what could be wrong with each one. It will often find double counting, a filter that does not propagate, or a division that should guard against zero.

The prompt structure section of the prompt library shows how to phrase these requests, and the DAX library has tested measures for the common diversion metrics that you can adapt instead of writing from scratch.

Validation Without Sharing PHI with AI

The whole method rests on one separation. The AI sees the shape of your data: column names, types, a data dictionary, your metric definitions, and invented sample rows. It never sees a real row. Everything it produces is code, and that code runs on your managed device, inside Power BI or Excel, against the real extract. The data and the AI never meet.

What the AI Is Allowed to See

• Column names and data types.
• Your data dictionary and project brief.
• Metric definitions written in words.
• Synthetic rows that you or the AI invented.
• Error messages that you have read and confirmed contain no data values.
• Formulas, measures, and queries, and the AI's own output.

What the AI Never Sees

• Any row from a real extract, including a single row pasted as an example. One dispensing row contains a staff member, a patient reference, a drug, a location, and a time. That is enough to identify both people involved.
• Medical record numbers, patient tokens from your real system, dates of service, or any of the HIPAA identifiers. Under the Safe Harbor standard at 45 CFR 164.514(b), dates more specific than the year and record numbers of any kind are identifiers. Removing names is not de-identification.
• Employee names or IDs. These are not PHI, but they are confidential workforce data and they are not needed for the build.
• Screenshots of any visual, table, or query preview that shows real values. Crop screenshots to the error dialog or the formula bar only.
• File paths, table names, or report titles that contain a person's name.

Build a Synthetic Sample Instead

Whenever a prompt needs example rows, use rows you made up. Ask the AI to generate them from your column list, then use that file for every subsequent prompt and for testing. A good synthetic set is small enough to count by hand and deliberately includes the edge cases you want your measures to handle.

Here is my data dictionary for a dispensing cabinet transaction extract: [paste dictionary].
Generate 40 rows of realistic but entirely fictional sample data as CSV with these exact columns. Use employee IDs E001 through E006, units MS4 and MS5, three fictional medication names, and dates in a single two-week period in 2024. Include: at least five override transactions, two rows with a blank witness field, one exact duplicate row, one transaction at 23:59 and one at 00:01 on the same night, and one waste event recorded 95 minutes after its removal. Then tell me, for each employee ID, the override count and override rate so I can test my measures against known answers.

Validation Tests You Run Yourself

The AI can help you plan these tests and can compute expected answers on the synthetic set. The tests on real data are yours, done inside Power BI or Excel, with no AI involved.

1. Synthetic known-answer test. Load the synthetic file into the same report. Every measure must return the answer you already know. This catches formula errors before real data is involved.
2. Hand count on real data. Pick three users and one week, including one user with zero overrides. Filter the raw extract in Excel and count by hand. The measure must match exactly. Record the three results.
3. Totals reconciliation. The row count in the report equals the row count in the extract. Unit totals sum to the grand total. A figure the source system reports on its own, such as total transactions for the month, matches the report.
4. Boundary tests. Check a transaction at midnight, the last day of the month, the day the clocks change, a duplicate row, and a blank witness field. Each should land where your definition says it should.
5. Filter test. Change the date and unit filters and confirm every visual responds and the totals still reconcile.
6. Independent review. A pharmacist or analyst who did not build the report reads each definition sentence, reads the matching measure, and repeats one hand count. They sign the log.

The Validation Log

Keep a simple table: date, test, expected value, actual value, pass or fail, who ran it. Store it with the report. It is the evidence that the numbers in the weekly surveillance meeting are right, and it is what HR and legal will ask for if a finding leads to an investigation. The governance section of the prompt library covers documenting the human review step.

A Clinician Can Build This

A nurse with no prior programming or Power BI experience built a barcode-scanning compliance report using AI as her only source of technical help. The report worked and produced excellent results for her program. There was no analyst on the project and no formal training beforehand.

A build like hers follows the pattern on this page. The builder describes the extract's columns to the assistant and asks for the Power Query steps to clean them. She defines the compliance measure in a sentence, asks for the formula, and asks for an explanation of it. She builds one visual at a time, pastes each error back, and checks the output against counts from the source system before trusting it. The programming knowledge lives in the assistant. The knowledge of what the measure should mean, which units to compare, and whether the number looks right lives in the clinician.

That division is the point. The hard parts of a diversion report are clinical and operational: knowing which question matters, which data answers it, how the workflow produces the numbers, and what a wrong answer would look like. A clinician or pharmacy leader already has that knowledge. The syntax is now available on request. With one approved tool, one clean extract, a written brief, and the validation habit above, a first useful report is within reach of anyone who can describe their own workflow clearly.