blocks|key|2559600|text|我将在您的数据集中创建两个计算字段，以分解数据并将其添加到图表中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2559601|第一个字段用于确定数据是当前年份、上一年还是上一年。也许只有0、1或2？此字段将用作图表系列。叫它ENTRY_YEAR吧。|2559602|第二个是日期字段，年份都设置为当前年份。您可以只将第一个字段中的整数相加。这将使您的所有数据标准化为一年时间线。实际上，您不会使用年份--这只是为了在年假时分隔数据。|2559603|=DATEADD("y",+ENTRY_YEAR,+Fields!EntryDate.Value)|CODE|2559604|entityMap^0|D|4|0|16|4|0|0|0|1D|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|U|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|V|8|@$9|W|A|X|B|L]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|N|$]]

I would create a couple of Calculated Fields in your dataset to break up the data and add them to the chart.

The first would be a field that determines which year the data is in - Current, Previous or the Prior. Maybe just 0, 1 or 2? This field would be used as your chart series. Call it ENTRY_YEAR.

The second would be a date field with the years all set to the current year. You could just add the integer from the first field. This will normalize all your data to a single year timeline. You won't actually use the year - that's just to separate the data at the year break.

<code>=DATEADD("y", ENTRY_YEAR, Fields!EntryDate.Value)</code>

blocks|key|2559739|text|我相信有一百万种更优雅的方法可以做到这一点，但以下是我可能采用的方法……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2559740|下面是基于微软提供的NorthWind数据库，所以如果你真的想要，你可以重建它。|2559741|我在这里设置了实际的开始和结束日期值，但是您可以注释掉前几行，然后应用您的SSRS参数。|2559742|因此，首先使用orders表，因为它有一些日期，并连接到一些基本的订单数据，以便我可以看到结果看起来很好，大多数列都没有使用。|2559743|我的数据集是这样的.|2559744|SET+DATEFORMAT+YMD

DECLARE+@startDate+date+=+'1998/04/01'
DECLARE+@endDate+date+=+'1998/07/30'

SELECT+
+++++++e.EmployeeID,+e.FirstName,+e.LastName,+e.Title
+++++++,+o.OrderID,+o.OrderDate
+++++++,+c.CustomerID,+c.CompanyName
+++++++,+CASE+
++++++++++WHEN+(OrderDate+between+@startDate+and+@endDate+)+THEN+'This+Year'+
++++++++++WHEN+(OrderDate+between+dateadd(YYYY,-1,@startDate)+and+dateadd(YYYY,-1,@endDate+))+THEN+'Last+Year'+
++++++++++WHEN+(OrderDate+between+dateadd(YYYY,-2,@startDate)+and+dateadd(YYYY,-2,@endDate+))+THEN+'2+Years+ago'+
+++++++++END+as+YearGroup
+++++++,+MONTH(OrderDate)+AS+OrderMonth
+++++++,+Day(OrderDate)+AS+OrderDay
++++FROM+Employees+e+
+++++++join+Orders+o+on+e.EmployeeID+=+o.EmployeeID
++++++++join+Customers+c+on+o.CustomerID+=+c.CustomerID
++++WHERE+
+++++++(OrderDate+between+@startDate+and+@endDate+)++OR
+++++++(OrderDate+between+dateadd(YYYY,-1,@startDate)+and+dateadd(YYYY,-1,@endDate+))+OR
+++++++(OrderDate+between+dateadd(YYYY,-2,@startDate)+and+dateadd(YYYY,-2,@endDate+))|code-block|syntax|javascript|2559745|SELECT子句中的Case语句检查日期是否属于以下三个组之一|offset|length|style|CODE|2559746|开始日期和结束日期之间的时间间隔相同的日期范围，但减去相同日期范围之间的一年，但减去两年|2559747|计算出的OrderMonth和OrderDay在那里，因为我假设您会想要‘堆叠’这些线，因此，假设6月1日，所有三个组在图表上处于相同的水平位置。**请参阅后面的备注以更改此设置。|2559748|WHERE子句执行类似的测试，以确保我们只返回所需范围内的数据。|2559749|我所做的只是简单地添加一个折线图并设置|2559750|将系列分组添加到[YearGroup]字段中，并将[OrderMonth]和OrderDay添加到CategoryGroup+|2559751|中，我使用OrderID的和作为值。|unordered-list-item|2559752|2559753|**如果要将时间范围表示为一个连续时间，请从类别组中移除OrderMonth和OrderDay，并替换为OrderDate|2559754|得到的图表看起来很糟糕，但这只是数据的问题。|2559755|entityMap^0|0|0|0|0|0|0|0|6|0|0|4|A|F|8|0|0|5|0|0|8|B|P|C|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1G|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1H|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1I|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1J|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1K|8|@$Q|1L|R|1M|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1N|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1O|8|@$Q|1P|R|1Q|S|T]|$Q|1R|R|1S|S|T]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1T|8|@$Q|1U|R|1V|S|T]]|9|@]|A|$]]|$1|10|3|11|5|6|7|1W|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1X|8|@$Q|1Y|R|1Z|S|T]|$Q|20|R|21|S|T]]|9|@]|A|$]]|$1|14|3|15|5|16|7|22|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|23|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|24|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|25|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|26|8|@]|9|@]|A|$]]]|1D|$]]

