Posts Tagged Report Studio
Sorting Section Headers in Report Studio
Posted by Scott Andrews in Cognos on November 4, 2012
In Report Studio, Section Headers add a format option that allows for your report to be broken into logical sections. However, you may find that no option exists to sort these items besides standard alphabetical order. Crosstab node members can have their sort order set to any data item in your query under the property Data > Sorting. Yet if you flip this crosstab node member into a section header, the sort option disappears. Worse yet, if you set the sort option on your crosstab node member and then attempt to turn it into a section header, you get the message:
Unable to create sections because no query item is associated with this object
So how can you sort your section headers? The best approach I found is to apply two section headers, and make one invisible:
- Add your sort criteria to your crosstab as a crosstab node member in the row section
- Add your title to your crosstab as a crosstab node member in the row section
- Select your sort criteria and press the section header button
- Select your title and press the section header button
You will now have two section headers, one showing your sort order and one showing your title. Because your sort order should be listed first, it should define the sort for your titles. Now we want to make the sort order section header disappear.
- Select the sort order section header and set property Box > Box Type to “None”
Now your title section header should appear alone, in your defined sort order.
How to Create a Single-Page Cognos Report
Posted by Scott Andrews in Cognos on July 26, 2012
If you have a Cognos Report Studio report with multiple data objects on it, you may find that these various objects get split up on multiple HTML pages. Cognos defaults to 20 rows per page, so multi data objects will be split up regardless of how large you set their rows per page properties. But there is one setting that keep all of your data objects together – it is the Run Option “Rows per Page”.
You can find this setting inside Report Studio under Run > Run Options > Rows per Page. If you set this value to zero, all of your disparate data objects will appear together on one HTML page when you run your report from Report Studio.
Unfortunately that setting will only work in development mode. As soon as you leave Report Studio, your report will resort back to the Cognos default of 20 rows per page. If you want a persistent setting in your report to show more than 20 rows per page, you need to change the run options on the report properties:
- In Cognos Connection, go to Report Properties
- Go to the Report Tab
- Set the checkbox “Override default settings”
- Click on ”Advanced options”
- Set the number of rows per page. The maximum setting here is 1000 rows per page. You cannot set to zero, no doubt because this has the potential of generating a massive amount of data in a single HTML page.
Tearing Down and Building Up Report Studio Parameters
Posted by Scott Andrews in Cognos on June 14, 2012
Sometimes in Report Studio, you may find it necessary to dice up your report parameter values with substring functions, especially if you are drilling from a cube source to a relational source. But be careful how you approach this. Report Studio can behave in unexpected ways.
Consider the following example:
You have a cube report that drills through to a relational report with a single parameter defined, called My_Parameter. You pass a single parameter value which is equal to DIM1-0001 in your cube source. Your relational source is expecting the value 0001. So you code the following filter in your relational source report:
[Code] = Substring(?My_Parameter?, 6, 4)
For a single parameter value filter, this will work.
Now you want to pass multiple parameter values, so you modify your filter to read:
[Code] in ( Substring(?My_Parameter?, 6, 4) )
This will appear to work, but really it doesn’t. If you pass only one parameter value it will work perfectly, but only because it will read the first parameter value and ignore all of the others. Attempting to dice up Report Studio parameters like this doesn’t fly when it is receiving an array of parameter values. Incidently, adding parameters to query calculations will behave the same way – the first parameter value is used, the rest ignored.
So how can you pass multiple parameter values, but still dice up your parameter values as needed?
You simply do the opposite. You build your code value to match your parameters, not deconstruct your parameters to match your code value.
The filter that works in this instance looks like this:
‘DIM1-’ || [Code] in (?My_Parameters?)
Cognos 10, Report Bursting and Saving Output to File
Posted by Peter Beck, CBIP in Business Intelligence, Cognos on August 7, 2011
(The instructions below present setting up C10 for output to a file location on the network within the context of bursting reports, but there is no reason you can’t set up file output for the normal manual or scheduled execution of reports – PB)
Cognos 10 (like all versions of Cognos BI since ReportNet) has a fairly straightforward way of configuring a given ReportNet report for “burst” output – that is, for generating a set of reports from a specific report specification, where the only difference between the reports is some selected value. Consider a generic sales report, where we have 2 different sales reps.
We might want to “burst” the report across the sales rep identifier, so we would get one report for each sales rep. We could then distribute each report to the appropriate rep.
Setting a report up for bursting is performed in the Report Studio interface. Under File… Burst Options we set how the report will burst. We also have the option of selecting how the report will be distributed – either as an email or as a Cognos directory entry. The value for the both the burst specification and the distribution must come from a query in the report.
However, it is quite possible that we might want the output to go out to a file location instead. To set this up requires a little bit of configuration, but it is quite straightforward. In versions of Cognos BI prior to 8.3 this was a bit limiting – we essentially had only one destination we could output to. In even older versions controlling the name of the output report was a pain as well – we needed secondary scripting to re-name the report in the output file based on an associated XML file. This is no longer necessary.
Note about the instructions below: this is not limited to burst output – setting up C10 for file system output can be useful for saving any report you run to the file system – a manually run report, a scheduled report, or burst report.
First, we need to create a shared folder on our server. This can be any name, but should not be located in the installation directory. The user under which the C10 service runs must have full rights to the folder. In this case I’ve created a folder called CognosOutput.
Now I must start Cognos Configuration, and navigate to Actions… Edit Global Configuration:
Under General, I enter the value of my \\server\share combination, prefixed with file://
Click the Test button, and then OK.
Returning back to the main configuration screen, select Data Access… Content Manager, and set the Save Report Outputs… value to True
You are now set up for report output. IBM notes that it is very important that you not be running your Cognos installation as “localhost”, but rather under the name of the server the service is running on.
These steps have set up the top-level directory under which we can save report output. Within Cognos Connection we must now define what the actual destination output locations within this folder will be.
Open up IBM Cognos Administration from the Launch menu in Cognos Connection. Then navigate to the Configuration tab and select Dispatchers and Services, and in the upper right side of the screen select Define File System Locations:
Give the new location a name under the Name section, and (optionally) a description and screen tip. Finally, give it a location – this is where it will appear under the output file folder you set up above. You can use the “\” character to nest a folder beneath another folder. You do not declare the top level folder, so in this case NewOutput could be used as a location, but not CognosOutput\NewOutput.
Now you are ready to burst the report to the file system! Select Run with Options for the report in Cognos Connection, and under Delivery method select Save the Report. Then click Advanced Options and on the the next page, select Save To the Filesystem, and select Edit the Options
In this case I have selected “New Output”, which I have set up to output to NewOutput/NewOutput1 on my file system. I have also renamed the report to August_Sales_Reports
Select OK, and select Burst The Reports from the radio button on the lower left side. Then click Run.
The reports will now be burst to the CognosOutput/NewOutput/NewOutput1 folder:
A couple of quirks: Cognos will append the language setting to the name of the report. It will also append the value by which the report was burst (useful for organizing the reports). It will also output a second XML file that describes the report.
Show me what didn’t happen…
Posted by Peter Beck, CBIP in Business Intelligence on September 8, 2010
In the Sherlock Holmes story “Silver Blaze”, Holmes has the following famous exchange with a policeman:
Policeman: “Is there any other point to which you would wish to draw my attention?”
Holmes: “To the curious incident of the dog in the night-time.”
Policeman: “The dog did nothing in the night-time.”
Holmes: “That was the curious incident.”
A common requirement in reporting is a report that shows not only what happened, but what *didn’t* happen. As an example, consider sales of several different products in a number of territories to a number of customers. A specification for a report may require it to show entries for sales to customers in a particular territory, for *all* products – even for products for which there were no sales.
To end users this is the most normal request in the world – as a sales manager, I want to see a line on a report that shows “0” for the products I didn’t sell.
From a reporting perspective however this can sometimes be a little tricky, especially in a non-OLAP, relational reporting environment. As an extreme example, I was once asked to create a report of just this type, in an organization that in theory had over 250,000 SKUs. Obviously the customer didn’t want a report that included the 1000 products we normally sold and “zero” entries for 240,000 SKUs, but it took a lot of back and forth to figure out just what the customer did expect to see.
Even a seemingly simple example can have unexpected difficulties however. Consider an example where we have 3 customers, each in a different Province, and each capable of buying 3 different products:
In Framework Manager our dimensions are joined to our fact table using Outer Joins. That way we should return columns in out reports for customers or products that had no sales, right?
Here is a snippet of a Report Studio report for sales for these products, from a query called Sales:
It looks like Cust01, Smith Company, bought nothing, and no-one bought any Oranges.
We have sales in Alberta (AB) for CUST02, but all they bought are Prod03 – Pears. We’ve been directed to produce a report filtered on AB that shows ‘0’ entries for the products that weren’t sold.
Since we’ve used outer joins in our model, we should be able to filter the query Sales on AB:
… except that we get this result:
The filter is applied to the result set returned from the server, and the entries for Apples, which were sold in QC, and the entries for Oranges, for which there were no sales (and so the ProvCd is null) are filtered out.
One fairly simple way to get around this is to create a local Product Dimension, and Outer Join that on the result set with a local join. This will attach a list of all products to the result set regardless of what filter is applied to it.
First we create a Product query:
Then we join the Product query to our original, filtered result set, joining on the common ProdNo <-> ProdNo field (which is in the original result set query called Sales.) We use an Outer Join here to ensure that ProductDescription is returned regardless of whether or not there is a corresponding sale of the product in Fact_Sales:
Now we can bring ProdNo and ProductDescription from the query Products into the new result query Products_And_Sales (When we do so they are automatically appended with the number “1” to distinguish them from ProdNo and ProductDescription in Sales):
Now we get a result that shows one entry – with null sales – for products for which there were no sales in Alberta – the ProdNo and ProductDescription will come from our Products query:
It is now a simple matter to turn Sales_Value into a calculated column in the Products_And_Sales query, putting in a “0” value in the event it is null, for example (in a cross-tab this can be done automatically in the cross-tab settings.)
This is a very simple example that can get much more complex – it may be useful to create “join columns” filled with a “1” value, for example, and join on those. Pay careful attention to your Facts – it is possible that for some dimension there is no activity at all.
Finally, another approach, not shown here, is to create an *unfiltered* copy of the original result set with “null values” for all the “fact” columns, and use a Union-join to join it to the original result set.
Cartesian joins and the Oracle WITH clause in Cognos 8.3
Posted by Peter Beck, CBIP in Cognos on June 7, 2010
Cartesian joins are joins that are the “product” of two sets. A popular join used in Report Studio is the UNION join. This will take the result of one set – the results of, for example, a SELECT statement that yields 5 columns – and “merge” it with the result of a second select statement that has the same number of columns of the same type. Each query might return 10 rows, but the result of the UNION join between them will result in 20 rows (Other Cartesian joins are the EXCEPT, which returns rows that are only in one of the source queries or the other, but not both, and the INTERSECTION, which returns elements that are in both source queries).
Cartesian joins are useful when you need to present several different results within the context of a single query – you can create 3 different queries, and then UNION, INTERSECT or EXCEPT them together and present them in a single List object. As long as they have the same number of columns this should work fine.
In the example below, Query5 is the result of an INTERESCT between Query3 and Query4 – it will select the elements that are common between them.
Cognos 8.3 doesn’t always behave as expected however, and a Report Studio report that that uses UNION joins may result in an unexpected error:
ORA-32035: unreferenced query name define in WITH clause
This can be maddening because the report may work correctly when rendered in HTML, but generate the error when the user requests a PDF.
The source of the error is an inappropriately formed query generated by Cognos, and it can be avoided by restricting the query that is the result of the Cartesian Join from using the WITH clause. The WITH clause allows the database server to perform calculations on sub-queries more efficiently. However, in this case Cognos may not manage the formation of the clause correctly, and using it should be avoided to avoid this error. This setting is found under the Properties of the query, in the setting “Use SQL With Clause”:
Setting this to “No” should allow your report to run correctly.
