Search code examples
loopsrepeatcognos

Creating a Repeating/Looped Table in Cognos

I need to create a table of lat/lon coordinates with repeating values, in order to calculate distances between all locations.

In the DB I have the following fields to use:

Location Code Latitude Longitude
ABC123 39.426413 -77.458716
DEF456 25.708686 -80.378430
ZYX123 38.734117 -77.473075

What I'd like to do is have each location repeat in column 1, with column 2 having all the other locations.

Location Code 1 Location Code 2 Lat 1 Lon 1 Lat 2 Lon 2
ABC123 ABC123 39.426413 -77.458716 39.426413 -77.458716
ABC123 DEF456 39.426413 -77.458716 25.708686 -80.378430
ABC123 ZYX123 39.426413 -77.458716 38.734117 -77.473075
DEF456 ABC123 25.708686 -80.378430 39.426413 -77.458716
DEF456 DEF456 25.708686 -80.378430 25.708686 -80.378430
DEF456 ZYX123 25.708686 -80.378430 38.734117 -77.473075

etc.

Is there a way to do a Union or Join to get this kind of query setup?

I've tried a few different types of joins and nothing seems to be coming out like I'm looking for it to. Any help on this is greatly appreciated.

Solution

  • It's not a loop. You are not using structured programming to solve the problem. You are using Cognos Analytics, which deals with data. So what you need is a way to appropriately join the dataset to itself.

    Use a CROSS JOIN, also known as a cartesian join. This is a join where you don't relate the tables in any way. So rather than something like...

    SELECT A.*
, B.*
FROM A
  INNER JOIN B ON B.ID = A.ID

    ...you want this...

    SELECT A.*
, B.*
FROM A
  CROSS JOIN B

    To to this in a Cognos Analytics report:

    Let's assume you have a query named Query1.

    1. Copy Query1. Query2 is created.
    2. Create a new query (Query3).
    3. Add a join to Query3.
    4. Drag Query1 and Query2 onto the boxes leading to the join.
    5. Open Query3 and add data items as needed.

    Notice you did NOT define the join.

    Now, there's a setting in Cognos that may keep you from doing this. If you get a message stating that cross joins are not allowed, you need to do a little extra work.

    1. In Query1 and Query2, create a data item named joincol and use the expression 'a'.
    2. Open the join leading to Query3 and create a join on joincol--joincol.