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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,582)