Accepting number of late deliveries prompt

[oscarca]

*solved it*

So all your guys solution worked but there was one small thing messing it all up.

When I looked in the [Requested date] table in excel it contained "-1" for missing dates and when I excluded -1 it worked.

Just want to thank all of you for great solutions and I am truly learning so much from this community.

So now I can try to get this dynamic (based on two prompts early & late)
case
when
([Mottagetdatum] = [Önskat Leveransdatum] and [Best Inköpskvant GrundEnh] = [Mottagen Inköpskvant GrundEnh]) then 1
else 0
end

Could I use datediff functionality to be able to use how many dates late/early a delivery would be accepted or should I do something like described earlier in the post
cast([Mottagetdatum],integer) >= cast(_add_days([Önskat Leveransdatum], ?Early? * -1),integer)
and
cast([Mottagetdatum], integer) <= cast(_add_days([Önskat Leveransdatum], ?Late?),integer)

[cognostechie]

Could I use datediff functionality to be able to use how many dates late/early a delivery would be accepted or should I do something like described earlier in the post
cast([Mottagetdatum],integer) >= cast(_add_days([Önskat Leveransdatum], ?Early? * -1),integer)
and
cast([Mottagetdatum], integer) <= cast(_add_days([Önskat Leveransdatum], ?Late?),integer)

Why would you try to cast it to Integer when it is already cast to Date?

Datediff is specific to SQL Server and to use that in Cognos, you have to set the SQL to Native in FM. That should not be done though because in case of a change in the database later on , it will stop working. _add_days is a cognos function and should be used so that cognos can generate appropriate SQL even if the DB changes.

The idea should be to learn the subject instead of attacking the current problem only and making things work with your existing knowledge of another tool. 

[Robl]

So your date is an integer and it shows -1 when it's empty.
That sounds a lot like it a surrogate key for a date dim.

Are you certain there's not a date dim in the model or in the DB to join to?

[oscarca]

Hey Robl,

It is joined with time dimension.
and this solution works now for me that you guys suggested when excluding/filtering -1:
case
when
[Mottagetdatum],integer) >= cast(_add_days([Önskat Leveransdatum], ?Early? * -1)
and
cast([Mottagetdatum], integer) <= cast(_add_days([Önskat Leveransdatum], ?Late?)

So I am really happy it works now and apologize for making this more complicated than it had to be. Haven't been using Cognos for that long so just trying to learn and get better everyday.

[MFGF]

It is joined with time dimension.

Hi,

In that case, you could make your life and your report so much simpler by just using the real date from the time dimension, rather than trying to force your integer value to a date datatype in the report.

Cheers!

MF.