Sorting Month column when it is in Jan, Feb Mar …. Format

This is the way I defined it in new data item.

case
when ([Month]='January') then 01
when ([Month]='February') then 02
when ([Month]='March') then 03
when ([Month]='April') then 04
when ([Month]='May') then 05
when ([Month]='June') then 06
when ([Month]='July') then 07
when ([Month]='August') then 08
when ([Month]='September') then 09
when ([Month]='October) then 10
when ([Month]='November') then 11
when ([Month]='December') then 12
else
0
end

How to calculate percent change for months

Calculate % change between months with relational data

Steps:--Add Order year, Month, count(Order number) in List
-- Under the query:
--create "data item1" with expression moving-total([count],2)
--create "data item2" with expression ([Data Item1] -[count])
--create "data item3" with expression ([count]- [Data Item2])
--create "% change" with expression (if ( (([Data Item3] * 1.00)/([Data Item2] * 1.00)) is null) then (1) else ((([Data Item3] * 1.00)/([Data Item2] * 1.00)) ) and add it to the List as 4th column

How to Use a Prompt Macro to Dynamically Select a Database Table

In Framework Manager, embed a prompt macro at the data item level in a Model Query Subject. Eg: DB Table1 = [Year 2009] DB Table2 = [Year 2008] Both tables have a 'Key' column and a 'Month' column. 1) Using one of the tables, create a model query subject. 2) Edit the data item definitions of the model query subject to have the following syntax. #'[OrclYears].[Year ' + prompt('year','token','2008') + '].[MONTH]'# #'[OrclYears].[Year ' + prompt('year','token','2008') + '].[KEY]'# 3) Create a report which uses the 'year' parameter. Attached is a sample model and report which demonstrates this.

Using Prompt Macro for Optional and/or Required

[gosales].[COUNTRY].[COUNTRY] = #prompt('countryPrompt','string','''Canada''')#

Required Prompt Syntax
[gosales].[COUNTRY].[COUNTRY] = #prompt('countryPrompt','string')#

How to add additional column titles into a list

Use a table object that contains several lists. Show only these columns in a table cell which should have the same additional column title. You can format now some table cells like titles. They will size with the cells they own.

For details, please have a look to the attached document.

Steps:

1. Insert a table in the report
2. Add as much lists as needed to the table (all lists should run with the same query)
3. Format the list cells

Hiding a list or crosstab by using a render variable

 

Instead of using a Boolean variable to handle the Render,  use a String
Variable.
1) On the main menubar, select the "View" menu and select "variables"
2) Create a new String Variable by dragging a String Variable from the Insertable Object pane to the Variables box.
2a)Set the variable syntax to:

ParamValue('DisplayPrompt')

ParamValue('DisplayPrompt')

2b) click OK
2c) Rename the Variable name in the Properties - Variable pane to a friendly name (suchas Page Render)
3) In the Values window do the following for each value you have in your prompt ('List','Crosstab','Both')
3a) Click the new button at the bottom
3b) Type in the value you are using

'List'

3c) click okay
NOTE: The values here must match your USE Values of your prompt.
4) Assign the Render variable on the object you want to display to this variable. Check the box for each item you want it to render for.

Scrolling data from Right to left in Report studio

Scrolling data from Right to left in Report studio with HTML TAG only the following thing

(

<MARQUEE>

First select Start Date and End Date, then click on Finish</MARQUEE>)

Converting months into Quarters.

Create Quarters from Date

There are probably many ways to do this, but I would use the built in date
functions such as:

extract(month,[date]) which will give you the numeric month.
then do a case statement such as: 

case extract(month,[date])
when 1 then 1^st  
when 2 then 1^st  
when 3 then 1^st  
when 4 then 2^nd

When 5 then 2^nd

When 6 then 2^nd

When 7 then 3^rd  

When 8 then 3^rd  

When 9 then 3^rd  

When 10 then 4^th

When 11 then 4^th  

When 12 then 4^th

end 

Now you will have a dataitem which contains the quarter.
You can use it as you desire.

 

Cognos Controller

Financials Cognos

Users Guide

 

Introduction

Financials (FS) Cognos is the Financials reports application within Reports.SMU.  This guide describes available report features and options and how to run them.

Features & Benefits

