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

Recent Posts

Pages: [1] 2 3 ... 10
1
Administration and Security / Re: How do I restore a deleted report
« Last post by dmusson17 on 01 Feb 2023 10:16:13 pm »
Thanks MF. 

I first tried restoring a backup of the content store db and pulling the xml from the spec column of CMOBJPROPS7 (got the CMID by looking it up in CMOBJNAMES).  However when I tried to create a new report from that xml I got the following error:

This document does not contain a valid report specification.

Reason:
XML Parsing Error: not well-formed

URL:
https://ourcognossitecom/ibmcognos/bi/pat/rsapp.htm

Line:
9038

Character:
8


I didn't spend too much time trying to figure that out.  The xml looked complete and this is a pretty gnarly report so I moved on to plan b, which was connecting our Cognos install to the restored content store backup, logging in, extracting the spec/xml from the report that got deleted, connected Cognos back to the live content store db, and used that spec/xml to recreate the report with no issues.

I don't why the spec pulled from the db table didn't work, but luckily we are a small enough shop that plan b worked fine.
2
Reporting / Re: Bar Chart column order
« Last post by dougp on 01 Feb 2023 06:11:25 pm »
Define "bar chart".  Different visualizations from the different libraries...

11.1 visualizations
11.0 visualizations
Legacy visualizations
Charts

...behave differently.
3
Reporting / Bar Chart column order
« Last post by Quigwam2058 on 01 Feb 2023 10:47:29 am »
I have a bar chart in my report that shows data that has been grouped via a case statement that groups years of service

case when [Personnel Info].[Personnel Detail].[Years in District] <= 5 then '5 years or less'
when  [Personnel Info].[Personnel Detail].[Years in District] between 6 and 10 then '6 to 10 Years'
when  [Personnel Info].[Personnel Detail].[Years in District] between 16 and 20 then '16 to 20 Years'
when  [Personnel Info].[Personnel Detail].[Years in District] between 21 and 25 then '21 to 25 Years'
when  [Personnel Info].[Personnel Detail].[Years in District] between 26 and 30 then '26 to 30 Years'
when  [Personnel Info].[Personnel Detail].[Years in District] > 30 then '31 years and up'
end


I want my bar chart to show the data in chronological order.  Is there a way to sort the chart based on the Years in District field to get this to be chronological, but only show my groups as defined in the case statement?

I need to use the data in my package and not in a dashboard where a data set would be used.
4
Reporting / Re: Sort Order using Case Statement
« Last post by MFGF on 01 Feb 2023 10:44:40 am »
I have created a query calculation that is a case statement that is used to group account numbers for a list report.  I also want to sort those groups in a custom order, so I also have a case statement that used to list the groups according to the order I want them to appear:
case
when [Unit Group] = 'Enhancement' then 'a'
when [Unit Group] = 'Business Office' then 'b'
when [Unit Group] = 'Human Resources' then 'c'
when [Unit Group] = 'Maintenance & Operations' then 'd'
when [Unit Group] = 'M&O Split' then 'e'
when [Unit Group] = 'Public Information' then 'f'
when [Unit Group] = 'Board of Education' then 'g'
when [Unit Group] = 'Superintendent' then 'h'
when [Unit Group] = 'GSRP' then 'i'
when [Unit Group] = 'SW MiTech' then 'j'
when [Unit Group] = 'S4S/GSC' then 'k'
when [Unit Group] = 'Print Center' then 'l'
when [Unit Group] = 'RTSI' then 'm'
when [Unit Group] = 'EFE' then 'n'
when [Unit Group] = 'EFA' then 'o'
when [Unit Group] = 'ISCS' then 'p'
when [Unit Group] = 'Head Start' then 'q'
when [Unit Group] = 'YOU' then 'r'

when [Unit Group] = 'Fund 22 Operations & Maintenance' then 's'
when [Unit Group]  = 'Fund 22 Payout for Local Programs' then 't'
when [Unit Group] = 'Fund 22 MPSRS UAAL' then 'u'
when [Unit Group] = 'Fund 22 Other' then 'v'
when [Unit Group] = 'Fund 22 YAP' then 'w'
when [Unit Group] = 'Fund 22 Juvenile Home' then 'x'
when [Unit Group] = 'Fund 22 PET' then 'y'
when [Unit Group] = 'Fund 22 WELC' then 'z'
when [Unit Group] = 'Fund 22 Valley Center' then 'z1'
when [Unit Group] = 'Fund 22 Deaf/HI' then 'z2'
when [Unit Group] = 'Fund 22 Other Grants' then 'z3'
when [Unit Group] = 'Fund 22 Cent Svcs' then 'z4'
when [Unit Group] = 'Fund 22 Early On/ECSE' then 'z5'
when [Unit Group] = 'Fund 26 Maint. & Operations' then 'z6'
when [Unit Group] = 'Fund 26 Career Connect' then 'z7'
when [Unit Group] = 'Fund 26 CTD' then 'z8'

Else 'Other'
end

The above expression works when I run the preview the query with just the items in red or just the items in green, but not if I try to run it all together. 
I am receiving the following error:  I have tried running this with one additional line at a time, but continue get an error. 

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-239'.
UDA-SQL-0460 A general exception has occurred during local processing.
There is an ExpEng message system failure at eemisc.cpp:191
RSV-SRV-0042 Trace back:
RSReportService.cpp(734): QFException: CCL_CAUGHT: RSReportService::pro

