Report Studio

How to Render Report Studio Report Multiple Outputs

by

I poke fun at myself quite a bit about how lazy I am and that’s why I am the master of productivity, automation and dynamic reporting. While I’m joking, there is a lot to be said for simplifying and consolidating tasks. In the current era, it’s actually a necessity. The corporate world that has evolved out of the 2008 economic downturn is a lot leaner and cost conscious. Most of us now work in positions that were once performed by two or more employees. So when it comes to performing mundane tasks like running the same report multiple times for different departments in the organization, you have to be asking yourself, “Is there a better way for me to be spending my time? Productivity is the name of the game right?”

This is a common thought among many financial planning and reporting departments who want to look at several department budgets at the same time. Or they may want to run the same report for various versions like actuals, budget and forecast. This week alone, I had two clients asking this exact question, and, yes, Cognos does provide a way to run a report once and have it produce the same report for multiple aspects of the business.

With the use of Page Sets and Master Detail Relationships, you can allow end users to select multiple items from a prompt and run each prompted item on its own page or its own tab in Excel.

Build your report and all the necessary queries. Then add 2 more queries, one for the Page and one for the Page Set. Name them something meaningful. I typically name them Page Query and Page Set Query.
Report Studio Report Multiple Outputs

Add the object(s) that you are going to prompt your report on into both the Page Query and the Page Set Query. You will be using them in the master detail relationships you are going to set up.

In the Page Explorer, click on the Report Page folder. Drag a Page Set object above the Page1. Make sure that Page1 (the actual report page) is nested below the Detail Pages folder.
Report Studio Report Multiple Outputs

Click on the Page Set and, in the Properties pane, set the query to the Page Set Query you created. Then in the Grouping and Sorting property, drag the data item(s) into the Groups folder.
Report Studio Report Multiple Outputs

Next, click on Page1 and set the query to the Page Query. Then click on the Master Details Relationships property and link the data items in the Page Query to the Page Set Query.

Create your prompt page. When you create your multiple, select prompt filter on the Page Query and Page Set Query, in addition to the report query,  with the parameter data item.  Then, in addition to the report query, both the Page Query and the Page Set query needs to be filtered by department prompt value.

The last thing is to create a Master Detail Relationship between the report container and the report page. Highlight your report container, whether it’s a list or crosstab, and ensure that the correct object is highlighted by looking at the ancestor description at the top of the Properties pane. Then set the Master Detail Relationship(s) between the report object and the page.
Report Studio Report Multiple Outputs

You can also take the automation one step further and set up agents in Event Studio or schedule views to automatically generate the report.

Report Studio Dynamic Report Writing – Layout Calculation

by

I have come to realize that most people don’t understand what it means when a Report Studio Dynamic Report is written. For the most part, reports created in a Cognos Studio are already dynamic, as long as the data sources are regularly updated.  This is because every time the report is open or run, it renders the most recent data from the data warehouse.

However, Report Studio has some really cool features you can incorporate into your reports without having to be a macro guru. One of my favorites is Dynamic Report Titles using a Layout Calculation. This will change the title of your report to match a prompt/filter.  For instance, let’s say you run the same report each month for each division of your company. Instead of manually changing the filter and title for each division; or worse, creating the same report 10 times, you can create a prompt and have the report’s title change with the prompted data.

This is a two part process. First, we need to create a parameter and prompt; and second, use a Layout Calculation item in the title.

To start, drag a prompt item from the toolbox either into the report or onto the prompt page of the report. If using relational data, try using a Value Prompt. This allows your users to select from a list. If working with dimensional data, use a Tree Prompt.  You will then be asked by the Prompt Wizard if you want to either create a new parameter or use an existing one. A parameter is just a fancy filter that works with your prompt.

For our example, we want to create a new parameter named pDivision. Connect to your package item and make sure in the operators box that you select “in”. This is sql speak for “I want to choose from all my options in this column”; aka, “I would like to be able to choose from all available divisions”.

Report Studio Dynamic Report Writing
Dynamic Writer

Once you have created your prompt, drag a Layout Calculation item from the toolbox to the title area of the report and select the parameter you just created from the Parameters tab in the Expression Editor. Now run your report and the report will prompt for which data you would like to filter on and your report will automatically change the title to match your filtered data.

For more information about Report Studio, contact us a Sales@LodestarSolutions.com.

3 Keys When Deploying IBM Cognos 10 BI

by

