SCCM diet

Online notes for reference

Replicating SCCM DB and adding replicated DB to SCCM Reporting – Part 5

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.

Part 5

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: