Creating a report from Oracle WebCenter Analytics db using SQL Reporting Services

Recently we had a request to view some information about users that had viewed particular documents from our KD.  Unfortunately Analytics doesn't have the capability of displaying more than the user name values in it's user reports so we had to look elsewhere.  Creating a SQL query to determine which users had viewed particular documents involved five tables but was slightly tedious to constantly run.  Instead I took a few hours and learned how to generate reports using SQL's Reporting Services tool.  This isn't super hard, but I figured I'd share what I learned in case anyone else out there runs into this type of issue.

Here are the steps to generating the report within MS SQL's Reporting Services (I used both 2005 and 2008 and didn't notice a difference between the two)

1. Open "SQL Server Business Intelligence Development Studio" (aka Visual Studio)
2. Click "File>New>Project"
3. Select the project type called "Business Intelligence Report", and use the "Report Server Project Wizard" template
   
4. Name the project something intuitive and go on to the next screen
5. Select the data source
1. Once you have named your source click the Edit button to connect to the database
2. Add your server name and select the particular database that you need to connect to. Then be sure to test the connection to make sure it works.
6. The next screen will prompt you for the query that your report will be built from.  In this case I've already built out the query and it can be pasted into place.  This query identifies who has looked at a particular document from the KD and displays a few user profile fields along with the document name.
7. At this point you can take a look at the Query Builder in case you want a little assistance or to see what else you can play with.  Having two columns with the same name caused an issue when I did my report development on SQL 2005 so I chose to use an alias which solved the issue.
   
8. For the report type I selected Matrix
   
9. Placing the DOC_NAME field into the Page section allows the report to create a new tab for each of the documents that I want to generate this report for.
   
10. Choose your style
    
11. Choose your deployment location
    
12. Complete the Wizard steps
    
13. The report we generated will display like this within the Visual Studio editor that we are working in (sorry I had to scrub the data since it wasn't just employee information)
    
    
14. The next step is to right click on the report and select deploy and then you are all set and can go to a web browser and pull it up at this URL: http://yourserver/Reports
1. I ran into a security issue with connecting to our database server from off the server.  Turns out the data connector was going through as an anonymous user when I was going through the website. To rectify the issue I modified the data source to use "Credentials stored securely in the report server" instead of using Windows authentication.
2. Log files are here in case you have issues: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles

Now that the easy part is out of the way, if you really care about doing this to track document views in Analytics you'll need to consider the best course of action given it's use of dynamic tables based on year/month and how the current months table is stored.

In order to use dynamic table names in a SQL query you'll need to use a different form of SQL known as dynamic SQL. Unfortunately dynamic SQL isn't supported in SQL Reporting Service, but you can get around that by creating a stored procedure and setting that as the data set from your report!

Yes, it is getting complicated:)

The dynamic SQL command that I'm using is:

Declare @tblName Varchar(4000)
Declare @SQL Varchar(5000)

Set @tblName =
'ASFACT_DOCUMENTVIEWS_'
+ CONVERT(VARCHAR,DATEPART(yyyy,GETDATE()))
+ '_'
+ CONVERT(VARCHAR, RIGHT('0' + RTRIM(MONTH(GETDATE())-1), 2));

SET @SQL = 'SELECT     t2.OCCURRED, t2.ID, t2.USERID, t3.NAME, t2.DOCUMENTID, t1.NAME AS DOC_NAME, t4.PROPERTYID, t5.NAME AS PROP_NAME, t4.VALUE
FROM         ' + @Tblname + ' AS t2 INNER JOIN
                      ASDIM_USERS AS t3 ON t3.ID = t2.USERID INNER JOIN
                      ASDIM_USERPROPERTYVALUES AS t4 ON t4.USERID = t3.ID INNER JOIN
                      ASDIM_KDDOCUMENTS AS t1 ON t1.ID = t2.DOCUMENTID INNER JOIN
                      ASDIM_USERPROPERTIES AS t5 ON t4.PROPERTYID = t5.PROPERTYID
WHERE        (t2.DOCUMENTID IN (33449, 36241, 36566))
            AND (t4.PROPERTYID IN (26, 156, 157, 158, 159, 325, 160, 162))
            AND (OCCURRED>CONVERT(VARCHAR,MONTH(GETDATE())-1) + ''/15/2011'')
ORDER BY t2.DOCUMENTID, t2.OCCURRED';
Exec(@SQL)

Technically the order by command isn't needed because sorting is done from within the report itself, but it helps to debug the data you are expecting in your report!

I've skipped a lot of steps that I did to get this all working (because most people probably wouldn't care about the small details) but if you have any questions feel free to ask in the comments and I'll try to help you out.

UPDATE:  After consulting with Oracle support I'm no longer using the dynamic tables, nor the dynamic SQL. Instead I'm going directly against the ASVFACT_DOCUMENTVIEWS table which makes this a little easier.

Read More