Is it possible to join a table of text items to a query?

[psrpsrpsr]

Hello folks, here is my use case: I only have access to Report Studio, not Framework Manager. I work for a company that has franchise locations, and I need to create a total by month for locations whose open date falls into certain months. A caveat is that 'pre-sale' franchise locations must be excluded, which is indicated in the 'Franchise Open Date' field as 1900-01-01.

The desired output is:
YearMonth   # Locations
201506          1,001
201507          1,015
201508          1,021
...and so on.

I have the correct calculation for the aforementioned "# Locations" column, which is as follows:

TOTAL(CASE
WHEN [Franchise Open Date] <= 1900-01-01 THEN 0
WHEN [Franchise Open Date] <= 2015-06-30 THEN 1
ELSE 0
END)

Now for the tough part. I want to join this to another query from a namespace that has an Attribute for YearMonth. The namespace from which I am calculating the # Locations DOES NOT have a similar attribute. (I'm not a Framework Manager expert so I hope I'm explaining this well.)

My question is: Can I create a table that contains text items on the left for YearMonth, and query calculations on the right giving me my desired Total() value, and then join that table as a 'query' of sorts to the other namespace? I'm specifically wondering if Cognos requires 'joinable' data to be actual queries to the Framework Manager/database.

If further clarification is needed as to my motive or methods, please let me know. Thank you for your insight.

[Lynn]

If you transform [Franchise Open Date] to the desired YearMonth format does that solve your problem and negate the need for a join?

Assuming your franchise date is an actual date data type, then you can use the extract function to produce the year/month as a number. Wrap that whole thing in a cast function to convert it to a string if you like.

Code Select Expand


extract ( year, [Franchise Open Date] ) * 100
+
extract ( month, [Franchise Open Date] )


I realize this isn't a direct answer to your question but based on what you described it sounds like you're only joining to get the year/month attribute.

If you must join, you can create two queries in your report studio report and then join them together by dragging a join tool into the query from the toolbox. You would then need to define how the two queries relate to one another as the basis performing the join. If you look at the tabular data returned by the two queries you must be able to define how a row in one data set matches up to the other data set.

Be aware that joins created within the report result in processing on the Cognos server rather than on the database server which can be slow. If you have two highly summarized result sets it may not be a problem, but joining many thousands of rows could be problematic.

[AnalyticsWithJay]

I'm specifically wondering if Cognos requires 'joinable' data to be actual queries to the Framework Manager/database.

No, it does not require it. It will retrieve data from the database and join it with your static data locally.

I agree with Lynn's suggestions.

[psrpsrpsr]

Hi folks, thank you for your responses. Allow me to clarify and expand on some of your thoughts:

Lynn:
- the available [Franchise Open Date] from an existing package is indeed a date data type.

- I hadn't thought of using functions to create a pseudo-primary key, I will try that. This leads to another question: Do joined fields in Cognos have to be the same data type? Or in theory can you join a primary key field of any data type (varchar, int, date) to a table constructed within Cognos that might just be comprised of Text Items?

- Allow me to clarify your statement:

...based on what you described it sounds like you're only joining to get the year/month attribute.

If you must join, you can create two queries in your report studio report and then join them together by dragging a join tool into the query from the toolbox. You would then need to define how the two queries relate to one another as the basis performing the join. If you look at the tabular data returned by the two queries you must be able to define how a row in one data set matches up to the other data set.

I'm well versed in joins from a SQL standpoint, so to reiterate I am trying to join this table of Text Items (replicating YearMonth) and Query Calculations to another query. My thought is: 'well, there is no [YearMonth] field available in the package where I need to do the Query Calculation. So I'll try to make a faux YearMonth Text Item field that will serve as the primary key in this join.'

So here's the crux of my question: In Cognos terms, can any old Table that you drag and and populate with Text Items and Query Calculations serve as a DE FACTO table/query result/data set that can be joined in the same manner as any other data in the data warehouse?

  -  IF YES, how do you do that?
  -  IF NO OR 'SORT OF', can you explain why?

Here's another example that might illustrate my thought process. Using the Great Outdoors data set that I understand is a common training data set, let's say that you've been asked to buy lunch for all employees and want to quickly report on their choices.
- You drag in a List query and display all employee names and locations.
- You drag in a 2-column table. One column for employee (Text Item), and one for food choice.
- With your table object populated, you have a de facto data set/table that you want to join back to the Employee namespace and all the great information contained therein.
How do you do that?

Thanks again!!!!!!!!!

[Lynn]

Tables that you drag in from the toolbox are there to help you organize your layout. They aren't tables in the sense of database tables.

All layout containers in Cognos (lists, crosstabs, charts, singletons, etc.) are fed by queries. You may also associate a query to a page and then plop the information in a free form manner anywhere on the page, often using tables and blocks for this purpose.

The underlying queries which feed layout containers are the things you can join, not layout containers themselves. You can go to the query explorer and create multiple queries which can then be combined in various ways: join, union, intersect, or except. Chapter 10 of the report studio user guide explains these concepts, but if you are familiar with SQL then you will understand the concepts easily.

When joining or comparing, the standard SQL rules apply such that data types generally need to match. You can't join a date to an integer, for example. Some databases will implicitly convert things so referencing '1' might work the same as 1 without the single quotes, but generally sticking to the proper data type is probably best.

For your generous lunch extravaganza example, I'd probably opt to create a query calculation and avoid a join. In this list with the names and locations from the package I'd also include the employee ID. Then add a query calculation to indicate each person's order.

Code Select Expand


case [Employee ID]
  when '06543' then 'Egg Salad on Whole Wheat with lettuce and tomato'
  when '83492' then 'Bologna with cheese on white bread and dijon mustard'
  when '23742' then 'Italian Hero with the works'
  .... etc ....
end


If you really (really, really, really) must create joined queries I'll reiterate my performance caution that may be an issue due to local processing. I'd further suggest that these situations can be a red flag warning signifying that your Framework Model can't easily meet reporting requirements. There may be an opportunity to revisit the data warehouse and/or model design to simplify report authoring.