Sql server 2016 ?

Topics: Developer Forum, User Forum
Oct 5, 2016 at 10:12 AM
Tried registrering the dll on 2016 but i get error. Doesnt the 2014 version Work for 2016 ?

TEXT
Coordinator
Oct 5, 2016 at 7:19 PM
You could try the beta 1.4 release for 2016 https://asstoredprocedures.codeplex.com/releases/view/625191
Oct 10, 2016 at 9:46 AM
tx for input and link

however when i try installation procedure like this i still get same error:
  • unblock file
  • management studie new assembly, unrestreticted, default
Here error Clr Assembly must have main file speicied still comes

Any ideas ?
Oct 10, 2016 at 9:50 AM
should i try including it in project insteasd of on as server ? i only used the project in one solution ?

do i need to registrer dll manually but it doesnt say so in installaiton procedure
Oct 10, 2016 at 9:53 AM
I'm on build 13.0.2164.0 that should be the latest 2016 version
Coordinator
Oct 11, 2016 at 10:22 PM
Hmm, I'm getting this too now. It looks to me like it might be a bug in SSMS. Have you tried running the XMLA script version of ASSP? That's what I've been using and that installs fine for me at the server level.
Oct 13, 2016 at 3:28 PM
The XMLA works and i can see the ASSP as assembly after but my custom drillthrough doenst work

TEXT
Oct 13, 2016 at 3:29 PM
It works on 2014
Coordinator
Oct 13, 2016 at 7:38 PM
When you say it works in 2014 do you mean that you took a backup of the 2014 databaes, then restored it on 2016 and it stopped working?

From the error message it looks like ASSP generated a valid drillthrough statement, but it's just been incorrectly configured to point at a calculated measure instead of a physical one.
Oct 13, 2016 at 7:42 PM
yes i took backup and restored and didnt work

the action shows in excel on the calculated measure like in 2014 and the whole idead of the drillthrough in the stored procedure is that it is possible aalso through calculate measures where u control the slice in the action syntax

so i use it a lot with calculated measures - and worked before
Coordinator
Oct 13, 2016 at 8:01 PM
So are you using v1.4.0 in 2014 or an older version? (you can run CALL ASSP.AssemblyVersion() to get this information)

We made one change recently to the drillthrough functions, but that was just to exclude redundant all member references so it should not have affected the measure. You could try adding false as the last parameter to the getcustomdrillthroughmdx function as this will disable this new behaviour and see if that makes a difference
Oct 13, 2016 at 8:04 PM
tx for input

ill try tomorrow once im at client again (Danish foreign miinistry)
Coordinator
Oct 14, 2016 at 12:44 AM
Ok I found the issue, the problem is that we added a new single parameter overload to the drillthrough functions. So there is now 2 ways of calling these functions with a single parameter:

ASSP.GetDefaultDrillthroughMDX( bool skipDefaultMembers) << New
ASSP.GetDefaultDrillthroughMDX( Tuple tuple)

and the problem is that when you pass a tuple SSAS does an implied cast to a numeric value and then it treats 0 as false and <>0 as true and only ever hits the first overload so it's not passing through the non-calculated measure reference. The quick fix is to just add a true parameter after the tuple reference, then you will hit the following overload and everything will work as it used to

ASSP.GetDefaultDrillthroughMDX( Tuple tuple, bool skipDefaultMembers)

I think we might just have to remove the version of the function call with a single bool parameter. It was only included for backward compatibility so that people could trigger the old behaviour if the new code caused an issue.
Oct 20, 2016 at 9:46 AM
I'm not using hte getdefaultdrillthrough - im using combination of the getcustomdrillthrough and executedrillthroughandfixcolumns

'call ASSP.ExecuteDrillthroughAndFixColumns("'+
ASSP.GetCustomDrillthroughMDX(
"membervalue ([$FinanceDrillthrough].[DocumentDate]),
NAME ([$FinanceDrillthrough].[DocumentNo]),
NAME ([$FinanceDrillthrough].[Description]),
[Finance].[Amount],
NAME ([$Budget group].[Subcompany code]),
NAME ([$LDV Standard specification].[Standard specification]),
NAME ([$LDV Job].[LDV Job Description]),
NAME ([$LDV Delregnskab].[LDV Delregnskab]),
NAME ([$Frame].[Frame responsible]),
NAME ([$Account].[Account]),
NAME ([$Budget].[Job]),
NAME ([$FinanceDrillthrough].[MDS Delregnskabs Kode]),
NAME ([$Report Hierarchies].[Danida File No])
",
[Accountgroup].[Commitment Flag].&[True])+'")'

How can i change this aNd makE it workd +
Coordinator
Oct 20, 2016 at 11:31 AM
GetCustomDrillthrough uses the same internal logic as GetDefaultDrillthroughMDX. However this should be fixed in the 1.4.2 release. Have you tried that?
Oct 20, 2016 at 12:34 PM
I still had to use the XMLA version but then it worked, Darren

Tx a lot !
Dec 19, 2016 at 12:12 PM
After deploying some of them fails and i have no clue why they are called differently

This simple one

