Cognos Go Office: PowerPoint Integration w/ Cognos

PowerPoint Integration w/ CognosSo, it’s 10pm on a Thursday night and you’re still in the office preparing for tomorrow’s early morning board presentation. You have transformed from a business analyst to a technology multi-tasking robot. You have three reports open on one part of your screen, tomorrow’s PowerPoint presentation open on another part of the screen and Excel in the background to double check your figures. So, you dive into the task at hand.

Your Mission: Complete the company presentation and merge data from 5 different places. And here’s the kicker, the numbers have to be CORRECT.

So, you put on your thinking cap, you fit more windows in your tiny computer screen than you can really read, and you begin the task you dread every month. You first make sure you have all the data that you need to compile. Check. Then you open last month’s presentation and start removing all the reports, graphs and data from last month. Check. You open your Excel spreadsheet that checks your numbers to make sure they are correct. Check. You are armed with the tools and ready to go to battle because that is really what this whole process is.

Your organization owns IBM Cognos and you know it is a tool that creates reports but you haven’t had the time to know much more than that. You have heard that you could make this process much simpler by creating reports to do what you need but who has time for that?! You know that the data comes from four different places and the thought of merging that in a reporting tool is a bit daunting. So, you continue to work since you have a proven method and process that works regardless of how painful it is. But is it really that painful? Would it take more time to create the reports than you spend compiling data each month for the same presentation?? ABSOLUTELY NOT!!!!

IBM Cognos Go Office – you probably own it and aren’t using it. Go Office was introduced with a few other extended BI capabilities in 2006. Go Office is included with a BI Consumer license or above (original contracts need to be reviewed to confirm). Therefore every admin, every report author, every professional author may also own Go Office. IBM Cognos 8 Go Office eliminates the need to cut and paste or manually load data into spreadsheets or presentations. This saves time, prevents errors, and lets you focus on making decisions rather than data entry. In other words, first you create the reports you need to be included in the presentation in the IBM Cognos BI portal. Then, you can embed that same report in Microsoft Word, Excel and PowerPoint. And the crowd roars!!!!

PowerPoint Integration w/ CognosThis would be your life using IBM Cognos Go! Office for your monthly company presentation.

Thursday, the day before your presentation:
2:00pm Open BI Portal, perform a quick check that the numbers are accurate
2:05pm Open the presentation refreshing it and validate the numbers have been updated.
2:10pm Send out a meeting reminder with the updated presentation attached.
3:00pm Make up for lost time on Thursdays and go hit a couple of balls on the range or get a mani/pedi, whichever is most needed

It is not as easy as a blink of an eye but this is a sampling of the efforts this would take.

Step 1:   Articulate what reports need to be included in the presentation or documents.
Step 2:   Work with your IT team to see if Go Office is installed. If not, they can go to IBM.com and download their software. You can also call Lodestar Solutions and we can help you find it. There are Go Office server and desktop components to be installed.
Step 3:   Once Go Office is installed, work with the IBM Cognos Report Writer to create the reports you need. While they are building reports, look at who should receive the Office documents with reports embedded. Anyone can see the documents, however they can only update the data if they have a BI Consumer license or above. In some cases, companies have stopped distributing the documents and they simply ask their users to open the document thru the IBM Cognos Portal and update the data.
Step 4:   Once the reports have been created, you can now replace your manual charts, graphs and data, with the reports. Determine the best method of distribution and, your Thursdays are now yours again.

Just to recap, here are some of the benefits of Go! Office:
• Toolbar icons let users consume and save BI content, or create sophisticated desktop reports such as briefing books and dashboards.
• Users can refresh their MS Office documents with updated information on-demand.
• Users can modify and share IBM Cognos BI content using familiar applications and interfaces.
• Publish and share IBM Cognos-enabled files to the secure BI portal.

Top 5 Cognos Framework Manager Tips

Cognos Framework Manager is the brains of IBM Cognos 8. It is often misunderstood and therefore abused by the misguided. So let’s talk about Framework Manager do's and don’ts.

