CurrentMember and a set

I have an excel sheet that people are using with pivot tables. The users want to be able to select a set of dates, and get a current year measure, along with the corresponding last year measures on a day to day comparison (not day of year, or date vs. date, but a "monday this year vs. monday last year", which is 364 days ago). They want to be able to pick and choose which days go into the set, for instance they want 1/1/cy, 2/1/cy, 2/5/cy and the sum of a measure vs. 1/2/ly, 2/2/ly, and 2/6/ly same measure summed up.

I can do this for a single selection (using ([Calendar].[Date].CurrentMember.Lag.(364), [Measure]), but I don't know how to access a list of items in the calcluated measure when they select a set.

If I could write this in MDX it would be much easier, but they would like to have control over it and do it through Excel (who can blame them?).

Does anyone have any suggestions?

thank you in advance,

John Hennesey

Assuming that you're using AS 2005, which version of Excel is involved (2003 issues different multi-select MDX queries than 2007)?|||

Sorry I left that detail out. I am using Excel 2003.

Thanks in advance,

John

|||

Hi John,

With Excel 2003, you should be able to use the "Existing" approach, discussed in Mosha's blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

...

But obviously we wanted AS2005 to work well with existing client tools too. Therefore, AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

...

>>

So, in your scenario, the calculated measure MDX expression could be like:

Aggregate(Generate(Existing [Calendar].[Date].[Date], {[Calendar].[Date].Lag(364)}), [Measure])

|||

Awesome - thank you very much for the response. I will give it a shot. One question - when it iterates through the currently selected set, how will it know which ones are selected? Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember? If this is a dumb question, please forgive me - I'm still fairly new to MDX.

Once again, thanks!

John

|||

"when it iterates through the currently selected set, how will it know which ones are selected?" - Mosha's blog entry above explains this better than I could.

"Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember" - no, this is the [Date] level of the [Calendar].[Date] hierarchy, and is shorthand for [Calendar].[Date].[Date].Members, which you can use instead. This all applies if you're using AS 2005, of course.

|||Cool - I will dig into Mosha's blog. Thank you for your quick response!

current users

Is there a limit on how many concurrent users that can use SQL Server 2005 Express Edition?

I haven't been able to find any facts about that

Hi,

no there isn′t and there never has been. MSDE either didn′t havelimited concurrent users, the workload governor only throttled the query performance for more than 5 users.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is correct, we do not limit the number of concurrent users in SQL Express. We have also removed the workload governer in SQL Express as well. Check out Euan Garden's Blog for a historical discussion on the workload governer in MSDE.

http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx

Mike - SQL Express team

sql

Cube usage by users

Hi,

What would be the correct way to generate some kind of log or report that will tell me which user used which cube in which database along with the number of times the user connected to each cube each day? The number of queries sent by each user to each cube by day would be ok. I am not interested by the query content for this exercise. I just want Userid, DB name, Cube name and Hits.

Then I can take it from there and create a report for our security/sox guy with User name, Cube name, Week of year and number of hits by week, meaning if the users send 36 queries to Cube A on Monday and 2 queries on Friday, it counts for 2 cube accesses for that week.

This assume that I use only TCP/IP access but if the same mechanism would work for HTTP access that would be fine. I try not to use HTTP but I may be forced to do it.

Regardless of network transport, the only logins allowed are though Windows Domain Global Group Membership.

Seems simple but I do not find where to start.

Thanks,

Philippe

Hello,

I have found a solution to this issue.

In the Server property box, you will enable query loging to a table and set the interval to 1 instead of 10.

Stop and Start the AS Server

Create some query or cube to access your log data, you are done.

I cannot really provide a query example since it depends on your situation. Mine is linked to a Calendar and to a Users tables. I convert the query time to keep only the day and the username to keep only the userid, then I build some Cube and/or Report that joins all that stuff and do some DistinctCount on the daily access by Cube by User and voila. I keep my infosec guy happy and I know what are the cubes I can retire for cause of no use.

You know, sometimes they ask you to build a cube real quick, they use it once and then they forget about it (and do not tell you). So I am not maintaining useless cubes :-)

Regards,

Philippe

CSV in EXCEL = Users not happy

I am rolling out reporting services at my company and so far, everything has
gone smooth. The main problem for users testing reports has been that when
they open a CSV file in EXCEL, it opens up with everything in the "A" column.
Our current reporting platform does not have this problem and the users want
to keep using the data in this format (CSV files in Excel).
What I would like to do is to modify the CSV rendering format in order for
it to put the CSV files straight out in ASCII format. Does anyone know if
this is possible or if there is a better workaround?First, to explain what is happening. The default rendering of CSV is Unicode
which excel doesn't know what to do with and so it puts it all in the same
column. Here is a workaround. Add a textbox at the top of the report (I call
it Export Data), change to blue and underlined. Then you want to do a jump
to URL. (right mouse click, properties, advanced properties, navigation tab)
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
RS 2005 will allow you to modify the config file so it defaults to rendering
in ASCII so this problem will go away. RS 2005 will be released in November.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:3AF6B09C-1FAC-48B5-A6A0-9024ABC586D5@.microsoft.com...
>I am rolling out reporting services at my company and so far, everything
>has
> gone smooth. The main problem for users testing reports has been that when
> they open a CSV file in EXCEL, it opens up with everything in the "A"
> column.
> Our current reporting platform does not have this problem and the users
> want
> to keep using the data in this format (CSV files in Excel).
> What I would like to do is to modify the CSV rendering format in order for
> it to put the CSV files straight out in ASCII format. Does anyone know if
> this is possible or if there is a better workaround?