User Account Admin on Windows 7 Home Premium

On Windows 7, press Windows key + r --> open the "Run" window.
Type "control userpasswords2" and press enter. It opens User Account Control.

Resolve problem running report to Excel

In case some users have problem to run report to Excel format. They may want to check these settings on their browser.

1. Ensure Popup blockers are not enabled (this includes Internet Explorer’s own pop up blocker, as well as Google or Yahoo tool bar popup blocker)

2. Ensure Automatic prompting for file downloads is enabled
          a. On IE browser go to Tools > Internet Options > Security
          b. Select “Local intranet”
          c. Click on “Custom Level”
          d. Scroll to “Downloads” section and ensure “Automatic prompting for file downloads” is enabled
          e. Repeat above steps for “Internet” and “Trusted” sites

3. Add Cognos web server to trusted sites
          a. IE > Tools > Internet Options > Security (tab) > Trusted Sites > Sites
          b. Add Cognos Web server URL
          c. You may need to clear the Require server verification checkbox

How to set date picker to tomorrow's date

Name the date & time prompt as "FromDate".
Add in a HTML item (after the prompt).
In the HTML item, write the definition like this: (replace $$ with triangle brackets )

$$script$$

function addDay ()
{
var dtToday = new Date();
var dtTomorrow = new Date( dtToday.valueOf() + 86400000 );
// NOTE 86400000 = 24 hours * 60 (minutes per hour) * 60 (seconds per minute) *
// 1000 (milliseconds per second)
var strTomorrow = [dtTomorrow.getUTCFullYear(), dtTomorrow.getMonth()+1, dtTomorrow.getDate()].join("-");
return strTomorrow ;
}

function ReturnTime ()
{var Time = "00:00:00.000"; return Time;}

pickerControlFromDate.setValue( addDay() );
timePickerFromDate.setValue( ReturnTime() );

$$/script$$

Cognos 8.3 BI (and later versions) provides capability to format prompt display

When the prompt page appears, press OK to run the report:

Note the format of the pDate parameter is in ISO standard timestamp format.
2008-10-14T00:00:00.000

We can use the string2timestamp( ) function to cast it to a proper timestamp.

Return to Report Studio and Edit the existing Layout Calculation as so:
string2timestamp(ParamValue(‘pDate’))

Run the report:

The timestamp is displayed with the default date formatting.
Oct 14, 2008 12:00:00 AM

Return to Report Studio.
Select the Layout Calculation item.
Right click and select Style … Data Format.
Set the data format as so whatever you want.

How to fill in a Text Box Prompt with database value

To fill in a Text Box Prompt with database value, we need a value prompt (for example, a dropdown combo box) to pull data out of database and then pass to the Text Box prompt. By the way, you can always hide the drop down to not display it.

Below is a piece of sample code to be put into a HTML item. (replace $$ with <> )


$$script type="text/javascript"$$
function init()
{
document.forms["formWarpRequest"]._oLstChoicesDROPDOWN[2].selected = "true";

document.forms["formWarpRequest"]._textEditBoxHEADER.value = document.forms["formWarpRequest"]._oLstChoicesDROPDOWN[2].value;
}
$$/script$$
$$body onLoad=init()$$


The Text Box is called "HEADER", and the value prompt is called "DROPDOWN"

The first statement selected the line 2 in the drop down, this is not necessary to pass info to the Text Box, just an example how you could make a default selection in a value prompt.

The 2nd statement passes the line 2 in the drop down to the Text Box and display it by default.


First created Dec 05, 2008

Last Business Day (Monday to Friday)

(1) The date range:

[GHA_Report].[Auth_Header].[Entered_Date]

between

if (_day_of_week(current_date, 7) = 2)
then (DATEADD({dd}, DATEDIFF({dd},3,current_date), 0))
else (
if ( _day_of_week(current_date, 7) = 1)
then(DATEADD({dd}, DATEDIFF({dd},2,current_date), 0))
else(DATEADD({dd}, DATEDIFF({dd},1,current_date), 0))
)

and

if ( _day_of_week(current_date, 7) = 2)
then (dateadd({ms},-3,DATEADD({dd}, DATEDIFF({dd},2,current_date), 0)))
else (
if ( _day_of_week(current_date, 7) = 1)
then(dateadd({ms},-3,DATEADD({dd}, DATEDIFF({dd},1,current_date), 0)))
else (dateadd({ms},-3,DATEADD({dd}, DATEDIFF({dd},0,current_date), 0)))
)


(2) Last Business Day Display

if (_day_of_week(current_date, 7) = 2)
then (DATEADD({dd}, DATEDIFF({dd},3,current_date), 0))
else (
if ( _day_of_week(current_date, 7) = 1)
then (DATEADD({dd}, DATEDIFF({dd},2,current_date), 0))
else (DATEADD({dd}, DATEDIFF({dd},1,current_date), 0))
)

Olympic Ranking & Serial Ranking

[Olympic Ranking] = rank ([Charges] desc)

[Serial Ranking]= rank ([Olympic_Ranking] + rank ([Carrier ID] asc for [Olympic_Ranking]) asc)

How to do AR Flow List

From a transaction log, to make the Account Receivable Flow List like this:

(step 1).
Make a Query A called "AR Balance":
1. Transaction Date
2. AR Balance [use running-total(transaction amount)]

(step 2).
Make a Query B called "Transactions":
1. Transaction Date
2. Charges
3. Adjustments
4. Denials
5. Payments

(step 3).
Join the two queries together on Transaction Date (1,1) -- (1,1)
Ending Balance = [AR Balance].[AR Balance]
Pre AR Balance = [Ending Balance] + [Denials] + [Payments] + [Adjustments] - [Charges] (calculated backwards)
Create a Month data item to group by. (_make_timestamp(extract(year, [Trans_Date]), extract(month, [Trans_Date]), 1))

(Step 4)
Create the Main Query:

[Month_Year] = [Join].[Month_Year]
[Charges] = total ([Join].[Charges] for [Join].[Month_Year])
[Payments] = ...
[Adjustments] = ...
[Pre AR Balance] =
maximum(
if ([Join].[Trans_Date] = minimum([Join].[Trans_Date] for [Join].[Month_Year]))
then ([Join].[Pre AR Balance])
else (-10000000000)
for [Join].[Month_Year])

[Ending AR Balance] = [Pre AR Balance] + [Charge] - [Payments] - [Adjustments] - [Denials]

[Month-Year-Display]:

case (cast(extract(month, [Join].[Trans_Date]), varchar(2)))
when '1' then ('Jan ')
when '2' then ('Feb ')
when '3' then ('Mar ')
when '4' then ('Apr ')
when '5' then ('May ')
when '6' then ('Jun ')
when '7' then ('Jul ')
when '8' then ('Aug ')
when '9' then ('Sep ')
when '10' then ('Oct ')
when '11' then ('Nov ')
when '12' then ('Dec ')

else ('')
end
+
cast(extract(year, [Join].[Trans_Date]), varchar(4))

Done!

My first Blog

博客, 博客, 博客...
My first Blog!