Month's end

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work?

http://msdn2.microsoft.com/en-us/library/ms175623.aspx

>>

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property

Definition

AggregateFunction

Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

>>

|||Or, depending on your data, you may want to use more optimal LastChild aggregation function.sql

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi,

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

<Culture>de-DE</Culture>

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Monster Query Assistance

I realize this query is inherently incorrect, but my issue is mainly

syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong.

I want to ensure that a.order_id is not in the settlement table. So I

was thinking something along the lines of "WHEN a.order_id not in

(select order_id from settlement)" which I know will cause a slower

response time, but I'm willing to deal with it. In any case, that

syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!

Keep in mind that the effect of the CASE structure is to return a value, in this case a date value. That value is then place inside these parentheses.

Can you spot the syntax problem simply by replacing the entire CASE (inside the parens) with a date value?

and ( CASE
WHEN a.order_id <> b.order_id THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

|||Erm. I'm sure I don't follow you. used_date will be a.transaction_date if a.order_id is not b.order_id. Correct? Are you saying that my syntax is incorrect inside of the case statement? If it is, I just don't see where.

Also, can I simply use

and ( CASE
WHEN a.order_id not in (select order_id from settlement) THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

?|||

This statement makes no sense: (the substituted date value is immaterial)

and ( '01/01/2007' ) used_date datediff(d,used_date, ".$cutOffDate.") < 30)

|||Hrm, so you're saying that I need some sort of separator. I tried parentheses, but those did not work. To quote your example:

and (( CASE ... END) used_date) datediff(d,used_date, ".$cutOffDate.") < 30)

I also tried adding a comma after "used_date)" and just after used_date and removed the parentheses. Can you simply not nest cases in this way or am I completely destroying the query?|||

You can next CASE structures, BUT each nested level MUST be a syntactically complete CASE structure.

I have not attempted to work out your structure, you have not provided any sample data, etc. (See: http://www.aspfaq.com/5006)

I am attempting to point out that (( CASE ... END) used_date) seems meaningless. There is no evaluation, assignment, or other indication about how these values are to be used. And then somehow there is this magical continuation of the line into the datediff function. I don't get what you are attempting with this line.

I suggest that you separately TEST each CASE structure, determining the return value, and then add that value to the outer CASE structure. ONLY when the entire CASE structure works with those substituted values, then you can carefully replace the substituted value with the CASE structure that derived that value.

|||Awesome. Yeah, all I needed to do was remove the "used_date" from that. I had assumed that I needed to alias out the result of the case statement and use the alias in the query later on. I guess I just have to constantly use the same case statement. Oh well, still works so I'm happy.

Thanks much for your help!

Monitoring Tools.

All, I seek your knowldege. I am looking for a monitoring tool that
has the abilility to page and email me should something go wrong with
SQL server. I know of two such tools: 1.) BMC Patrol, and 2.) Quest's
Foglight. I was hoping that some of you have had some experience with
other such tools. Let me know please.

LouisTSQL is also pretty good when
linked with xp_sendmail.

"Louis Frolio" <froliol@.yahoo.com> wrote in message
news:94c28610.0401132001.73fc6a4e@.posting.google.c om...
> All, I seek your knowldege. I am looking for a monitoring tool that
> has the abilility to page and email me should something go wrong with
> SQL server. I know of two such tools: 1.) BMC Patrol, and 2.) Quest's
> Foglight. I was hoping that some of you have had some experience with
> other such tools. Let me know please.
> Louis