Skip to main content

Posts

Showing posts from 2012

Cognos 10.1.1 Report Studio - Creating Dynamic Filter Based on Tab Selection

Creating Dynamic Filter Based on Tab Selection Use Report Studio to get the following end result. (Yesterday) (Month to Date) (Life to Date)   Create a Query Create a new query to be used solely to hold the tab values. Call it “TimeFrame Query” and create data items for every time frame that you’d like to appear. The expression definition should be a string encapsulated in single quotes. Create a String Variable Next let’s go ahead and create a string variable called “Tab Condition”. Define the expression as shown below: if (paramvalue('paramTabChoice') is null) then ('LTD') else (paramvalue('paramTabChoice')) This expression will set a default tab value for the report. Next, we will need to create a string value to correspond to every data item we created in the query. Note: The values of this variable must exactly match the expression definition of the data items we created. It is not necessary to use single quotes when

Calling AS400 Stored Proc from MS SQL

Being a newbie to AS400/RPG, it took me a few hours to figure out how to call a stored procedure on AS400 from MS SQL. Here is the straight forward syntax: declare @sql varchar(100) = 'CALL LIBRARY_NAME.STORED_PROC_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')' exec (@sql) AT NAME_OF_LINKEDSERVER   Make sure that the LIBRARY_NAME is a part of user's library. Make sure to use IBMDASQL OLE DB Provider.    

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.

Linked Server to IBM AS400 from MS SQL Server

I am back after a while! I have accepted a new position which means more responsibilities. I now lead a Business Intelligence initiative. This opportunity is exciting and interesting. I get to do what I love to do! So first task at hand was to be able to access data from the AS400 system for ETL to MS SQL 2008. Simple, right? Simple? I thought so, but got stumped for the first few minutes and thought many others might have the same issue! If you do not see IBMDA400, IBMDARLA and IBMDASQL as in the above screenshot, you are mssing some drivers. Issue at hand - cannot find proper drivers on MS SQL installation to connect to the AS400 system. What you need to do is install IBM Access iSeries for Windows software on your SQL Server box. http://www-03.ibm.com/systems/i/software/access/windows/index.html . After installing this software, logout and log back in. Once that is done follow these steps. 1. Navigate to SQL Server Installation > Server Objects > Linked Servers