{"id":5759,"date":"2018-12-27T13:04:45","date_gmt":"2018-12-27T13:04:45","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5759"},"modified":"2025-03-19T12:46:26","modified_gmt":"2025-03-19T12:46:26","slug":"read-jira-data-in-ssrs-reports","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-jira-data-in-ssrs-reports\/","title":{"rendered":"Read JIRA data in SSRS Reports (SQL Server Reporting Services)"},"content":{"rendered":"

Introduction<\/span><\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Jira Online<\/a> which makes it much simpler to\u00a0Read\/Write Jira Data in ODBC<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"jira-to-ssrs\"<\/a>In our previous blog we saw how to read rest api in SSRS reports<\/a>. We recommend you go through that article first. In this post we will look at specific example on\u00a0How to read JIRA data in SSRS Reports (SQL Server Reporting Services)<\/strong>, along with few other topics such as how to generate an API Token using Atlassian Account for\u00a0JIRA REST API Call<\/strong>, how to read all Issues data from JIRA using our ODBC Driver and load it into the SSRS reports.<\/p>\n

We will go through the steps to read data from JIRA and Load into SQL Server using\u00a0JSON Driver of ODBC PowerPack<\/a><\/p>\n

In nutshell,\u00a0this post will focus on how to call JIRA API using ODBC PowerPack and how to create SSRS Report.<\/p>\n

So let\u2019s get started.<\/p>\n

Requirements<\/span><\/h2>\n
    \n
  1. Make sure you have SSRS designer installed (You can download\u00a0SSDT BI for Visual Studio<\/a>).<\/li>\n
  2. Finally, make sure to have\u00a0ZappySys ODBC PowerPack<\/a>\u00a0installed.<\/li>\n<\/ol>\n

    What is JIRA<\/h2>\n

    JIRA is Atlassian\u2019s popular project- and issue-tracking platform used by development and technical support teams to get more work done and faster.<\/p>\n

    It combines:<\/p>\n

    issue tracking<\/strong> – a software application that allows to record and follow the progress of every problem or “issue” that user identifies until the problem is resolved.
    \nagile project management<\/strong> – an iterative approach to planning and guiding project processes. customizable workflow, and a pluggable integration – integrates with Freshdesk, GitHub, Zendesk, Zapbook, Asana and so on.<\/p>\n

    Getting Started<\/h2>\n

    First of all, we will see several examples. ZappySys includes an\u00a0SSIS JSON Source Connector<\/a>\u00a0that will help you to call JIRA API, read various data from JIRA with SSIS and do many more actions using REST API. To learn more about JIRA API check this help file<\/a>.<\/p>\n

    Generate API Token<\/h2>\r\nTo access JIRA data using REST API call you need to generate an API Token in your Atlassian account.\u00a0Follow the steps mentioned below to generate API Token:\r\n
      \r\n \t
    1. you can directly login to the JIRA Atlassian Cloud site<\/a> with your credentials, and redirect to the JIRA Atlassian Cloud Account's\u00a0Profile Page<\/a> for generating an API Token.<\/li>\r\n \t
    2. Click\u00a0API tokens<\/strong>, then\u00a0Create API token<\/strong>.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 1<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    3. Create API Token - Give Label name to your API token which you are generating now\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 2<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    4. Use\u00a0Copy to the clipboard<\/strong>,\u00a0and paste the token into your or elsewhere:\r\n
      Note:\u00a0 * For security reasons it isn't possible to view the token after closing the creation dialog; if necessary, create a new token. * You should store the token securely, just as for any password.<\/blockquote><\/div>\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 3<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    5. If you forgot API token, then you can Revoke your old API token and create new API token OR you can create multiple API tokens too.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API token - Step - 4<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\nNow we have successfully generated an API token. Let\u00b4s start with an example. In this example, we will show how to read Tasks\/Issues data from JIRA and store that data into a SQL table with SSIS.<\/div>\n

      About JIRA REST API<\/h2>\r\nJIRA On-premises and Cloud Version both offers REST API. Here are some examples of\u00a0JIRA REST API<\/a>.\u00a0For reading JIRA REST API data we will use JIRA REST API Search method<\/a>\u00a0which returns data in JSON format. Search API support use of JQL Syntax<\/a> to filter issues. If you don't specify JQL<\/strong> parameters in \/search endpoint then all issues will be returned. Check this link to learn how to build JQL for JIRA REST API Call<\/a>. If you don't have JIRA hosted instance yet but still want to try REST API Examples on public JIRA Server then try below URL (Its official JIRA site which lists their own issues). For example, try to copy below URL in a browser and hit enter.\r\n
      https:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER<\/pre>\r\n
      Step-by-Step: Import JIRA data using REST API\u00a0<\/span><\/h2>\r\nNow once you have API token in the JIRA Atlassian Cloud website we can move to ODBC JSON Driver Configuration Part. For this example purpose you can use the following URL:\r\n
      https:\/\/YOUR-DOMAIN.atlassian.net\/rest\/api\/2\/search?startAt=0&maxResults=100&jql=project=MYPROJECT<\/pre>\r\nhence,\u00a0your-domain is your Atlassian Cloud account\u2019s domain name (we are using zappysys2.atlassian.net).<\/strong>\r\nTherefore\u00a0startAt<\/strong>\u00a0is the index of the first item returned in the page of results.\r\nAnd another\u00a0maxResults<\/strong>\u00a0is the maximum number of items that can be returned per page.\r\n\r\nAbove all, each API endpoint may have a different limit for the number of items returned, and these limits may change without notice. (For more See this\u00a0JIRA API Documentation for Pagination<\/a>)\r\n
      Configure Data Source in Gateway (ZappySys JSON \/ REST API Driver Example)<\/span><\/h2>\r\nNow let's look at how to configure\u00a0ZappySys Data Gateway<\/a>. This feature acts as a bridge between Client App and ZappySys Drivers. Using data gateway you can use ZappySys Drivers inside applications \/ operating systems where ZappySys drivers may not be available directly for some reason (e.g. You don't have access to Server for Installation or System does not support ODBC drivers like JAVA programs). Click here to read more<\/a> on various use cases of Data Gateway.\r\n
      Configure Data Gateway User \/ Port<\/span><\/h3>\r\nNow let's look at steps to configure Data Gateway after installation. We will also create a sample data source for ODATA API (i.e. JSON based REST API Service).\r\n
        \r\n \t
      1. Assuming you have installed\u00a0ZappySys ODBC PowerPack<\/a>\u00a0using default options (Which also enables Data Gateway Service)<\/li>\r\n \t
      2. Search \"Gateway\" in your start menu and click ZappySys Data Gateway\r\n
        \r\n\"Open<\/a>\r\n
        Open ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t
      3. First make sure Gateway Service is running (Verify Start icon is disabled)<\/li>\r\n \t
      4. Also verify Port on General Tab\r\n
        \r\n\"Port<\/a>\r\n
        Port Number setting on ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t
      5. Now go to Users tab. Click Add<\/strong> icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.\r\n
        \r\n\"Add<\/a>\r\n
        Add Data Gateway User<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n \r\n
        Configure Data Source<\/span><\/h4>\r\n
          \r\n \t
        1. After user is added, go to Data Sources tab. Click Add<\/strong>\u00a0icon to create new data source. Select appropriate driver based on your API \/ File format. You can choose Generic ODBC option to read data from ODBC DSN or use Native Driver option.\r\n
          NOTE:<\/strong> Whenever possible use native driver option for better performance \/ security and ease of use.<\/pre>\r\n
          \r\n\"Add<\/a>\r\n
          Add Gateway Data Source (Native JSON Driver)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        2. Now it's time to connect with JIRA. Let's use JSON Driver Data source with the\u00a0following URL. Change domain with your own domain.\r\n
          \r\n
          \r\n
          https:\/\/{yourdomain}.atlassian.net\/rest\/api\/2\/search?startAt=0&maxResults=100\r\n<\/pre>\r\n<\/div>\r\n<\/div>\r\n
          \r\n\r\n\"ODBC<\/a>\r\n\r\nODBC JSON Driver: Enter Request URL\r\n\r\n<\/div><\/li>\r\n \t
        3. It is time to configure the Other Settings. Select HTTP Connection Type from the Connection Type drop-down and Click on Click to Configure Link to Configure the Connection Manager.\r\n
          \r\n\r\n[caption id=\"attachment_5596\" align=\"alignnone\" width=\"1245\"]\"JIRA<\/a> JIRA REST API Connection - Use ZappySys JSON Driver HTTP Connection[\/caption]\r\n\r\nJIRA REST API Connection - Use ZappySys JSON Driver HTTP Connection\r\n\r\n<\/div><\/li>\r\n \t
        4. Now, we are going to set Pagination related configurations for JIRA Search request. Page number indicator must be startAt<\/span><\/span><\/span> and maxResults<\/span><\/span><\/span> value in URL must match with Increment By (i.e. 100)\r\n
          \r\n\r\n\"ODBC<\/a>\r\n\r\nODBC JSON Driver - JIRA request Pagination Configurations Settings\r\n\r\n<\/div><\/li>\r\n \t
        5. After adding pagination parameters let's add JQL<\/span>=<\/span><\/span><\/span> search parameter to get specific data. Here we are searching the data by Project name and status. You can find more information about JQL on this link:\u00a0Search JIRA like a boss with JQL<\/a>\r\nHere is example JQL search query for Public JIRA site. Replace jira.atlassian.com and project\u00a0JRASERVER with your own values.\r\n
          https:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER\r\nhttps:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER AND status=Open\r\nhttps:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER AND status in (Open,Done)\r\nhttps:\/\/jira.atlassian.com\/rest\/api\/2\/search?jql=project=JRASERVER AND status=Open order by lastViewed DESC<\/pre>\r\n
          \r\n\r\n\"Search<\/a>\r\n\r\nSearch using JQL parameter\r\n\r\n<\/div><\/li>\r\n \t
        6. Click on Select Filter to set the Filter.\r\n
          \r\n\r\n\"ODBC<\/a>\r\n\r\nODBC JSON Driver for JIRA API: Select Filter\r\n\r\n<\/div><\/li>\r\n \t
        7. You are done with the Configurations, Click on Test Connection to test the Connection.\r\n
          \r\n\r\n\"ODBC<\/a>\r\n\r\nODBC JSON Driver - Test Connection\r\n\r\n<\/div><\/li>\r\n \t
        8. After that select the table from where you want to get data.\r\n
          \r\n\r\n\"JSON<\/a>\r\n\r\nJSON Driver: Create Query of JSON data\r\n\r\n<\/div><\/li>\r\n \t
        9. Click on the Preview Data button to preview the data.\r\n
          \r\n\r\n\"Preview<\/a>\r\n\r\nPreview issues table data\r\n\r\n<\/div><\/li>\r\n \t
        10. And that\u2019s it, Now we will move forward to the next Phase.<\/li>\r\n<\/ol>\r\n
          Build your Own JQL Search queries for JIRA Issue<\/h3>\r\nIn previous sections we saw some basic JQL queries to search JIRA Issues using API call. Now lets look at how to build such queries using online UI (Think like a query builder). from JIRA.\r\n
            \r\n \t
          1. JIRA has its own public Issue repository which you can browse any time by going here https:\/\/jira.atlassian.com\/issues\/<\/a><\/li>\r\n \t
          2. Now try to change filter settings for search criteria (E.g. change Project, Status etc)<\/li>\r\n \t
          3. You will notice when you make change your URL keeps changing too. From that URL you can extract ?jql=xxxxxxx\u00a0 part and use it as template for your own JIRA API search queries.<\/li>\r\n<\/ol>\r\nFor more information\u00a0check\u00a0how to build JQL for JIRA REST API Call<\/a>.<\/div>\n
            Create and configure new SSRS Reports (Using ODBC Connection)<\/h3>\n
            Let’s create a new SSRS project.<\/p>\n
              \n
            1. In SSRS, in a new Reporting Service Project, add a new Data Source:\n
            2. Also, specify a name for the data source and in type select ODBC. Press Edit to select the ODBC connection for JSON and REST API and select the ZappySys Data Source name that we created before.\n
            3. Now let\u2019s create a new SSRS report which uses the API data source we created in the previous section.<\/li>\n
            4. Right-click on the Reports node and click Add New Report (Wizard mode)\n
            5. In New report, wizard window clicks on next button until you reach the step of query builder like below screen. Now Press the query builder button.
              Note: You can also copy and paste the query directly into the Query string box which we created in the previous\u00a0phase.\n
              SELECT * FROM \"issues\"<\/pre>\n<\/div><\/div>\n
              \"Create<\/a>
              Query builder<\/p><\/div><\/li>\n
            6. Now, press the Add table icon and go to Views. Select issues(DATA) and press Add.\n
            7. Now remove DATA.<\/strong> prefix from the query so that our query\u00a0look’s like as below and Click Ok button:\n
              SELECT issues.* FROM issues<\/pre>\n<\/li>\n
            8. Now press Next Button and select Tabular<\/strong> Report Type and go to next in the following option until the completing wizard and specify a name for the report and finally\u00a0press\u00a0the Finish<\/strong> button.<\/li>\n
            9. Now click on the\u00a0Preview a report. It shows the report and exports the REST API data to Word, Excel, PowerPoint, etc:\n
            10. That\u2019s all we have successfully\u00a0imported the JIRA data in SSRS Reports (SQL Server Reporting Services).<\/li>\n<\/ol>\n
              Conclusion<\/span><\/h2>\n
              In this blog, we have learned how to retrieve the data from JIRA using ODBC JSON Driver and then generated the\u00a0SSRS Reports using those data with ODBC DSN in a very simple way. You can achieve many more functionalities with the use of\u00a0ODBC PowerPack<\/a>.<\/p>\n
              References<\/span><\/h2>\n
              Finally, you can use the following links for more information about the use of JIRA API with our tools:<\/p>\n