Accepting number of late deliveries prompt

[oscarca]

Hey Cognos community,

I want to create a prompt where I can input a number for how many days I will accept a delivery being late.
[Business].[Purchase Order Measures].[Requested Delivery Date]

I have made a query to find deliveries that was received the correct Delivery date with the correct quantity:
case
when
([Business].[Purchase Order Measures].[Goods Receive Date] = [Business].[Purchase Order Measures].[Requested Delivery Date] and [Business].[Purchase Order Measures].[Ordered Purchase Qty BuM] = [Business].[Purchase Order Measures].[Received Purchase Qty BuM]) then 1
else 0
end

But now the deliveries has to match the the exact date but I want to give the option of accepting deliveries being for example 5 days late then still getting a match.

Anyone done something similar ?

Thanks in advanced fellow Cognos friends!

[Robl]

something like;

...
Received date > requested date
and
Received date <= _add_Days(requested date, 5)
....

[oscarca]

I want the _add_Days(requested date, 5) to be dynamic depending on what I input in the prompt.
It should be able to be any number of days.

[Robl]

then
...
_add_Days(requested date, ?Days?)
...

Then just make sure Days is either a numeric prompt box or perhaps a fixed set of select values from 1-10 (or whatever)

[oscarca]

Getting the Error that _add_days cannot be used with the datatype INTEGER.

[Robl]

something like;

_add_Days(requested date, cast(?Days?, numeric))

[oscarca]

Does [Requested date] have to be a date format cause I'm not getting it to work

[oscarca]

I'll start of to give you more information. The data package is relational.

The two time dataitems  [Business].[Purchase Order Measures].[Requested Delivery Date] and [Business].[Purchase Order Measures].[Goods Receive Date] are INTEGERS.
They are being used in this data item called "Correct time, Correct quantity":
case
when
([Business].[Purchase Order Measures].[Goods Receive Date] = [Business].[Purchase Order Measures].[Requested Delivery Date] and [Business].[Purchase Order Measures].[Ordered Purchase Qty BuM] = [Business].[Purchase Order Measures].[Received Purchase Qty BuM]) then 1
else 0
end

The report needs two text box prompts:
Late deliveries: If I choose 10 then the case query should accept 10 late days as being a match.
Early deliveries if I choose 10 then the case query should accept 10 early days being a match

These two prompts then create a span of 20 acceptable days being late/early.

I am trying to convert
[Business].[Purchase Order Measures].[Goods Receive Date] to a date using cast([Business].[Purchase Order Measures].[Goods Receive Date], date) but didn't work so I tried to convert in first to a VARCHAR then from a varchar to a date but didn't work..

[MFGF]

Getting the Error that _add_days cannot be used with the datatype INTEGER.

You can only use date functions such as _add_days() with data items that are a Date data type. The Information pane for this function in the expression editor implies this, although it isn't explicit in saying that "date_expression" must be a Date data type:

_add_days ( date_expression, integer_expression )
Returns the date or datetime, depending on the format of "date_expression", that results from adding "integer_expression" days to "date_expression".

What do your [Requested date] values look like? Are they in a structured format such as YYYYMMDD or are they meaningless numbers that can only be resolved by linking to a time dimension table? Do you have any real date representations of these values anywhere you can use? If not, you'll need to construct your own date item using the _make_timestamp() function.

If you need to construct your own, and the Requested date values are YYYYMMDD, you can extract the year part like this:

floor([Requested date] / 10000)

You can get the month part like this:

floor(mod([Requested date], 10000) / 100)

And you can get the day part like this:

mod([Requested date], 100)

The _make_timestamp() function expects the arguments in the order year, month, day

Cheers!

MF.

[oscarca]

Hey MFGF!

the format of [Requested date] is YYYYMMDD.

Would it be possible to cast([Requested date],VARCHAR(10)) and then substring([Requested date],1,4) + '-' + substring([Requested date],5,2) + '-' + substring([Requested date],7,2). ?