The data in FS Cognos is copied (refreshed) nightly from SMU Financials database.  As a result, reports run in FS Cognos reflect almost real-time data.  The refresh of the priot day’s data finishes approximately 6:30 AM each morning, so reports run on a Thursday morning (after 6:30 AM) includes data entered and processed from the previous Wednesday evening.  Run Individual and Rollup Q Drive Org reports on-demand for the current month without having to wait for days or weeks for month-end to process.

Pending data can be included on reports.  Multiple orgs can be selected and included in a single Individual Org report.  The built-in security prevents users from retrieving restricted data.

These and other features are explained in further detail in this guide.

Logging On

Financials Cognos can be reached at https://reports.smu.edu/ .  Click on the Reports.SMU link to reach the login page:

Login using your employee ID and network password.  You will see a list of folders:

Click on the Financials folder to bring up a list of reports that can be run.

Run reports by clicking on the report link to initiate.  Depending on the type of report, a prompt page will appear asking for report parameters. 

Run Individual Report

The Individual report is a replica of the Individual Q Drive Org report.  Clicking on the Individual link from the report list brings up the Individual prompt page.

As is the case for most reports in FS Cognos, you first need to define various system parameters for your Individual report:

Select Fund(s) – Check the fund code(s) to include.  Multiple funds can be selected and will be combined on the report.

Select Org(s) – Select the org or orgs to include.  Type in the entire org number in Keywords box and click Search OR type in a partial number and click Search.  The org(s) now appear in the Results box.  If you searched on a partial number, the search is filtered by the partial number and all orgs that begin with those numbers appear in the Results box:

Highlight the org(s) to include in the report and click Insert to move it to the Choices box.  You can move multiple orgs and include them in a single report with this method.

You can also search by the org description, or even partial description.  Click the Options link under the keyword box to view the list of search options using partial or multiple descriptions:

Include Pending Journal Amounts

Pending journals are journal entries that have been input into SMU Financials but are not posted to the ledger.  Pending journal entries have Valid (V), Error (E), or Needs editing (N) status.  If you choose to Yes, include pending journal entries, your reoprt has a column for Pending balances and detail queries have a column displaying the journal status.

Run Report As Of

Select the As Of Date of the report.  Note that Individual reports will always run with an As Of Date of the last day of the month you selected.  This is to ensure that JEs with automatic end-of-month date are included.  For example, AP accruals and Payroll feed journals always have an end-of-month date.  Note: Year to Date always means Fiscal Year to Date, i.e., beginning June 1.

Kicking Off the Report

When all required parameters have been selected, the Finish button at the bottom becomes active and can be selected to initiate the report.  If any required parameters have not been chosen, the button remains grayed-out.  Click on Finish to run the report.

View Individual Report

The Individual report that generates is virtually identical to the Individual Q Drive Org report.  Slight exceptions are:

• If multiple orgs are included, they are listed in the title section
• If Pending journals have been chosen to be included, a Pending Actuals column appears
• Links to generate MTD or YTD Actuals detail reports or YTD Budgets detail report

Summary/Detail Account View

The default view for Assets, Liabilities, Revenue and Expenses is summarized category (e.g., Compensation, General & Administrative).  Click on the summarized category to view sub-categories beneath it:

To view detail list of individual accounts instead of categories, click Page down link on the bottom left:

Month and Year To Date Detail

Click on links above the column headings to view Month to Date or Year to Date Actuals or Budgets detail.  The Detail reports are identical to those on the Q Drive Org reports.  They open in a separate window.

Print, Output to Excel and Email Options

In the top-right section of the Individual report is the FS Cognos toolbar.  These buttons control print, output and email features.

Output to Excel

The Individual report – and every other report – can be exported into MS Excel for further analysis and manipulation.  This is particularly helpful in the case of MTD or YTD Actuals Detail reports.  To do so, click the down-arrow on the second-to-right button in the row:

Clicking the arrow brings up three options:

• Select Single Sheet Format option to output the current report to a single tab in Excel.
• Select Format option to output Individual report and all detail reports simultaneously to multiple tabs in Excel (essentially recreating the Q Drive workbook).
• Select CSV Format to output in comma seperated value format.

Print Report

