cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan47
Helper V
Helper V

Use SCCM Online Reports for Flow actions

Hi There

 

We are using Configuration Manager / SCCM and can access SQL Reports from the Browser Portal.

Now I got the Idea to use these Informations in Power Automate.

What I want to do is make a flow that runs a sql software report with a search parameter and use the result for adding the Users to a mailing list.

How do I get the report in?

 

Many Thanks

1 REPLY 1
Dan47
Helper V
Helper V

Just found this:

Power Query can read the atom file because it’s actually just a text file. You can open it in notepad and see it’s contents. It is an XML document that contains a URL. That URL can be used to download the report in your browser, but the encoding and formatting of it needs to be cleaned up a bit for Power Query to be able to use it. So save the atom file to your desktop, copy it’s file path, and paste it into the top line of the below query code. Run the query and BAM! The same data from the export to CSV option in SSRS should appear in Power Query.

//let
    FilePath = "C:\Users\Jeremy\SSRS_Example.atomsvc",
    Source = Xml.Tables(File.Contents(FilePath)),
    Table0 = Source{0}[Table],
    Table = Table0{1}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table, {{"Attribute:href", type text}}),
    #"Attribute:href" = #"Changed Type"{0}[#"Attribute:href"],
    #"Clean Ampersands" = Text.Replace(#"Attribute:href", "amp;", ""),
    #"Change to CSV Format" = Text.Replace(#"Clean Ampersands", "Format=ATOM", "Format=csv"),
    Request = Excel.Workbook(Web.Contents(#"Change to CSV Format"))
in
    //Request

Source: How To Scrape SSRS Reports with Power Query – Spreadsheets 'N Things (jjohnson.org)

 

Hope this helps to connect the dots. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,645)