Skip to main content

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 defining the values of the string variable.


Create a Hidden Value Prompt

Drag a value prompt onto the page. Name the parameter “paramTabChoice” within the prompt wizard and click “Finish”



Once created, within the properties menu, set the Box Type to “None” in order to make this prompt disappear.



Associate the Query to the Page

Navigate to the page explorer. Select the page we are working on and associate it to the TimeFrame Query we created in the earlier steps.



Create Tabs

Navigate back to the report and drag a table object onto the page. Define it with 3 columns and 1 row. Drag Text Items into each one of these cells and name them appropriately with each time frame (Month to date, Life to Date, Yesterday etc). Add 20px of padding to each one of these cells.



Add Data Object

Add List, Crosstab, Chart etc field under the table inserted above.





Create Filter Based on Selected Tab

Select the data object, right click and select filter. Create filter using advanced option. Enter the below IF ELSE condition for this example.


if (?paramTabChoice? IS NULL) THEN ([Presentation View].[Registration Date].[Registration Date] BETWEEN _first_of_month (current_date) AND current_date)


else if (?paramTabChoice? = 'LTD') THEN ([Presentation View].[Registration Date].[Registration Date] BETWEEN _add_years (current_date,-150) AND current_date)


else if (?paramTabChoice? = 'MTD') THEN ([Presentation View].[Registration Date].[Registration Date] BETWEEN _first_of_month (current_date) AND current_date)


else if (?paramTabChoice? = 'Yesterday') THEN ([Presentation View].[Registration Date].[Registration Date] = _add_days (current_date,-1))


else ([Presentation View].[Registration Date].[Registration Date] BETWEEN _add_years (current_date,-150) AND current_date)



Run the report and test!


With help from: http://www.ironsidegroup.com/2011/11/14/ibm-cognos-tabbed-report-design/
Labels:

Comments

Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...
25 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...
5 comments
Read more

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.
Post a Comment
Read more