Report can be printed using the browser print function, but for better results use the .PDF view option to print.  Click the third button from the right to switch the reort to PDF view:

The report appears in PDF format:

Click the Print icon in the upper-left toolbar of the window to print the report in PDF format or the Save a Copy icon to save the report as a .pdf file:

E-mail Report

To email a copy of the report, click the Email icon in the same toolbar:  The message appears:

Select Send Copy and the report will be attached to an email that can be addressed and sent.

HTML View

To switch back to the default HTML (web) view of the report, click the View in HTML Format icon on the FS Cognos toolbar:

Return to Reports List

To return to the list of Report that can be run, click the Back icon on the FS Cognos toolbar.

Run Rollup Report

A Rollup report very similar to the Rollup Q Drive Org report can be run by clicking on the report link from the report list page above.  One key difference of the Rollup report in FS Cognos is the ability to drilldown to YTD Actuals Detail.  Certain high-level rollup reports on the Q Drive only contain MTD Actuals Detail due to size constraints. Click on the Rollup link to reach the Rollup report prompt page.

Select Fund(s) 

Check the fund code(s) to include.  Multiple funds can be selected and will be combined on the report.

 

Include Pending Journal Amounts

Choose to include or exclude pending journal entries.  If included, a Pending column will appear in the report.

 

Select org tree node(s) to report on

Select the tree node or rollup level of the report.  Click the +  icon next to a node level to expand and reveal the nodes below.  Once you have located the node level to include in your report, check the box next to the node level to select it:

NOTE: School nodes can be found under Total University > VP Provost > Provost Schools.

Run Report As Of

Select the As Of Date of the report.  Note that Rollup reports will always run with an As Of Date of the last day of the month you selected.  This is to ensure that JEs assigned an end-of-month date are included.  For example, AP accruals and Payroll feed journals always have an end-of-month date. Note: Year to Date always means Fiscal Year, i.e., starting on June 1.

Kicking Off the Report

When all required parameters have been selected, the Finish button at the bottom becomes active and can be selected to initiate the report.  If any required parameters have not been chosen, the button remains grayed-out.  Click on Finish to run the report.

View Rollup Report

The Rollup report that generates is very similar to Rollup Q Drive Org reports.  Exceptions are:

• By default there are not separate Fund 10, Fund 20, and Consolidated sections.  If multiple funds have been chosen, balances are consolidated rather than displayed in separate Fund sections.
• If Pending journals have been chosen to be included, a Pending Actuals column appears.
• Links to generate MTD or YTD Actuals detail reports or YTD Budgets detail report.

Summary/Detail Account View

The default view for Assets, Liabilities, Revenue and Expenses is summarized category (e.g., Compensation, General & Administrative).  Click on the summarized category to view sub-categories beneath it.

To view the detail list of individual accounts instead of categories, click Page down link on the bottom left.  For large or lengthy reports, you may have to Page down several times to reach the detail account view.

Month and Year To Date Detail

Click on links above the column headings to view Month to Date or Year to Date Actuals or Budgets detail.  They open in a separate window.  The Detail reports are identical to those on the Q Drive Org reports.  This feature enables you to view YTD Actuals Detail for a rollup that may not be available on the Q Drive report.

Print, Output to Excel and Email Options

These features are identical to the Individual report.  Please refer to Viewing Individual Report section for an explanation of these features.  (Note Excel output line limitations still apply).

Run Journal Details Report

Click Journal Details link to initiate the report.  The prompt page appears:

Select Fund(s) – Check the fund code(s) to include.  Multiple funds can be selected and will be combined on the report.

Select Org(s) – Select org(s) to include.  Refer to Running Individual Report section for additional details on selecting orgs.

Include Pending Journal Amounts

Choose how to treat pending journals in the Journal Detail report:

• No, include only posted journals: Only journals with Posted status are included in the report.
• Yes, include pending journal entries: Report includes both posted and pending journals.
• Only display pending journal entries: Report displays pending journals only; posted journals are not picked up.

Desired Detail

Select the type and level of detail to include in the report:

• Actuals or Budgets amounts
• Month to Date or Year to Date (Fiscal)
• Original or Revised Budget only or both

Run Report As Of

