how to create a data item in qry subject which can act as an incrementing factor

[UseCog]

Hi All,

I have created 2 queries (in report studio 8.4) and I want to join both of them using a key. unfortunately I dont have a key to join those 2. But In both queries I have 13 rows always which is based on the dates in this year and previus year.

So i think I can create an ID in both of the query subjects and I can join using that. But I cant find out any way to create an incrementing factor or a row count(not in report design page) in query subject. Is there any way to accomplish this in report studio query subject

Thanks in advance

[blom0344]

rank(some unique dataitem) seems to work. This is added by the server to the returned set (not executed in the query)

[UseCog]

Thanks Bolm for your help. I tried this, but the join has produced some error. My scenario as follows

I have a report contains a cross tab and it needs to show the measures in last 13 weeks based on the week selected by user. Also I need to show some data from the same period in last year. But when I use a filter on the query to show the data between the latest year week range and report studio filters the report for this period and can’t see the measure for the last period.

E.g.:- I want to show measure Total collection value for the period of 200901 to 200913 and at the same cross tab I need to show the data from 200801 to 200813.

Is there any way to implement this in Cognos Report studio 8.4

[blom0344]

If at all possible design a union query to fetch 2 seperate datasets (each dataset can then have it's own filter, without interfering with the other set)

If you work with a regular join, then the filter will effect all data that is fetched.

From what you sketh in your example your situation is the perfect candidate for a union, certainly not one for a regular join.

Do you understand the union principle?

[kaevne]

You can do it with two queries and union them or two queries and join them as 0..n 0..n.  1 query will have the 1-3 filter and the other the different filter.

[david.stachon]

you could add the following data-item to each query and join on it:

running-count(1)

(this adds an incrementing integer to each row returned in your query)

[UseCog]

Thanks Blom, David and Keavne. unfortunately nothing is working here...

I have around 50 measures(fyi: I have subtotal on month and quarter, and other filter on dimensions) in my query and i need the previus year data for only 5 measures and also these 5 measures need to be shown in seperate columns with the current year measures. So I cant be able to use the union here. I thought join is the right candidate here. Unfortunately, it gave me the following error

QE-DEF-0360 The query contains a reference to at least one object '[qryYear].[Id]' that does not exist. Possible cause is the use of 2-part reference to a model query item, which is a deprecated feature.

My data source is a cognos cube.

Any other ideas are welcome.

Thanks

[blom0344]

The KB gives this explanation:

Report has joined queries populating a prompt that has a cascade source set. When the parent prompt is selected, Cognos 8 attempts to apply a filter to the child prompt query. However, there was a explictly defined parameterized filter in the sub-queries. This was confusing the query engine, giving the above error.

I have no experience with a dimensional model, but creating crosstabs on joined query sets seems to yield these type of errors..

[UseCog]

Thanks blom for you support.

This issue is annoying me for the last couple of days...