Stored Procedure Macro - Use Query Item As Default

[MadSween]

Hi All,

I am having an issue in FM using a macro to feed a meaningful default value to a stored procedure. The idea is that on 1st run a report uses the current week by default so that there is not a prompt page but allows users the ability to pick any week they want using a prompt in RS.

The macro below works fine but as you can see week 5 is hard coded as the default.

prompt('WKParam','int32','5')#

In the real world that won't cut it so I want to be able to feed in a Query Item which hold the current week value as the default but retain the ability for a user to re run the report with a different value if needed. I have tried loads of variations of the macro below but no luck..

#prompt('WKParam','int32','[Database View].[CurrentWeek].[CurrWeek]')#

Is this possible or am I missing something here?

Thanks in advance
MadSween

[CognosPaul]

Without knowing which database you're using, you could try one of the following:

#prompt('WKParam','int32','_week_of_year(current_date)')#')#
#prompt('WKParam','int32','floor(_day_of_year (current_date)/7)
#prompt('WKParam','int32','DATEPART( {wk}, getdate())')#
#prompt('WKParam','int32','to_char(sysdate,''WW'')')#
#prompt('WKParam','int32','datepart("ww",date(),vbSunday,vbFirstFullWeek)')#
#prompt('WKParam','int32','Int(((Today() - DateSerial(Year(Today()), 1, 0)) + 6) / 7)')#

Some of them are database specific. Hopefully at least one of them should work with what you have.

[MadSween]

Thanks for the reply. I was hoping to use a query item from our dates query subject as its already in FM but these look like they could do the trick by hitting the db..

Were are using 8.4 over SQL 2008 by the way.

Many Thanks
Mark