如何获取 mdx 中的最新日期?
如果我像这样通过我的 mdx,我会得到我所期望的..
With
Set [TimeLimit] as
'{([CurrDate].[All CurrDate].[2010].[Q3].[Jul].[2010-07-21])}'
select
NON EMPTY {[Measures].[Balance], [Measures].[Peso Equiv]} ON COLUMNS,
NON EMPTY {
[PARTICULARS].[All Particulars],
[PARTICULARS].[All Particulars].[RESIDENTS],
[PARTICULARS].[All Particulars].[NON-RESIDENTS]
} ON ROWS
from [depositlib_22]
where ([TimeLimit])"
问题?:- 我不想硬编码这部分“[2010].[Q3].[Jul].[2010-07-21]”,我需要从我的多维数据集获取日期(多维数据集始终从数据库表获取最新日期)。
让我知道我们怎样才能得到?
请帮助我们解决这个问题,我在过去的两天里一直在尝试......仍然无法找到解决方案。
提前谢谢你...
if i pass my mdx like this i am get what i am expecting..
With
Set [TimeLimit] as
'{([CurrDate].[All CurrDate].[2010].[Q3].[Jul].[2010-07-21])}'
select
NON EMPTY {[Measures].[Balance], [Measures].[Peso Equiv]} ON COLUMNS,
NON EMPTY {
[PARTICULARS].[All Particulars],
[PARTICULARS].[All Particulars].[RESIDENTS],
[PARTICULARS].[All Particulars].[NON-RESIDENTS]
} ON ROWS
from [depositlib_22]
where ([TimeLimit])"
Question?:- i dont want hardcode this part "[2010].[Q3].[Jul].[2010-07-21]",i need to get date from my cube(cube always geting latest date from database table).
let me know any one how we can possible to get?
pls,help us on this i am trying past 2 days...still unable to fondout solution.
Thanking u in advace...
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
在 MDX 中,有两种方法可以处理此类日期问题。一种是使用 VBA 日期函数来使用所需的日期构建字符串。如果您使该字符串与维度中的成员名称匹配,则可以将其转换为集合。请参阅 Sql Analysis Services Current Date
第二种方式,我认为您需要查看,就是使用 MDX 函数找出 [CurrDate] 维度中最后一个(即最新的)成员。然后您可以在查询中使用该成员。只要您的维度仅包含过去的日期,这就可以正常工作。我曾经实现过这个想法,然后偶然发现 2015 年的未来日期悄悄出现在立方体中,然后我的 MDX 没有显示今天的日期(通常是最新的成员)。
您需要的套件可能是:
{[CurrDate].[Date].members.Item([CurrDate].[Date].members.count-1)}
或者同样不优雅:
{
[CurrDate].lastChild.lastChild.lastChild.lastChild.lastChild}
You have two ways to deal with date issues like this in MDX. One is to use a VBA date function to build a string using the date you want. If you make this string match the member name in your dimension you can then convert it into a set. See Sql Analysis Services Current Date
The second way, which I think you need to look at, is to use MDX functions to find out which is the last (i.e. most recent) member in your [CurrDate] dimension. You can then use that member in your query. This will work fine as long as your dimension contains only dates in the past. I once implemented this idea and then found a future date of 2015 crept in the cube by accident, and then my MDX didn't show me todays date (which was usually the most recent member).
The set you need might be:
{[CurrDate].[Date].members.Item([CurrDate].[Date].members.count-1)}
Or the equally un-elegant:
{
[CurrDate].lastChild.lastChild.lastChild.lastChild.lastChild}