(long) querry doesn't fit in "query-string" window

hi,

i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...

One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.

But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.

Any solutions?
put it in a stored procedure |||yes of course, but this was not asked by the client.

i tried already to edit the rdl file directly. (i paste my whole query in the xml structure), but then when i ask a report preview via BIDS, the BIDS crashes !!!!!!!!!!!!!!!!

i have no other choice then do the workaround via the stored procedure,
but it is really a bug in microsoft i say, isn't it?

|||

You have another alternative...

Create your query string in embedded code, like this:

Function GetSQL
DIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function

... Now your query string looks like this

= Code.GetSQL()

HTH, and yes it works <g>, and when you're trying to create a dynamic query out of a lot of parameters it's a heck of a lot more maintainable/legible, too,

>L<

"Now" for time dimesion selection

A common requirement we see is to report against a sliding time window.
However, as far as I can tell, SSAS has no native ability to give me the "Now" time dimension member (that is, the year, month, day, ..., dimension member for the current moment in time). Is that correct?

Are there any techniques people have used to derive that info? It seems like something other people must have run in to.

Kevin,

You can use the VB Now() function and then format the result to build a member matching the format you use for the date in your time dimension. Here is an example against "Adventure Works". I had to subtract 900 days from the current time to render a member that exists in the "Date" dimension included with "Adventure Works", but I think you will get the idea.

WITH

MEMBER [Now Formatted]

AS

Format(Now() - 900,"MMMM d, yyyy")

SET [Current Day]

AS

StrToSet("[Date].[Calendar].[Date].[" + [Now Formatted] + "]")

SELECT

{[Current Day] } ON COLUMNS,

{[Measures].[Order Count],[Now Formatted]} ON ROWS

FROM

[Adventure Works]

|||

It is not good style to concatenate the unique or fully qualified name of members.

Another much more elegant way in the AS2005 is to use MemberValue.
MemberValue is a strongly typed value. You shouldn't use awkward formatting.

|||

Vladimir is correct in making the observation that the previous solution I offerred is not optimal. I was posting a "hammer and nails" approach to the question. As Vladimir points out, a better method would be to set the "Member Value" property of your date attribute to be the date from your dimension table. You could then use the following MDX:

WITH

SET [Current Day]

AS

Filter([Date].[Calendar].[Date].Members,

[Date].[Calendar].CurrentMember.MemberValue = (Round(Now()) - 900))

SELECT

{[Current Day] } ON COLUMNS,

{[Measures].[Order Count]} ON ROWS

FROM

[Adventure Works]

HTH,

Steve

|||

There are many ways to make a sliding time member in MDX. It all depends on your business requirements and how you have constructed your cube.

One approach is already presented.

The second way will work if you have a time dimension with members only to the current date. In this case you start at the top of your time dimension and use(MDX) lastchild (from the top member)until you hit the right level and member.

The third approach will work if you have a time dimension that extends across the current date to the full year or an additional year. This is the approach when you have budgets or forcasts involved. In this case you will have to find the last measure that it is not empty. MDX Tail() and Filter() on a measure that shows the current date(like actual sales) will help you here. Make a named set of this Time member.

Mosha and his cowriters have an interesting solution to the third approach in "Fast Track To MDX-Second edition". They are talking about recursion in a chapter in this book.

HTH

Thomas Ivarsson

"Hang" when deploying SSAS solution?

I've noticed that BI Studio seems to 'hang' when I do a Build -> Deploy Solution. The output window will just say "Sending deployment script to the server" and the Deployment Progress window will just churn, with no commands listed. It's been running for nearly ~ 1 hr without feedback - how can I tell if AS is working or not? CPU on the AS instance is idle.

The one workaround I've noticed is to open the database directly in BI Studio, and make the changes there, but this is obviously less than ideal for multiple reasons.

hello,

i'd suggest to check what's the deployment server specified in your project and whether it's as expected. right click on the project node in solution explorer, and go to Properties->Deployment, Target->Server.

also, it should be possible to use SQL Server Profiler to start a trace for the specific AS server, and see what happens on the server when you do a Build->Deploy Solution...

hope this helps,

|||The target server is just "localhost" (which is correct). When I put profiler on it, I can see the large XMLA deployment script, but it doesn't reveal anything about the current status..|||Ok. I created a fresh, new, import from 9.0 project, and made my changes there. Now when I tried to deploy it actually looks like it's doing something (i.e. there's activity in profiler, and the deployment progress tab actually shows what dimensions/measure groups are being processed..)

Very weird.|||Restart the Analysis Services Service on the target deployment server (if possible) before you try to deploy the roject again and see what happens. Seems like something may be hanging up the service when this happens.