Author Topic: Add Connection Name to Report Header  (Read 142 times)

Offline Cape Cod Gunny

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 25
  • Forum Citizenship: +0/-0
    • How to Get Out of Debt and Achieve Financial Success One Debt at a Time
Add Connection Name to Report Header
« on: 02 Dec 2019 12:59:39 pm »
I have several End of Month database snapshots associated with the same Package. When a user runs a report it requires them to choose which Connection Name to use. I would like to include the "Connection Name" in the report header.

I've looked at all the "Layout Calculation" Report Expression Functions and did not see how to include the "Connection Name".

How do you include the "Connection Name" in a report header?
Michael Riley
Marine Corps Gunnery Sergeant (Retired)

"We may never pass this way again!"

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,852
  • Forum Citizenship: +265/-1
    • Paul's Cognos Blog
Re: Add Connection Name to Report Header
« Reply #1 on: 02 Dec 2019 10:41:37 pm »
This one was tricky but I got it. The data source connection prompt creates a parameter named "credential:DATASOURCENAMEGOESHERE". In order to work with it, you'll need to know the datasource name.

In my test I took the great_outdoor_sales data source and added a new connection. When I selected the connection in the report, it created the parameter "credential:great_outdoors_sales".

Now here's the interesting thing. If you try to call that in a prompt, it won't work. It creates a text prompt but never actually submits. You can call it in a report expression with ParamDisplayValue. The value returned looks like this:

Code: [Select]
<credential><dataSourceConnection name="great_outdoors_sales1">CAMID(&quot;:&quot;)/dataSource[@name=&apos;great_outdoors_sales&apos;]/dataSourceConnection[@name=&apos;great_outdoors_sales1&apos;]</dataSourceConnection></credential>
The name always starts at position 41, and ends at the first instance of '">'.
Wrap that in a substring and position function, and you can get exactly what you need.
Code: [Select]
substring(ParamDisplayValue('credential:great_outdoors_sales'),41,
position('">',ParamDisplayValue('credential:great_outdoors_sales'))-41
)

Offline Cape Cod Gunny

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 25
  • Forum Citizenship: +0/-0
    • How to Get Out of Debt and Achieve Financial Success One Debt at a Time
Re: Add Connection Name to Report Header
« Reply #2 on: 04 Dec 2019 02:27:35 pm »
@CognosPaul Thank you very much!  ;D
This was an awesome starting point for me. My system produced slightly different results. I was able to adapt your code perfectly to work on my system.
+∞
Michael Riley
Marine Corps Gunnery Sergeant (Retired)

"We may never pass this way again!"

 


       
Twittear