timestamp to date function

timestamp to date function:

cast([Audit].[Threshold Violations].[Time stamp], date) = current_date

Running a Report via Trigger

Running a Report via Trigger


https://www.youtube.com/watch?v=yhV8Q5SeTSU

Cognos audit package name conver to readable format :

Cognos audit package name conver to readable format :


SUBSTRING(
SUBSTRING([Audit].[Run Reports].[Package]
,CHARINDEX('=',[Audit].[Run Reports].[Package]
)+2,1000)
,1,
CHARINDEX(''']',SUBSTRING([Audit].[Run Reports].[Package]
,CHARINDEX('=',[Audit].[Run Reports].[Package]
)+2,1000)))

SQL Server with Cognos Year, Month, Day convertion

SQL Server with Cognos Year, Month, Day conversion

Year
convert(Char(4),Year(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))


Month

case when convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))<10 then
'0'||convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))
else convert(Char(2),Month(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))
end

Day

convert(Char(8),Day(CAST ( [Audit].[Run Reports].[Time stamp] AS Date )))

cognos visualization library and customizer

IBM Vizualization Customizer : https://www.ibm.com/communities/analytics/cognos-analytics-
blog/cognos-business-intelligence-cognos-analytics-visualization-customizer/

1. Run VisCustomizer.jar
2. Open vis.sample.clusteredbar.zip from file menu
3. Amend the vizJSON part as you required .
4. Select Chart Type Definition tab
5. Change the values of Bundle Name text box  
6. Example : in place sbm any text you  can mention
7. vis.sample.clusteredbar.zip to vis.sample.clusteredbar.sbm.zip
8. Select String Resources tab 
9. Tag Metion : clusteredbar
10. Change the values of lblChartType=clusteredbar to lblChartType=clusteredbar-sbm
11. Validate Bundle button
12. Upgrade Definition  button
13. Generate Vis Bundle Definition button
14. click to save / save as
15. Give new name : vis.sample.clusteredbar.sbm.zip
16. import file to cognos

        2.3 : Select String Resources tab and change the values of lblChartType=Chord to lblChartType=Chord-sbm
        2.4 : Click on Validate Bundle button to check the validity of changes. (Valid message will appears).
         2.5 : Click on Upgrade Definition  button. (The definition was successfully upgrades message will appears).
               2.6 : Click on Generate Vis Bundle Definition button then click to save the file with new name  (ex: vis.sample.chord_sbm_10_2_2.zip.)
               2.7 : Import the file to your Cognos Environment then you will have two visualizations as below

==================================================

If you are facing some issue while open library file ?

1. Open VisCustomizer.jar
2. Remove validate check box then click on Draw chart.
3. then open library file

cognos Audit Milisecond to hour convert

Milisecond to hour calculate in cognos audit

[Audit].[Run Reports].[Execution time]/(1000*60*60)

Cognos report studio image file configuration IIS

Details find in link : https://www-304.ibm.com/support/docview.wss?uid=swg21506690

Configuring WebDav on IIS 7.5 to have access to your image on Report Studio:

 Verify in Server Manager that the following IIS components are installed:
- Webdav Publishing
- Windows Authentication

Go then on Default Web Site and activate Webdav and Windows Authentication (both are disabled by default):

 

 

Double click on WebDav Authoring Rules on Default Web Site level and then click on WebDav Settings…

- Modify "Allow Anonymous Queries" to "True"

- validate clicking on "Apply"

Go then in Samples/images in Cognos IIS alias and add a Webdav rule, for example all content / all users / read as below:

You can now navigate in the Report Studio Browser Image:

Distinct count on report studio

Count(distinct [MBRSHP_NO_BK] for [DOCTOR_NAME])

cognos report Total defined at any level in dyanmic cube

I have measure column called "Member_Count"
I need to extract member total to "Member_Count"
you can define which level you wanted to make total for my case defined at doctor level.

Make new iteam:

total ([Member_Count] for [Query3].[Doctor])

Derived / Extracted measure in report studio using cognos dynamic cube

If want to create new measure in dyanamic cube cognos while reporting in cognos.

like below.

Please note don't take level don't take description so its better to take Key to calculate extracted measures.

---
CASE WHEN
[Main Service - Key] = 'ACCOMODATION'
THEN [Inputed Quantity]
ELSE 0
END
----

Saving Report Output to the Cognos Server File System - Automatically

Saving Report Output to the Server File System


ENABLING THE SAVE TO FILE WITHIN COGNOS CONFIGURATION

1. From Cognos Connection, click on Launch (top right)
2. Click Cognos Administration
3. Click on Configuration tab
4. Click Dispatchers and Services (left side)
5. Click the Define File System Locations button ( top right)
6. 
   

Cognos dynamic cube with stand alone / New table, no relationship with fact table

Cognos dynamic cube  with stand alone / New table, no relationship with fact table

Separete new table / dimension need to publish in cognos dynamic cube package without joining fact table - wihtout relationship with fact or dimension table, New table means just stand alone table.

Steps:

1. Get Table ready in Database
2. Create new dimension with new table
3. Drag new dimension under existing cube
4. Drag new dimension out of existing fact table (trick is here : Don't put inside measure folder new dimension move to outside of measure folder )
5. Need to define relationship to existing fact table because its (new stand alone table) has no relation with existing fact table.
6. Validate it and publish the cognos dynamic cube package

Cognos dynamic cube with stand alone / New table, no relationship with fact table

Cognos dynamic cube  with stand alone / New table, no relationship with fact table

Separete new table / dimension need to publish in cognos dynamic cube package without joining fact table - wihtout relationship with fact or dimension table, New table means just stand alone table.

Steps:

1. Get Table ready in Database
2. Create new dimension with new table
3. Drag new dimension under existing cube
4. Drag new dimension out of existing fact table (trick is here : Don't put inside measure folder new dimension move to outside of measure folder )
5. Need to define relationship to existing fact table because its (new stand alone table) has no relation with existing fact table.
6. Validate it and publish the cognos dynamic cube package

Dynamic cube error attached.

If you are facing issue dynamic cube error.


..\data\relmd_HRMTGT_HRMTGT_dbo.xml (Access is denied.)

Solution 1. Make sure you should have access to cognos installed location , admin shold be good.
Solution 2.  Just open cognos dynamic cube as administrator.

your problem solved
 

One cognos filter and multiple behaviour

If I select query1 for 2016 automatically I can get query2  data minus1 year and querey3 data minus2 years
Automatically my below three query will filter..
[Contract_YYMM]=?yrm?
querey -2
[Contract_YYMM]=
cast(substring(Cast(?yrm? as char(6)),1,4)-1 as char(4))
+
cast(substring(Cast(?yrm? as char(6)),5,6) as char(2))

querey -3
[Contract_YYMM]=
cast(substring(Cast(?yrm? as char(6)),1,4)-2 as char(4))
+
cast(substring(Cast(?yrm? as char(6)),5,6) as char(2))

Cognos picklist reports - javascript code

If user want to load file with multiple values to value prompt from desktop (local) and then select values after all values loaded into value prompt.

Browse value prompt input file in prompt page : java script - drop html item before value prompt in prompt page.

Copy and paste this script before value prompt.
Note : don't remove 123 points its within the script, entire code.
=====================================
<script>
function load()
{
var form = getFormWarpRequest();
var list1 = form._oLstChoicesABC;
var name= form.cmuds.value;
upload(list1, name);
function upload(listPrompt, fileName) {
            var objFSO, objTextFile;
            var sReadLine, exception;
            var ForReading = 1, ForWriting = 2, ForAppending = 8;
            while (listPrompt.options.length != 0) {
listPrompt.options.remove(0);}
            try{               objFSO = new ActiveXObject("Scripting.FileSystemObject");
                        objTextFile = objFSO.OpenTextFile(fileName, ForReading);
                        while (!objTextFile.AtEndOfStream) {
                                    sReadLine = objTextFile.ReadLine();
                                    listPrompt.options[listPrompt.options.length] = new Option(sReadLine, sReadLine);
}
objTextFile.close();                              
            } catch (exception) {
                        alert("Invalid file name" + fileName);
            }
}}
</script>
1- Click "Browse" to select text file required:<br>
<input type=file name="cmuds"><br><br>
2- Click "Open File" to load data:
<input type=button onClick="load()" value="Open File">
<br><br>
3- Select required Data Item(s) and click "Finish":<br>

=====================================

Cognos PDF report columns page breaking

PDF reports all columns not showing in 1st page its going to second page: please do pagesetup paper size : custom, define height and width more as per need.

Cognos Cross tab row header

Add cross tab header on row columns : add 1 table with two columns > create two crss tab pointing to your 1 query > in second cross tab select rows data items, cross tab corner then boxtype: none >> in 1st cross tab select columns, measures,cross tab corner then boxtype: none > 1st cross tab align to right top and second cross tab align to left top > run report its done.

Cross tab measure use text value as data iteam

Text item in crosstab measure are : Change the property *Define Contents* to '*YES*" for crosstab Fact cells.

Convert cognos drop down prompt to type in / text box prompt

Convert cognos drop down prompt to type in / text box prompt

If you wants add drop down to type in prompt use caption() : Caption([Query1].[SR_AREA])=?prompt?

Wildcard text prompt - use like in prmpt

You are not sure what user is going to enter values in prompt, so design like this way.

Wildcard text prompt: [Query1].[SR_SUB_AREA] like '%'+ ?Parameter1? + '%'