and yes [Requested date] is linked to a time table.

Will this be 3 different data items ?

floor([Requested date] / 10000)

You can get the month part like this:

floor(mod([Requested date], 10000) / 100)

And you can get the day part like this:

mod([Requested date], 100)

[oscarca]

the last step of your solution MFGF creating the timestamp I am getting this error:
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Conversion failed when converting date and/or time from character string. (SQLSTATE=22007, SQLERRORCODE=241)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Arithmetic overflow error for type varchar, value = 20140204.000000. (SQLSTATE=22003, SQLERRORCODE=232)

This:
floor([Requested date] / 10000)

You can get the month part like this:

floor(mod([Requested date], 10000) / 100)

And you can get the day part like this:

mod([Requested date], 100)

Gave me:

Year       Month        Day
2,017        2             14

_make_timestamp([Year],[Month],[Day])       <-------(Here I am getting error)

[Cognos_Jan2017]

2,017 should not be read as an Integer.  Technically, it is a text.

That would cause your _make_timestamp to fail.

[oscarca]

Hey Jan!

What would you suggest then ?

Thanks for reply

[MFGF]

the last step of your solution MFGF creating the timestamp I am getting this error:
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Conversion failed when converting date and/or time from character string. (SQLSTATE=22007, SQLERRORCODE=241)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Arithmetic overflow error for type varchar, value = 20140204.000000. (SQLSTATE=22003, SQLERRORCODE=232)

This:
floor([Requested date] / 10000)

You can get the month part like this:

floor(mod([Requested date], 10000) / 100)

And you can get the day part like this:

mod([Requested date], 100)

Gave me:

Year       Month        Day
2,017        2             14

_make_timestamp([Year],[Month],[Day])       <-------(Here I am getting error)

Hmmm. Ok. Try this:

cast(floor([Requested date] / 10000), integer)

cast(floor(mod([Requested date], 10000) / 100), integer)

cast(mod([Requested date], 100), integer)

That ought to do it :)

MF.

[Cognos_Jan2017]

Oscara.. the Cast post by MFGF should work.

In this example, whatever you are assigning as [Year] will have to result in the integer 2017.

Thank you MFGF, Bob (aka Jan)

[oscarca]

.

[oscarca]

the _make_timestamp gives me this error now:
[Microsoft SQL Server Native Client 11.0]Conversion failed when converting date and/or time from character string. (SQLSTATE=22007, SQLERRORCODE=241

cast(floor([Requested date] /10000),integer)
still gives me 2,017
Thank you guys MFGF and Jan for quick replies

Not getting why [Requested date] has to be converted to int when its already an int32

[cognostechie]

I'll start of to give you more information. The data package is relational.

The two time dataitems  [Business].[Purchase Order Measures].[Requested Delivery Date] and [Business].[Purchase Order Measures].[Goods Receive Date] are INTEGERS.
They are being used in this data item called "Correct time, Correct quantity":
case
when
([Business].[Purchase Order Measures].[Goods Receive Date] = [Business].[Purchase Order Measures].[Requested Delivery Date] and [Business].[Purchase Order Measures].[Ordered Purchase Qty BuM] = [Business].[Purchase Order Measures].[Received Purchase Qty BuM]) then 1
else 0
end

The report needs two text box prompts:
Late deliveries: If I choose 10 then the case query should accept 10 late days as being a match.
Early deliveries if I choose 10 then the case query should accept 10 early days being a match

These two prompts then create a span of 20 acceptable days being late/early.

I am trying to convert
[Business].[Purchase Order Measures].[Goods Receive Date] to a date using cast([Business].[Purchase Order Measures].[Goods Receive Date], date) but didn't work so I tried to convert in first to a VARCHAR then from a varchar to a date but didn't work..

The problem is that you are using the Date keys (integers) instead of the Date ! The idea is to convert that integer value to a Date format which is what MFGF originally suggested and run te calculation off of that using _add_days function.


Create two Data items which will contain the integer values converted to a Date.

[Requested Delivery Date] =

                cast(
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),1,4) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),5,2) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),7,2)                                         
                     
               , date
                    )


     [Goods Received Date] =

                cast(
                 substring( cast ( [Business].[Purchase Order Measures].[Goods Received Date], varchar(8) ),1,4) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Goods Received Date], varchar(8) ),5,2) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Goods Received Date], varchar(8) ),7,2)                                         
                     
               , date
                    )

     
    Now create a filter with the following expression:

    [Goods Received Date] >= _add_days( [Requested Delivery Date], ?Early Deliveries? * -1) and
    [Goods Received Date] <= _add_days( [Requested Delivery Date], ?Late Deliveries? )   

   This means that the data type in your prompts should be integer. If this doesn't work then try the cast function within the filter to cast the prompt values to integers.

