Hey, so I have a few request here. I've recently inherited a cognos system that was being maintained by another user. Well that user has abruptly left the company and I need some help.
They were setting it up to go through an AWS ALB and for the most part it's working correctly. But with some of the reports they're getting an error 404 when they click the link.
We have Server1 and Server2. Server1 sends the URL link1.app.domain and it works fine. Server2 sends link2.app.domain and it fails. But if we take that URL and manually change it to link1.app.domain then the URL works.

If anyone has any way to fix this please let me know. I'd like to have Server2 send the correct URL.
Also, what are your recommendations for learning material on cognos?

Hi All:

I just have a couple questions about importing data into a report with in a package. I have an Excel and a text file that I want to import (they are not the best use cases for Cognos, but this is the best option for it) and modify a small portion of the data and reexport; I have run into the following problems:

• Can you import a multi-tabbed Excel report? The Excel file I have has 2 tabs
• For a text/CSV file, does the first row *always* have to be the header? I receive a file that does not contain headers, so I just wanted to ask.

Thank you

That doesn't sound simple at all.  And it's not necessarily a crosstab.

This will have 2 rows?  If not, more examples and information about how it gets summarized is critical.

Option 1:  Craft the query to compute the various "month" category values (column labels) and use a crosstab.  (so, you'll have 2 columns:  amount and quantity)
Option 2:  Pre-pivot the data into columns and compute values as needed.

Either way, it's not necessarily really difficult, just tedious.  But it's also very specific to your need.  There is no generic solution.

It looks like LAG() is the reason to write custom SQL.  But that's only for the Last_Salary_Change query.  The other three queries can all be built in the usual way and can be JOINed or UNIONed normally in Cognos. 

Date BETWEEN ?Start_Date? AND ?End_Date?

will work as a filter expression in Current_Manager, all built in the GUI.

WITH Current_Manager AS (
    -- Get the current salary of employees who are managers within the specified date range
    SELECT
        Seqno,
        Date,
        EmployeeID,
        Designation,
        Salary
    FROM
        REGEMP
    WHERE
        Designation = 'Manager'
        AND Date BETWEEN ?Start_Date? AND ?End_Date? -- Prompt macros for date range
),
Last_Salary_Change AS (
    -- Identify the most recent salary change for each employee
    SELECT
        EmployeeID,
        MAX(Date) AS Last_Change_Date
    FROM
        REGEMP
    WHERE
        Salary != LAG(Salary) OVER (PARTITION BY EmployeeID ORDER BY Date) -- Only include rows where the salary changed
    GROUP BY
        EmployeeID
),
Last_Changed_Salary AS (
    -- Get the details of the last salary change
    SELECT
        R.Seqno,
        R.Date,
        R.EmployeeID,
        R.Designation,
        R.Salary
    FROM
        REGEMP R
    JOIN
        Last_Salary_Change LSC
    ON
        R.EmployeeID = LSC.EmployeeID
        AND R.Date = LSC.Last_Change_Date
)
-- Combine the current salary and last changed salary
SELECT
    Seqno,
    Date,
    EmployeeID,
    Designation,
    Salary,
    'Current Manager Salary' AS Row_Type
FROM
    Current_Manager
UNION ALL
SELECT
    Seqno,
    Date,
    EmployeeID,
    Designation,
    Salary,
    'Last Changed Salary' AS Row_Type
FROM
    Last_Changed_Salary
ORDER BY
    EmployeeID, Row_Type DESC;

Hi everyone,

I want to create a sales controlling report consisting of a crosstab. Unfortunately I get stuck at one point. I use relational data coming from a data module.

The crosstab is supposed to show the development of sales amount (EUR) over the months of the current period (a period starts from first of calendar year until the last month with data for the whole month - which at the moment would be Jan until Oct - but without the last full month (October).
Next to that (= to the right of the months columns) I need the measure for the last full month (Oct 2024).
Next to that I need the measure for that month one year prior (Oct 2023).
Next to that I need the difference / derivation in absolute numbers between Oct 2024 and Oct 2023.
Next to that I need that difference in percent.
Next to that I need the total (aggregated) sales for that period (= current period).
Next to that I need the total sales for the same period one year prior (= prior period).
Next to that I need the difference / derivation in absolute numbers between current and prior period.
Next to that I need that difference in percent.

Underneath the sales measure (= currency) I have numerous other measures like sales quantity, profit etc.

To me that sounds simple but I'm not able to get the results without using some - in my opinion not well designed - solutions where I hide columns with render variables, which can be messy in a crosstab, especially when nesting node members. I've also found a solution using dimensional functions but I think using these functions with relational data is not the best idea, either.


Here's a  sketch how it's supposed to look like:


                                                           2023                                      |            |             | Diff Oct24/ |              |Jan-Oct|Jan-Oct| Diff Jan-Oct    |
                                   Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep | Oct 24 | Oct 23 | Oct 23(abs) | Diff(%) | 2024    | 2023    | 2024/23 (abs) | Diff(%) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
sales amount (EUR)    10   5      5       5      3      2      5     10    5     | 10       | 5           | +5               |+100% | 60       | 48         | 12                   | +25%
sales quantity              X    X      X    ...
...



I'm very thankful for your answers.

You appear to be confused about the difference between a union, a join, and a filter.

Can you post a minimal example of what you are trying to do as SQL code?

Update:

Encountered the following error while using prompt macro.
RSV-SRV-0001
 
      The request is missing the objectPath element.
 
 

But theres no error validating the SQL.

I have not tried using prompt macros. Let me try and provide feedback.

Irrespective of the SQL Syntax( Native or cognos or pass through the prompt doesn't work)

The reason for using the union all in the same query is .

Example - query 1 or first part select employees with designation Manager for a given date range .
query 2 select a record for those employees where the salary was last changed.
So in the second query there's no date or designation filter.

Seqno    Date           EmployeeID   Designation    Salary
1       11/20/2024         RON.B       Manager       $140,000
2       09/20/2023         RON.B       Manager       $140,000
3       08/15/2022         RON.B       Analyst       $120,000
4       09/12/2021         RON.B       Jr Analyst    $95,000

In this scenario the output shoud display row 1 and row 3.

If I separate the queries out and add detail filter . How to add query 1 as the source for 2 without union all in 1 query.

So current query is pulling employees that are managers and then maximum of the date for the changed salary record.

Quote from: actcognosuser on 20 Nov 2024 11:50:49 AMHi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

Hi,

Have you tried using prompt macros in your SQL rather than Cognos parameters? For example, replacing ?Start_Date? with #prompt('Start_Date','Date')#

Is there a reason you are coding this as SQL rather than adding detail filters to each query that feeds into your Union object? If you must use SQL, why are you using pass-thru SQL rather than Cognos SQL (or Native SQL)?

Cheers!

MF.