Hi,

have new install of Analytics. When selecting a package to use in report studio, get the following error message

TypeError: window.parent.require is not a function
http://<server>:9300/pat/rsstartupblock_1.js?v=167871828217

Any help appreciated

Pre-pivoting the data would look like this in SQL:

select
  <dimensions>
, sum(
    case
      when MyDimension = 'a' then MyMeasure
    end
  ) as 'A'
, sum(
    case
      when MyDimension = 'B' then MyMeasure
    end
  ) as 'B'
, <etc>
So, all of your measure values are pre-aggregated by the query into each respective bucket.  Calculations are handled as needed.  Each bucket becomes a column in a List visualization.
That won't work if you don't know how many columns you want in your output.

You need option 1.  You'll need to get creative.  Each group of columns comes from a query.  You'll build this in Cognos, but the SQL would look something like...

Query1
(months to date this year)
select
  <dimensions>
, d.MonthAbbrev
, cast(d.Year, varchar(4)) as 'Year'
, <measure>
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year = _year(_add_months(current_date, -1))
  and d.MonthNum <= _month(_add_months(current_date, -1))
Query2
(recent full month a year ago)
select
  <dimensions>
, d.MonthAbbrev
, cast(d.Year, varchar(4)) as 'Year'
, <measure>
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year = _year(_add_months(current_date, -1)) - 1
  and d.MonthNum = _month(_add_months(current_date, -1))

Query3
(difference between last month and same month a year ago)
select
  <dimensions>
, d.MonthAbbrev || cast(_year(_add_months(current_date, -1)) - 1, varchar(4)) || '(abs)' as 'MonthAbbrev'
, 'Diff ' || d.MonthAbbrev || cast(_year(_add_months(current_date, -1)), varchar(4)) || '/' as 'Year'
, abs(
    sum(
      case
        when d.Year = _year(_add_months(current_date, -1))
          then <measure>
      end
    ) -
    sum(
      case
        when d.Year = _year(_add_months(current_date, -1)) - 1
          then <measure>
      end
    )
  ) as 'MeasureName'
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year in (_year(_add_months(current_date, -1)) - 1, _year(_add_months(current_date, -1)))
  and d.MonthNum = _month(_add_months(current_date, -1))

...etc...

Query 9 is a UNION of all of the other queries and is the source query for the crosstab.

You'll probably need a ton of conditional formatting based on the column labels.

I'm also not sure how you'll handle mixing what I presume is 8 different measures into this.

Like I said -- Not simple.  You're basically using Cognos Analytics as an integrated development environment.

This topic has been moved to Reporting.

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

Hello, i'm having a problem concerning cognos 11.0.13.
One of my user has a report with a crosstab that contain a great amount of data and he noticed a problem starting at row 45001 (on excel) where data start going missing in a stair like way and come back in the same way after a few thousand rows.


To try and solve his problem, I increased the parameter RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE to 150000 which pushed the problem to row 650001.I Increased this parameter again to 300000 but the problem stayed at row 65001.

These numbers feel odd and i wonder if another parameter could be the cause.


Thank you in advance for your insight

Hi Fil,
I ran into the same situation. I have YearMonth in columns and product category in rows.

In order to filter the measure (just for this particular part of the crosstab, I enabled the option "define contents" and added:
TOTAL(
CASE [Year Num]
WHEN 2018
THEN [Sales Amount]
END for [Product Category])
In the "properties" property of the Product Category I checked the calculated measure above. What Cognos gave me as a result is the total Sales Amount for each category for the year 2018, which is fine for that. But it completely ignores the YearMonths in the columns and repeats the total 2018 numbers in each column.

I tried your solution with "mother query" and "child query" but it didn't work. Is it possible for you to explain your solution in a bit more detail? Or maybe attach your report specification here?

Thank you in advance.

Thanks for the reply.

The crosstab will have 8 rows total. Some of them measures, some are calculated based on the measures values.

Option 1 wouldn't be sufficient for me. I want the user to select the start and end month individually, also across years. So I need the amount of node members (months) to be flexible.

I don't entierly get your idea behind Option 2. Is it meant in the same way as Option 1? What do you mean by "pre-pivot the data" ?

Hey, so I have a few request here. I've recently inherited a cognos system that was being maintained by another user. Well that user has abruptly left the company and I need some help.
They were setting it up to go through an AWS ALB and for the most part it's working correctly. But with some of the reports they're getting an error 404 when they click the link.
We have Server1 and Server2. Server1 sends the URL link1.app.domain and it works fine. Server2 sends link2.app.domain and it fails. But if we take that URL and manually change it to link1.app.domain then the URL works.

If anyone has any way to fix this please let me know. I'd like to have Server2 send the correct URL.
Also, what are your recommendations for learning material on cognos?

Hi All:

I just have a couple questions about importing data into a report with in a package. I have an Excel and a text file that I want to import (they are not the best use cases for Cognos, but this is the best option for it) and modify a small portion of the data and reexport; I have run into the following problems:

• Can you import a multi-tabbed Excel report? The Excel file I have has 2 tabs
• For a text/CSV file, does the first row *always* have to be the header? I receive a file that does not contain headers, so I just wanted to ask.

Thank you

That doesn't sound simple at all.  And it's not necessarily a crosstab.

This will have 2 rows?  If not, more examples and information about how it gets summarized is critical.

Option 1:  Craft the query to compute the various "month" category values (column labels) and use a crosstab.  (so, you'll have 2 columns:  amount and quantity)
Option 2:  Pre-pivot the data into columns and compute values as needed.

Either way, it's not necessarily really difficult, just tedious.  But it's also very specific to your need.  There is no generic solution.

It looks like LAG() is the reason to write custom SQL.  But that's only for the Last_Salary_Change query.  The other three queries can all be built in the usual way and can be JOINed or UNIONed normally in Cognos. 

Date BETWEEN ?Start_Date? AND ?End_Date?

will work as a filter expression in Current_Manager, all built in the GUI.