Don’t think Framework Manager is an ETL tool? – Framework Manager is a tool that sets on top of your data sources which includes databases. It can do complex calculations, table joins, execute stored procedures, and manage data level security. However it does not convert data nor does it apply business rules to data to improve quality.  If you need an ETL tool, of course you can purchase Cognos’ Data Manager, or my personal favorite Microsoft SSIS. You decide which one will work for you but be sure to work on your data because Framework Manager cannot disguise poor data quality.

Do create calculations – if Cognos business users will be using the same calculations for multiple reports, then make the calculations in Framework Manager. It will institutionalize the calculation and it will be the same in every report.

Do use business terms – when published in business and presentation view, change query subjects and query items to business terms and names. If you are unfamiliar with Lodestars recommended Framework Manager Vies, stay tuned I will cover that in another post.

Don’t work on Framework Manager at the same time – if two people are in the same project at the same time and try to save their work, the last to hit the save button wins. If you need to work on a Framework Manager Model as team, then consider using source control software.

Do use Parameter Maps – Parameter maps are a prompts best friend. When a report writer creates a prompt using a description instead, a key performance can suffer.  I point that out because description fields are not typically indexed while keys are. A parameter map allows the report writer to still use the description field, but Framework Manager will pass the key to the database instead of the description.

Cognos BI Promptmany Macro Function Error

Cognos BI Promptmany Macro Function Error

The promptmany macro function error message is such a letdown. Error QE-DEF-0406 is normally generated when you try to use the function to pass multiple values to a Sql stored procedure. By nature if a stored procedure has parameters, you can only set the parameter equal to a single. This is a problem if you want and need to pass multiple values to a stored procedure. Traditionally, Sql programmers use cursors to handle passing individual values one at a time to a procedure. Of course programmers would rather bite off their arm than willingly write a cursor if there is another viable alternative.

So when you see the promptmany macro function in Cognos, one gets all excited with the prospect of an application allowing you to use an array. Ah Yeah you start dancing cause this will save programming time so you rush to use it in Framework Manager. Framework Manager is the natural selection because you want to pass the prompt values from the report to the stored procedure query subject. From there, Framework Manager will handle passing multiple values to the database. As soon as you go to test your report and use a prompt that requires more than one value you get Error QE-DEF-0406.

According to Cognos, the promptmany macro was not designed to handle an array. The promptmany macro only works in a query (inside a report) and not in Framework Manager. The solution they offer is to write a java script to handle multiple values. The idea is to embed the script into the prompt page of the report and the java script will take over. Good luck with that as I was unsuccessful with the script so I invented my own workaround. My process is much easier and if you are comfortable with Sql this will be a breeze for you.

1. Write a web application using Visual Studio. The web application shows the prompts and then writes the prompt values to a table. When the user click save you generate a Group ID.

2. Use the Group ID in a sub select in your where clause and set your stored procedure parameter equal to the Group ID.

3. Publish the application to your www root directory in IIS

4. Put a link to your web application on your prompt page

Avoid Writing Cognos Report Studio SQL Queries

IBM Cognos Report Studio is a powerful tool. It is flexible which makes it powerful and dangerous at the same time. You can create a report based on a Framework Manager Package or go directly to the database. I tend to advise that you should always go through Framework Manager to create your reports instead of building custom Report Studio SQL Queries within the report.

Often, people bypass Framework Manager because they may feel that it is rigid. I will admit that it is rigid and for good reason. Whenever I see custom sql code in a report, it is there for really one reason. The code is compensating for what is lacking in the data, whether it is data structure or data quality. I know some report writers are getting all worked up saying that Framework Manager takes a lot of work to do one thing that can be accomplished faster in a report. I agree, but how scalable is the fast route? If you have another report that needed that same code, you would have to report with the same code that has to be managed and maintained separately. However, if it were maintained in Framework Manager you would have one place to make the change and it would apply to all the reports built on the package. Framework Manager allows you to scale your work and custom sql in a report is more of a one off…or as I like to call it …a run off…they tend to run off with your time.

