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

DBRepl48

Configure the report server as http://<sql reporting services server name>/reportserver and click ok

DBRepl49

Now on the bottom of report builder console click on connect

DBRepl50

As seen below , Report builder is not connected to reporting server and can use the data sources from the servers.

DBRepl51

Click on new – Data source

 

 

DBRepl52

Provide the name of data source ( LocalSQL01DataSource in this case) and click on browse

 

DBRepl53

Browse to folder on reporting server , SCCM Reports from SQL01 and select the SQL01 data source

 

DBRepl54

Now click on test connection

 

DBRepl55

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

 

 

DBRepl56

As seen the data source appears under Data sources on the left side menu in report builder window

 

DBRepl57

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 .

 

 

DBRepl58

Provide the name of Dataset

select data source from drop down and click on query designer

 

DBRepl59

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.

 

 

DBRepl60

Expand views and select v_R_System and click OK

 

 

DBRepl61

You can review this query and what columns are listed and click OK

 

 

DBRepl62

Now in report builder console under datasets the data set is display . This is from view v_R_System

 

 

DBRepl63

Finally ………………………………………. Its time to create a report 🙂

On the ribbon click on insert and select insert table

 

 

 

 

DBRepl64

This by default will add a blank table

 

 

DBRepl65

Drag and drop the fields you want from data set to table , I added Name0, Obsolete0 and client0 and click on run

 

 

DBRepl66

You can view what the report looks like now

 

 

DBRepl67

Click on save and browse to the folder created to storing SCCM reports from SQL01 .

Provide the name of report and click save

 

 

DBRepl68

Report is generated from alternate SQL01 server and published to SQL reporting services server too

 

 

DBRepl69

 

 

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: