I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

My apologies. I am am new to Cognos analytics. Still learning. Yes, the vendor has already created packages for us with the required data. I created the report and created the required queries within the report. Tried multiple methods (Ex: create multiple lists to display old and new) to display the info but was not successful. Will try your steps and report.

Yes, the second post was created by mistake. 

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

No need to apologize - it can be difficult to convey things clearly in a forum post, especially when you are new to the technology.

You can definitely skip the data module part, and just start at the point where you create a new report. Hopefully you can follow the steps I posted - hoping it works for you!

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 04:06 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

My apologies. I am am new to Cognos analytics. Still learning. Yes, the vendor has already created packages for us with the required data. I created the report and created the required queries within the report. Tried multiple methods (Ex: create multiple lists to display old and new) to display the info but was not successful. Will try your steps and report.

Yes, the second post was created by mistake. 

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 02:05 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 10:27 AM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

------------------------------
Ody Mendis

Original Message:
Sent: Thu August 03, 2023 04:47 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Wed August 02, 2023 12:50 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

------------------------------
Ody
------------------------------

Thanks. I did the steps few times thinking I made a mistake and still my new and old location come as 1.
I then added 3 list items to display data from Query2, Quer3 and Query4.
Query2 and Query3 shows the info for New and Old locations. However, Query4 new and old come as 1. Triple checked the join and all look good.
Not sure why I am getting 1?

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 04:11 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

No need to apologize - it can be difficult to convey things clearly in a forum post, especially when you are new to the technology.

You can definitely skip the data module part, and just start at the point where you create a new report. Hopefully you can follow the steps I posted - hoping it works for you!

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 04:06 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

My apologies. I am am new to Cognos analytics. Still learning. Yes, the vendor has already created packages for us with the required data. I created the report and created the required queries within the report. Tried multiple methods (Ex: create multiple lists to display old and new) to display the info but was not successful. Will try your steps and report.

Yes, the second post was created by mistake. 

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 02:05 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 10:27 AM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

------------------------------
Ody Mendis

Original Message:
Sent: Thu August 03, 2023 04:47 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Wed August 02, 2023 12:50 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

------------------------------
Ody
------------------------------

Just noticed that in your Query2 and 3 the New and Old location property Detail Aggregation was set to "Maximum".

I didn't do that in mine. Is that the issue?

Thanks. I did the steps few times thinking I made a mistake and still my new and old location come as 1.
I then added 3 list items to display data from Query2, Quer3 and Query4.
Query2 and Query3 shows the info for New and Old locations. However, Query4 new and old come as 1. Triple checked the join and all look good.
Not sure why I am getting 1?

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 04:11 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

No need to apologize - it can be difficult to convey things clearly in a forum post, especially when you are new to the technology.

You can definitely skip the data module part, and just start at the point where you create a new report. Hopefully you can follow the steps I posted - hoping it works for you!

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 04:06 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

My apologies. I am am new to Cognos analytics. Still learning. Yes, the vendor has already created packages for us with the required data. I created the report and created the required queries within the report. Tried multiple methods (Ex: create multiple lists to display old and new) to display the info but was not successful. Will try your steps and report.

Yes, the second post was created by mistake. 

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 02:05 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 10:27 AM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

------------------------------
Ody Mendis

Original Message:
Sent: Thu August 03, 2023 04:47 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Wed August 02, 2023 12:50 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

------------------------------
Ody
------------------------------

Hi,

Sorry, yes - set the Detail Aggregation property for New Location and Old Location to Maximum in Query2 and Query3.

What version of Cognos Analytics are you using? In some older versions, aggregations don't automatically carry through when you join queries into a new one.

Also check:

In Query4 - both New Location and Old Location need to have the Detail Aggregation set to Maximum. Do the same for them also in Query1.

I'm guessing the current aggregation is Count or Count Distinct - which is why you see 1 in each column?

Cheers!

MF.

Just noticed that in your Query2 and 3 the New and Old location property Detail Aggregation was set to "Maximum".