Maybe you invested in upgrading to IBM Cognos 10 BI but you are not realizing the benefits you thought you would experience. If so, you might have missed the fact that IBM Cognos 10 BI should change your approach to report design and deployment. It may even cause you to modify your Data Warehouse or Data Mart. Let’s talk about the how's and why's of deploying IBM Cognos 10 BI:
1. A SINGLE REPORT TO ANSWER MANY QUESTIONS – Historically, reports have been created to answer one specific business question. The reports are designed to answer that single question only, not to answer many questions at once. With IBM Cognos 10, reporting can be designed to answer many questions at once and empower the end user to interact with the data to answer all the questions they have. This allows the power users to create one report instead of many versions of a similar report. Always keep the end users experience in mind when designing reports and dashboards.
2. CREATING OBJECTS INSTEAD OF REPORTS – You might have a pie chart, a bar chart and a cross tab all being displayed in the same report. With IBM Cognos 10, Lodestar recommends you change your philosophy and build the three separate objects representing the pie chart, bar chart and cross tab, and create a dashboard with Business Insight to display the 3 components. This will allow the end users to interact by modifying the filter and even remove or change the objects in their workspace to better meet their needs. By creating objects, you empower the end users to “self-serve” their BI.
3. DON’T PUT THE LOGIC IN THE REPORT. Many BI developers build logic in Report Studio. Often, they use this as a patch to get the data to present the way they want it when the Framework or data source doesn’t have the necessary format. Although this has been effective over the years, it’s a bit of a duct tape and super glue approach, which could be exposed as you deploy C10 Business Insight. The idea of Business Insight is to empower the end users to “self-serve”, providing better information without burdening IT. If the Framework you provide to the end users is not clean and efficient, the end users will experience major issues, especially if they are not completely familiar with the data. To fully realize the benefits of Cognos 10 BI and its powerful Dashboarding capabilities, you may have to take a couple steps back and make sure your FM Packages and Data sources are optimized.

Lodestar Solutions can help you make the transition when deploying IBM Cognos 10 BI so you can maximize your ROI. We offer customized training for power users and end users. Additionally, we offer FM Package and Design reviews as well as consulting on all aspects of IBM Cognos.

The Difference Between Cognos Express and Cognos Enterprise

by

The Difference Between Cognos Express and Cognos Enterprise

IBM Cognos has two offerings for organizations looking for a complete Performance Management solution. One is IBM Cognos Enterprise Solutions and the other is Cognos Express. If you are looking for the power IBM offers, you may have some questions about what is the difference between Cognos Express and Cognos Enterprise.

So, we thought we would take a moment to walk through some of the differences. Note with each new release this information will change. IBM is expected to release a new Cognos Express version in the very near future and we will write again on Express once it’s released. As for now, IBM Cognos Express is positioned as the mid-market solution from IBM however, large companies can purchase it.

Cognos Express offers integrated modules for query, reporting, analysis, visualization, dashboarding and planning. Once installed, the product essentially creates a local data mart (via TM1) behind the scenes.

The biggest difference between Cognos Express and Cognos Enterprise is the number of users.

Express product supports a maximum of 100 users running a single server. Originally IBM had four modules in Cognos Express: Reporter, Advisor, Xcelerator, and Planner. Over the years, they simplified the licensing. In Express you can invest in Performance Management (TM1) or Business Intelligence, or both. Below is an older comparison to help you translate the old license to the Enterprise licenses. For the latest information on the differences between Cognos Express and Cognos Enterprise check out our most recent Cognos Express comparison blog.

Cognos Express LicensesClosest Comparable Cognos Enterprise Studios
ReporterReport Studio & Query Studio
AdvisorAnalysis Studio
XceleratorTM1 Modeler
PlannerTM1 Contributor
CompleteReport Studio, Query Studio, Analysis Studio, TM1 Modeler, TM1 Contributor

The biggest difference between Cognos Express and Cognos Enterprise is clearly the price. As of this posting, Cognos 10 Enterprise has a lot of new functionality that is not available in Cognos Express. Functionality like mobility, Business Insight, Active Reports and Metric Studio is missing. My recommendation is that you evaluate your business requirements and explore how both options meet your needs. Consider your expected growth. Make sure you will not out grow IBM Cognos Express too quickly and you will have to upgrade to Enterprise. Both offerings provide powerful Performance Management solutions, but check it out for yourself. Lodestar Solutions would be happy to answer any questions you may have on the differences between Cognos Express and Cognos Enterprise.

Link to Latest Version of Cognos BI
http://www-01.ibm.com/software/analytics/cognos/express/trial-demo.html