The point of this conversation is to point out that every tool has its place. It is my position that code in a report is not the best use of Report Studio capability. It merely masks the real issue. Data quality and structure must be addressed at the database level. Any attempt to bypass that work will only lead to more headaches later. Ok one more thing before I get off my soap box, even the most talented report writer would run up and kiss the person that could fix their data. I have been asked to write a post on how to use and leverage Microsoft SSIS to scrub your data and I promise I will get to such a post.

Ok so back to this post. When you get into writing code directly into a report, you have moved into programming and out of report writing. You can write the code in the report, then place that code on the database and call it from Framework Manager by using a stored procedure to build the query subject. After that, you can write your report based on the query subject. This is a much more efficient way to build a report using custom code. The database can handle commands and syntax better than the report and this method is more scalable.

If you lessen the amount of custom work in the report and correct the issues in the database directly, you will see a marked improvement in report runtime and happier report writers.

Cognos Report Studio Nested If Statements – HELP!

​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.

BI is a Business Process, Not Software

BI is a Business Process, Not Software,

One of the most common misconceptions about Business Intelligence is that it is a technology solution.  I buy the right software, hire the right consultants, plug it in and Boom……I have Business Intelligence.  The reality is that Business Intelligence is a Business solution supported by technology not a technology solution supported by the business.  It’s a business process; and if it is not implemented as a business process, it won’t matter how much money you throw at it, you are not going to get the value you intended.

In order for Business Intelligence to become a valuable tool for the business it has to be designed to drive decision making and become part of the daily culture of an organization.  The business has to be an integral part of the effort to implement a business intelligence solution, be committed to the process, define the goals and establish the view of the business that is needed to support their decision making.  In most cases information technology resource can’t do it.

Business Intelligence is about understanding information about your business and the relationship between what may sometimes seem like disparate information.  We do this so that we can look back and understand why things happened; look forward and forecast reliably and look at the business right now, in real time, and react while you have a chance to impact the business today.  It doesn’t matter if you are using the most sophisticated software on the market to do it or you are using an excel spreadsheet.  Business Intelligence is a business process that transcends the technology behind it.

Have you joined the Lodestar community yet?  If not, what are you waiting for?  It take less than 1 minute and will ensure that you are kept up to date on our new blog and education as well as any new unique programs we offer.  Just log onto lodestarsolutions.com and sign up today!

Crosstabs Dimensional Data in Report Studio

​Crosstabs Dimensional Data in Report Studio

Let me first start by saying this is not a how to or even necessarily the right way to do things with Dimensional Data in Report Studio, rather some options to explore learned from hours spent cursing at the computer trying to get the seemingly ‘simple’ things to work in crosstabs. You know, things like adding two columns together, calculate a variance, or filter out crosstab rows with no data. I run into these issues most when I’m building financial reports where we’re comparing multiple forecast/budget versions across specific spans of time.

Dimensional Data in Report Studio

----The Tuple

Love me some Tuple! Whenever I need to pick some data point seemingly out of thin air… the answer usually lies in the form of a tuple. Basically, the tuple function allows you to pinpoint cross sections of dimensions. Note: it only works with data MEMBERS!
Sample: tuple([2009],[Budget])

----Calculating Totals/Variance
For those occasions when clicking the nice little ‘Total’ icon isn’t working for you… don’t be afraid to explore the currentMeasure function and build the total yourself. It will really help you start to understand what the crosstab is trying to do when you click the ‘total’ icon and help you create more robust crosstabs. Some sample syntax:
Sample: total(currentMeasure within set[Forecast]).
If your Forecast is nested in the crosstab you might want to try using within detail.
Sample: total(currentMeasure within detail[Forecast]).
This same logic can be extended to help compute variance.
Sample: total(currentMeasure within detail [Budget]) – total(currentMeasure within detail [Actual])

