find better solutions for analytics world
To pass a prompt value say – “p_reportdate” to sql query then syntax is as below
select * from table1 where table1.date = #prompt(‘p_reportdate’)#
if the prompt- “p_name” is multi select then
select * from table2 where table2.name in (#promptmany(‘p_name’)# )
few other related articles
http://www-01.ibm.com/support/docview.wss?uid=swg21341148
When we use page set, report output in excel will have tab names as page1, pag1_2, page1_3, so on..
If you want to display this tab names by report section names then follow below configuration methods.
In cognos configuration settings at Report service for each dispatcher add below advanced settings
Parameter column, type- RSVP.EXCEL.PAGEGROUP_WSNAME_ITEMVALUE
Value column, type- TRUE
In report studio go to: File > Report Properties > Page break by data container for interactive HTML : Yes
This option would make the html output in one page with out “page down”.
If this doesn’t work make sure you put the Lists/Crosstabs in table cells inside Block.
We can configure IIS using below video.
to download ARR –
https://www.iis.net/downloads/microsoft/application-request-routing
The announcement states that, On 2018-04-30 IBM will reach End of support for Cognos BI 10.2.x. The link to the announcement can be found here Link at the IBM site.
So if you are one of those shops running Cognos BI 10.2.x, It’s time to start thinking and planning your migration to Cognos Analytics.
Issue:
When a new column is added at database table, FM model query subject doesn’t reflect new column though SQL used – “select * from table”. Validating object doesnt work either.
Solution:
select the required query subject . At toolbar choose – “Tools” > “Update Object”. Framework Manager will start verifying the object with the database. As a result the new column will be added to the Query Object.
to change values of default date prompt dates
below excel sheet gives you the java script for below scenarios
Youtube video –
Determinants define functional dependencies between query items. Use determinants to avoid double-counting in denormalized data
Multi grain means having multiple granularities at FM i.e. we use determinants for this.
Example:
When we want to join 2 tables with different granularity we use determinants.
Determinants are required to avoid double counting of fact data when dimensions connect to fact tables at levels of granularity that have repeating keys. A simple example that illustrates this scenario is that of a time dimension that has months and days. The month will repeat for each day in the month. For example, May 1, May 2, May 3, et cetera. You wouldn’t want to aggregate the values of the month for each day in the month.
The second scenario is one where a query subject behaves as a dimension and has multiple levels of granularity and will be joined to fact data on different sets of keys. For example, the time dimension has multiple levels. It is joined to inventory on the month key because inventory is tracked by month. It is also joined to the sales fact on the day key because sales are tracked by day.
Another less common scenario includes the use of BLOB data types in a query subject. Query and BLOB data type values in a query subject require additional key and indexed information. If this information is not present in the data source, you can add it via determinants.
http://cognosteacher.blogspot.com/2012/01/determinants-explained-with-example.html
Sometimes you may need to pass the current date time value to the report.
This can be achieved by using following option
select “Date & Time Prompt” –> General –> Clock Mode – default value is “Static”, change this value to “Live”.
Now when you run the prompt page you will see current date time.
Data Engineering and BI BLOG 