Hi,
I want to do a count of computers we have in our system where the Query1.FirstDayOfMonth - Date is bigger than the Query2.StatusDate
By doing this, we will get a snapshot over how many active computers we had in our systems month by month back in time.
When I manually set det dates for Query 2 I get the result that I want, but I have not Idea of how I should use Query1 and the Query1.FirstDayOfMonth - field as a paramenter/filter and then group the count on Query1.Month
Any Cognos super experts here that can help me out with this?
Query 1: List with filters for the report.
Query 2: List of computers and timestamps on when the status is changed.
Query 1:
FirstDayOfMonth Month Year
apr 1, 2014 12:00:00 AM April 2 014
mai 1, 2014 12:00:00 AM May 2 014
jun 1, 2014 12:00:00 AM June 2 014
jul 1, 2014 12:00:00 AM July 2 014
Query 2:
assettag StatusDate Status_Year Status
WS14900 jun 19, 2014 12:55:00 PM 2014 OPERATING
WS200325 apr 5, 2013 12:54:38 PM 2013 OPERATING
WS200488 aug 28, 2013 9:07:49 AM 2013 OPERATING
Desired Output
FirstDayOfMonth Month Year CountComputersInOperating
apr 1, 2014 12:00:00 AM April 2 014 2
mai 1, 2014 12:00:00 AM May 2 014 2
jun 1, 2014 12:00:00 AM June 2 014 3
jul 1, 2014 12:00:00 AM July 2 014 3
As you see. For June/July the count is 3 computers since WS14900 was set to operating on jun 19, 2014 12:55:00 PM.
How about something slightly different? Could you create columns in your report for each month..something along the lines of
if([Status Date] <= _add_months(?StartDate?,0)) then (1) else (0) -- operating at start of report
if([Status Date] <= _add_months(?StartDate?,1)) then (1) else (0) -- operating in month 1
etc.
You'd need to create a column for each month, but it wouldn't be too much trouble. You could even do something clever with render variables so that you only displayed the right number of columns
Hi,
Thank you for your answer.
It's a good ide, but I'm not sure if it solves my problem. I think this was more complicated than I thougt at first, since I now see that I have to compare different dates to calculate if an asset in this report should be counted or not.
How about joining the queries using >= (for example)? So you'd set the join between status_date and first_day_of_month, but instead of (=) you'd use (>=) as your operator? Wouldn't that achieve what you're after?
Quote from: Nimrod Avissar on 20 Nov 2014 06:38:17 AM
How about joining the queries using >= (for example)? So you'd set the join between status_date and first_day_of_month, but instead of (=) you'd use (>=) as your operator? Wouldn't that achieve what you're after?
Of course! I think this might work. Thank you!