How to Display one list in another list (at sub totals or any other way)? please

[aswani]

Hello There,

Could you please assist me how to place one list in another list in cognos report studio,

We are using Cognos 10.2

i have a report with two lists in it (both lists were based on sql query), and a prompt for Account Num, (both lists filterd by acctNum prompt) ........
please see the attached expected results for an idea what i am talk about....


please help me out here, i greatful to you, here i tied from sql itself using union betweek queries but the thing is how to show them in the report, because both lists has different number of columns also i have doubt is there any way ti place one list in another list in subtotals or some otherway please

please kindly help me, with any open ideas...


Thank you in advance
Milani

[aswani]

Could somebody please guide me here, with any open ideas please please


thanks in advance
milan

[aswani]

it the attached 1st list and 2nd list are the present , the third list report is the expected result

please share any ideas

sorry for keep asking

thanks

[aswani]

any  help  please...........

[CognosPaul]

There's no need to post multiple times. We are all volunteering our time here; nobody here is paid to answer, and we all have our own responsibilities. Posting multiple times in the same day will only serve to annoy people.

As you said, you need to do a union between the two queries. The biggest issue that I see is OpenDate appears to be a date while AccdStatus is a string.

First step, in the first query cast the OpenDate into a string.
Now, in the second query, create 4 new columns.
SyndBal = cast(null as integer)
CommDate = cast(null as date)
Settle Batch = cast(null as varchar)
Cines Load = cast(null as varchar)

Get rid of all excess columns.

Now you can make the union. The results of that union will be your report.

[aswani]

Hi Paul,.

i am sorry, just in frustration i did this, i will make sure this will never repeat again.,

with the union we have so many issues (such as output in my first list i have 15 columns but in second list it is just 3 columns), is there any possible way in report studio alternate ways

please kindly

thanks
milan

[CognosPaul]

There are a few possibilities here. How many fields do you have in common?

You can either attempt to a union statement, which means setting up each query to have exactly the same number of fields, with the same data types. Alternately, you could set up a full outerjoin between the two, and use coalesce to merge the fields in common.

I described the process to use a union in my first post, so I'll describe the full outer join.

Create a new data item in both queries. In query one, put the value 1. In query two, put the value 2. Now create a join between the two queries based on that field. It needs to be a full outer join, so 0..n - 0..n.

In the new query, create a data item for each field that you have in common. The expressions should be something like:
coalesce([Query1].[Field],[Query2].[Field2]). Coalesce requires that both fields be the same data type, so you'll need to cast OpenDate to a string to coalesce it with AccdStatus. Now you can drag the other fields from the queries If all goes well, you should have a single list that looks like your example output.

[aswani]

Thank you Paul God,

i will go through this, implement now itslef, let you know,

i greatful to you

Best Regards
milan