0

I have a report in SSRS that I want to create an expression on the row header to pull the next date value from a dataset.

="Week of " + First(Fields!FiscalDate.Value, "ds_FD")

Something similar to this, but it needs to go through the next value and display it for each text box I have.

How can I do that?

Here is the query I used to create the dataset:

SET DATEFIRST 7;

;WITH x AS 
(
  SELECT FiscalDate, rn = ROW_NUMBER() OVER 
    (PARTITION BY FiscalWeekNum ORDER BY FiscalDate)
  FROM dbo.FiscalCalendar
  WHERE FiscalDate >= CONVERT(DATE, GETDATE())
    AND DATEPART(WEEKDAY, FiscalDate) = 2
)
SELECT FiscalDate FROM x WHERE rn = 1;

The report is nothing more than just a table with data from another dataset that does not have an affect on what I'm looking to do.

tsqln00b
  • 355
  • 1
  • 4
  • 20
  • Can you give some more info (or example) about your datasets and report design? – Anup Agrawal Aug 30 '13 at 16:17
  • Can you give me an example of output you would like as that would make it easier to understand and solve your issue? – Anup Agrawal Aug 30 '13 at 17:05
  • I would like to see column headers with the output of: Week of: 9/2013, Week of: 9/9/2013, Week of: 9/16/2013, etc. – tsqln00b Aug 30 '13 at 17:17
  • I am sorry It would be difficult to help if you cannot show what is the data your dataset returns and what is the output you like. Do you have two different datasets 1 for header and 1 for detail? What do you mean by **it needs to go through the next value and display it for each text box I have**? http://stackoverflow.com/questions/how-to-ask – Anup Agrawal Aug 30 '13 at 17:42
  • It's hard to explain without being able to have a conversation. Can we chat? – tsqln00b Aug 30 '13 at 17:48
  • Here is the expression I am trying to write and I am getting an error. It will hopefully give a better idea of what I am trying to do. ="Week of " + DateAdd("d",7,Fields!FiscalDate.Value, "ds_FD") – tsqln00b Sep 03 '13 at 16:31
  • Try this `="Week of " + DateAdd("d",7,Fields!FiscalDate.Value)` – Anup Agrawal Sep 03 '13 at 16:35

1 Answers1

0

="Week of " + DateAdd("d",7,First(Fields!FiscalDate.Value, "ds_FD"))

tsqln00b
  • 355
  • 1
  • 4
  • 20