For the latest information, contact Lodestar Solutions at Services@lodestarsolutions.com.

Calculating Prior period in Cognos BI

by

Calculating Prior period in Cognos BI

Have you ever needed prior periods or last period within a report? For financial reporting, these calculations are a must and also a headache for report writers. This is the first issue you have to contend with when calculating last and prior is current. I know that line seems strange, but in order to calculate the prior period or the last period, you have to know that current period. Now, you can take the easy way out and ask the user to tell you the current period via a prompt, but what happens when the report has to be run on a schedule and you can’t rely upon a user?  See … you need to calculate the value.

Step one – Calculate the current date

Date prompting is pretty straight forward in IBM Cognos Report Studio and there are a bunch of functions available to manipulate a date field. If you want to get the current date, you will use the current date function. It looks weird when you create data items based on the current data function because it creates what looks like a hardcoded date. However, it is not hardcoded but will change each day the report is run.  More importantly, if you try to type in the exact same thing into the data item and expect the date to update when the report runs…get ready to stare at the screen, because it won’t work. I know it looks the same, but when you bring over the current data function, it does something in the background that you cannot replicate with a hardcoded date.

Step two – Get Year Part

Now that you have the current date, you may need to get the year portion of the date in order to calculate the fiscal year. This is really important when fiscal calendars do not match with the Gregorian calendar. If you have ever tried to extract the year part of a date within IBM Cognos, you know that it can’t really be done with a standard function. Before my dba’s get all worked up and correct me, let me just say that I am aware of a little trick … don’t steal my thunder I am getting there. What I was saying is that IBM Cognos functions for a Sql database does not have year as a function built in, however; you can still use it in an expression. To extract the year part out of a date field, use year(date value) and that will return the year part. If you need to add to the year, you can nest the add_year function as using a variation of this calculation add_year(-1,year(date value))

Putting it all together

Once you have the current date and can extract the various components of a date such as year, month, day; you can calculate last and prior very easily for your financial reporting. Of course, experienced dba’s will point out that we could build a flag within framework manager that would identify the current period, week, month, and year instead asking report writers to calculate it individually. In many ways, the dba is right and these values should be created in framework manager and available to report writers, but I am a realist and know what sometimes the right thing to do is not always the easiest or common thing done within a business.

For more information on this or other topics, email us at Sales@LodestarSolutions.com.

Bursting Cognos Reports in Report Studio

by

​Save time by Bursting Cognos Reports in Report Studio

Bursting Cognos Reports is one of the best functions within IBM Cognos Report Studio. If you are not familiar with bursting let me explain how it works. You have a report that is for your sales team. Each salesperson is responsible for a certain region and you want each person to only see their region’s sales. Normally, you would make a report for each sales person because we know how territorial sales people are about their commission. Report writers love bursting because they can use one report to serve multiple people with what looks be a custom report.

Here is how it works best. The salesperson name and email address should exist in a burst table within Framework Manager. Granted, you can make a burst query within Report Studio, but that would mean the burst query is unique to that report. Each time you make a report for sales people and you want to use bursting, you would have to make another query inside the next report. By putting the bursting items in Framework Manager, you can simply select the items and they are available to you whenever you use the Framework Manager Package. See what I am saying about how it will work best? I am aware there are many ways to do something, but I try to give you my best advice first!

Bursting Cognos Reports

Anyway, back to the bust function. You select the salesperson name and email address from the burst table and put them into a query. You then create your report using another query, which can be a list or a crosstab. After you have made your crosstab or list report, drag another list on to the page and set the query property to the bust query. Then drag the report crosstab into the list. This means the crosstab should be imbedded into the list, because we all know that you can’t burst a crosstab report so you need to make it a list report.

When you set the burst options within the report, make sure you are using the burst query. The crosstab and the burst query must have a relationship. This means that the salesperson name must somehow exist in the crosstab because you will need to join the two together so that the crosstab will be filtered properly.

After you have setup the report to burst, make sure you go into the properties of the report and check the burst option. This process will work also work if you want to burst multiple list on the same report.  For more information on bursting check out this link .

Did you like this blog on Bursting Cognos Reports, if so check out this blog on Using Cognos Lifecycle Manager to Validate Your Migration by clicking here.

IBM Cognos 10 BI – Worth the Hype?

by

Cognos 10 BIIBM has released Cognos 10 and there are some skeptics out there wondering if it is worth the hype. In my opinion, the release warrants a conversation because it is a major release. Note that they went from Cognos 8 right to Cognos 10 and there are major differences. Some of the additions and changes are great … really … so I will give you the quick and dirty. You can read the complete release information in the upgrade whitepaper, so I won’t bore you with too much tech talk.

