How can I create dummy 'month' rows in a query subject with no date fields?

[psrpsrpsr]

Hi folks, I would like to create a dummy 'month' column that creates 12 rows for each Location ID in my query. I do not have a date field in this query subject with which to use the Cognos date functions.

I have tried using RUNNING-TOTAL() in a query calculation in various permutations to no benefit.

My next thought is to create 12 queries, each with a query calculation assigning a 1-12, and union them together. Is there a better way?

Here's what I have:
Location ID
1234
9876
4567
6543

Here's what I would like to have:
Location ID    Month
1234                          1
1234                          2
1234                          3
1234                          4
1234                          5...   >>>you get the idea
1234                       ...12
9876                          1
9876                          2
9876                          3
9876                          4....

Thanks for your input!

[dax]

Hi

So just to clarify, do you not have any time dimension at all in the package you're using?  So no Year/Month hierarchies?  It doesn't necessarily need to be in the same query subject.....

Dax

[hespora]

Dax is right, you just need to get months 1 through 12 from *somewhere*. If you absolutely don't have them, you can always manually create those. A better alternative than you proposed 12 queries is to create one query, point that at an SQL item rather than a data source, and use this SQL:

Code Select Expand

SELECT tbl.month

FROM

(VALUES 1,2,3,4,5,6,7,8,9,10,11,12) tbl(month)

Be sure to change the SQL syntax of your SQL item from native to IBM Cognos.

[Invisi]

The suggested query will solve your issue short term. What I wonder is why you want to get something based on months when you say you lack a relation to a date dimension. When you are using a regular database package, you can create a full outer join with your date dimension (no Cognos date functions...). Otherwise I don't get what you're trying to do.

[psrpsrpsr]

Thank you for the suggestions! Invisi, yes, this is a short term solution until the FM is reconfigured.

The reason I need this is because I have 5 individual query subjects with fact dimensions and associated date dimensions. However, there are metrics that may not have facts occurring in a month, resulting in a missing column in the crosstab.

I need to be sure that the crosstab will display 1-12 for every Location ID, regardless of whether activity occurred for each metric during any given time period. By extension, my design strategy is to create this 'dummy' field and perform a series of left joins on each query subject to pull in the associated fact data for each month.

Does that clarify? If my design strategy is clunky I'd love to hear alternatives. Thanks

[hespora]

Hi psr,

I don't think you need a series of joins per month. Here's what I do (cause if I'm understanding correctly, I have the same issue within my reporting environment as well):

1. create one query that has your location ID as its only data item
2. create another query that has months 1-12 as the only data item (like was said above, if you just dont have that in your data source, you can use the sql syntax I posted)
3. in both queries, add a data item [join] with a fixed value. I usually use '1'.
4. now setup an inner join between both queries on item [join]. pass location id and month from their respective query. this query results in every possible combination of location id and month.

You can now use this query to join against your data source and grab the relevant metrics. HOWEVER: you need to be aware that you now have 12 rows per location id. If you have nothing in your data source to join the month against and thus do not link on the month field, then you will get the same result in each of those 12 rows. If you leave the aggregate options at automatic and *do* aggregate here, then you will have faulty values!

[psrpsrpsr]

Helpful as always, thanks all! I will attempt this and report back.

[Invisi]

I had this issue looooong ago. I think there are 2 proper options to deal with it (assuming you do have a connection to a date or month dimension):

1) create your fact in the framework to have an outer join with your date dimension. You will probably use a variation of your regular fact for that, as mostly you will want an inner join. Using the outer join (in the proper direction) in your framework and package should get you the desired result by default,

2) what I write earlier: get a basic query in your report to get the month info you need of all months and then outer join that with your fact query. The join result should contain all your months.