none
APIs for PBIX in SSRS service RRS feed

  • Question

  • With Publishing Power BI reports directly to the SSRS Portal, do we have any APIs for uploading or creating a PBIX report on SSRS?

    I am using "rsClient.CreateCatalogItem" but it does not accept "PBIX" as valid ItemType. We tried "Report" but its specific to RDL report.

    Prompt response will be appreciated. 




    • Edited by Sagar Salvi Thursday, June 29, 2017 6:29 PM
    Tuesday, June 27, 2017 11:17 PM

Answers

  • Here you go. Note that this is untested and unsupported and likely will break if we make significant code changes. Does not support overwrite, so you will get an error and it will blow up if the file exists. 

    Calling code:

            private static void Main(string[] args)
            {
                var server = args[0];
                var filename = args[1];
                var targetPath = args[2];
                var targetName = args.Length == 4 ? args[3] : Path.GetFileNameWithoutExtension(args[1]);

                PowerBiUploader.UploadPbiReport(server, filename, targetName, targetPath);
            }

    PowerBiUploader.cs:

    // Untested and unsupported code

    using System;
    using System.IO;
    using System.Net;
    using System.Text;

    static internal class PowerBiUploader
    {
        public static WebResponse UploadPbiReport(string server, string filename, string targetName, string targetPath)
        {
            string apiString = String.Format("http://{0}/reports/api/v2.0", server);

            string cookie = GetCookie(apiString);

            string nonce = Extract(cookie, "XSRF-NONCE=");
            string token = ExtractToken(cookie, "XSRF-TOKEN=");

            return PostContent(filename, targetName, targetPath, apiString, nonce, token);
        }

        private static WebResponse PostContent(string filename, string targetName, string targetPath, string apiString,
            string nonce, string token)
        {
            string cookie;
            WebRequest webRequest = WebRequest.Create(apiString + "/CatalogItems");
            cookie = String.Format("XSRF-NONCE={0}; XSRF-TOKEN={1};", nonce, token);

            webRequest.Headers.Add("Cookie", cookie);
            webRequest.Headers.Add("X-XSRF-TOKEN", token);
            webRequest.Method = "POST";

            string content = Convert.ToBase64String(File.ReadAllBytes(filename));

            string postData = GetRequestData(targetName, targetPath, content);

            byte[] data = Encoding.ASCII.GetBytes((string) postData);

            webRequest.ContentType = "application/json;charset=utf-8";
            webRequest.ContentLength = data.Length;
            webRequest.UseDefaultCredentials = true;

            using (Stream stream = webRequest.GetRequestStream())
            {
                stream.Write(data, 0, data.Length);
            }

            return (HttpWebResponse) webRequest.GetResponse();
        }

        private static string GetCookie(string apiString)
        {
            WebRequest webRequest = WebRequest.Create(apiString + "/me");
            webRequest.UseDefaultCredentials = true;

            WebResponse webResponse = webRequest.GetResponse();

            string cookie = webResponse.Headers.Get("Set-Cookie");
            return cookie;
        }

        public static string Extract(string cookie, string tag)
        {
            int start = cookie.IndexOf(tag);
            string value = cookie.Substring(start + tag.Length);
            int end = value.IndexOf(";");
            string trimmed = value.Substring(0, end);
            return trimmed;

        }

        public static string ExtractToken(string cookie, string tag)
        {
            return WebUtility.UrlDecode(Extract(cookie, tag));
        }

        public static string GetRequestData(string targetName, string targetPath, string content)
        {
            string template = @"

                { ""@odata.context"":""http://ericgudev1/reports/api/v2.0/$metadata#CatalogItems/$entity"",""@odata.type"":""#Model.PowerBIReport"",""Id"":""00000000-0000-0000-0000-000000000000"",""Name"":""$$Name$$"",""Description"":null,""Path"":""$$Path$$"",""Type"":""PowerBIReport"",""Hidden"":false,""Size"":0,""ModifiedBy"":null,""ModifiedDate"":""0001-01-01T00:00:00Z"",""CreatedBy"":null,""CreatedDate"":""0001-01-01T00:00:00Z"",""ParentFolderId"":null,""ContentType"":null,""Content"":""$$Content$$"",""Properties"":[
                  {
          ""Name"":""IsMobileOptimized"",""Value"":""False""
        },{
          ""Name"":""PbixShredderVersion"",""Value"":""1""
        }
      ],""IsFavorite"":false,""HasDataSources"":false
    }";
            return template.Replace("$$Name$$", targetName)
                .Replace("$$Content$$", content)
                .Replace("$$Path$$", targetPath);
        }
    }

    • Marked as answer by Sagar Salvi Thursday, July 6, 2017 6:19 PM
    Wednesday, July 5, 2017 9:50 PM