'call ASSP.ExecuteDrillthroughAndFixColumns("'+
ASSP.GetCustomDrillthroughMDX(
"Name([$Date].[Date_key]),
[Frame].[Disbursement frame],
NAME ([$Frame].[Frame responsible]),
NAME([$Frame].[RAM Description])")+'")'

Fails. And this Works

'call ASSP.ExecuteDrillthroughAndFixColumns("'+
ASSP.GetCustomDrillthroughMDX(
"membervalue ([$Date].[Dato DK]),
NAME ([$FinanceDrillthrough].[DocumentNo]),
NAME ([$FinanceDrillthrough].[Description]),
NAME ([$Budget group].[Subcompany code]),
NAME ([$LDV Standard specification].[Standard specification]),
NAME ([$LDV Job].[LDV Job Description]),
NAME ([$LDV Delregnskab].[LDV Delregnskab]),
NAME ([$Frame].[Frame responsible]),
NAME ([$Account].[Account]),
NAME ([$Budget].[Job]),
[Finance].[Amount],
NAME ([$FinanceDrillthrough].[Currency Code]),
[Finance].[Currency Amount],
NAME ([$FinanceDrillthrough].[MDS Delregnskabs Kode]),
NAME ([$Report Hierarchies].[Danida File No])
",
[Accountgroup].[Disbursement Flag].&[True])+'")'

When i make trace i see that the first one puts the calculated measure into the drillthrough statement (the one that i click on)

call ASSP.ExecuteDrillthroughAndFixColumns("drillthrough select (([Date].[Date].[Year].&[2016],[Date].[Year].&[2016],[Measures].[Disbursement frame (DKK)],[Report Hierarchies].[Administrative type].&[Commitment],[Report Hierarchies].[Budget group].&[06.32 - Bilateral Assistance],[Report Hierarchies].[Budget section].&[06.32.01 - Developing Countries in Africa],[Report Hierarchies].[Budget Sub Section].&[06.32.01.05 - Ethiopia],[Report Hierarchies].[FL Responsible unit current].&[2026 - Addis Ababa],[Report Hierarchies].[STRAM].[Budget sub section].&[06.32.01.05 - Ethiopia],[Report Hierarchies].[STRAM S].[Budget sub section].&[06.32.01.05 - Ethiopia].&[2026 - Addis Ababa],[Report Hierarchies].[STRAM S Finanslov].[Budget sub section].&[06.32.01.05 - Ethiopia],[Report Hierarchies].[STRAM S_old].[Budget sub section].&[06.32.01.05 - Ethiopia],[Report Hierarchies].[STRAM x].[FL Responsible unit current].&[2026 - Addis Ababa].&[06.32.01.05 - Ethiopia],[Report Hierarchies].[X].&[Not X])) on 0 from [TILSKUD] return Name([$Date].[Date_key]),
[Frame].[Disbursement frame],
NAME ([$Frame].[Frame responsible]),
NAME([$Frame].[RAM Description])")

I have no clue why it tries to drillthrough [Measures].[Disbursement frame (DKK)] because its not mentioned in the code above ?

/m
Coordinator
Apr 4 at 12:54 AM
Sorry I missed this post.

What measure do you want to drillthrough on? Is that Disbursement measure your default measure? What measure are you right clicking on to launch this action?

You can override the measure by specifying it in the second parameter of GetCustomDrillthroughMDX. In your example replace [Accountgroup].[Disbursement Flag].&[True] with ([Accountgroup].[Disbursement Flag].&[True], [Measures].[Drillthrough Measure Here])
Apr 5 at 12:35 PM
Sorry for the bad screenshot

This is the Excel error

https://dl.dropboxusercontent.com/u/106184319/error.PNG

And here is the action setup:

https://dl.dropboxusercontent.com/u/106184319/error1.PNG

Here it shows which measure and as iunderstand it doesnt have to be default - it can be any calculated measure - and it Works for a lot others.

In the last screenshotted example i didnt pick a measure - only 1 column - is it necessary to pick one ? Through it would just take the columns chosen inside the drillthrough statement ?

/m
Coordinator
Apr 6 at 1:00 AM
In the Condition you can mention a calculated measure.

But if you do that you need to specify a physical measure as the second parameter of GetCustomDrillthroughMDX. Make sense?
Apr 6 at 9:07 AM
Thought i did.

Made it like this but still error

https://dl.dropboxusercontent.com/u/106184319/error2.PNG

Also tried having [Measures].[MissingRegistrationx] inside "" as column 2 ?
Coordinator
Apr 6 at 11:49 PM
Hmm. If [Measures].[MissingRegistrationx] is a physical measure it should work. Do you have any other calculated members on dimensions?
Thu at 1:51 PM
No other and [Mesures].[MissingRegistrationx] is physical, really have no idea why some Work and others dont

Can i trace more myself ?
Thu at 1:55 PM
Ah, MissingREgistrationx is a distinct Count physical measure. Maybe thats why.
Thu at 2:09 PM
Now i know more

ASSP.GetCustomDrillthroughMDX(
"[UMF Provisions].[Beløb]"
,
[Accountgroup].[Disbursement Flag].[All])

If i add the ,[Accountgroup].[Disbursement Flag].[All] which is a nonsense slice the other ones that failed will Work - so it seems that , plus slice is necessary for it to work