List of Contributions

Michael Walter

TX HHSC

Contact Details

TX HHSC

My Content

1 to 20 of 20 total

RE: Report - Format type

Posted By Michael Walter Wed April 27, 2022 01:47 AM
Found In Egroup: Cognos Analytics \ view thread
Not sure I have had your identical problem, but I've had similar, where I could not specify a date format for what I thought was a date field. What's worked for me is adding to the data item a CAST, e.g.: CAST([DepartureDate],date). ------------------------------ Michael Walter Business Analyst ...

RE: Pull report fields out of sql

Posted By Michael Walter Fri November 05, 2021 02:07 PM
Found In Egroup: Cognos Analytics \ view thread
Welcome to my messy world, Jenifer. :-) Multiple queries under a single query object can mean a couple of things, and you can't tell which without inspecting both. The first (and worst) reason is because Cognos is sending 2 separate queries to the database, and "stitching" those results together. ...

RE: Pull report fields out of sql

Posted By Michael Walter Fri November 05, 2021 04:24 AM
Found In Egroup: Cognos Analytics \ view thread
Not sure exactly what you're looking for, Jenifer, but this would be my approach to what I think you are asking. I use the Tools->Show SQL/MDX command to generate the SQL, then Select All, copy, and paste it into Word. I've built some macros to handle the next steps, but the essential one is to strip ...

RE: Performative prompt pages

Posted By Michael Walter Mon September 20, 2021 02:53 PM
Found In Egroup: Cognos Analytics \ view thread
The answer you're looking for is almost totally dependent on the size and structure of the table from which you are extracting the LOV. It's not always feasible, but avoiding tables altogether can be helpful. I have several reports where I generate a picklist of dates, either the last ten years, or ...

RE: Putting chosen value from prompt page in report

Posted By Michael Walter Thu September 16, 2021 05:49 PM
Found In Egroup: Cognos Analytics \ view thread
To put the user's input into your filter, just use the prompt name ?derived_FY?. Your filter will look something like: [myDatabase].[FY] = ?derivedFY? ------------------------------ Michael Walter Business Analyst TX HHSC Austin ------------------------------

RE: Merge two timestamps

Posted By Michael Walter Thu September 16, 2021 09:39 AM
Found In Egroup: Cognos Analytics \ view thread
This is a pretty generic approach, and might not work for your use case, but in general, were I getting multiple timestamps in a query where i only needed one of them, I'd likely use a MIN or MAX function on the paired timestamps. Hope that helps. ------------------------------ Michael Walter ...

RE: Cognos Analytics 11.2 - Running report as PDF gives me access denied