----Filter crosstab rows.
I recently created a report where I had to show Hours worked by employee by project for the entire company. Many projects had employees that didn’t have any worked hours allocated to a project for a particular time period… yet these employees were still showing up in the report. Naturally, the report ballooned to well over 100 pages. To fix this, I embeded the filter function in a new data item to help filter out the unwanted rows.
Sample: filter([Employee],tuple([2009],[Actual Hours]) >0 or tuple([2009],[Actual/Fcst]) >0)

As a side note to this, in Cognos 8.4 there are options to filter out rows with nulls and zeros, however, I know most of you are still 8.3 or earlier versions. So until you’re on board with 8.4, hopefully this will help you stay afloat!

If you liked this blog on Crosstabs Dimensional Data in Report Studio, then check out this blog on what is Cognos TM1 performance modeler by clicking here.

Cognos vs Business Objects…What’s The Difference?

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.

BI Reports Not Used?

Report writing is difficult for most people. It requires a level of detail and skill that most people simply don’t have the patience to learn let alone apply. Historically, most report writers are primarily Information Technology (IT) people with very little business experience which makes it impossible to explain why a transaction must be a debit instead of a credit. The language gap between report writers and users can be the difference between speaking English and Spanglish. The report writer might understand basic information about the business but not enough to optimize a report or the user doesn’t understand real constrains such as data quality, database design, or application limitations to understand why a report can’t be written just like their excel spreadsheet.

Report writers – I have two words for you: Analysis Studio! Learn it, love it, use it. Business users – I have the same two words: Wait for it … Analysis Studio. Let’s assume that you have the groundwork covered such as a multidimensional Framework Package and you have Analysis Studio deployed, you can use Analysis Studio drop and drag functionality to bridge the gap between report writer and business user.

Analysis Studio allows a user to see their data in a way that makes sense to the business. The business user can drag data items from the Insertable objects pane to the crosstab. Think of the crosstab as follows:

  • Row – What do I want to see?
  • Columns – When did the information occur? (Year, Month, Week)
  • Measure – How many or how cost? (Mathematical  values)

Once the you have placed the data items you want to work with into the crosstab you can slice and dice, create calculations, suppress certain items … the list goes on and on. The point of Analysis Studio is that interaction is very similar to a Microsoft Pivot table, but don’t be fooled to think that it is a Pivot table. It is much more but I use this reference to coax business users to the table to write their own reports because most people are comfortable with Pivot tables. Ok, so the business user is wheeling and dealing in Analysis Studio and they are happy because they can ask questions of the data and get immediate answers. Now they want what they have in Analysis Studio as a report because they need to share it and schedule it – which neither can be done in Analysis Studio. Report writers get on your knees and thank the heavens they are bringing you an Analysis Studio project to convert to a report instead of an excel spreadsheet. Stay on your knees because I am going to tell you how to convert that project with a single click… Open in Report Studio. It is the button you love and want to kiss Cognos on the mouth for … ok if Cognos was a person you would.

When you create something in Analysis Studio you can open it in Report Studio and Cognos does all the translations including formatting. I know you are saying really, “I didn’t learn that in my training class!” or  “I don’t remember that!” Don’t worry you are learning it now –  better late than never. I think this tip is worth subscribing to the blog. Anyway, Query Studio and Analysis Studio interplay with Report Studio very well and this is where Cognos separates itself from the competition. Remember I talked about the business users being comfortable with Analysis Studio because it is a lot like Microsoft Pivot Table, well the report writer can leverage that and write a report that the business will understand and use immediately. Moreover when you create a report from Analysis Studio, a report writer can learn how Cognos understands calculations and functions, which if the report writer is smart – will review those query items to beef up their skills. Also, if a report writer is struggling with something in Report Studio, they should do it in Analysis Studio and convert it to Report Studio and see the calculation…. it is a great self-tutoring method.

I could go on forever about how to use Analysis Studio and Report Studio, but I will leave you with this. Analysis Studio is at a higher level than transactional data. That means that the “when” (refer back to the Who, When, How section above) is summarized, therefore you can’t see a specific transaction. However in my next blog posting I will talk about bridging the gap between multidimensional and transactional reporting.

Follow my blog for a continuation of this topic.

X