Morgado asked a question.
PowerBi to retrieve SLM Reports
Hello,
Could someone point me to the correct sql tables to find the SLM reports (custom or not..) to be open within PowerBI?
Thanks.
- SamuelBaughanThe SLM Reports are usually in views called dbo.RsXxxxxxxxxxxxx The report itself is actually a query against those views as far as I understand. has dug into his database so much that he must have reached the other side already , he might have some more insight.Expand Post
- MarcoCambonahah that's true Samuel! so many holes in my DBs after years of digging! anyway...talking about reports, for Goncalo. The easiest way to help you would be to know which Report in particular you need. Reports are not stored in tables: they're generated on the fly through StoreProcedures. In more details: - Stock (built-in) Reports have a SP underneath - Custom Reports are generated through a SQL Query that is stored in a string column inside the [SnowLicenseManager].dbo.tblReport. Have a look at that tblReport yourself to identify which StoreProcedure is generating the report you're looking for, by checking the name of the report in columne "Name" and the SP in column "SQLQuery". Let's take the "All Computers" report as an example select * from tblReport where name like 'all computers' Under columng SQLQuery you'll see: StockReportCustomFieldsPerComputer That's the SP you need to call to get that report. You need to provide 3 parameters to that SP (2 mandatory, the last optional): @CID @UserID @Parameters @CID: In a non-SPE environment you should pass the only CID (CustomerID) you have, by checking in tblCID, otherwise check the specific @CID of the customer. @UserID: you need to provide a UserID that has the right to run that Report. You ususally want to pass a UserID of an Admin account. Check which is the UserID of your admin account by looking in the tblSystemUser. @Parameters (optional) : I don't really know what you can specifiy as optional parameter., In my experience I've never passed any and it works like a charme...maybe someone else knows. Then execute the SP with this statement: EXEC [ dbo] . [ StockReportCustomFieldsPerComputer] @CID = YOURCID, @UserID = YOURUSERID On the other hand, for Custom Reports, just check which query is specified in the "SQLQuery" column and replace {0} with the CID and {1} with UserID.Expand Post
- peryngfalkHi Marco and thanks. Very useful. However I have a big issue. Whatever report I try to execute I receive "Msg 8114, Level 16, State 5, Procedure StockReportAllLicenses , Line 0 [Batch Start Line 2] Error converting data type nvarchar to int." I have made sure the CID is correct and also tried different Admin accounts. Any idea? Best, PerExpand Post
- MarcoCambonHi Per, y're welcome. I guess you're putting a string instead of an integer in the UserID parameter. Check what your UserID is in tblSystemUser regards, MarcoExpand Post
- peryngfalkAgain thanks, works like a charmExpand Post
- SamuelBaughanI knew I was asking the correct person ! Thanks Marco for that insightExpand Post
- Vinay to add further i am also trying way to inter link excel reports to Power BI features. can you please suggest whether the above steps will work, if i want get information from SLM- API - Power BI - Dashboard for customer visibility and referenceExpand Post
- MarcoCambonHi Vinay, a limitation I see in going through Excel is that you can only import SQL data from a DB Table or View, where the above solution is build around a StoreProcedure execution or a query. I'm not an expert of PowerBI but maybe is easier if you import Snow DB data straight into PBI report skipping Excel.Expand Post
