NW Business Intelligence

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

Archive for the ‘Tech Tips’ Category

Technical, hardware and coding tips

Bluetooth headset on Google Nexus 4 can’t voice dial now

Posted by Brad Greene on April 16, 2013

I recently made the switch from Apple to Android for my mobile phone. It’s been a great experience over all. However, recently I found myself ranting when my Bluetooth headset failed to voice dial when I really needed it to. I was in the car needing to make a call. It had worked fine so far. I realized that when I activated the voice dial the prompts were coming from the phone instead of the headset! Not great. In fact, a fat juicy moving violation was waiting to happen if I picked up the phone while still driving.

I spent hours dredging the internet trying to find a fix when I finally ran across a post in a Google coding forum. Some kind sole had the decency to post what they had done to fix the exact same problem. Obviously an Android update has toggled a setting but this little tip fixes it and makes everything right.

1) Open Google Now
2) Scroll to bottom and touch on 3 vertical dots — bottom right
3) Pop up menu shows, select Settings (also shown should be Refresh, Search…, Help, Send feedback)
4) From Settings screen select Voice
5) Look for Bluetooth headset — if box is unchecked, check it

Test your headset again and if you that was your problem your voice dialing should be working again like mine did.

Posted in Tech Tips | Tagged: | 3 Comments »

Cognos Component List

Posted by Brad Greene on September 23, 2011

Want the list of installed components in a Cognos installation? Substitute your server name into the URL below and paste it into your browser. Assumes your are using CGI of course. Change it to cognosisapi.dll if you are using the IIS DLL. You should get a long list that starts out something like the text below.

http://server_name/cognos/cgi-bin/cognos.cgi?b_action=cmplst

; Licensed Materials - Property of IBM
; BI and PM: is
; (C) Copyright IBM Corp. 2004, 2010
; US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
[Product Information]
LICENSE_BI_SERVER_version=LICENSE_BI_SERVER-AW32-ML-RTM-10.1.9.0-0
LICENSE_BI_SERVER_name=IBM Cognos License
C8BISRVR_version=C8BISRVR-AW32-ML-RTM-10.1.4707.544-0
C8BISRVR_name=IBM Cognos Business Intelligence Server
LICENSE_BI_SAMPLES_version=LICENSE_BI_SAMPLES-AW32-ML-RTM-10.1.9.0-0
LICENSE_BI_SAMPLES_name=IBM Cognos License
...
...

 

Posted in Business Intelligence, Tech Tips | Tagged: | 2 Comments »

Cognos Report Studio, IIS 7 and HTTP 405 errors

Posted by Brad Greene on September 19, 2011

A lot of businesses are finally upgrading or installing IBM Cognos on Windows Server 2008 with IIS 7. The documentation for configuring IIS 7 with Cognos 10 is hard to find and often inconsistent leading to some frustration. I’m not going to document all the steps needed here because there are lots of them and they vary based on whether you happen to have IIS 7 or 7.5 for example. One thing is clear, Microsoft’s effort to tighten up security has resulted in a lot more work being required to get things working correctly. This follows an all too familiar pattern in software development, the more features you add the more complicated to use it gets. Tightening security is one of those “damned if you do and damned if you don’t” issues as well. Almost no one is going to be happy. You might be more secure but you also might not be up and running for a few days!

Just a point of clarification on one item. The WebDav feature/service/role is required and is not automatically included with the IIS 7 install. If you have IIS 7.0 you will probably have to download the module from Microsoft’s web site and install it using the Administrator account. If you have IIS 7.5 then all you need to do is enable the WebDav role from Server Manager. If you do not install or enable WebDav you will most likely get a “405 – Method Not Allowed” error in Cognos Report Studio when trying to browse the images directory to select an image to place on your report. This despite being able to browse the folder from Internet Explorer. Directory Browsing allows your images to be accessed by Report Studio still requires WebDav. Here are a few useful links provided by colleagues or found in my efforts to get through the process.

Configure WebDAV on IIS 7.5

Configure IIS 7 and Cognos 8

Cognos Admin errors and IIS 7

Posted in Business Intelligence, Tech Tips | Tagged: , , , , | Comments Off on Cognos Report Studio, IIS 7 and HTTP 405 errors

SQL Server mixed mode security

