COGNOS USER GROUP July 24, 2019 Agenda BICC Updates- Kelsie - PowerPoint PPT Presentation

COGNOS USER GROUP July 24, 2019

Agenda • BICC Updates- Kelsie Newberry • New Position Overview – Zach Yater • Boiler Insight Metadata HR and Finance - Monique Rawles • Hardcoded filters for over 1,000 – Sue Wilder • Cognos How To - Josh Newberry • Data Cookbook Cognos Portal – Sarah Bauer • Q&A

BICC Updates

Cognos Resources Upcoming Cognos Tool Trainings ● COG 301 - Cognos Analytics Authoring Lite - 8/27 ● COG 101 - Cognos Analytics Navigation - 9/9 ● COG 401 - Cognos Analytics Authoring - 10/7 & 10/8 ● COG 111 - Cognos Analytics Scheduler - 10/21 ● Cognos Resources Page ● Prompt Blank Row Workaround ● Creating a Cognos Schedule Video ● Located on the Cognos reporting tool training page ●

WHO DO I CONTACT? Data Questions/Issues: Email: Boiler Finance: treasreporting@purdue.edu Data-related questions/issues should first be directed to your supervisor. GM: spsrept@lists.purdue.edu Insight HR: HR Data Request Form Data Questions/Issues: Email: Student CognosStudentHelp@purdue.edu Data-related questions/issues should first be directed to your supervisor. Reporting Tool Questions/Issues: Email: BICC BICC@purdue.edu Cognos tool issues - error messages, unexpected behavior, etc.

New Features Security Best Practice Best Practices Document ● Creating reports, dashboards, or stories using new ● features Example - uploading a file to use in a data module and ● then building a report from that module Reference for how to secure “new features” content that ● contains upload file, data set, and/or data module

BI Consultant Zach Yater

TECHNICAL SKILL ENGAGEMENT INSIGHTS

How to accomplish? Improve Infrastructure ▪ Usage Statistics ▪ Testing methodology ▪ Database modifications Develop Community ▪ Involved in user groups and advisory ▪ Communication & Resources Build Skills ▪ Help with onboarding ▪ Provide resources – Videos, Articles, Best Practices

What are my goals? ▪ Content Expert – Cognos – Tableau ▪ External Engagement – Evangelize Purdue Data – Bring outside knowledge in

Boiler Insight Metadata HR & Finance

Boiler Insight Metadata HR and Finance ● Field Metadata ● HR updated - June, 2019 ● Finance updated - June, 2019 (more to come) ● Metadata Report - standard report (Team content > Boiler Insight > Standard Content) ● Content Metadata ● HR and Finance updated - June, 2019 ● BI Content Catalog - standard report (Team content > Boiler Insight > Standard Content) ● HR Data Training ● Documentation published on the Cognos Resource page ● Finance Data Training ● Documentation - coming soon ● Other Resources ●

Hardcoded filters for over 1,000

THE BASICS: HARD CODE 1,000+ [Student Detail].[Student].[ID] in (‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’) Or [Student Detail].[Student].[ID] in (‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’) Or [Student Detail].[Student].[ID] in (‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’, ‘123456789’) • Individual unit is '123456789', • Each group <1000 • Note: These are plain quotes, remove autocorrect for smart quotes

EXCEL CELL FORMAT TO ADD PUNCTUATION "'"000000000"', " • Double Quote, Single Quote, Double Quote • Zero = Placeholder for Numbers(s) • Double Quote, Single Quote, Semicolon, SpaceBar, Double Quote • Remove autocorrect for smart quotes, these need to be plain quotes

IN EXCEL AFTER FORMAT CHANGE

CHANGE LIST TO PARAGRAPH IN WORD • Paste as text • Replace ^p with nothing. (Removes hard return) • This is case sensitive.

PARAGRAPH FORMAT IN WORD '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789',

FINALIZE FORMAT IN WORD • Add Package/Table/Column info • Add 'in', 'or', and parenthesis • Use Word Count in to keep groups <1000 [Student Detail].[Student].[ID] in (' 123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789') Or [Student Detail].[Student].[ID] in ( '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789') Or [Student Detail].[Student].[ID] in ( '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789', '123456789')

PASTE TO COGNOS FILTER AND CHECK FORMAT If problems, check for matching and correct • Parenthesis ( ) • 'or', 'in' • Quotes (straight quotes, not smart quotes)

TIME SAVER • Add file description at top of file • Save • If same group is needed at another time: • If same package and table, just cut and paste • Use word Replace function to replace current source information with new source • i.e., replace 'Student' table with 'Academic' table to use same list against an academic table

Cognos How To

Visually Grouping Without Using Grouped Columns

Through Use of Count/MOD function and Conditional Formatting

Count Vs Count Distinct

Running-Count Vs Running-Count Distinct

All Counts Together

MOD Function • • • • • •

Data Cookbook Cognos Portal

Community Feedback Questions?

THANK YOU Sue Wilder Monique Rawles Leslie Rody Sabrina Tanner Julie Jang Katie Hendryx Kelsie Newberry