Year as a Selectable Parameter

[Former Member]

Hi Guys,

I have the following query to show me the sales per month by country, However at the moment I have the year is hard coded in and I would like to have it as a selectable parameter, I am having a complete brain blank and cannot get it to work, could someone please point me in the right direction.

SELECT * FROM (

SELECT DISTINCT Country, SUM(SalesYTD) AS YTDSales, [MONTH] FROM

(

SELECT DISTINCT T2.Country, SUM(T1.Debit) - SUM(T1.Credit) AS SalesYTD, T1.Shortname, MONTH(T1.RefDate) AS [Month] FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode

WHERE Year(T1.RefDate) = 2011 AND T1.TransType IN ('13','14') and T2.CardType = 'C'

GROUP BY T2.Country, T1.Shortname, MONTH(T1.RefDate))

sYTD

GROUP BY Country,[MONTH]

) Q

PIVOT (SUM([YTDSales]) FOR [Month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Any help gratefully appreciated

Kind regards

Sean

[vasileiosfasolis]

Hi Sean

try this

SELECT * FROM (
SELECT DISTINCT Country, SUM(SalesYTD) AS YTDSales, MONTH FROM
(
SELECT DISTINCT T2.Country, SUM(T1.Debit) - SUM(T1.Credit) AS SalesYTD, T1.Shortname, MONTH(T1.RefDate) AS Month FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode
WHERE Year(T1.RefDate) = '[%0]' AND T1.TransType IN ('13','14') and T2.CardType = 'C'
GROUP BY T2.Country, T1.Shortname, MONTH(T1.RefDate))

sYTD
GROUP BY Country,MONTH
) Q
PIVOT (SUM(YTDSales) FOR Month IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Edited by: Fasolis Vasilios on Dec 8, 2011 1:10 PM

[Former Member]

Hi Fasolis,

Thanks for the post, unfortunately tried that prior to posting and it does not work, also tried it with [%0] as well and it returns the error message 'Conversion failed when converting the varchar value '%0' to data type in'

I just cant see what I am doing wrong, thanks for the effort though, appreciated.

Kind regards

Sean

[vasileiosfasolis]

what is the wrong by typing the above query?

[Former Member]

Hi Fasolis

It returns the error message 'Conversion failed when converting the varchar value '%0' to data type in'

Kind regards

Sean

[vasileiosfasolis]

you have to try with the following

'[%0]'

did you try it?

i suppose you execute it through Report Designer in SBO

Edited by: Fasolis Vasilios on Dec 8, 2011 1:57 PM

[Former Member]

Hi,

Yes tried both ways, returns the same error message.

Regards

Sean

[vasileiosfasolis]

what about this?

declare @date as datetime
set @date= (select top 1 t0.refdate from jdt1 t0 where refdate='[%0]')

SELECT * FROM (
SELECT DISTINCT Country, SUM(SalesYTD) AS YTDSales, MONTH FROM
(
SELECT DISTINCT T2.Country, SUM(T1.Debit) - SUM(T1.Credit) AS SalesYTD, T1.Shortname, MONTH(T1.RefDate) AS Month FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode
WHERE Year(T1.RefDate) = @date AND T1.TransType IN ('13','14') and T2.CardType = 'C'
GROUP BY T2.Country, T1.Shortname, MONTH(T1.RefDate))
 
sYTD
GROUP BY Country,MONTH
) Q
PIVOT (SUM(YTDSales) FOR Month IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

[Former Member]

Hi,

Thanks again for the post, have tried this, and whilst I now have a selectable parameter, it is asking for a full date i.e. ddmmyy (this would only give me data for one particular day) were I would like to base it on a year (yyyy) as per my query, would even settle for two parameters to select a from and to date if necessary, however just the one for the year would be better as I am working on this for use in a dashboard I am creating.

Again, really very many thanks for your efforts it is greatly appreciated.

Kind regards

Sean Martin

[vasileiosfasolis]

what about this?

declare @date as datetime
set @date= (select top 1 year(t0.refdate) from jdt1 t0 where refdate='[%0]')

SELECT * FROM (
SELECT DISTINCT Country, SUM(SalesYTD) AS YTDSales, MONTH FROM
(
SELECT DISTINCT T2.Country, SUM(T1.Debit) - SUM(T1.Credit) AS SalesYTD, T1.Shortname, MONTH(T1.RefDate) AS Month FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode
WHERE T1.RefDate = @date AND T1.TransType IN ('13','14') and T2.CardType = 'C'
GROUP BY T2.Country, T1.Shortname, MONTH(T1.RefDate))
 
sYTD
GROUP BY Country,MONTH
) Q
PIVOT (SUM(YTDSales) FOR Month IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

[Former Member]

Hi Sean,

Year as a Selectable Parameter is a common sense to most users. However, technically it may not be achievable.

I have tried many ways but all failed. You have to allow select a specific date for that year. It is not difficult to use. Only give user instruction one time. That is it.

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thanks very much for the information, would it be possible to use 2 paramaters then in the form of a from date and a to date ? I am glad it was more difficult that I thought it was, thought I was going mad for a little while :O)

Kind regards

Sean

Edited by: Sean Martin on Dec 8, 2011 3:07 PM

[Former Member]

That would be very simple to have two parameter: i.e. @fromdates and @todate.

However, if these two inputs are in the different year, the query will become invalid.

[Former Member]

Thanks again Gordon, could you show me how I would put that into my query ? No sure I know where it would fit into the query.

Many thanks as always.

Regards

Sean

[Former Member]

You may check: