August 2, 2014 at 12:26 pm
Hi to all of you.
Using SSAS DrillThrough Actions i discovered there are any assemblies to help programmers ..
I found this article https://asstoredprocedures.codeplex.com/
Here i read there a lot of very useful SP ...
My silly question is ..'What do i have to do to use them i.e inside a DrillThrough Action or in whatever othet calculated member in MDX ???'
Are they inside in SSAS 2008 R2 Version ???
Or what do i have to do to use them ???
I am not a very expert of .NET Environment...
I.E there are some SP to tell SSAS to customize order columns and names in DrillThrough Actions..
Regards in advance,
August 5, 2014 at 9:05 am
All of the documentation for the the assp project is on their codeplex page.
In answer to your specific question, the only way I know of to change the order of columns in a drillthrough action is to edit the xml of the cube directly. Right click on the cube and choose View Code then find your drillthrough action and change the order accordingly. Be careful in there though :unsure:
August 6, 2014 at 5:10 am
Thank you for your kind reply.
In fact all documentation is in their page..i was so stupid...:-)
I read it and i was able to registry their assembly.
I was also able to change columns order using these ASSP libraries...
I solved all my problems creating a normal ACTION (i read their examples)...
I was able to :
Change Order columns
I can run an action also over a calculated measure not only over a standard measure
I can change the name of the columns with a more friendly name..
I think their very good...
I.E
ASSP.GetCustomDrillthroughMDX(
"[Vendite].[Importo Venduto] as [Importo Venduto]
,NAME([$TessereClienti].[Codice Tessera]) as [Codice Tessera]
,NAME([$TessereClienti].[Codice Tipo Tessera]) as [Tipo Tessera]"
)
There a lot of ASSP useful for generic problems...not only DRILLTRough..
I suggest to you to test them...
Thanks again.
August 8, 2014 at 3:35 am
Well i think they are useful...
A question if you know..
Can i have Actions both (Rowset) and Action Drillthrough ???
In Action (Rowset) can i write everything in CAPTION ???
Can i have multiple Actions(Rowset) with different names in CAPTION ???
Thanks in advance.
August 8, 2014 at 4:12 am
You can have multiple actions on a cube, doing different things. As for the caption, you are limited only by your imagination (and any reserved characters).
The caption can also be MDX (which you specify by setting Caption Is MDX? to True). So you could have something like (pseudo code) "Show drillthrough for "+[Dimension].[Attribute].CURRENTMEMBER
This would show a contextual caption based upon what the user was clicking on.
August 8, 2014 at 4:21 am
Thanks for your kind reply..
Uhm it is very interesting what you are writing to me..
I have a last question...if you are so kind 🙂
I am writing a ACTION(Rowset) like that ..
My trouble is that i receive an error about [Margine Percentuale]
[Margine Percentuale] is a calculated member and it is not included in a Measure Group..
How can i do that ???
Excel when i invoke the action, tell me that [Margine Percentuale] is not present in the cube ..
I tried also writing [Measures] but i receive error ...
Regards in advance.
ASSP.GetCustomDrillthroughMDX(
"[Margine Percentuale] as [Margine Percentuale]
,[Vendite].[Quantita] as [Quantita]
,[Vendite].[Importo Venduto] as [Importo Venduto]
,NAME([$Codice_Filiale].[generated attribute 24]) as [Codice Filiale]
,NAME([$Tipo Documento].[Tipo Documento]) as [Tipo Documento]
,NAME([$DettaglioVendite].[Numero Riferimento]) as [Numero Riferimento]
,NAME([$DettaglioVendite].[Data Riferimento]) as [Data Riferimento]
,NAME([$DettaglioVendite].[Numero Riga]) as [Numero Riga]
,NAME([$Marca_Modello].[SOLO CodiceInterno]) as [Codice Interno]
")
August 8, 2014 at 4:34 am
I'm not 100% sure but if it's anything like KPIs then there are restrictions when it comes to calculated members - this is because MDX is calculated on the fly and Actions/KPIs are part of the larger MDX code.
A way around this I have heard of (but never tried) is to put the calculated member into a SCOPE, thereby tricking SSAS into thinking it's an actual measure, rather than a calculation.
So in your measure group, create an empty measure and call it what you need to. Then your SCOPE statement would be
SCOPE [Measures].[Your Empty Measure];
THIS = Your calculation;
END SCOPE;
There is an alternative to doing this here: http://sqlblog.com/blogs/mosha/archive/2008/09/01/drillthrough-on-calculated-measures.aspx
August 8, 2014 at 4:42 am
Many thanks.
I read it too..
So i will try and i will reply..in this blog...
What numbers of workarounds we need to do...:-D
Thanks again.
I am writing from ITaly and you ???
Have nice holidays if you will do !!!
August 8, 2014 at 8:04 am
I am happy you did good holiday in Italy.
Ok i will write to you about ASSP and calculated measure tests.
Regards.:-)
August 10, 2014 at 4:07 pm
Hi.
I tested both of solutions you suggested me..
Unfortunately both i had problems...:doze:
Probably my fault ..i explain..
1) I created a new measure in my measure group and with SCOPE Statement i calculate my new measure.
If i drag and drop in Excel this new masure Margine Percentuale DrillThrough it works fine..but if i use in an Action...ASSP give me a value that is not correct ..it seems it does not execute SCOPE..i receive the same value with i defined my empty measure ( i have SSRS 2008 R2 STANDARD ..perhaps i wrong to define my empty measure ??? When i create a new measure i need to define it equal to any field of my fact table)..
ASSP.GetCustomDrillthroughMDX("
[Vendite].[Margine Percentuale DrillThrough] AS [Margine Percentuale]
,[Vendite].[Importo Venduto] as [Importo Venduto]
,[Vendite].[Costo Vend Ultimo] as [Costo Venduto]
,[Vendite].[Quantita] as [Quantita]
,NAME([$Marca_Modello].[Codice Interno]) as [Codice Interno]
,NAME([$Codice_Filiale].[generated attribute 24]) as [Codice Filiale]
,NAME([$Tipo Documento].[Tipo Documento]) as [Tipo Documento]
,NAME([$DettaglioVendite].[Numero Riferimento]) as [Numero Riferimento]
,NAME([$DettaglioVendite].[Data Riferimento]) as [Data Riferimento]
,NAME([$DettaglioVendite].[Numero Riga]) as [Numero Riga]" )
2) Also with Mosha's Solution i ha d problems.
When i am in Excel no action appearing when i am over the interested cell...
I think i wrong to define Option in Action...
In Mosha's blog it was like that ...
Measures.CurrentMember IS [Measures].[Margine Percentuale]
[Measures].[Margine Percentuale] is a calculated member ..
Can i use a calculated member in option's Action ????
Sorry for my silly doubts..if you have any ideas , it wuold be appreciate very much 🙂
DRILLTHROUGH MAXROWS 100000 SELECT (" +
ASSP.ASStoredProcs.FindCurrentMembers.CurrentCellAttributes()
+ ") ON 0 FROM [Vendite]
RETURN
[Vendite].[Importo Venduto]
,[Vendite].[Costo Vend Ultimo]
,[Vendite].[Quantita]
,NAME([$Marca_Modello].[SOLO CodiceInterno]) as [Codice Interno]
,NAME([$Codice_Filiale].[generated attribute 24]) as [Codice Filiale]
,NAME([$Tipo Documento].[Tipo Documento]) as [Tipo Documento]
,NAME([$DettaglioVendite].[Numero Riferimento]) as [Numero Riferimento]
,NAME([$DettaglioVendite].[Data Riferimento]) as [Data Riferimento]
,NAME([$DettaglioVendite].[Numero Riga]) as [Numero Riga]
Regards in advance.
August 10, 2014 at 4:17 pm
Hi.
I tested both of solutions but i had probelms with both.
1) I created a new measure and with SCOPE statement i defined calculation.
If i use that new measure in Excel all it is fine..otherwise in action no.
The value i receive..it is not tha value in THIS= statement but the value of souce field with which i defined in measure group..
I have SSRS 2008 R2 perhaps i am wrong to define my empty measures ???
2) Also with Mosha's blog i had problem
In this case , it seems falis Option Action...
I wrote
Measures.CurrentMember IS [Measures].[Margine Percentuale]
where [Measures].[Margine Percentuale] is a calculate measure..
Perhaps i cannot use calculate measure in Action's Options ???
Any ideas it would be appreciate very much...
Sorry for my questions...
Thanks you.:-)
August 11, 2014 at 4:02 am
I have done a little digging and the issue you are having with Mosha's solution (no drill through action showing in excel) is to do with getting the code exactly correct as discussed here: http://asstoredprocedures.codeplex.com/discussions/404064
You're right, as standard Drill through actions cannot use calculated members - the two solutions we have discussed are workarounds. 🙂
August 11, 2014 at 6:46 am
Hi ..
Many thanks for your reply.
I wrote a message to Code Plex staff , will see...
Regards.:-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply