蒙德里安的日期范围
我想获取时间戳 t1 到 t2 的数据。但 t1 和 t2 可能不一定在我的数据库表中。所以，我想要的是：它应该找到大于或等于 t1 的下一个可用时间戳（如果 t1 不存在于数据库表中，则 > t1 或 t1 如果存在），最后一个可用时间戳小于或等于 t2 （ < t2（如果 t2 不存在，则为 t2）；如果 t2 在 MDX 查询本身中存在，则为 t2。
.FirstSibling、.LastSibling、.FirstChild、.LastChild、.NextMember、.LastMember、HEAD、TAIL 对我不起作用，因为我希望时间戳为 >= t1 和 <= t2 （两者都在数据库中可用）。 我该怎么做呢？
I want to fetch data for TimeStamp t1 to t2. But t1 and t2 might not necessarily in my database table. So, what i want is: it should find next available timestamp greater than or equal to t1 ( > t1 in case t1 is not there in db table or t1 if it is there) and last available timestamp less than or equals to t2 ( < t2 if t2 is not there or t2 if it is there in db) in MDX query itself.
.FirstSibling, .LastSibling, .FirstChild, .LastChild, .NextMember, .LastMember, HEAD, TAIL won't work for me as i want timestamp be to >= t1 and <= t2 (with both available in db).
How should i do it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用 MDX 以这种方式处理日期可能会很痛苦。我过去做过类似的事情，将
Name或Caption转换为字符串/数字/日期，并将其与输入参数 t1 和 t2 进行比较（类似地转换为字符串/数字/日期，格式相同）。也许 t1 是字符串“31/12/2010”，并且您的多维数据集包含 [Time].[All Time].[2010].[December].[29] 等成员那么您需要将两者转换为通用格式才能进行比较。 MDX 提供了诸如
MemberToStr()之类的方法，它将为您提供.CurrentMember的字符串表示形式，并让您比较两者。VBA 函数可用于某些 MDX 风格，因此您可以使用
Replace和InStr操作字符串，甚至可以调用CDate()等函数> 获取与输入参数形式相同的值。最终的 MDX 可能看起来像这样...尽管我已经大大简化了将成员的标题转换为 CDate 函数将接受的字符串所需的操作：
选择{Filter({[Time].[Day].members}, CDate([Time].CurrentMember.Caption) <= CDate("2010-12-31"))} ON ROWS,
[措施]。[某事] 列
FROM [CubeName]
ROWS 子句首先获取 [Day] 级别的所有 [Time] 成员的集合，然后对该集合进行过滤，仅留下 Caption（转换为日期对象）所在的那些成员。小于或等于您的输入字符串（转换为日期对象）。
Fiddling with dates in this way with MDX can be a pain. I have done similar things in the past by converting a
NameorCaptionto a string/number/date, and comparing it with your input parameters t1 and t2 (similarly converted to string/number/date in same format).Perhaps t1 is a string "31/12/2010" and your cube contains members such as [Time].[All Time].[2010].[December].[29] then you will need to convert both into a common format to be compared. MDX provides methods such as
MemberToStr()which will give you a string representation of the.CurrentMemberand let you compare the two.VBA functions can be used in some flavours of MDX, so you can manipulate strings with
ReplaceandInStr, and even call functions such asCDate()to get a value which is in the same form as your input parameter.You final MDX might look something like this...though I have greatly simplified the manipulation required to get the member's Caption into a string that the CDate function will accept:
SELECT{Filter({[Time].[Day].members}, CDate([Time].CurrentMember.Caption) <= CDate("2010-12-31"))} ON ROWS,
[Measures].[Something] ON COLUMNS
FROM [CubeName]
The ROWS clause is first taking a set of all [Time] members at the [Day] level, and then filtering down that set to leave only those where the Caption (converted to a date object) is less than or equal to your input string (converted to a date object).