The biggest change that they made was integrating dash boarding into IBM Cognos 10. In Cognos 8 when you wanted to do really flashy and pretty dashboards, you had to use Go Dashboard.  Now in Cognos 10, you have the Go Dashboard capabilities within Cognos 10. If you are like me, I like my dashboard to look good as well as tell me something.  You know that I am a fan of Go Dashboard, so I am happy to see that the functionality is now available within Cognos 10.

The second change, which is a change in philosophy, is that IBM introduced Business Insight and Business Insight Advance. For those of you that were fans of the studio structure, you can relax because Business Insight has the same functionality of Query Studio and Analysis Studio without having to navigate to different studios. What that means to you is that you can start with a report and if you want to know more, you do an analysis without having to go Query or Analysis Studio. If you are in Business Insight, you can open the report and do your analysis in the same place. I really like that they streamlined the studios and it really makes it easier to understand your data without having to struggle with knowing the “studio tango”.

The final change that I am excited about, is that Cognos 10 is now a real mobile device integration! Remember in Cognos 8 … you had Go Mobile and within Cognos 8 you could run prompted reports, but much like Go Dashboard, it was a separate application. I am happy that they have integrated mobile functionality into Cognos 10.

So, if I had to one line the changes from Cognos 8 to Cognos 10 it would be that IBM has streamlined Cognos and made it more fluid then Cognos 8. Now the next question on your mind is “Is it worth an upgrade?”. I have one answer for you and it is yes maybe no. You might have to read that line again because no one can tell your business you need to upgrade. You have to determine your business needs and match them with the latest software offering in order to come to the correct answer.

What You Should Know About IBM Cognos 10 Licensing

by

Last week, I attended the C10 BI partner training in Toronto and despite the chilly, snowy weather the product was hot!  Cognos 10 is like dessert to C8.  The base functionality for IBM Cognos 10 licensing is very similar to C8, so upgrades will be pretty painless; however Cognos added functionality that is SWEET.   My favorite was the Business Insight functionality.  Enhanced consumers and higher will be able to create their own workspace where they can analyze and interact with the information displayed through a dashboard unique to them.  For the semi-power user with a BI Advanced Business Author license or higher they will be able to use Business Insight Advance which starts to combine the functionality of C8 Query Studio, C8 Analysis Studio and Report Studio Express in to one.  The good news is that unlike Report Studio Express, Business Insight Advance can report off relational data.

I believe as clients start to understand the functionality and benefits of Business Insight, end users will leverage it to be their “Start of the Day” dashboard.  The items that they are most concerned about each morning will be displayed whether it is sales metrics, inventory data, or cash forecasting.  It will be displayed how they want to see it in drillable tables, charts, graphs… The user can select a prompt value to filter all the panes simultaneously to reflect your selection.

So what you need to know about C10 licensing is that if you are on C8 and current on support, you can migrate your licenses to C10 without cost; however if your end users are Consumers or Recipients they will not get rights to the new functionality like Business Insights.  They will be business as usual with C8 functionality.   If you want the consumers to get the sweet dessert offered by C10, the licenses will need to be upgraded to the Enhanced Consumer license and there is a cost associated with this upgrade.   Similarly, if you have a Business Author license which gives you Query Studio but want more robust functionality offered in the Business Insight Advanced, you would need to upgrade to the BI Advanced Business Author at a cost.

The IBM Cognos 10 licensing at first glance can be confusing. But Lodestar Solutions is here to help answer your questions.  The graph below should help clarify the functionality associated with the various licenses.

IBM Cognos 10 licensing functionality

 

For more information, call Lodestar Solutions 813-254-2040

 

Cognos Report Studio Nested If Statements – HELP!

by

​Cognos Report Studio Nested IF Statements

Have you ever tried to do Cognos Report Studio nested IF statements and created a mess? If you have, you might run screaming because you can’t get the syntax just right. Granted, you could read the tips at the bottom as you build the syntax but shoot yourself in the head if you are not a programmer. You probably could pickup Chinese faster.

Here is an example of a Cognos Report Studio nested IF statement I want to create:
I want the total of A if A is greater than B, however if C is greater than D then I want the Average of C. If neither condition is met then I want the count of A.

In this example I am using total, count, and average all as expressions and each requires its own syntax. First build each expression:

• Total (A)
• Average(C)
• Count (A)