[cognostechie]

Read those 'cool' icons as 8

[cognostechie]

and by the way, who created your FM model? Why do you have an integer date in a query subject that is supposed to contain measures?

[Business].[Purchase Order Measures].[Requested Delivery Date]

This proves my point that I made in another post today about how Cognos implementations effect the success/failure of the tool. How would you expect the users to make their own reports with an integer date? Putting it in a query subject which is named for Measures makes it un-intuitive so it defeats the purpose of a self-service BI.

[oscarca]

not even this worked:
[Requested Delivery Date] =

                cast(
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),1,4) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),5,2) + '-' +
                 substring( cast ( [Business].[Purchase Order Measures].[Requested Delivery Date], varchar(8) ),7,2)                                         
                     
               , date
                    )

Still getting:
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Conversion failed when converting date and/or time from character string. (SQLSTATE=22007, SQLERRORCODE=241)

The FM modell was created a few years back and have no idea who built it. I absolutely get your point and it makes great sense. It would be nice to know why they decided to put integer date in the measure query. This makes it difficult to make simple calculations with dates.  Should this be changed in the FM model or in the sql server ? maybe it could be set as a date there because Cognos Analytics really doesn't want to convert my int no matter what solution I try.. Getting slightly frustrated but learned a great deal about conversions thanks to you guys which is highly appreciated.

[MFGF]

the _make_timestamp gives me this error now:
[Microsoft SQL Server Native Client 11.0]Conversion failed when converting date and/or time from character string. (SQLSTATE=22007, SQLERRORCODE=241

cast(floor([Requested date] /10000),integer)
still gives me 2,017
Thank you guys MFGF and Jan for quick replies

Not getting why [Requested date] has to be converted to int when its already an int32

Can you check for us what exact data type Requested date is? Is it a character value?

I just tried my approach using the samples (there is a Day key item which is a YYYYMMDD integer value) and it works fine there. I Have attached my report spec - if you have the samples, can you try it there?

Cheers!

MF.

[oscarca]

According to the FM modell [Requested date] is an int32.

I just think this is very strange.. it feels like it shouldn't be this difficult..
And don't get why floor([Requested date] / 10000) gives me a decimal 2,017 instead of just 2017 and also when I cast [Requested date] to an integer it looks like this: 20,180,820

Sorry for lots of posts...

[MFGF]

And don't get why floor([Requested date] / 10000) gives me a decimal 2,017 instead of just 2017

I think you're assuming something that isn't necessarily the case. 2,017 isn't necessarily a decimal - just the way the number is being displayed by the default format, with a comma separating the thousands. If you change the data format of the result in properties to Number and set the ""Use thousands separator" to "No" you should see just 2017.

and also when I cast [Requested date] to an integer it looks like this: 20,180,820

As above, it's just the default format separating the thousands/millions in the number.

Did you manage to test the sample report?

MF.

[oscarca]

I could open the report but not run it, since I have not access to the sample database. But I can see how you made the query and that is exactly the way I have made it aswell...

The only difference I can see is that in your XML it says RS_dataUsage value = '1' and for me it says 'attribute'.
[Verksamhet].[Inköpsorder mätvärden].[Requested date]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/