Cognos 8.4.1 sql server 2008 r2 custom function

Hi
I write a sql function and publish to Cognos via Framework manager. I am not sure anyone here can help?

It does not work.

The function shown in Congos Report studio:
"OutputDate;0" (MyDate)
The message say it is not working.

--my sql server function code
create FUNCTION [dbo].[OutputDate]
(
      -- Add the parameters for the function here      
      @INPUTDATE DATETIME
)
RETURNS DateTime
AS
BEGIN
      DECLARE @MYDATE DATETIME
      if (@INPUTDATE is null)
      BEGIN
            SELECT @MYDATE = GETDATE()
      END
      if (Year(@INPUTDATE) = 1900)
      BEGIN
            SELECT @MYDATE = GETDATE()
      END
      else
      BEGIN
            SELECT @MYDATE = @INPUTDATE
      END      
      -- Return the result of the function
      RETURN @MYDATE

END


GO
power98888Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DALSOMCommented:
Hi,
Why you dont use a cognos dataitem? and use the expression :

case when ((year([myfield]) <=1900)or([myfield]is missing)) then cast(#$current_timestamp# ,date)
else
[myfield]
end

Remember, that you must have on currentdate field with your current date, or like in this example, a cast of the timestamp variable to date.

Hope this help!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
power98888Author Commented:
My question is related to the cusom function from SQL SERVER to Cognos Report studio.

It does not really involve with DataItem because the function can be use in the filter as well.

DALSOM's answer is not exactly answer my question.
0
DALSOMCommented:
Hi,
Sorry, It won.

I will check again at my work cognos installation, how to use your not null or empty date function from both framework and Report Studio, directly from a filter, in order you can use in a filter, or like an item or singlenton.

But still I think you can format your date from your datawarehouse into a new field, and then use this one instead the old one, or in a second try, use my first comment sugestion.

Please, tell me how you will use this function, maybe it help to get better your idea. I think you try to do this :

(mysqlfunc([mypackage].[MyDim].[myHyr].[myDate].[myDateYear]))=[mypackage].[...]. myotherfieldDate

Please, show me!

I will try them both app and, if get an Answer, I will post it.
Bye,
Dalsom.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.