Creating a Environment Variable

[jaymoore1756]

Can a Cognos Environment Variable be used to identify the database against which the report is being run so that some kind of information (such as "test", a watermark, or exclusion of signature) can be properly placed for identifying non-production information

[RichardP]

Hi...

I'll explain what we do and you can determine if you want some additional info. 

We use Oracle as our DB.
In our FM Model - we create a query against the DUAL table and create a bunch of columns that returns values for various Oracle functions.

We retrieve Oracle Meta Data via sys_context( namespace, parameter, [ length ] ) function

This is  sample SQL statement you could use for a Query in FM.

Select
sys_context('userenv','current_schema')  AS "current_schema",
  sys_context('userenv','current_user')   AS "current_user",
  sys_context('userenv','current_userid')   AS "current_userid",
  sys_context('userenv','db_domain')    AS "db_domain",
  sys_context('userenv','db_name')      AS "db_name",
  sys_context('userenv','instance_name')  AS "instance_name",
  sys_context('userenv','server_host')   AS "server_host",
  sys_context('userenv','service_name')    AS "service_name"
from DUAL

db_name and instance_name in our environments return the DB we are connected to
Ex
BIDEV
BITEST
BIUAT
BIPROD

As this is a query in your FM model...you could create a query is your report (or template) that would possibly display
the result of db_name in a singleton query in say... the FOOTER of the report?

is this sort of what you might be looking for?

[bdbits]

I really like that idea, RichardP.

And welcome to Cognoise.  ;D

[jaymoore1756]

Thanks ... this is a great idea. I will create and test it on our box... thanks and yes welcome to Cognoise !!!!

[RichardP]

Thanks for the welcome...

[RichardP]

But I should confess - the approach of creating a query that returns a series of DB function values is not my own.
I'm sharing a fellow colleagues' implementation that has become a standard  in our FM models.

Cheers.

[jaymoore1756]

I have this working in an Oracle environment that I am working with, now I want to apply the same logic to a transact MSQL environment. Have you seen type of Query used there ?

[RichardP]

Hi...

Sorry just familiar with Oracle.
But did a quick google lookup.

Would this work for SQL Server by creating a SQL Query in your FM model similar to this... ?

select
db_name() as [Current Database]

I tried a few system functions
original_db_name()
schema_name()
host_name()

but it looks like db_name is the one that is the best candidate to return the Environment DB Name.

I saw another post that suggested that if you wanted to use DUAL in SQL server you could fake it out by simply creating the table

CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

[bdbits]

You can get a list of all the SQL Server metadata functions here.
https://docs.microsoft.com/en-us/sql/t-sql/functions/metadata-functions-transact-sql

You don't really need DUAL in SQL Server. For instance, "select db_name()" will work fine, no from clause is needed.

[jaymoore1756]

Agreed  "select db_name()" works fine. Thanks for the help everyone.