I didn't do that in mine. Is that the issue?

Thanks. I did the steps few times thinking I made a mistake and still my new and old location come as 1.
I then added 3 list items to display data from Query2, Quer3 and Query4.
Query2 and Query3 shows the info for New and Old locations. However, Query4 new and old come as 1. Triple checked the join and all look good.
Not sure why I am getting 1?

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 04:11 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

No need to apologize - it can be difficult to convey things clearly in a forum post, especially when you are new to the technology.

You can definitely skip the data module part, and just start at the point where you create a new report. Hopefully you can follow the steps I posted - hoping it works for you!

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 04:06 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

My apologies. I am am new to Cognos analytics. Still learning. Yes, the vendor has already created packages for us with the required data. I created the report and created the required queries within the report. Tried multiple methods (Ex: create multiple lists to display old and new) to display the info but was not successful. Will try your steps and report.

Yes, the second post was created by mistake. 

------------------------------
Ody Mendis

Original Message:
Sent: Fri August 04, 2023 02:05 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

I'm a bit confused. In your other post, you mentioned you had created some queries already but couldn't get the desired format in your report? Are the tables already modeled in a package or data module? If so, you won't need to create a new one - you can just jump in at the part where you create the report.

Is there a reason you've posted the same question in two different threads?

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Fri August 04, 2023 10:27 AM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

Thanks a lot for the detailed explanation. Yes, the data is already in the system. Just included the sql code to simulate the requirement. It's easier to explain when you have sample data.

Unfortunately, when I open Cognos Analytics, I only see "Dashboard, Report, Story and Job". Data Module option is not available. Will have to check with the admin;

------------------------------
Ody Mendis

Original Message:
Sent: Thu August 03, 2023 04:47 PM
From: Mark Fry
Subject: Custom SQL in Cognos Analytics report

Hi,

I'm assuming the data already exists in the master and detail tables - the creates, inserts and drops are just for your SQL simulation?

If this is the case, you don't need to use SQL in your report. First you will need to bring both tables into a Data Module, and add a join between master and detail based on IT_ID 

Then save the data module, and create a new report based on the data module.

Within the report, add a List object, based on Query1. We will come back to Query1 at the end.

Then go to the Queries pane, and drag in a new query (Query2)

Within Query2, add IT_ID and OrdDate as data items

Then from the toolbox, add a Query Calculation, and name it MaxOrdDate. Define the expression as: 

if ([OrdDate] = maximum([OrdDate] for [IT_ID])) then ([OrdDate]) else (null)

After clicking OK, click on the MaxOrdDate item to put it in focus, and in the Properties pane on the right, set the Detail Aggregation property to "Maximum"

Then add a second Query Calculation, and call it New Location. Define the expression as:

if ([OrdDate] = [MaxOrdDate]) then ([C].[C_MasterDetailDemo].[Sheet1_1].[Location]) else (null)

Note: In my case the data is in Excel rather than real tables, so the item for Location appears as [C].[C_MasterDetailDemo].[Sheet1_1].[Location]. In your case, just drag the item into the expression from the source tree - it will look a bit different.

Next add a detail filter to the query, with the expression:

[MaxOrdDate] is not null

Set the Application property to be "After Auto Aggregation"

Add a second detail filter with the expression:

[OrdDate] <= _add_days(current_date, -4)

Your query should now look like this:

Now go back to the Queries pane, and copy and paste Query2. This will give you an exact copy called Query3.

Go into Query3, and modify a couple of things:

• Change the name of the New Location query calculation to Old Location
• Modify the second detail filter to use the expression [OrdDate] <= _add_days(current_date, -20)

So now you have Query2 retrieving the new location and Query3 retrieving the old location. Next we are going to join them into a new query.

Go back to the Queries pane, and drag in a new query - Query4

From the toolbox, drop a Join object on top of Query4. The join will have two empty placeholders for the queries you are joining together. Drag Query2 to the first placeholder and drag Query3 to the second placeholder.

