If you are unable to create a new account, please email bspsoftware@techdata.com

Recent Posts

Pages: [1] 2 3 ... 10
1
In the content store database, look at CMOBJPROPS26.DELIVOPTIONS.  Good luck pulling that apart reliably.  You'll need to use a lot of SUBSTRING, CHARINDEX, and probably some XPATH.  And you'll probably encounter a lot of      Invalid length parameter passed to the LEFT or SUBSTRING function.      along the way.  I just dug up an ancient script I had used at one time.  Apparently it is now broken by new data that hits edge cases I hadn't considered at the time.
2
We've created some tables from the Cognos 11 Audit package (c111_audit) and the content store (c111_content), but haven't cracked the nut on getting a report of all the people a report is scheduled to.  Anyone figured this out yet?  (No, I'm not going to buy a service or tool or engage anyone to figure it out.  Just trying to save a little time.)  Thanks.
3
Reporting / Re: Scheduling issues
« Last post by dougp on 26 May 2023 11:18:17 am »
Does it persist after you clear your browser cache and refresh the Cognos tab to get a fresh Cognos session?
4
Reporting / Re: Ratio query with parameter filter
« Last post by MFGF on 26 May 2023 08:59:26 am »
Hi,

I'm new to Cognos BI. I basically need to create a query expression for a report, concretely a percentage for a measure where the numerator's total is filtered by an input parameter (from a request page), and the denominator is tied to all unfiltered items in respect to the former. Both parts are also filtered by two parent dimensions coming from the request page, too.

In short, the scheme is the following: X, Y are the two parent dimensions (these are directly nested themselves, as well). Z is the filtered dimension for the numerator. Also ?p_z? is the parameter related to Z I let the user provide values into, from the request page (it allows multiselection).
I am using a normal detail filter for X and Y (for the report), since they are common to both ratio parts. But, since Z filter is only to be applied to the numerator, I can't add it as a detail filter since otherwise the ratio result would naturally be 100%.

I have tried the following in a query calc, and I hope it makes sense with what I explained. I'm just calling the target measure "Measure":

Code: [Select]
total([Measure] within set filter([Measure];[Z] IN ?p_z?)) / [Measure]
The expected result is a value in [0.0, 1.0] range that I'll format later.

When I add the query calc in a report list, I get the following error:

Code: [Select]
PCA-ERR-0021 Unable to evaluate a value expression on a set with more than one member.
How to fix the formula? Thank you.

Hi,

First, a sanity check and a few questions.

Your package is a dimensional model, not a relational, correct?

What is the source - is it a real OLAP cube (eg Powercube, Dynamic cube, TM1/PA cube etc) or is it Dimensionally Modelled Relational (DMR)?

What exactly are you filtering here - ie what is [Z]? Is it a level? A defined set of members? A hierarchy? Something else?

What is your ?p_z? parameter prompt returning? Is it a member? A set? Something else?

How are you filtering the X and Y dimensions? You mention a detail filter - can you specify exactly what this is doing?

Now those pesky questions are out of the way, let's take a broader view.

Firstly, if this really is a dimensional package, detail filters are probably not the right choice to use. They can give inconsistent and sometimes incorrect results when dealing with sets of members from a cube. The preferred option is to use dimensional expressions to derive the appropriate set of members from each dimension. For example, rather than bringing in an entire level then using a detail filter to focus in on the members of the level you require, instead create a set() expression in a query calculation to bring in just the members you require, and use this instead of the level. There are many more examples - for example if you want a prompted set of members, instead of bringing in the level and using a detail filter with a parameter, use a dimensional expression with a parameter in a query calculation - eg set([Your level or set of members] -> ?Your Parameter?)

Secondly, the filter() function you are using here is designed to filter a set of members - based on a measure value or a descriptive attribute. It isn't something you can use to filter a measure.

A couple of examples:

filter( [Your set of Region members], [Measure] > 100)  - this will return just the Region members whose aggregated measure value is greater than 100

filter( [Your Product Line level], caption([Your Product Line level]) contains 'Equipment')  - this will return the Product Line members which contain the word Equipment in their caption

In this case, I would first replace the detail filters with dimensional expressions to isolate the required members for those dimensions, then I would change the expression for Z to use a dimensional expression with the parameter embedded (see example above) so that it returns a prompted set of members.

Cheers!

MF.
5
Reporting / Re: "View the Most Recent Report" option in drill through navigation
« Last post by MFGF on 26 May 2023 08:13:43 am »
Hi MF,

I'm using 11.2.1. and these are the steps taken:
1. Right click on the report, then select run as
2. Select Run in Background, ticked PDF and HTML
3. Click run button
4. Once the report finishes running, click on the report

The report when navigating using default page up and down button, URL doesn't change (this means that the report is not interactive correct?)

Best regards.

Hi,

Are you rendering the report as HTML or XHTML? It should show the report name, date and format in the switcher at the top. If just regular HTML, I wouldn't expect there to be Page Up and Page Down buttons - just a long output you can scroll through?

Cheers!

MF.
6
Reporting / Ratio query with parameter filter
« Last post by Adrianilloo on 26 May 2023 06:43:19 am »
Hi,

I'm new to Cognos BI. I basically need to create a query expression for a report, concretely a percentage for a measure where the numerator's total is filtered by an input parameter (from a request page), and the denominator is tied to all unfiltered items in respect to the former. Both parts are also filtered by two parent dimensions coming from the request page, too.

In short, the scheme is the following: X, Y are the two parent dimensions (these are directly nested themselves, as well). Z is the filtered dimension for the numerator. Also ?p_z? is the parameter related to Z I let the user provide values into, from the request page (it allows multiselection).
I am using a normal detail filter for X and Y (for the report), since they are common to both ratio parts. But, since Z filter is only to be applied to the numerator, I can't add it as a detail filter since otherwise the ratio result would naturally be 100%.

I have tried the following in a query calc, and I hope it makes sense with what I explained. I'm just calling the target measure "Measure":

Code: [Select]
total([Measure] within set filter([Measure];[Z] IN ?p_z?)) / [Measure]
The expected result is a value in [0.0, 1.0] range that I'll format later.

When I add the query calc in a report list, I get the following error:

Code: [Select]
PCA-ERR-0021 Unable to evaluate a value expression on a set with more than one member.
How to fix the formula? Thank you.
7
Reporting / Re: Scheduling issues
« Last post by wenrmz on 25 May 2023 08:00:47 pm »
Hi,

The reports does not involve any prompt, I have installed version 11.1.7 FP6.

Thanks,
WR


8
Reporting / Re: "View the Most Recent Report" option in drill through navigation
« Last post by Newb on 25 May 2023 05:35:37 pm »
Hi MF,

I'm using 11.2.1. and these are the steps taken:
1. Right click on the report, then select run as
2. Select Run in Background, ticked PDF and HTML
3. Click run button
4. Once the report finishes running, click on the report

The report when navigating using default page up and down button, URL doesn't change (this means that the report is not interactive correct?)

Best regards.
9
Reporting / Re: Scheduling issues
« Last post by MFGF on 25 May 2023 02:10:57 pm »
Hello,

I’m experiencing issues when trying to create a Schedule. I click on the Report Properties → Schedule and follow the steps to set the schedule, but once I try to save it, I’m getting an error:

“An error has occurred so the schedule could not be created”

A couple of months ago I was able to successfully create the schedule, so I have renewed password, I tried in different browser (Chrome, Edge) by cleaning cookies and cache, I tried my ID on a coworker's computer and got the same error, so seems like it is not the computer.

In the meantime I have been creating Jobs to get the automated deliveries, but I'd like to recover the Scheduling option as it was before.

Have you seen that kind of error message? Any help will be appreciated,


Thanks!

Hi,

What version of Cognos Analytics are you using (and fix pack, if any), and do the reports involved contain prompts?

I think there were known defects that could cause similar errors, but 11.1.7 FP5 was supposed to fix them.

Cheers!

MF.

10
Reporting / Re: "View the Most Recent Report" option in drill through navigation
« Last post by MFGF on 25 May 2023 02:05:52 pm »
Yes MF,

I have tried using bookmark but it will only work in PDF output. For saved report output in HTML format, clicking on the button which uses bookmark to land to other page yield no response at all.

Best Regards

Hi,

Bookmarks should work for both HTML and PDF rendered reports. When you are testing, you need to make sure that you are running and saving the HTML output first, then viewing that saved output. If your testing is running the report interactively, only the first page gets rendered, so bookmarks do nothing.

Cheers!

MF.
Pages: [1] 2 3 ... 10