NW Business Intelligence

thoughts on technology, B.I., and more…

Archive for April, 2011

SSRS security setup on Windows 7

Posted by Brad Greene on April 26, 2011

Here are a collection of tips for getting SSRS 2008 R2 working under Windows 7 on a simple setup like a single user laptop. I have had to go through this a couple times and were a few things that tripped me up so I thought I would try to pull all the solutions into one post in case it helps someone save some time. If you are getting the error message…

User ‘someDomain\someUser’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

…when trying to open Report manager there is a good chance the following may help.

This approach is the easiest and is only appropriate for a single user laptop or developer kind of set up. If you need UAC then go figure out how UAC is supposed to be configured and make that work for you. This way just makes it work by putting your user account into the security role SSRS needs to avoid the UAC conflict.

First you need to run your browser as Administrator. This the first place you can get tripped up. The “Run as…” option is used (accessed with a right of the mouse if you didn’t know) but don’t try to do this on the IE icon in the Taskbar. Open Explorer and navigate to the the folder that has iexplore.exe executable and do it there. Now you will actually be running IE as the Admin account you select from the dialog that pops up.

If you have done the above correctly you should be able to successfully navigate to your new SSRS Reports folder. The default URL would typically be http://localhost:80/Reports. If you used a name instead of “localhost” then it might be http://lenovo-t60p:80/Reports for example. The 80 port is the default and obviously you need to change that if you used a different port. The main point is that if you installed 2008 R2 and SSRS correctly, then configured SSRS correctly and are running IE as Admin you should get to a page that looks something like the following. If you are still getting the original error that brought you here it is time to think about starting the installation over and checking for any missed steps. Is the Report service is running? Can you see the databases created by the SSRS configuration tool in SSMS?

Once you have gotten the screen above the rest is easy in my opinion. All that remains is to add your user account to the Admin roles in two places under SSRS and then you will be able to run IE from your normal user account.

So, while still running IE as Admin, navigate to the Site Settings, click on Security, then New Role Assignment and add your account to the list. After adding myself to the role list my setup looks like the screen shot below.

Now, we need to do this in one more place. We need to add our account to the Home folder security role list. Navigate back to the default start screen which is the Home folder and click on Folder Settings. Then using New Role Assignment add your account to the list.

If there is nothing else wrong with your SSRS configuration you should be good to go. Exit IE and start IE again using your normal account to see if you can access the SSRS service URL now.

The only other issue I had was not having http://localhost/ (or in my case http://lenovo-t60p/) in the list of Trusted sites in IE’s security settings. To do this you will have to uncheck the check box that restricts entries to https only.

 

Good luck with your new SSRS 2008 R2 installation. The tool is looking better than ever!

[Edit] – One more thing I ran into on an early attempt. I was using AVG Free 9.x anti virus software. It was interfering with SSRS so I removed it. I probably could have tweaked it to exclude some folders or whatever but I didn’t have the time for that so I tried Lavasoft’s Ad-Aware and have had no problems with it right out of the box.

Posted in Tech Tips, Technology | Comments Off on SSRS security setup on Windows 7

Using server side functions in Cognos

Posted by Brad Greene on April 24, 2011

When creating Cognos reports using Report Studio you have the option to embed functions in queries and query items. There are two basic types of functions available. There are Cognos provided functions that are built into the product. Using these functions insures that there is no dependency on a third party database. This is important for reasons of portability. The other type are database specific functions which are vendor specific.

Cognos does not provide functions for every situation and it is sometimes necessary to use a database vendor provided function. It is important to realize two things when you do this. 1) Your report is very likely no longer portable between database environments and 2) You may have to change the Processing settings for the query subject in order for the Report to function. The reason you may have to change the Processing settings is because you may have used both Cognos and database functions in the same query. This confuses Cognos in a way. It tries to process every function as local and fails. You will have to set the processing to Database only to force Cognos to convert its own functions to database versions of the same and thus all processing will be done in SQL before the data is returned to Cognos. Below is an example of using the MS SQL Server Datepart function. Note, the use of curly braces in place of quotes around the parameters.

  datepart({year},[time_stamp]) * 100 + datepart({month},[time_stamp])

Posted in Tech Tips | Comments Off on Using server side functions in Cognos

Building a new blog site

Posted by Brad Greene on April 22, 2011

Just put this site up tonight. Going to move all the content from the old site over here and start a new one using WordPress. It’s been a few years since I created the old one and while Joomla is a good CMS it is overkill for my needs. WP is just so much easier to manage when all you need is a blog and a few pages of static content. Keeping WP up to date is also a snap and Dreamhost makes the installs a no brainer as well.

Getting this far took less than an hour of total effort. How cool is that? Now the real work begins…

Posted in General News | Comments Off on Building a new blog site

Microsoft’s BI Stack is Back

Posted by Brad Greene on April 15, 2011

A couple of years ago I had figured Microsoft was killing their BI product efforts. Word on the street was that key people and products were being slayed left and right. Well that may have been true but it seems to have been part of a plan to attack the market from a different angle. I’m not a sales guy or even a marketing whiz but I am good with technology and from what I have seen in 2008 R2, Microsoft BI is back! OK, maybe I’m late to the party but I finally arrived and it looks pretty good. I’ve been reading a bunch of “what’s new in R2” articles and blogs and finding them interesting so I ordered up my copy of 2008 R2 a few weeks ago.

The upgrade was pretty painless. I had some issues with my laptop installation and setup of SSRS but thanks to some help from a very smart friend and the removal of AVG Free antivirus software things are humming along nicely. Report Builder is looking really good and they have done a lot in the area of ease of use based on my recollection of 2005. I know Cognos Report Studio very well and I have to say Cognos, get cracking on some new updates. This stuff looks quite good at a price point that a lot of companies are going to find hard to pass up. I’ve never been really thrilled with the charting features in Cognos so I want to see what I can do with the latest in Report Builder.

Next stop is PowerPivot then SSAS. SSAS being the bigger nut to crack but I want to see how it stacks up too. I’ve heard there are even more cool things on the horizon so it may be time to fully spool up on Microsoft BI. After all, they are at the top of the Gartner BI Quadrant this year. Amazing.

Posted in Business Intelligence | Comments Off on Microsoft’s BI Stack is Back