In this post I will show you how you can create SQL Reporting Services Report using analysis Services. If you have to setup Reporting Services. Please follow my previous blog Part 1 to 5.
I went to my Business Intelligence site and my reports library where I have attached Reports Content Types. First I will create a report data source.
And I got the error mentioned below.
I Opened ULS Viewer and open the current log and then filtered by the ID mentioned above.
Error being shown: Microsoft.SharePoint.SPEndpointAddressNotFoundException: There are no addresses available for this application. This error means that either my connection to service application is not available or service is not available. I went to Manage Service application and taken properties of SQL Server Reporting Services Service Application.
There was no problem in there. I went to Manage Services and server and found the issue that my Reporting services service was not running. I started it and did an iisreset.
I refresh the page and tried again. Here you go.
I clicked OK and now we will create a report and will update data source to this library in few minutes.
Now we have to install the report builder application.
Lets Click on Blank Report.
First we have to add a data source to the cube.
Type name select use a connection embedded in my report and click Build
Type Server name and select your analysis services database.
Copy connection string text and go back to Report library and Edit the Report Data Source.
Click on the connection name.
Now Click Ok. Go back to Report Builder tool and select use Existing Connection and click Browse. Select Report library and select the connection.
Now lets add a new Dataset
Enter Name and use a dataset embedded in my report and select existing data source. Now click on Query Designer.
As soon as I click on Query Designer I got the following error.
I need to fix the data source to use windows integrated security. Although I could provide the user name and password in this dialog and select use as Windows Credential but it is better to update the data source.
I choose Stored Credential, typed user name and password and selected Use as Windows Credential and click Test Connection.
Went back to SQL Reporting Builder tool. I deleted the data source and created again. I was able to connect to Query Designer.
Although I had specified the unattended account but seems like I entered wrong password. So I went to Central Administration à Manage Service Application à SQL Reporting Service Application à Execution Account
I entered the password again and confirm it. And click Ok.
Came back to report and refresh it and the error went away.
I also choose Product and selected filter to Product Line
Review the Result and Click OK.
Let’s Insert a Chart and Table
Product Line as Category and Sales Amount as Values. Click Next
Choose the Style of your choice and Click Next
Change the Title of Report.
Save the Report to Report Library before we try it in Report Builder
Click on Home Ribbon and select Run
Its works fine here. Click on Deign to go back to design view.
Now let’s see the report in browser. Close Report builder and go to reports library.
As we have now see Report creation in Excel and Reporting Services. In the next blog post we will create report using PerformancePoint Services.
End of Part 6.