How to Generate SSRS report from SharePoint List

In SharePoint sometimes not all the data or information stored directly within the database. The problem comes when we want to generate a report that will need data stored inside SharePoint List. In this blog, I will show how to create report which the data is collected from the SharePoint list. And for generating the report I use SQL reporting service.

First of all, let's create sample SharePoint list:


Just create a simple SharePoint list, I would to like to give a name as A Movie List

And I also would like to add some custom columns to beautify my list my custom list looks like this:

The next step is adding information into the list

Now it's time to create the report from the list using SQL Reporting Service (SSRS). The step by step to generate the report is similar with the regular report that we would like to create. The different is that the type of the connection when creating the data source is quite different. I will show how to create the connection linked directly into SharePoint list.

When creating data source just chose "Embedded connection" then choose the type "Microsoft SharePoint List". For the connection string, just type the URL of your SharePoint web application where the list is placed so the configuration will look like this:

For the credential itself, just choose "Use Windows Authentication (integrated security)"

Once you're done creating the data source, it's time to make the report by creating a new data sets. In the report data pane, right click on datasets folder -> add datasets...

In the query tab, choose "Use a dataset embedded in my report", choose the data source that previously has been created, and click "Query designer" to see the list you want to generate as a report





Based on my scenario, I will just choose the list I already created named "Movie List" and just choose 3 columns to be added to my report







Click "Run Query" to see the data that will be gathered




The next step is just do drag and drop the datasets into your table, and design the table based on your desire as a final report that is taken from SharePoint List just like what I design as the picture below :D







And..... the last step is deploying your report. Depend on your business needs. If you are using SQL Reporting Services with SharePoint Integrated, just save your report directly in site contents, or if you are using native SSRS, just deploy to your report server...

Happy Trying :D


Thanks for visiting my blog 

Loosing and forgetting all administration account within SQL Server

Has it ever occurred to you that you lost all SQL user login with sysadmin privilege and forgot the SA password?
Or you changed the server name and forgot to enable SQL login authentication ?

Well, people who are working with SQL Server did feel that too. There are some ways to solve this kind of problem.

It also happened to me when I changed the server name that places the SQL Server application inside it. After changing the computer name, I tried to login to SQL server and I was unable to do it because the SQL server reads the new name of the computer and it is considered that all the windows user login is not registered yet. And it gets worse when I realize that the server authentication that I configured is only Windows Authentication mode only which means that I was only able login to SQL server using windows user login.

In this blog I will show you step by step how to login to SQL server with minimum configuration and you will be able to create the user login through SQLCMD.


1. Using cmd, run the script below:

========================================================================
SQL server without instance name:

net start mssqlserver -f

SQL server with instance name:

net start mssql$<instance name>
========================================================================

2. open sqlcmd using command prompt, just type SQLCMD then hit enter button. This will enable you to write a query just like when you open query window using SQL server management studio

3. Start creating new user. According to my case, I didn't activate SQL server authentication mode, thus I need to register the windows user so that I'm able to login into SQL server. Just run the query below:

========================================================================

If you  register the windows user under specific domain:

Create login [DomainName\UserName] From Windows

go

If you register under the local user:

Create login [ComputerName\UserName] From Windows

go

=======================================================================

4. Give permission as sysadmin to the user you just created so that you will have full access to the SQL Server

sp_addsrvrolemember 'domainName\UserName','sysadmin'

5. Stop the sql Server service then re-start the service normally (whether through cmd or using SQL configuration manager)

6. Try to login using the user you just created into your SQL Server

Note: if you are using windows user authentication to login to SQL Server, make sure that particular user has a privilege to open SQL server application 

Happy Trying :D

Hope this is useful for you 

No GUI in Windows Server 2012

Unable to see the windows start button and other user interface where you normally see once you login into your Windows server and the only thing you see is only the command prompt (CMD) and the black background on the desktop

Whether you realize or not, it can happen for some reasons.

This can happen if start installing the OS using server core installation without GUI

Or you accidentally remove 'user interface and infrastructure' feature from server manager and when you are back to perform login windows....... the GUI is gone

Here I will show you how to bring back the GUI on Windows Server 2012.

