Combining Relational and olap 2 different packages to 1

[cognos05]

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,

[MFGF]

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.

[cognos05]

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'

[MFGF]

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.

[cognos05]

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?

[MFGF]

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.

[cognos05]

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

[cognos05]

should i cast the prompt value to  a string , looks like its not liking combining caption