I need a bit of help here. I have been unable to create an 'Month To Date' expression to a single column on SSRS.
I tested the following expression from a similar question in the forum, but it gives me a squiggly line below the variable 'd'
=IIF(Fields!CreateDate.Value >= DateAdd(d,-7,Today()), Sum(Fields!Sales.Value), 0)
If I run it, of course I got an error telling me that 'd' is not declared. ;)
I changed it to ... DateAdd("d",-7,Today()), Sum(Fields!Sales.Value) ... following the example and the squiggly goes below the brackets of "today()" and needless to say it...but still not working.
I tried a Dateadd(mm..Datediff ... and still nothing.
My report has the following columns:
Country | CustomerName | Sales | InvNotProcessed | Open Order | Orders | TotalbyCust
What I need is to show the new MTD sales only in the column named "Sales" while the other three shows the rest of the query, which should be open as some orders may take quite a while to manufacture and invoice. the last column sums the totals of all other columns.
Any help will be much appreciated.
Regards, Eric
Thanks Guys
I solved the issue, but I used a different approach. I added the MTD expression to the Sales Column on my query, like this:
(CASE WHEN Documents.Status = 'Posted' AND Documents.DocType = 'CINV' AND
(
DATEDIFF(MONTH,Documents.CreateDate,GETDATE()) = 0 AND
Documents.CreateDate <= GETDATE())THEN DocumentsLines.TransSell END
) AS Sales,
and then I just used SUM(Fields!Sales.Values) on the reporting services' cell.
Regards,
Eric
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments