Recent Posts

Pages: [1] 2 3 ... 10
1
Framework Manager / Re: FM Filters from two different databases
« Last post by bus_pass_man on Today at 02:04:23 pm »
Isn't wonderful that the Cognos query engine has built into it functionality to plan queries which go against different data bases, which would be quite handy in cases such as yours?  The trick is to actually make use of it.

You are going about the process the wrong way.

The cognos best practices require that the query layer consist of query subjects formed by the expressions of the simple select * from {table} syntax. 

The filter should exist in the business layer.

There was a similar question asked here.
https://www.cognoise.com/index.php/topic,36881.0.html
and here.
https://stackoverflow.com/questions/64102239/cognos-11-filter-between-query-subjects

I asked you several question in the former and got answers to some of them and some of the answers actually helped but others made it less clear what you have set up in your model. 

Define 'without luck'.  What results did you get?  How was it wrong?

Are you doing action 2  as a data source qs?  Why?  Do that sort of thing in the business layer.  Filtering a query subject is not a particularly intellectually challenging task so I don't understand why you are running into problems even given the special hoops you want to try to leap through.

What does "move the Query Subject down a level" mean?

I'm assuming that table A and table B are part of a dimension (no not the thing with hierarchies, levels and members). 

2
Thanks for your Reply!!

I have created Dataitem by using my [Timestamp] column as : cast(extract(Month, [Timestamp]), char(4))+'-'+cast(extract(Year, [Timestamp]), char(4)) which is giving result as

2 -2020
2 -2020
4 -2020
4 -2020
2 -2020
2 -2020

Thanks,
Swetha
3
Report Studio / Re: Duplicate values getting in prompt for Month-Year
« Last post by MFGF on Today at 01:09:03 pm »
Hi Everyone,

I'm using cognos 10.1 version. In my report studio, I have Timestamp column and converted to Date format as cast([Timestamp] ,Date) and given Dateformat as 'MM-YYYY' in propertie pane for that column, which is giving result as below. May I know how can I remove duplicates and have to do sorting. I need to use this column in value prompt.

[DataItem1]

Feb-2020
Feb-2020
Apr-2020
Aug-2020
Feb-2020
Jan-2020
Jan-2020
Apr-2020
Feb-2020

Can anyone please help me on this.

Thanks,
Swetha

Hi,

What you are seeing is correct here. Although your display format is MM-YYYY, you still have complete dates held in the item behind the scenes, so although it looks like you are getting duplicates because of your format, you are not, in fact. You will see this if you remove the format and run the report again. If you really want just month and year you're going to have to put those values into the item - ie extract those parts from the complete date.

Cheers!

MF.
4
Report Studio / Duplicate values getting in prompt for Month-Year
« Last post by cognosbi.dwh on Today at 01:03:06 pm »
Hi Everyone,

I'm using cognos 10.1 version. In my report studio, I have Timestamp column and converted to Date format as cast([Timestamp] ,Date) and given Dateformat as 'MM-YYYY' in propertie pane for that column, which is giving result as below. May I know how can I remove duplicates and have to do sorting. I need to use this column in value prompt.

[DataItem1]

Feb-2020
Feb-2020
Apr-2020
Aug-2020
Feb-2020
Jan-2020
Jan-2020
Apr-2020
Feb-2020

Can anyone please help me on this.

Thanks,
Swetha
5
Framework Manager / FM Pass-through SQL issues
« Last post by BIengineer on Today at 04:10:04 am »
The following query subject, utilizing two databases from each their own data source and running with Pass-Through SQL, with Oracle SQL on the DB side:

Select
   *
From
   CDB.PRODUCT as PRODUCTS
Where
   PRODUCTS.PRODUCT_ID not in (Select PRODUCT_ID from REPORTS.PROTECTED_ID);

Gives the following errors:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'
UDA-SQL-0107 A general exception has occurred during the operation "prepare"
ORA-00933 SQL command not properly ended
6
Reporting / Re: TypeError: Cannot set property 'src' of null
« Last post by oscarca on Today at 01:18:40 am »
I can confirm that the latest fix pack for 11.1.7 solved the issue.

best regards, Oscar
7
Reporting / SQL Window Functions
« Last post by dougp on 21 Oct 2020 12:50:22 pm »
Using Cognos 11.0.13 with a relational model...

Is it possible to use SQL window functions (like LEAD, LAG, etc.) without using SQL objects using Native or Pass-thru SQL syntax?
8
Framework Manager / FM Filters from two different databases
« Last post by BIengineer on 21 Oct 2020 06:40:56 am »
Hello!

I have a package containing tables from different Data Sources. These Data Sources are based on different databases, which in Oracle SQL cannot be queried on one another. Having the Query Subjects in the model separately works, and they retrieve data when testing just fine.

What I need to do however, is to filter out values in DB1.TableA.ColA from values in DB2.TableB.ColB

What I have tried so far, without luck:

1* Adding a "Where in" filter in the query subject, ie:
Select * From DB1.TableA
Where DB1.TableA.ColA not in (select ColB from DB2.TableB)

2* Adding a Query Subject containing "accepted values" - which returns what is expected when testing the query.
I then move the Query Subject down a level, and create a Filter object using an expression like:

DB1.TableA.ColA not in (DB2.TableB.ColB)

And then adding the filter to the corresponding table that needs to be filtered


Both of these approaches give me the UDA-SQL-0333 error, saying that it cannot find the given tables.

Is there some form of work-around that can be done here? Doing some DBA magic on the Oracle SQL side is not an option sadly, and it can't be implemented in Cognos itself either. It has to be made in FM.

Any ideas?
9
Reporting / Re: Navigation Paths in Data Modules
« Last post by bus_pass_man on 20 Oct 2020 05:44:51 pm »
No I can't see any way to do either. 

You might want to log an enhancement.

https://ibm-data-and-ai.ideas.aha.io/
10
Reporting / Re: Navigation Paths in Data Modules
« Last post by chr.93 on 20 Oct 2020 08:27:22 am »
Just to confirm, you want to be able to reorder the navigation paths so that you have control over where each one appears in the navigation path metadata tree.  Is that a correct understanding? 

You also want to have the ability to create folders in the navigation path tree.  Is that a correct understanding?

Also, you know about how to order elements in a navigation path and that is not part of the problem you are encountering.  Is that a correct understanding?

Yes all three of them are correct!
Pages: [1] 2 3 ... 10