The ALIS API is a data tool that empowers your team to access hundreds of data points captured in ALIS for your own discrete reporting needs. This guide outlines two powerful methods for accessing data via the ALIS API (v2), each tailored to a specific destination.
For more information on ALIS API Exports, refer to this guide: ALIS API Reference Guide.
About Paged Exports (Microsoft Suite)
Depending on your end goal, you will choose one of the following paths:
- Advanced Power Query (Excel): This option is ideal if you need a spreadsheet and want to see rows and columns, perform manual calculations, use Pivot Tables, or save a static file to your computer. Using the Advanced Power Query provides you a workbook where data is loaded directly into sheets.
- Power BI: This option is ideal if you need a visual dashboard and you want to build charts, graphs, and KPIs that can be published online and shared with stakeholders. Using Power BI provides an interactive report hosted in the cloud.
Even though both tools look similar, they require different scripts (code) to function correctly:
The Excel Script is optimized for local performance on your desktop.
The Power BI Script is specifically modified to work in the cloud, allowing you to use Scheduled Refreshes so your dashboard updates automatically overnight.
Advanced Power Query (Excel)
Create a "live" connection in Excel that automatically loops through all pages of data, eliminating the need for manual downloads. Use this method if you need to build dynamic reports, operational spreadsheets, or merge data from multiple endpoints.
Once you have selected your endpoints from the ALIS API v2 Index, follow these steps:
Step 1: Open the Power Query Editor
Open a blank worksheet in Excel. Be sure to have ALIS API v2 Index open in a separate tab for quick reference.
Click on Data > Get Data > From Other Sources > Blank Query. This will open the Power Query Editor window.
Once in the blank Power Query Editor, you must add a new query. To do so, click Advanced Editor towards the top left of the Home tab, or right click 'ABC Query1' and select Advanced Editor from the sub menu.
Step 2: Insert the script (query)
Copy and paste the following script (query) into the Advanced Editor, then move on to Step 3. For other example query templates you can copy based on specific ALIS API endpoints, refer to this ALIS API v2: Microsoft Suite Templates (to copy) guide.
let
SourceUrl = "https://api.alisonline.com/v2/export/[PATH]?pageNumber=1&pageSize=10000",
RequestHeaders = [
#"Content-Type" = "application/json"
],
GetPage = (url as text) as record =>
let
RequestHeaders = [
#"Content-Type" = "application/json"
],
WebContent = Web.Contents(url, [Headers = RequestHeaders]),
JsonContent = Json.Document(WebContent),
Result = JsonContent
in
Result,
GetAllPages = (initialUrl as text) as list =>
let
PageList = List.Generate(
() => [
CurrentUrl = initialUrl,
Page = GetPage(initialUrl),
HasMore = true // Flag to control the loop
],
each [HasMore],
each let
nextUrl = [Page][nextPageUrl],
nextPage = if nextUrl <> null and nextUrl <> "" then GetPage(nextUrl) else null
in
[
CurrentUrl = nextUrl,
Page = nextPage,
HasMore = nextPage <> null
],
each [Page]
)
in
PageList,
AllPagesData = GetAllPages(SourceUrl),
ListOfItemLists = List.Transform(AllPagesData, each [items]),
CombinedItemsList = List.Combine(ListOfItemLists),
ResultTable = Table.FromList(CombinedItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedTable = Table.ExpandRecordColumn(
ResultTable, "Column1",
Record.FieldNames(CombinedItemsList{0})
)
in
#"ExpandedTablePasted query:
Step 3: Configure your endpoint
Next, you need to locate the SourceURL ("https://api.alisonline.com/v2/export/[PATH]?pageNumber=1&pageSize=10000") based on the request you are trying to make which you will use to update the pasted query. You will likely also need to update the endpoint Parameters.
To locate the SourceURL and update Parameters, follow these steps:
Separately navigate to the ALIS API v2 Index page.
Click the down arrow to the far right of the desired endpoint to expand the section.
-
Under 'Parameters', update the Description fields. Example Descriptions:
The "monthAndYear" field follows the "YYYY-MM" format.
A "communityId" number can be located within your company URL when you navigate to any page in ALIS. Alternatively, locate the ID for all communities within a company portfolio by clicking the Communities dropdown menu > Community Tab > Export to Excel. The "DB ID" column shows each community's unique ID.
Once parameters are updated, click the blue Execute button.
In the 'Responses' section, select and copy the Request URL.
Example Source URLs
Alternatively, you can update the parameters (e.g., "[MONTHANDYEAR]") within these example SourceURLs:
HQ Occupancies Source URL:
https://api.alisonline.com/v2/export/communities/floorPlan/hqOccupancie?pageNumber=1&pageSize=10000&monthAndYear=[MONTHANDYEAR]&communityId=[COMMUNITYID]Order Administration Source URL:
https://api.alisonline.com/v2/export/clinical/orderAdministration?pageNumber=1&pageSize=10000&communityId=[COMMUNITYID]&startDate=[STARTDATE]&endDate=[ENDDATE]GL Transactions Source URL:
https://api.alisonline.com/v2/export/billing/glTransactions?pageNumber=1&pageSize=10000&monthAndYear=[MONTHANDYEAR]&communityId=[COMMUNITYID]&payerType=[PAYERTYPE]Recorded Care Source URL:
https://api.alisonline.com/v2/export/care/recordedCare?pageNumber=1&pageSize=10000&communityId=[COMMUNITYID]&careStartDate=[STARTDATE]&careEndDate=[ENDDATE]
Step 4: Replace SourceURL, Authenticate, and Load
Navigate back to the Advanced Editor window in Excel.
Paste the Request URL (that you copied in above Step 3) over the existing SourceURL at the top of your query. Then click Done.
Next, you may see a yellow warning bar asking for credentials. Click Edit Credentials.
-
In the 'Access Web Content' pop-up window, enter the following credentials:
Select Basic (on the left sidebar).
Enter your generated API User name and Password.
Select Level (https://api.alisonline.com/)
Click Connect.
Once the data preview loads, click Close & Load (top left) to return to Excel with your data.
PowerBI Reports
Create an interactive, visual dashboard that lives in the cloud. Use this method if you want to publish charts, graphs, and KPIs to share with stakeholders.
Important Note: The script used here is different from the Excel version. It is specifically designed to handle "Scheduled Refreshes" without generating data source errors.
Step 1: Initialize the Report
Open Power BI Desktop and create a New Blank Report.
On the Home tab, click Get Data > Blank Query.
This will open the Power Query Editor.
Step 2: Insert the Power BI Script
In the editor, right-click Query1 (in the left sidebar) and select Advanced Editor.
-
Paste the Power BI-specific script into the window. (See the example template below). For example query templates you can copy based on specific ALIS API endpoints, refer to this ALIS API v2: Microsoft Suite Templates (to copy) guide.
let // 1. Define the static root URL (required for scheduled refresh) RootUrl = "https://api.alisonline.com", // 2. Define the static base path and initial parameters // The [PATH] part should be replaced with the actual static path component. StaticPath = "/v2/export/[PATH]", InitialQuery = [ pageNumber = "1", pageSize = "10000" ], // Helper function to build the query parameters for a given page number GetQueryParams = (page as number) as record => [ pageNumber = Text.From(page), pageSize = "10000" ], // 3. Rework GetPage to use RelativePath and Query options GetPage = (page as number) as record => let QueryParams = GetQueryParams(page), // Use Web.Contents with RootUrl, RelativePath, and Query WebContent = Web.Contents( RootUrl, [ RelativePath = StaticPath, Query = QueryParams, // Power BI can now 'see' the static RootUrl Headers = [#"Content-Type" = "application/json"] ] ), JsonContent = Json.Document(WebContent), Result = JsonContent in Result, // 4. Update the logic to use List.Generate with a page counter GetAllPages = () as list => let PageList = List.Generate( () => [ PageNumber = 1, Page = GetPage(1), HasMore = true ], // Condition to check if there is data on the page each List.Count([Page][items]) > 0, // Assuming 'items' contains the data each let nextNumber = [PageNumber] + 1, nextPage = GetPage(nextNumber) in [ PageNumber = nextNumber, Page = nextPage, HasMore = List.Count(nextPage[items]) > 0 ], each [Page] ) in PageList, // 5. Rest of the transformation remains similar AllPagesData = GetAllPages(), ListOfItemLists = List.Transform(AllPagesData, each [items]), CombinedItemsList = List.Combine(ListOfItemLists), // Assuming the structure is consistent: records in a list // Use the record fields from the first item to define the table columns SampleItem = CombinedItemsList{0}, ResultTable = Table.FromList(CombinedItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandedTable = Table.ExpandRecordColumn( ResultTable, "Column1", Record.FieldNames(SampleItem) ) in ExpandedTable
Click Done.
Once the preview data loads successfully, click Close & Apply in the top left corner.
Step 3: Load and Publish
To build your report, use the Data pane on the right to drag and drop columns to create tables or charts.
When finished, click the Publish button in the top ribbon to upload the report to your online workspace.
Power BI: Turn on Scheduled Refresh
Once you have published your Power BI report, you must configure it to update automatically so your stakeholders always see fresh data.
Step 1: Access the Semantic Model
Open your web browser and navigate to your Power BI Workspace.
Locate your report in the list.
-
Find the Semantic Model associated with your report.
Note: This icon looks like a database/hard drive, whereas the Report icon looks like a bar chart.
Step 2: Configure Credentials
Hover over the Semantic Model name and click the Settings (gear icon) or the Refresh icon.
If this is your first time, you may need to go to Data Source Credentials and enter your ALIS API User name and Password (select "Basic" authentication).
Step 3: Set the Schedule
Expand the Refresh or Scheduled Refresh section.
Toggle the switch to On.
Set your desired frequency (e.g., Daily) and time (e.g., 6:00 AM).
Click Apply.
Related Articles:
- ALIS API Reference Guide
- ALIS API v1: Standard Excel Exports
- ALIS API v2: Templates to copy (Microsoft Suite)
- ALIS API v2: Paged Exports (Google Workspace)
- ALIS API v2: Templates to copy (Google Workspace)
Comments
0 comments
Please sign in to leave a comment.