I'm sure there are a million more elegant ways to do this but here is how I might approach it...

The following is based on the Microsoft supplied NorthWind database so you can recreate it if you really want to...

I've set the actual start and end date values in here but you can comment out the first few lines and then your SSRS parameters will be applied.

So to start with a took the orders table as it had some dates in and joined to some basic order data just so I could see the results looked OK, most of the columns are not used.

My dataset looks like this...

<pre><code>SET DATEFORMAT YMD

DECLARE @startDate date = '1998/04/01'
DECLARE @endDate date = '1998/07/30'

SELECT 
 e.EmployeeID, e.FirstName, e.LastName, e.Title
 , o.OrderID, o.OrderDate
 , c.CustomerID, c.CompanyName
 , CASE 
 WHEN (OrderDate between @startDate and @endDate ) THEN 'This Year' 
 WHEN (OrderDate between dateadd(YYYY,-1,@startDate) and dateadd(YYYY,-1,@endDate )) THEN 'Last Year' 
 WHEN (OrderDate between dateadd(YYYY,-2,@startDate) and dateadd(YYYY,-2,@endDate )) THEN '2 Years ago' 
 END as YearGroup
 , MONTH(OrderDate) AS OrderMonth
 , Day(OrderDate) AS OrderDay
 FROM Employees e 
 join Orders o on e.EmployeeID = o.EmployeeID
 join Customers c on o.CustomerID = c.CustomerID
 WHERE 
 (OrderDate between @startDate and @endDate ) OR
 (OrderDate between dateadd(YYYY,-1,@startDate) and dateadd(YYYY,-1,@endDate )) OR
 (OrderDate between dateadd(YYYY,-2,@startDate) and dateadd(YYYY,-2,@endDate ))
</code></pre>

The Case statement in the <code>SELECT</code> clause checks to see if the dates fall into one of three groups, either 

<ol>
<li>between the start and end dates supplied</li>
<li>between the same date range but minus a year</li>
<li>between the same date range but minus two years</li>
</ol>

The computed <code>OrderMonth</code> and <code>OrderDay</code> are there as I assume you will want to 'stack' the lines so say, 1 June, across all three groups is in the same horizontal position on the chart. ** See notes later for changing this.

The <code>WHERE</code> clause does similar tests to make sure we only return data from the ranges we need.

All I did them was simply add a line chart and set 

<ul>
<li>the Series Groups to the <code>[YearGroup]</code> field</li>
<li>the <code>[OrderMonth]</code> and [OrderDay] to the CategoryGroup </li>
<li>and (for no other reason than I didn't have much else to display) I used the sum of OrderID as the values.</li>
</ul>

** if you want to represent the time range as one continuous time, then remove the OrderMonth and OrderDay from the category groups and replace with OrderDate

The resulting chart looked awful but that was just down to the data.

I have a table that is set up like

<pre><code>SELECT [EntryDate] --Date
 ,[StoreId] --Nvarchar
 ,[PassFailElement] --Int, 1 or 0
</code></pre>

And the SSRS report is set up for the user to input @StartDate and @EndDate to bookend the [EntryDate]s they want to see.

Is there a way to create a line graph that shows the values for [PassFailElement] from @StartDate to @EndDate as the first series, DateAdd(DateInterval.Year,-1,@StartDate) to DateAdd(DateInterval.Year,-1,@EndDate) for the second series, and then two years back for the third series?

SSRS - How do I format SQL data to generate line chart of time series?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一张桌子，它是这样设置的SELECT [EntryDate]        --Date      ,[StoreId]          --Nvarchar      ,[PassFailElement]  --Int, 1 or 0并且为用户设置SSRS报告以输入@StartDate和@EndDate来添加他...

问SSRS -如何格式化SQL数据以生成时间序列折线图？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSRS -如何格式化SQL数据以生成时间序列折线图？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSRS -如何格式化SQL数据以生成时间序列折线图？
EN