How to make monthly report with multiple date filter in COGNOS

[ksbtcon]

Hello, would you please give your advice to make report in COGNOS.  I would like to set filter on "Sent for Payment Date"  as "last month" and "last date".

The reason why I need to add the last date, there is the difference of time zone at data warehouse between Concur and our SAP.  For example, SAP recognized a data on November 1st but Concur submitted data on October 31st (Sent for Payment Date).

I appreciate your insight and comments.

[KevinD]

@ksbtcon I don't believe you can have two filters on one data item. 

What data are you trying to see with your report? Knowing the "what" might help me help you. 🙂

[ksbtcon]

Hello Kevin,

I appreciate reaching out to me.  I would like to load following data.

• Concur Data Model : only Expense Folder
• Data Elements : Expense Report (Report Header Information, Report Dates and Statuses, Entry Information)
• Paying attention on Expense data model relationships.
• Date data : Last Month and Last day in Last 2 Month from Sent for Payment Date
• Update timing : every 2nd calendar day A.M JST / 1st calendar day P.M. PST

I recognized I made mistake on the explanation regarding date setting.  Please let me revise as follows.

For example, I  loaded October expense report on November 2nd.  The date range of "Sent for Payment Date" is from September 30th to October 31st.  Our SAP load Concur submitted data of September 30th on October 1st.  After loading from Concur/COGNOS, I will verify two data; general ledger in SAP and expense report in Concur.

I want to update and send the monthly expense report automatically.  I have already set schedule and COGNOS send mail with attached excel file automatically.  But I still need change date.

Thank you.

ksbtcon

[KevinD]

@ksbtcon I'm a little confused on what you mean by Last Date. There isn't a data item in the Data Warehouse labeled Last Date. In your example you mentioned September 30 and October 31st, which would be the last day of each of those months. Is that what you mean? 

If so, instead of using the Last Month filter, you could use a Date Prompt for your filter that allows you to choose the exact date range you want. Right now with the Last Month filter, you will not be able to see the date of September 30th. If you remove this filter from the report, then do the following on your report:

1. Click on the Sent for Payment Date column title. 

2. Click the Filter (funnel) icon on the toolbar. 

3. Select Create Custom Filter

4. On the window that opens, look for a little checkbox that says, "Prompt every time report runs" and then check this box.

5. From the Filter Condition field, click the drop down arrow then select Range.

6. Click the radio button for the From date.

7. Click OK. 

What this does is every time you run the report, you can choose the data range you want. So, for your example you can select Sent for Payment Date from September 30 to October 31. 

Now, the limitation of this date prompt is that you won't be able to schedule the report. The reason is, you need to make a date range selection before the report can run. You will need to manually run the report each time so you can select the appropriate date range from the prompt. You can have the report email automatically every time you run the report. Just be sure when you run the report, you choose the option to Save the report, DO NOT choose View Now.  To email the report, you will select the advanced options (see screenshot). The advanced options will allow you to email the report (see second screenshot). 

Each time you need this report with the new dates, follow these steps. Unfortunately since you are spanning two different months, you cannot have the Last Month filter and another filter to tell it to add the last day of the month before the last month. 

I hope this helps.

[ksbtcon]

Hello Kevin,

I appreciate your detail explanation.  Unfortunately, I cannot solve the issue.  But it is OK and I will run the report manually.  If I have another issue, I will post question.

best regards,

ksbtcon