My company is moving from another BI dashboard software suite and I'm trying to re-create our existing reports in the PowerBI environment. I'm new to PowerBI but have spent several weeks working in PowerBI learning the ropes from the ground, up so I have an idea of the basics.
I've set up a subscription to a PowerBI report that repeats hourly and sends out email notifications of any new backordered products that have been received since the last time the report subscription was executed. I have a SQL table that contains all of the information from the products when they are received (tblItemReceived) and another table that keeps track of the notifications that have been sent containing the product UID, and date/time the notification was sent (tblItemReceivedNotificationSENT). Data for the report comes from a SQL view (vwItemReceivedNotification) that shows any of the UID's from the "Received" table that aren't included in the "Sent" table.
Once the PowerBI report runs at it's hourly subscription time to send the email notification for any products in the SQL view, I need to execute a SQL statement to insert the UID and date/time the email was sent into the "Sent" table which clears out the view for the products that were just sent and readies it for the next product(s) to be received:
INSERT INTO [tblItemReceivedNotificationSENT]
SELECT UID, GETDATE() as TimeOf
FROM [vwItemReceivedNotification]
WHERE UID NOT IN (SELECT UID FROM [tblItemReceivedNotificationSENT])
Is there a way to execute a SQL statement like this from within the PowerBI report when the subscription executes to insert the information for the products that have been received into the "Sent" table?
Thanks for any thoughts/suggestions