Hi,

I'm wondering if the expression is too long for the database connection you are using? I see a UDA error in there, which means you are using Compatible Query - is it a connection via native client or are you using an ODBC connection?

One thing you might try is using a simple case construct rather than the search case construct you are using currently, eg instead of

Code: [Select]
case
when [Unit Group] = 'Enhancement' then '01'
when [Unit Group] = 'Business Office' then '02'
...
else '99'
end

try
Code: [Select]
case [Unit Group]
when 'Enhancement' then '01'
when 'Business Office' then '02'
...
else '99'
end

It might shorten the expression enough to work?

Cheers!

MF.
5
Reporting / Re: Sort Order using Case Statement
« Last post by Quigwam2058 on 01 Feb 2023 10:35:04 am »
This is partially correct.  I ended up having to break out this sort order field into a new query and join it.  Since the Unit Group was also defined in the same query, cognos couldn't interpret the unit group case statement at the same time as the sort order case statement - if that makes sense.
6
Data Modules / Re: Change data module source - issues
« Last post by bus_pass_man on 31 Jan 2023 10:24:47 am »
I think you lucked out getting anything to work.   

I don't think I've read anywhere that IBM says it could magically figure out how to map tables of different names when it relinks. 

In FM you can remap model query subjects to use tables or columns of completely different names but that was a manual process.  I have not seen that functionality in data modules.

You will need to make sure the tables and columns of both schemas are the same.  My question is why are you trying to relink to a schema with different metadata?

I relinked a schema to something which has some, but not all, tables in common with another schema and I got validation errors for the tables which do not exist in the relinked schema and for columns which do not exist in tables which do exist.
7
Data Modules / Change data module source - issues
« Last post by K1245 on 31 Jan 2023 07:40:10 am »
Hi,

I'm trying to relink the existing data module to a new source(Cognos 11.1). I am able to do this using "Relink" option and get no Validation error but Cognos does not use the correct tables for the whole module. For a few tables, he connects to random tables and I can't figure out why.


Example_1:
The table name in the old data source: user . The table name in the new data source: t_user .  Cognos replaces tables correctly.
Example_2
The table name in the old data source: work. The table name in the new data source: t_work - Cognos does not link these two tables. It uses some random table t_customer which has similar columns.


Is there a way where I can manually fix this? For example, choose the existing table in the data module and point it to the right table.
8
Administration and Security / Re: How do I restore a deleted report
« Last post by MFGF on 30 Jan 2023 03:15:31 pm »
Hello,

We are running single dispatcher environment, 11.2.3 on-prem with Content db on an on-prem SQL Server.  It's Monday and we need to retrieve a report definition that was deleted on Friday.  Our ops team is in the process of restoring a snapshot of the dispatch server from before Friday that I can log into and download the XML report definition from, which I can them upload to the prod server.  My question is, is the report xml stored in a retrievable format the Content Store db (table CMOBJPROPS7)?  If yes, is an alternative method of restoring the deleted report to restore a backup of the content store db and pull the XML from there?

Thanks for any insight/input/pros/cons,
Dave

Hi,

The report spec will be held in the content store, but the problem you will have if you just restore the content store is you won't know which of the many report specs in there are the specific report you are looking for. You could spin up a temporary install of Cognos Analytics and point it to the restored content store, possibly, but would that take longer than just restoring the entire server?

Cheers!

MF.
9
Administration and Security / How do I restore a deleted report
« Last post by dmusson17 on 30 Jan 2023 11:07:53 am »
Hello,

We are running single dispatcher environment, 11.2.3 on-prem with Content db on an on-prem SQL Server.  It's Monday and we need to retrieve a report definition that was deleted on Friday.  Our ops team is in the process of restoring a snapshot of the dispatch server from before Friday that I can log into and download the XML report definition from, which I can them upload to the prod server.  My question is, is the report xml stored in a retrievable format the Content Store db (table CMOBJPROPS7)?  If yes, is an alternative method of restoring the deleted report to restore a backup of the content store db and pull the XML from there?

Thanks for any insight/input/pros/cons,
Dave
10
I have modified a FM model and published a new package in our DEV environment.
Subsequently, I have copied a report from Production (which runs without issue) down to the DEV environment.

As a run a test of the report in DEV, it is failing with a "UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function" error message.
In isolating the error, I can see that two queries are joined in the report and the resulting query has a substr calculation (but, this is the same in Production).
The only difference (that I am aware of) is the newly published package in DEV.
Due to the package change the driving queries of the join have different SQL's compared to Production (I would expect this).
I cannot get the SQL of the failing query in the report as it is failing!

I have set the Data Source in the package to allow Oracle functions so I am not sure what else may have caused this.
Is there something at a Package or Environment level that could be causing this?
Ideally, I do not want to change the Oracle functions to be native Cognos functions as there may be significant other report changes if this is caused by the new package.

Any thought would be greatly appreciated.
Thanks in advance,
Adam.

Hi,

As I see it, you have two choices here. Firstly, you can try to find what change in your FM model has caused this to happen. Typically you see this kind of issue where the query processing isn't being passed to the database. Substr() isn't a Cognos function, so the only way it will run is if it's passed to the database. Secondly, you could try replacing the Substr() function with a Substring() function, which is an inbuilt Cognos function. I'd generally recommend you use the inbuilt functions wherever possible to give yourself the best chance of avoiding issues like this one.

Cheers!

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