Because the only tool you have is the command prompt, you will play around with it in order to bring the user interface back.

First try to run the command "SConfig" to see the server configuration

If you see the number stated "Restore Graphical User Interface (GUI)" like the picture below, well you are in luck, just type the number and restart the system then all the GUI will be back normally. 

But if you don't have that kind of information in server configuration as the previous screen shoot, don't have to worry , just go back to your earlier command prompt and run this script:

=====================================================================

dism /online /enable-feature /featurename:server-gui-shell /source:\\vmm\winsxs /all

====================================================================

When the it asks you to reboot the system after enabling the feature, press Y button then hit Enter

and it takes few minutes to start. 

Once the system is back in, and you login to the system, Clinggg.... the beauty of GUI is finally back as it suppose to be :"D

Are we done ?

I don't think so

There is one more step to do.....

If you go to windows start button and look for server manager, you will find nothing from the menu 

After bringing back the GUI of the server, we now have to bring back the server manager as well. In order to enable the server manager, I did run this script through powershell:

=======================================================================

install-windowsfeature server-gui-shell

=======================================================================

After you finish running the script above, try to go to start menu and look for the server manager.
Then, a magic happens doesn't it?.... The server manager now is there without rebooting the system :D

I found the way to solve this through this blog:

https://yungchou.wordpress.com/2014/12/09/changing-server-installation-option-from-server-core-to-server-gui-shell/

The article he wrote finally saved my life of the day after few hours searching on the internet trying to troubleshoot this issue :D

I also found nice article for you to practice in your own environment how to turn off the GUI and bring it back on using Windows Server 2012 using the link below:

http://www.howtogeek.com/111967/how-to-turn-the-gui-off-and-on-in-windows-server-2012/

Try to avoid doing this in the production environment though :D

Hope this will be useful and informative for you

Thanks your visiting my very first blog, Cheers :D 

Happy trying guys.... 

SQL Server reporting and SQL PowerPivot Disappear after Removing from and existing farm and re-join to the existing farm

If you have more than 1 server to place sharePoint farm, sometimes you would like to remove a particular server from the existing farm due to some reasons that you must do and afterwards you plan to join it back to the existing farm.

When you are having Powerpivot within your sharepoint web application, you will realize that you need at least two services on server in order to make your powerpivot ready to use. The first service is SQL Server PowerPivot System Service and the second one is SQL Server Reporting Service.

I write this blog because I experienced this kind of case when I was setting up Availability Group in SQL server where I need to reconfigure the connection string from sharepoint to virtual IP of the database so that the HA is working properly. After reconfiguring the connection between SharePoint and SQL Server, I was having problem with the secure store service which is used for my powerview web application. In order to solve the problem what I did was removing sharepoint server from the existing farm then rejoining again with the new name of SQL server instance.

Once I finished disconnecting the server to the existing farm, then I directly Re-joined the server to the previous farm with the new database server instance name to retrieve sharepoint database configuration. 

After I successfully rejoined my sharepoint server into existing farm, I started to play around with the SQL Server by failing over between primary and secondary server. And every time I swing the server, I at the same time check the secure store service is working properly... :D

I thought my job with the issue was done and I can use my sharepoint server normally, but when I was trying to open my powerview file through sharepoint content, It gave me the error which I don't remember exactly the message, but what I know is that I was unable to open the file because the service is missing and then I panic. I tried to check the uls log, but it did not give me enough information until I check the service on server from central admin. Then I realized that two services that have been used to run the powerview (SQL Server PowerPivot System Service and SQL Server Reporting Service) in my application went missing :O

After I find out that these two services were gone, I started to search through the internet regarding this case happening to me. Until I got an idea. What I did is I run the Sharepoint Shell management run it as administrator and I run the script as picture below 

After running the script, I ran PowerPivot for SharePoint 2013 Configuration tool chose 'configure or repair powerpivot for sharepoint'

After I finish reconfiguring powerpivot sharepoint, I then refreshed my central admin and went back to manage services on server, and then check the services are back on track and at the same time my powerview is back to normal as well :D 

Happy trying...

Hope this will be useful and informative for you

Thanks your visiting my very first blog, Cheers :D