Before you run off to build your nested calculations, you must remember that ELSE must go before each IF after the first IF in Cognos Report Studio. Now that you have the proper syntax for the expression part of the IF THEN ELSE you are ready to build your nested statement.

The Cognos Report Studio nested IF statement is as follows:
IF (A > B) THEN (Total(A))
ELSE IF(C>D) THEN (Average (C))
ELSE (Count(A))

Remember that every IF must have an ELSE match in order for the syntax to be correct in Cognos Report Studio. I always think of the ELSE as an period at the end of a sentence. When I have more to say I end my first statement and start talking (insert an IF) and end that statement with a period (ELSE).

I hope this has helped you become an expert at Cognos Report Studio nested IF statements. If you liked this tip or want to share others, please add a comment. For more tips join the Lodestar Solutions community on our website at www.lodestarsolutions.com. If you need additional consulting or training on IBM Cognos BI please contact us at services@lodestarsolutions.com.

Cognos vs Business Objects…What’s The Difference?

by

I have been asked many times “Cognos vs Business Objects…What is the real difference between them?”  First, let me say that this is my opinion and not a recommendation for either product. I know that might sound strange because you are reading a blog written by a Cognos partner. Well I am going to give you my perspective based on my experience as a customer, which I think you would want instead of a sales pitch.

If you are in a position to evaluate both Cognos and Business Objects, you should let your stakeholders guide your focus. I hear the project managers getting all worked up saying that you need a standard process for such evaluations and it shouldn’t be done in a vacuum. I agree, so calm down PM’s and keep your shirts on … there will be work for you I promise! If your main stakeholders are the business user community and the expectation of the project is to get a tool that will facilitate business user report creation, then I am writing for you.

Let me just say that when you start to talk about the difference between Cognos and Business Objects, don’t even think about starting at the architecture.  I am telling you your audience will look at you like you have two heads and neither one of them speak English.  That is not to say that you should not consider and evaluate architecture, but that is another conversation for a difference audience.

Most users’ software skills are about intermediate or basic and I am using Excel as the guidepost. I have rarely come across an expert Excel business user. I mean one that can write a macro, complex calculations, and leverage advance functions such as, but not limited to, V and H lookups. You know the user that can figure anything out on their own, but have limited tools. Oh my bad if such a business user exists they have been recruited to IT or so overburden with helping everyone else in the business they quit for lack of support, money, or recognition. But I digress. So consider that most of your users are intermediate or basic and you ask them to create a report in Cognos and Business Objects.

In Business Objects the user would naturally go to Web Intelligence or Webi for short. If the report is more sophisticated then basic analysis they might be stuck which would require Crystal. Let me tell you that Crystal is a tech tool not for even the smartest or savviest business user. It requires substantial SQL skills to use well.  I see you Business Object loyalist turning red and I just have one question for you….what department do you work in? Yeah I thought so….IT…. go ahead take your seat. I am not saying anything bad about Crystal it is a killer report writer but not for business users.

If a user wanted to write the same report in Cognos, they could start in Query Studio which is drop and drag and then open that Query View in Report Studio for more features and options. I hear you Business Objects loyalist saying you can do the same thing in Webi and that is partly true. Business Objects’ Webi tool is nice and you can drop and drag, but the untruth is that you can’t open that report in Crystal or Desktop Intelligence (Deski). Are you guys yelling again saying that you can make any report in Webi that you can make in Crystal? Alright…make me a bill of lading in Webi. Make me a report that is not analyzing information, but just presenting data in a prescribed way….I’ll wait.  I just have one word for you Business Object loyalist….Crystal. It is still the cornerstone of Business Objects product offering. That is because it is designed to do the more sophisticated report writing. The drawback is that is does not interoperate with the other Business Object tools. Listen B.O. guys (no pun intended) I am just saying that you guys have some work to do around creating a complete solutions instead of several tool boxes with really nice tools that are sold from the same store. If you guys are honest with yourselves you’ll admit that IBM beat you to the punch by offering a complete Corporate Performance Management tool.

Sorry about the side track, but I always have to address the Business Object loyalist readers because they have validate points about their software and I don’t want to seem like I am not giving them their dues.

Ok let’s nutshell this thing. I believe the difference between Cognos and Business Objects is that Cognos is a complete and integrated tool where everything talks to each other, listens, and responds. Don’t just take my word… pull out your evaluation process and really understand your stakeholder’s goals and needs. PM’s in the room, here is where you earn your money….well documented and vetted requirements will ensure that your evaluation process will help you select the tool that best fits your organization.

X