December 19, 2013 at 9:18 am
I have a question, we recently started using report models as our data source and develop reports. Previously we used to create stored procedures.
Right now, I need to develop a trend report i.e. I need to add two date range parameters so that users can enter one for current year and other for prior and see the trend.
So for example report layout must be like (original request has many fields)
Customer Period1Sales Period2Sales %Change
I added the two set of data range parameters (i.e. StartDate1, EndDate1, StartDate2 and EndDate2) in query designer. But I’m struck on how to split Sales into Period1 and Period2?
On the report model we have Sales column. I tried to create new field in Query Designer by using Edit Formula, but when I type below formula it throws me error saying "The following character is not valid: :'. I could not find syntax on how to enter parameter name in the formula box.
IF(tblSales InvoiceDate >= Parameter: InvoiceDate AND tblSales InvoiceDate <= Parameter: InvoiceDate2, tblSales SalesAmount, 0)
The above formula is to differentiate Period1 Sales.
Can you please help me out?
Thanks in advance.
December 20, 2013 at 5:19 am
In SSRS you use IIF and not IF. Can you try that?
December 20, 2013 at 7:48 am
Yes, in SSRS expression we use IIF. But when in query designer when creating a dataset, we use IF. Please correct me if i'm wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply