That works perfectly, thanks so much!

We are changing to use AWS datalake source, seems the datalake data source connection is not working in Framework manager. Anyone has experience on it?

Thanks

Hi,

You could try adding a Query Calculation to your list with an expression of 1. In the properties of the column, set both the Detail Aggregation and Summary Aggregation properties to be Total.

Does this give you what you need?

Cheers!

MF.

I am new to the community, working on a report pulling numbers by County. I have one field "county" that has the name of a county. Trying to pull this into a report summary and show a list of each county name that is in the database with the corresponding count for each (i.e. Riverside appears 10 times and San Mateo appears 3) I need to show the counts for each county that shows in a list.

The counties change weekly in our database with new records and such, I don't want to create a count for each county with it always changing.

Can I break out each unique county name and create a count? Any assistance is greatly appreciated.

This topic has been moved to Reporting.

https://www.cognoise.com/index.php?topic=37998.0

We have a requirement to place a text field in the crosstab cells based on the expression at the end of this which looks something like:

12,345 (67.89%)

Where:
12,345 is the number of error records found
67.89% is the percentage of error records (12,345) divided by the total records (18,184)

Is it possible to do this? We are using Redshift for the reporting database. I created a column named Errors and Percentages based on the following expression and another column named Aggregate Errors with aggregate:[Errors and Percentages] as the expression and added that to the crosstab, but that does not seem to work.

CASE

WHEN [Details].[Error Count] / [Details].[Detail Record Count] = 1
THEN [Details].[Error Count] + ' (100.00%)'

WHEN [Details].[Error Count] / [Details].[Detail Record Count] >= .10 AND [Details].[Error Count] / [Details].[Detail Record Count] < 1
THEN [Details].[Error Count] + ' (' + substring (cast ([Details].[Error Count] / [Details].[Detail Record Count], varchar(22)), 1, 5) + '%)'

WHEN [Details].[Error Count] / [Details].[Detail Record Count] < .10
THEN [Details].[Error Count] + ' (' + substring (cast ([Details].[Error Count] / [Details].[Detail Record Count], varchar(22)), 1, 4) + '%)'

END

I chose to do it this way because the Cognos SuperStar, CognosPaul, had suggested in https://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/#comment-59474 to prefix expressions with aggregate: beginning with Cognos 10.2.1 FP3.

On the On-demand toolbar, there is a Style (a.k.a., "Style current selection") icon.  Clicking there opens a dialog box with the following four tabs in this order:

Text
Size and positioning
Background
Box.

Through my own tests and reading, I've been unable to determine what the "Size and positioning" tab controls.  It only contains two setting.  Page 58 is the only place where I can find any relevant reference to this in the "IBM Cognos Analytics - Reporting Version 11.2 - User Guide" but the function isn't really described nor defined there.

By the way, the Text tab also has similarly named settings for size and vertical/horizontal text alignment.

Anyone know what that second tab controls or influences?

Dear all,

I do understand that it was a nightmare for reports export to Excel with all formula not working with the 2007 format (XML).  I have tried to do something like printing them as "Rich Text" item as in Cognos 10 with "EXCEL 97" output.  It is no longer working in Cognos 11 / 12. 

Sample Output of Cognos in Excel:
Quantity | Unit Cost | Total Cost
===================================
256      | 15.62     | =(B2*C2)
92       | 49.69     | =(B3*C3)
162      | 80        | =(B4*C4)

Does anyone with work around?  It is because the finance / accounting users will use the Excel to do other calculation.  Please share your clues if any.  Actually, I have tried a number of Python library openpyxl / win32com.  Openpyxl was failed to create a "real" xls and win32com can create format "56" /
"39" (Excel 97).  The formulas are still reading as text with win32com.

Thanks in advance.

Regards,
AM

If you have set both prompts, why would you click Reprompt?  Maybe you need to click Finish.

Quote from: Ludijak on 30 Nov 2023 09:57:53 AMHi,

thank you for reply. Your assumptions are correct. I am using prompt() to get parameter value in Data item for period. That period Data item targets correct MUN to retrieve from the cube. All this is working as expected and there wasn't any issue with this report regarding data in the report.

I'll try to explain better what is the issue. Let's simplify and say I have two prompts, one for period and second for customer. Default value for Period is current period (that would be 202311 now) and default value for Customer is All.

Report is set to automatically run with default parameter values and everything is working as expected. When I change only period and repromt everything is OK, when I change only customer from all to specific it is also working as expected. But when I change both customer from all to specific (let's say A) and period to any other no data is returned on reprompt. Both prompts are single select. All is on the same level as specific customers.

Now what is interesting is that when I repromt second time with same parameters everything is working as expected.

Hi,

That sounds like odd behaviour. Can you post up an example of the expression you are using?

Cheers!

MF.