Go into Query4, and from the Source pane on the left, drag in IT_ID and New Location from Query2, and Old Location from Query3

Go back to the Queries pane, and click on the join object. In the Properties pane on the right, click on the ellipsis by the Join relationships property to open up the join dialog.

Click on "New Link", and choose IT_ID on the left and on the right. Set the Cardinality on each side to be 1..1

Now go back to the Queries pane. We need one more query to bring in data from the Master table. Drag in a new query (Query5), and from the Source pane, drag in IT_ID and Name from Master as data items.

We are almost at the end now. We just need to join together this query (Query5) and Query4 to assemble all our data into the query feeding your list (Query1).

Go back to the Queries pane, and from the toolbox, drop a Join object on top of Query1. Drag Query5 to the first placeholder, and drag Query4 to the second placeholder.

Let's add the join condition. Click on the join object, and (as before) open up the join dialog by clicking the ellipsis on the Join Relationships property

Click on "New Link", and select IT_ID on the left and on the right. Set the Cardinality both sides to be 1..1

Now double-click on Query1 to go into it, and from the Source pane, drag in Name and IT_ID as data items from Query5, and New Location and Old Location as data items from Query4

Now navigate to Report > Page 1

Click on the empty List object to put it in focus, and from the Data Items tab of the Insertable Objects pane, drag in the four items from Query1 into the list.

If everything was done as above, you should get the result you are looking for when you run the report:

Cheers!

MF.

------------------------------
Mark Fry
Technical Consultant

Original Message:
Sent: Wed August 02, 2023 12:50 PM
From: Ody Mendis
Subject: Custom SQL in Cognos Analytics report

I am trying to create a new list report in Cognos Analytics that should display the item details.
In my item detail table , I have the item id, order date and location.
An item could have more than one row that was in different locations.
I need to show the old and new warehouse in one line for each item ordered by order date
Logic for old warehouse : Show the top warehouse where OrderDate <= (getdate()-20)
Logic for new warehouse : Show the top warehouse where OrderDate <= (getdate()-4)
I am including the SQL script to simulate this in SQL.
I just don't know whether this is possible in Cognos Analytics as I don't have away to enter custom SQL queries.
Would like to know whether this is possible.

CREATE TABLE #master([IT_ID] [varchar](100) NULL,[Name] [varchar](100) NOT NULL ) ON [PRIMARY]
insert into #master values('V001','Item1')
insert into #master values('V201','Item2')
insert into #master values('V112','Item3')
insert into #master values('V333','Item4')

CREATE TABLE #detail([IT_ID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [Location] [varchar](100) NULL ) ON [PRIMARY]
insert into #detail values('V001','2022-01-10','Warehouse 1')
insert into #detail values('V001','2022-01-11','Warehouse 2')
insert into #detail values('V001','2022-01-12','Warehouse 3')
insert into #detail values('V201','2023-07-01','Warehouse 23')
insert into #detail values('V201','2023-07-25','Warehouse 56')
insert into #detail values('V112','2020-12-10','Warehouse 4')
insert into #detail values('V112','2023-01-15','Warehouse 67')
insert into #detail values('V112','2023-04-25','Warehouse 3')
insert into #detail values('V123','2022-01-10','Warehouse 1')
insert into #detail values('V333','2000-12-10','Warehouse 4')
insert into #detail values('V333','2010-01-15','Warehouse 67')
insert into #detail values('V333','2015-02-25','Warehouse 3')
insert into #detail values('V333','2016-12-10','Warehouse 4')
insert into #detail values('V333','2017-01-15','Warehouse 67')
insert into #detail values('V333','2023-07-25','Warehouse 3')

select rd.Name, rd.*
, [New Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-4)))

, [Old Location] = (Select top(1) [Location] from #detail a where rd.IT_ID = a.IT_ID and a.OrdDate = (Select max(x.OrdDate) from #detail x where a.IT_ID=x.IT_ID
and x.OrdDate <=(getdate()-20)))

from #master rd
drop table #detail
drop table #master

Result I need

------------------------------
Ody
------------------------------