Author Topic: Combining Relational and olap 2 different packages to 1  (Read 478 times)

Offline cognos05

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 706
  • Forum Citizenship: +1/-1
Hi ,

I have a Olap transformer cube and most of my report is created from olap cube . we have a requirement for one part of report to coem from salesforce which is loaded in sql table .

I was thiniking is it possible to have a fm package on sql table with security defined and another olap cube .

so now we will have 2 package can i add these two to one package and publish it ?

I will not link these 2 data but use them to build indepent report controls in 1 report .


Thanks,

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,089
  • Forum Citizenship: +657/-10
  • Cognos Software Muppet
Re: Combining Relational and olap 2 different packages to 1
« Reply #1 on: 09 Jul 2020 06:56:02 am »
Hi ,

I have a Olap transformer cube and most of my report is created from olap cube . we have a requirement for one part of report to coem from salesforce which is loaded in sql table .

I was thiniking is it possible to have a fm package on sql table with security defined and another olap cube .

so now we will have 2 package can i add these two to one package and publish it ?

I will not link these 2 data but use them to build indepent report controls in 1 report .


Thanks,

Hi,

Yes, you can create a package that contains both relational and OLAP. Just add both sources to your model in FM, and when publishing your package, make sure to include the cube as well as the query subjects based on your relational source. You won't be able to create a single container (crosstab, chart etc) in your report that contains data from both, but you can have data from each side by side on the same page in different containers.

Cheers!

MF.
Meep!

Offline cognos05

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 706
  • Forum Citizenship: +1/-1
Re: Combining Relational and olap 2 different packages to 1
« Reply #2 on: 16 Jul 2020 02:14:31 pm »
Hi MFGF,

I did that and it worked . Now I have one issue , i have prompt page for the report which takes the prompt from one of the dimensions from olap package . FOr example it lists all the agencies .

ANd when user selects and agencies the all the queries related to the olap cube are sliced based on the prompt value .

Now I have a report tab which comes from relation source.

So based on this prompt value I have to filter my queryitem on the realtional table .


Error : Invalid format for prompt 'prmAgency'. Expected format is unknown
Is this possible .

I am getting erros when i try to do it .

I did some thing like
[Business Layer].[Agency_Master_Data].[Agency]=
case when (?prmAgency? ='Agency1')
Then ('AgencyA')
Else ('Others')
END


where I am assign the column from realtion table a value which is checking the olap prompt value and then choosing 'AgencyA' or 'Others'

« Last Edit: 16 Jul 2020 02:30:13 pm by cognos05 »

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,089
  • Forum Citizenship: +657/-10
  • Cognos Software Muppet
Re: Combining Relational and olap 2 different packages to 1
« Reply #3 on: 16 Jul 2020 02:34:29 pm »
Hi MFGF,

I did that and it worked . Now I have one issue , i have prompt page for the report which takes the prompt from one of the dimensions from olap package . FOr example it lists all the agencies .

ANd when user selects and agencies the all the queries related to the olap cube are sliced based on the prompt value .

Now I have a report tab which comes from relation source.

So based on this prompt value I have to filter my queryitem on the realtional table .


Error : Invalid format for prompt 'prmAgency'. Expected format is unknown
Is this possible .

I am getting erros when i try to do it .

I did some thing like
[Business Layer].[Agency_Master_Data].[Agency]=
case when (?prmAgency? ='Agency1')
Then ('AgencyA')
Else ('Others')
END


where I am assign the column from realtion table a value which is checking the olap prompt value and then choosing 'AgencyA' or 'Others'

Hi,

The issue is that your prompt is returning an OLAP member, but your relational source requires a value. Assuming the caption of the member is the value you require, you could try creating an expression to isolate the caption, then use this in your filter expression?

Cheers!

MF.
Meep!

Offline cognos05

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 706
  • Forum Citizenship: +1/-1
Re: Combining Relational and olap 2 different packages to 1
« Reply #4 on: 16 Jul 2020 02:42:50 pm »
I tried this
[Agency]=
case when (caption(?prmAgency?) ='Agency1')
Then ('AgencyA')
Else ('Others')
END

but again the error is like

Rolevalue function is called with invalid syntax.

Is any workarounds there for this issue , this will be required on the is multpage report, or the only way is to use a static prompt with values?

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,089
  • Forum Citizenship: +657/-10
  • Cognos Software Muppet
Re: Combining Relational and olap 2 different packages to 1
« Reply #5 on: 16 Jul 2020 02:50:00 pm »
I tried this
[Agency]=
case when (caption(?prmAgency?) ='Agency1')
Then ('AgencyA')
Else ('Others')
END

but again the error is like

Rolevalue function is called with invalid syntax.

Is any workarounds there for this issue , this will be required on the is multpage report, or the only way is to use a static prompt with values?

Hi,

The first observation I'll make is that the expression you posted doesn't look like a valid filter expression. A filter should return either a true or a false.

Secondly, how is the prompt for the ?prmAgency? parameter defined? Can you give us more information?

MF.
Meep!

Offline cognos05

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 706
  • Forum Citizenship: +1/-1
Re: Combining Relational and olap 2 different packages to 1
« Reply #6 on: 16 Jul 2020 02:55:55 pm »
Okay here is it

PrmAgency prompt is filled by  below query

Descendants(set(region1,region2,region3),AgencyLevel)

so this will return values like agency1,agency2 etc...

And then in relational query

I have a table and a query item called agency whose values are like Aagency1 ,Aagency2 etc...

so i am filtering this table based on the prompt value

[Agency]=
case when (caption(?prmAgency?) ='agency1')
Then ('Aagency1')
Else ('Others')
END


there will be multiple cases for around 20 agencies



Offline cognos05

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 706
  • Forum Citizenship: +1/-1
Re: Combining Relational and olap 2 different packages to 1
« Reply #7 on: 16 Jul 2020 03:29:18 pm »
should i cast the prompt value to  a string , looks like its not liking combining caption