Select the As Of Date of the report.  Note that Individual reports will always run with an As Of Date of the last day of the month you selected.  This is to ensure that JEs assigned an end-of-month date are included.  For example, AP accruals and Payroll feed journals always have an end-of-month date. Note: Year to Date always means Fiscal Year, i.e., starting on June 1.

Kicking Off the Report

When all required parameters have been selected, the Finish button at the bottom becomes active and can be selected to initiate the report.  If any required parameters have not been chosen, the button remains grayed-out.  Click on Finish to run the report.

View Journal Details Report

The Journal Details report lists individual transaction detail for selected orgs.  The layout of the Journal Details report is identical to the MTD Detail and YTD Detail tabs on the Q Drive reports, with one key exception - if pending journals are included in the report, a Journal Status column displays that identifies the status of the journal line:

·         Posted – journal has posted to the ledger;

·         Valid – journal has been saved and passed edit but has not posted;

·         Error – journal has been saved and edited but has an error;

·         Needs edit – journal has been saved but not yet edited.

NOTE: This Journal Detail report run from the report list page is identical to journal detail reports created by drilling down from within an Individual or Rollup report using the links at the top.

Print, Output to Excel and Email Options

These features are identical to the Individual report.  Please refer to Viewing Individual Report section for an explanation of these features.  The Output to Excel feature is particularly useful when dealing with Journal Details data.

Run Org Account Balances Report

Select the Org Account Balances report link from the report list page.  The prompt page appears:

Select Fund(s) 

Check the fund code(s) to include.  Multiple funds can be selected and will be combined on the report.

Select org tree node(s) to report on

Select the tree node or rollup level of the report.  Click the +  icon next to a node level to expand and reveal the nodes below.  Once you have located the node level to include in your report, check the box next to the node level to select it:

Include Pending Journal Amounts

Choose how to treat pending journals in the Journal Detail report:

• No, include only posted journals: Only journals with Posted status are included in the report.
• Yes, include pending journal entries: Report includes both posted and pending journals.
• Only display pending journal entries: Report displays pending journals only; posted journals are not picked up.

Desired Detail

Select the type and level of detail to include in the report:

• Actuals or Budgets amounts
• Month to Date or Year to Date (Fiscal)
• Original or Revised Budget only or both

Run Report As Of

Select the As Of Date of the report.  Note that Org/Account reports will always run with an As Of Date of the last day of the month you selected.  This is to ensure that JEs assigned an end-of-month date are included.  For example, AP accruals and Payroll feed journals always have an end-of-month date. Note: Year to Date always means Fiscal Year, i.e., starting on June 1.

View Org Account Balances Report

The Org Account Balances report is a new report designed specifically for FS Cognos.  The report displays balances by org and account for all orgs in the selected node or rollup.  The report is a large grid with orgs running down the side (rows) and accounts spanning across the top (columns).  The benefit of this report is the ability to see every single account balance for every single org in one layout.

Summary/Detail Account View

The default view is summarized account categories (e.g., Compensation, General & Administrative).  Click the summarized category to drilldown to sub-categories:

To drilldown to detail list of individual accounts instead of categories, click Page down link on the bottom left.  If the report is large, you may have to Page down several times to see the individual accounts:

Drilldown on Individual Org/Account Balances

Every balance on this report is a hyperlink.  Click on any balance to drilldown to the individual detail that comprises it.  The detail appears in a new window:

Print, Output to Excel and Email Options

These features are identical to the Individual report.  Please refer to Viewing Individual Report section for an explanation of these features.  The Output to Excel feature is particularly useful with this report to be able to view the entire layout without having to scroll or page up/down.

How do we get the Previous Month Sales Data?

if(?Previous?='Previous Month') then ([Date Field] between _first_of_month (_add_months (current_date,-1)) and _last_of_month (_add_months (current_date,-1))) else ([Date field] in_range ?DatePrompt)

How do we calculate MTD, YTD in cognos

For Daily sales data item I have given as

case
when ( (extract(day,[SALE_DATE])=extract(day,?Parameter1?) ) and (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end

2) For Monthly sales data item I have given as
case
when ( (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end
3) For Yearly Sales I have given as
case
when ( (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end

How do we get the Last month last working day in Cognos?

Cast ([As Of Date], date) =_add_days (_first_of_month (?AsofDate?),-1).