In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database
In Part 2 I created a SQL job to run daily . This job will copy the backed up files from SCCM database server. Then this job will attach the copied database files .
In Part 3 I exported the certificate from SQL server and imported in on a machine where reports will be authored.
In Part 4 I created data source from SQL reporting services server to SQL01 server and verified connection is working.
In part 5 I am going to create SCCM reports using the data source created in Part 4 and then publish reports to SQL reporting website.
Open report builder and to system center icon and click on options
Configure the report server as http://<sql reporting services server name>/reportserver and click ok
Now on the bottom of report builder console click on connect
As seen below , Report builder is not connected to reporting server and can use the data sources from the servers.
Click on new – Data source
Provide the name of data source ( LocalSQL01DataSource in this case) and click on browse
Browse to folder on reporting server , SCCM Reports from SQL01 and select the SQL01 data source
Now click on test connection
Now you are able to connect to SQL01 database using a data source stored on reporting server from a window 7 client machine on the network. Click OK
As seen the data source appears under Data sources on the left side menu in report builder window
Up until now I am able to connect to database server . Next step is add some data from the database to create a report.
Subset of data from the database is called dataset. Dataset can tables , views and at time can contain joins between two tables or two or more sql views .
Provide the name of Dataset
select data source from drop down and click on query designer
When you click on query designer , It will take you inside CM_R01 database on server SQL01 .
Of all the data that is available in this data base , I am going to select a small amount of data to create a dataset.
Expand views and select v_R_System and click OK
You can review this query and what columns are listed and click OK
Now in report builder console under datasets the data set is display . This is from view v_R_System
Finally ………………………………………. Its time to create a report 🙂
On the ribbon click on insert and select insert table
This by default will add a blank table
Drag and drop the fields you want from data set to table , I added Name0, Obsolete0 and client0 and click on run
You can view what the report looks like now
Click on save and browse to the folder created to storing SCCM reports from SQL01 .
Provide the name of report and click save
Report is generated from alternate SQL01 server and published to SQL reporting services server too
Additional notes – There are multiple ways to author SQL reports , I am using report builder
You can store data source locally too , You can also store reports locally and publish them later .
This concludes Part 5