All replies

  • Hi Sagar Salvi,

    Based on my research, the pbix file is a document created by Power BI Desktop. You could import it to workspace with Power BI REST API, you can refer to Power BI REST API reference for details. In addition, if you want to upload pbix report on SSRS , you could use “upload ” button directly in SSRS. Because SQL Server Reporting Services (SSRS) 2016 now recognizes the .pbix file extension created from Power BI Desktop, you can refer to Power BI & SSRS 2016 for details.

    In addition, did you want to create pbix report in SSRS designer tools? If I have something misunderstood, please correct me. Current SSRS uses XML and Web Service Data Sources, it can’t use pbix file directly, so your requirement can’t be achieved.

    If you have any question, please feel free to ask.

    Best regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 28, 2017 5:41 AM
    Moderator
  • Thanks Zoe Zhi for the quick response.

    But we are aware of publish feature from PBI desktop and also we know we have upload option in SSRS. But we don't  want users to be exposed to SSRS and hence we want to control it and provide UI to upload the PBI file. And in this case, we want some SSRS API to upload this PBIX file.


    • Edited by Sagar Salvi Wednesday, June 28, 2017 6:48 PM
    Wednesday, June 28, 2017 6:10 PM
  • Hi,

    I tried tracing fiddler logs for upload operation of RDL and PBIX file on SSRS server and the only difference I noticed was Type property. It was Report for RDL as we all know and for PBIX it is PowerBIReport

    So I tried passing PowerBIReport as ItemType but still, it gave me the same error:

    System.Web.Services.Protocols.SoapException: The value of parameter 'ItemType' is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidParameterException: The value of parameter 'ItemType' is not valid.
       at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

    Is it so that ReportService2010.asmx is not updated with new APIs which supports PBIX report? Can you please confirm this?

    Thursday, June 29, 2017 9:52 PM
  • Sagar,

    You are correct that uploading PowerBI files is not supported by the SOAP apis...

    We're looking at providing at way to do this, probably as unsupported code, at least for now. Which of the following would be most useful:

    1. A powershell script that could do this
    2. A C# class that could do this
    3. A command-line executable (or the code to build one) that could do this.


    Friday, June 30, 2017 4:38 PM
  • Hey Eric,

    C# code would help us at this point of time. Thank you, appreciated your help.

    Note: This is just FYI, we are using custom authentication for SSRS server and we know that at this point of time it's not working with Power BI report server. So, for now, we are using default authentication to implement this functionality.



    Friday, June 30, 2017 4:55 PM
  • Hey Eric,

    We are waiting for your sample code to upload PBIX file. It will be great if we have something working early this week. Thank you.

    Monday, July 3, 2017 3:40 PM
  • Here you go. Note that this is untested and unsupported and likely will break if we make significant code changes. Does not support overwrite, so you will get an error and it will blow up if the file exists. 

    Calling code:

            private static void Main(string[] args)
            {
                var server = args[0];
                var filename = args[1];
                var targetPath = args[2];
                var targetName = args.Length == 4 ? args[3] : Path.GetFileNameWithoutExtension(args[1]);

                PowerBiUploader.UploadPbiReport(server, filename, targetName, targetPath);
            }

    PowerBiUploader.cs:

    // Untested and unsupported code

    using System;
    using System.IO;
    using System.Net;
    using System.Text;

    static internal class PowerBiUploader
    {
        public static WebResponse UploadPbiReport(string server, string filename, string targetName, string targetPath)
        {
            string apiString = String.Format("http://{0}/reports/api/v2.0", server);

            string cookie = GetCookie(apiString);

            string nonce = Extract(cookie, "XSRF-NONCE=");
            string token = ExtractToken(cookie, "XSRF-TOKEN=");

            return PostContent(filename, targetName, targetPath, apiString, nonce, token);
        }

        private static WebResponse PostContent(string filename, string targetName, string targetPath, string apiString,
            string nonce, string token)
        {
            string cookie;
            WebRequest webRequest = WebRequest.Create(apiString + "/CatalogItems");
            cookie = String.Format("XSRF-NONCE={0}; XSRF-TOKEN={1};", nonce, token);

            webRequest.Headers.Add("Cookie", cookie);
            webRequest.Headers.Add("X-XSRF-TOKEN", token);
            webRequest.Method = "POST";

            string content = Convert.ToBase64String(File.ReadAllBytes(filename));

            string postData = GetRequestData(targetName, targetPath, content);

            byte[] data = Encoding.ASCII.GetBytes((string) postData);

            webRequest.ContentType = "application/json;charset=utf-8";
            webRequest.ContentLength = data.Length;
            webRequest.UseDefaultCredentials = true;

            using (Stream stream = webRequest.GetRequestStream())
            {
                stream.Write(data, 0, data.Length);
            }

            return (HttpWebResponse) webRequest.GetResponse();
        }

        private static string GetCookie(string apiString)
        {
            WebRequest webRequest = WebRequest.Create(apiString + "/me");
            webRequest.UseDefaultCredentials = true;

            WebResponse webResponse = webRequest.GetResponse();

            string cookie = webResponse.Headers.Get("Set-Cookie");
            return cookie;
        }

        public static string Extract(string cookie, string tag)
        {
            int start = cookie.IndexOf(tag);
            string value = cookie.Substring(start + tag.Length);
            int end = value.IndexOf(";");
            string trimmed = value.Substring(0, end);
            return trimmed;

        }

        public static string ExtractToken(string cookie, string tag)
        {
            return WebUtility.UrlDecode(Extract(cookie, tag));
        }

        public static string GetRequestData(string targetName, string targetPath, string content)
        {
            string template = @"

                { ""@odata.context"":""http://ericgudev1/reports/api/v2.0/$metadata#CatalogItems/$entity"",""@odata.type"":""#Model.PowerBIReport"",""Id"":""00000000-0000-0000-0000-000000000000"",""Name"":""$$Name$$"",""Description"":null,""Path"":""$$Path$$"",""Type"":""PowerBIReport"",""Hidden"":false,""Size"":0,""ModifiedBy"":null,""ModifiedDate"":""0001-01-01T00:00:00Z"",""CreatedBy"":null,""CreatedDate"":""0001-01-01T00:00:00Z"",""ParentFolderId"":null,""ContentType"":null,""Content"":""$$Content$$"",""Properties"":[
                  {
          ""Name"":""IsMobileOptimized"",""Value"":""False""
        },{
          ""Name"":""PbixShredderVersion"",""Value"":""1""
        }
      ],""IsFavorite"":false,""HasDataSources"":false
    }";
            return template.Replace("$$Name$$", targetName)
                .Replace("$$Content$$", content)
                .Replace("$$Path$$", targetPath);
        }
    }

    • Marked as answer by Sagar Salvi Thursday, July 6, 2017 6:19 PM
    Wednesday, July 5, 2017 9:50 PM
  • Thanks, Eric.

    I almost coded Rest API call for CatalogItems but I was confused for the token information that we pass in cookie. I was using hard coded data that I extracted from fiddler.

    But your code worked like charm. Thank you appreciate your help.

    Just curious to know, are we planning to expose any APIs related to PBIX, just like we have for RDL, RSD and RSDS in Report? Bcoz post uploading PBIX file, we have to explicitly set the data source credentials. 


    Thursday, July 6, 2017 6:19 PM
  • You're welcome...

    WRT APIs, I know we have plans around our APIs but I don't know the details. 

    Thanks

    Eric

    Thursday, July 6, 2017 7:10 PM
  • I guess we can use UpdateItemDataSources to set the datasource for PBIX file:

    http://SSRS_Server/ReportSpbi/api/v2.0/catalogitems(da888ec7-d91e-4e76-86eb-71c7fe19c416)/Model.PowerBIReport/Model.UpdateItemDataSources 

    Thursday, July 6, 2017 8:26 PM
  • Hi

    The code above works very well, many thanks! 

    Question: Are there examples to change the data sources connection string too? 

    Greetings,

    Roli

    Tuesday, July 18, 2017 1:06 PM
  • This worked well. What needs to be changed in this code to make it work for mobile reports with .rsmobile extension?
    Wednesday, July 26, 2017 6:31 PM
  • Hi Eric,

    We are facing some issues with reading and creating PBIX files on PBI report server. We are trying to implement a copy <g class="gr_ gr_969 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="969" id="969">PBIX</g> file functionality on PBI report server using REST calls like above. 

    Code sometimes throws "The operation has timed out." error while reading file definition:

       var webRequest = WebRequest.Create(String.Format("{0}/api/v2.0", ssrsReportsURL) + "/CatalogItemByPath(path=@path)?@path='" + reportPath + "'") as HttpWebRequest;
            webRequest.CookieContainer = cookieContainer;
            webRequest.Headers.Add("X-XSRF-TOKEN", token);
            webRequest.Method = "GET";

    One weird observation is if we keep fiddler running then all the calls gets successful. We don't get any error. Any idea whats happening here. We also checked logs on report server but we see nothing relevant. 

    Any help or guidance would be appreciated.

    Monday, October 23, 2017 11:52 PM
  • Hi Eric,

    Do you have any idea abt issue mentioned in my previous comments?

    We are kind of blocked because of this issue. The worst part is SSRS also don't log any error or information. Any suggestions or help would be appreciated.

    Friday, January 12, 2018 6:19 PM
  • Have you ever found any examples to change the data source connections?
    Friday, October 18, 2019 4:06 PM