Recent Posts

Pages: [1] 2 3 ... 10
1
Report Studio / How native SQL prepared in Cognos report studio
« Last post by ajskumar on Today at 12:52:18 am »
How the native SQL prepared in cognos report studio

Using 32 bit ODBC driver to connect to database (Jethro)

1. ODBC drivers provided by Database owner
2. IBM query engine (UDA)
3. Any other methodology

Please confirm.
2
Security / Due to Security - Report Run Time is increased
« Last post by Kiran Kandavalli on 23 Aug 2017 11:51:13 pm »
Team,

I have Two Dimensions and a Fact Tables which are driving my Reports. I need to apply security based on AD Groups created.

We have a Lookup Security Table which is 'Security Table' in the below code

In the Framework Model --> Database Layer --> Using Native SQL using a query we are applying the Filter Conditions on the Fact Table as below. The Report use to Run for 10 Secs and after implementing the Security in FM the Report is Running for 1 minute.

Please let me know if I need to change the below SQL, to improve the performance.

Code: [Select]
select f.* from FactTable f
join DIMTable1 p on p.PCCode = f.PCCode
where exists (
    select distinct 1 from SecurityTable s
        join DIMTable2 c on c.CCode = s.N_KEY
    where s.line_of_biz = 'MF' and s.active_ind = 'Y'
    and (
        case
            when s.ldap_DSC = 'GROUP_NAME' and s.ldap_common_name in ( #CSVIdentityNameList()# ) then f.CCode = s.N_KEY
            when s.ldap_DSC = 'USER_NAME' and s.ldap_common_name = #sq($account.personalInfo.userName)# then f.CCode = s.N_KEY
        end
    )
)
union
select f.* from FactTable f
where exists (
    select distinct 1 from SecurityTable s
        join DIMTable1 p on p.PCCode = s.N_KEY
    where s.line_of_biz = 'MF' and s.active_ind = 'Y'
    and (
        case
            when s.ldap_DSC = 'GROUP_NAME' and s.ldap_common_name in ( #CSVIdentityNameList()# ) then f.PCCode = s.N_KEY
            when s.ldap_DSC = 'USER_NAME' and s.ldap_common_name = #sq($account.personalInfo.userName)# then f.CCode = s.N_KEY
        end
    )
)

Thanks!
Kiran
3
Framework Manager / Re: Merge query subject versus create model query subject.
« Last post by Penny on 23 Aug 2017 02:49:04 pm »
Answer:

I see now that the individual query items show the namespace that the item is derived from (the layer name).  Don't know how I missed that.  Guess I had a good vacation.
4
Framework Manager / Merge query subject versus create model query subject.
« Last post by Penny on 23 Aug 2017 02:35:09 pm »
Hi everyone
This may be a silly question, but I just want to confirm my understanding.  I am modelling against a relational database using Cognos Framework Manager 11.0.6.  The model has four layers:
   database layer - database query subjects from data source, all are select * from tablename
   consolidation layer - model query subjects created from database layer - all joins are here
   business layer - model query subjects created from consolidation layer - no joins are here, but there is some merging, calculations, renaming etc.
   presentation layer - all shortcuts to the business layer

1.  The database layer has database query subjects created with the metadata wizard using the data source (an Oracle database) as the metadata source.  These query subjects show up with the little database icon.

2.  In the consolidation layer, we have created model query subjects and used the database query subject as the source.  These query subjects show up with no database icon.

3.  In the business layer, we have created model query subjects and used the model query subject from the consolidation layer.

My questions:

If we 'merge in new query subject' from the database layer, creating a new query subject and answer no to the question 'Do you want to recreate these relationships with the new query subject?', the new query subject is sitting in the database layer, has no database icon, is named TABLENAME_TABLENAME.  If I move this to the next layer (business layer) by dragging and dropping, is this the same as result as step 2 above?

How can I tell afterwards what model source a query subject used ie. which layer.  The sql for both look the same, the icons are the same and I don't see a property that indicates the answer.

Thanks in advance for any comments/advice you may be willing to share with me.
5
Report Studio / Re: crosstab space height
« Last post by Michael75 on 23 Aug 2017 12:55:00 pm »
Hi,

A kind moderator has moved further discussion in thus topic to here, if you're interested: http://www.cognoise.com/index.php/topic,33483.0.html
6
Report Studio / Re: Crosstab space height
« Last post by Michael75 on 23 Aug 2017 12:51:58 pm »
Hi New Guy,

Thanks very much for your suggestions. I tried (I think) every permutation of setting Top & Bottom padding to 0px :
- text item in table cell
  - table cell
- text item in crosstab space
  - crosstab space (+ associated columns as my crosstab space is in a row)
- block in table cell
  - block

and there's no difference in HTML output.

After those tests, I set every possible height setting to 2px instead of 10px in the text item in the text item in crosstab space + associated columns. Result: the row height is possibly slightly lower, but only under a microscope ...

Two remarks:
1. Since I posted this, I've realised that the report will mostly be consumed in Excel, so the row height in HTML is probably not an issue for the users
2. I followed an IBM Cognos Advanced RS course a few years ago, hoping that this sort of stuff would be covered, but it wasn't. So I guess that people such as yourself who master this clever formatting stuff have all learnt it for yourselves, individually, by trial & error.

Unless there are some resources somewhere out there that somebody would be kind enough to point me to . . .

Thx
Michael
7
Report Studio / Re: Static Choice "ALL"-Element in Single-Value Prompt
« Last post by SimonWM on 23 Aug 2017 09:14:00 am »
Hi Chris,

thanks for the effort. Both, prompt and filter, were optional. The problem was, that "All"  (A, B, C, D) has more Elements than the subset-of-interest (A, B, C).

But I came up with a (not elegant but working) workaround.

thanks anyway!
8
Report Studio / Prompt query not using Distinct clause
« Last post by ajskumar on 23 Aug 2017 08:51:35 am »
Have created a value prompt to show the distinct values from one of fact table. Have created query (using package source) from Jethro Database.

When I try to see the Generated SQL option in report studio, the distinct clause not included in native SQL. However its added in Cognos SQL.

Only the native SQL sent to database, so its failing to fetch the data.


9
I use them for drill through.
10
Active Reports / Re: Send to mobile device
« Last post by donge900 on 23 Aug 2017 07:31:16 am »
same issue here and the following link was the solution for us:
http://www-01.ibm.com/support/docview.wss?uid=swg21617195

Reports send to mobile devices must have the same language as the receiver of the report....

nvm: the solution was already on page 2...
Pages: [1] 2 3 ... 10

       
Twittear