Posted by Brad Greene on June 26, 2011

Getting the “Microsoft SQL Server: Error 18456″ when trying to log into your SQL Server database after upgrading to 2008 R2 perhaps? Happened to me. I’d been using Windows Authentication since upgrading to 2008 R2 but then needed to utilize SQL Server Authentication after installing Cognos 10. I spent a little while scratching my head wondering why none of my SQL accounts worked and all I got  was the previously mentioned error.

I presume the default installation sets the Server to Windows Authentication only or I picked it during the upgrade and don’t remember. You have to set it to mixed mode by going into the Server properties. See screen shot below. These settings are under the Security page.

Posted in Tech Tips | Tagged: , | Comments Off on SQL Server mixed mode security

SSRS Report Builder 3.0 and IE9 needs .NET 3.5

Posted by Brad Greene on June 12, 2011

If you are using Microsoft IE 9 browser and the new Report Builder with SQL Server 2008 or later you may have run into the error below when trying to start RB 3.0. It seems odd at first because Windows 7 comes with .NET 3.5. I learned after a little searching that the issue lies with IE9. The problem is easily resolved by setting the Compatibility mode for this URL (the one you use to access your SSRS Report Server). You may have to enable the setting in IE9 under the Tools menu. Do a web search for how to enable Compatibility on IE9 and you will find an MS Support site article showing you how to do it. With that turned on the browser behaves like prior releases and, I assume, allows it to find the .NET Framework that RB 3.0 requires to run.

Report Manager error opening Report Builder 3.0

Report Manager error opening Report Builder 3.0

With the Compatibility mode set to “on” as indicated by the icon below Report Builder now runs as expected.

Report Manager opening Report Builder 3.0

Report Manager opening Report Builder 3.0

Posted in Business Intelligence, Tech Tips | Tagged: , , | 1 Comment »

SSRS Custom Assemblies require .NET 3.5?

Posted by Brad Greene on June 10, 2011

I’ve been working on prepping for my Microsoft 70-448 certification and learning a ton of things. Yesterday I was adding some custom assemblies to a report using VB as my code base. I decided to try using Visual Studio Express 2010 to build the DLL as an added exercise. That worked as expected. Then I copied the DLL to the required folders and defined the references in the report. So far no problems. However, when I went to preview the report I got an error which indicated that my assembly was of a version later than that supported. That left me scratching my head for a few minutes.

It occurred to me to check the Advanced Compiler properties in VSE for the assembly I had just built. There I noticed  the target .NET Framework was set to 4.0. I suspected that was the problem and set it to 3.5, recompiled the DLL and tried it all again. Sure enough, that was the issue. I then went to Google and confirmed it.

So, if you build custom assemblies for SSRS 2008 R2, as of June 2011, you probably have to stick with compiling for the 3.5 .NET Framework. I did not see any posts or articles for work-arounds but that doesn’t mean one isn’t possible. Never say never.

Posted in Business Intelligence, Tech Tips | Comments Off on SSRS Custom Assemblies require .NET 3.5?

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

Compare SQL Server tables

Posted by Brad Greene on January 5, 2009

Once in a while it’s nice to compare the list of tables in one database with another to see what might be missing. Here is a simple select statement to do that. In this case the databases are on different servers as well. The server names are in brackets. Any table missing in database B will show as ‘NULL’ in the list next to the table name found in database A.

You need the ability to connect to both of servers in SQL Server Management Studio. I’m sure there are other ways to do this depending on what level of access you have or tool you are using. This works in SQL Server 2005 at least.

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
order by 1

You can refine it by limiting it to only those that are missing in B this way:

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
where
B.TABLE_NAME is null
order by 1

Posted in Tech Tips | Comments Off on Compare SQL Server tables

Calculating next week

Posted by Brad Greene on January 1, 2009

Got this nice tip on how to calculate the date range for “next week” from a posting on IT Toolbox.

 Relies on the value of day number of the week in relation to the number of days in a week.

Here is the pseudo code: Some_Date minus Day_Number_in_Week + 8 gets the first day of the next week. If your weeks start with Sunday being day 1 then it would be Sunday. The last day of the week is derived the same way but by adding 14 instead of 8. Nice tip! Here is the link to the original posting.

Posted in Tech Tips | Comments Off on Calculating next week