Posted By Michael Walter Wed September 15, 2021 12:42 PM
Found In Egroup: Cognos Analytics \ view thread
Diego, Forgive me if I'm asking something obvious, but over the years I've learned never to assume. Did you try doing what the warning message suggests, i.e. opening and saving the report in Report Studio to restore the link to the data? (You might want to do a "Save as" to preserve the original ...

RE: Cognos Analytics 11.2 - Running report as PDF gives me access denied

Posted By Michael Walter Wed September 15, 2021 06:14 AM
Found In Egroup: Cognos Analytics \ view thread
Diego, I agree with Patrick completely. This most likely stems from something specific to your environment, rather than being a Cognos-based issue. I think running the report in the background, as Patrick suggests, is an excellent troubleshooting step. The only thing I would add to that is try ...

RE: Create a report from the content store db providing details of schedules

Posted By Michael Walter Mon August 30, 2021 02:06 PM
Found In Egroup: Cognos Analytics \ view thread
No guarantees, but have a look at content_store.p_job. It has a startdt and a lastcompletiondt. Both are timestamps, so you should be able to calculate the duration. Good luck. ------------------------------ Michael Walter Business Analyst TX HHSC Austin ------------------------------

RE: Requirement to hide particular columns of a list report based on a prompt value.

Posted By Michael Walter Mon August 16, 2021 09:52 AM
Found In Egroup: Cognos Analytics \ view thread
Brian, Using a render variable on a column is an inefficient way to handle this situation--regardless of the value of the prompt, your query returns 4 years of data, then suppresses display of half of the results. Instead, use the query filter to exclude and include the years you want. The report ...

RE: where are stored advanced settings of dispatcher and services?

Posted By Michael Walter Mon June 21, 2021 12:49 PM
Found In Egroup: Cognos Analytics \ view thread
Hi, Magnum, I've always set this kind of advanced parameter through Cognos Configuration, but I'm still on Cognos 10. The process may be slightly different in CA 11; it's documented here: Advanced settings configuration - IBM Documentation. The one you're interested in is documented here: Report ...

RE: DQM Behavior against Union or Join queries (reports run slowly)

Posted By Michael Walter Mon March 15, 2021 09:54 PM
Found In Egroup: Cognos Analytics \ view thread
Let me start by agreeing with @HENK CAZEMIER: take a look at whether you're preserving or removing duplicates (i.e., performing a UNION vs. a UNION ALL). It seems whenever I need a union, it's in circumstances where there's no possibility of duplicate rows, so dispensing with the costly need to check ...

RE: Report vs Dashboard

Posted By Michael Walter Wed March 03, 2021 08:58 AM
Found In Egroup: Cognos Analytics \ view thread
This article may be of some value to you: Comparing Reports and Dashboards in Cognos Analytics (senturus.com). ------------------------------ Michael Walter Business Analyst TX HHSC Austin ------------------------------

RE: Cross Join Error in Data Module

Posted By Michael Walter Tue March 02, 2021 07:48 PM
Found In Egroup: Cognos Analytics \ view thread
Nobody has asked if you actually want to create a cross join. A cross join creates a row in the resulting projection, for each row in one table, matched against every row in the other table. While they are rare, there are certainly valid use cases where a cross join is the right solution. See, e.g., ...

RE: Moving from SAS Analytics to Cognos Analytics

Posted By Michael Walter Fri January 29, 2021 10:55 AM
Found In Egroup: Cognos Analytics \ view thread
Having worked with both, there are some pretty significant differences between SAS and Cognos, and I suspect you're looking at a largely manual process. But if I was looking at a significant volume of reports to migrate, I would at least investigate costs and success rates of an automated tool. A ...

RE: HINT: add comments to your Report QueryItems

Posted By Michael Walter Thu December 31, 2020 01:38 PM
Found In Egroup: Cognos Analytics \ view thread
Been working with Cognos 8+ years, now, and quite sure I don't know it all... But I found how to add comments early on, and will share a tip that's been quite helpful to me. Comment your filters at the top with a short description of what they do, e.g.: #/* TransDate in Range per params */# [ ...

RE: Concatenated Fields - Detail Filter Slows Down Report

Posted By Michael Walter Tue November 24, 2020 01:05 PM
Found In Egroup: Cognos Analytics \ view thread
The most likely explanation for the performance degradation is that your database has no index reflecting your concatenated "column," and therefore has to perform a complete scan to honor your filter. You could work with your DBA to create a calculated index to match your concatenated column, but this ...

RE: FIRST_DAY(((CURRENT_DATE) + (-1) YEAR)

Posted By Michael Walter Fri November 20, 2020 11:46 AM
Found In Egroup: Cognos Analytics \ view thread
"FIRST_DAY" is not a Cognos function, but it appears to be a vendor-specific one in IBM DB2 (see, https://www.ibm.com/support/knowledgecenter/SSCRJT_7.0.0/com.ibm.swg.im.bigsql.commsql.doc/doc/r0061474.html). What DB is your function running against? In any event, per the above, "BETWEEN FIRST_DAY(((CURRENT_DATE) ...

RE: SQL in cognos

Posted By Michael Walter Mon November 09, 2020 07:06 PM
Found In Egroup: Cognos Analytics \ view thread
I believe these instructions are what you are looking for: https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cr_rptstd.doc/t_cr_rptstd_wrkdat_build_report_using_your_own_sql_mdx_rel.html Be aware that there are many negatives to bypassing the Cognos metadata layer ...

RE: Shared image folder on network drive, is it possible?

Posted By Michael Walter Tue October 20, 2020 10:31 AM
Found In Egroup: Cognos Analytics \ view thread
We're still using Cognos 10.x here, and I don't have a direct answer as to whether a single shared network location for images can be made to work, but I can offer the solution I've used in our multi-server environment, based on IBM's tech notes on the "missing images" in output issue. I always use ...