April 25, 2017 at 2:27 pm
Good Day all
Someone from our reporting team reached out to me today trying to write an SSRS report against a cube we have in SSAS.
Long story short he was trying to use a table to call out members that had already been precalculated in the cube. Adding a parent group to the tablix was by default summing these already calculated memebers rendering wrong results.
I directed him to use the AGGREGATE function instead which as i understand it was supposed to grab the member as is directly from the data source.
When we preview the report in SSDT, the report spins forever and we eventually have to kill visual studio only to come back in with the same problem. When i run the preview, i can see the query beginning in SQL profiler but it never comes back as completed. The same thing happens when we deploy the report to report manager (although in this instance i have the option of killing the report render).
I found this link on google (http://blog.datainspirations.com/2011/03/08/retrieving-aggregate-data/) which i assumed would help as it mentioned when using the aggregate() function, one need to add all levels inside an SSAS hierarchy to the table grouping. Well i cant even prove whether this works or not as the report preview fails everytime (and by fails i means spins for an eternity).
Does anyone have any help with this issue?
May 9, 2017 at 1:55 pm
After almost 2 weeks of research i have been able to locate my problem. Someone posted on this forum 5 years again with the EXACT same problem
https://www.sqlservercentral.com/Forums/Topic1246499-17-1.aspx
Long story short it has to do with the generated MDX from BIDS.
THIS FAILS
SELECT
NON EMPTY { [Measures].[Some Metric] }
ON COLUMNS,
NON EMPTY
{
[DimCommonName].[CENTER_COMMON_NAME].[CENTER_COMMON_NAME].ALLMEMBERS *
{[DimDate].[FSCL_YQM].[All]} *
{[DimEmployee].[AD_NAME].[All]} *
{[DimEmployee].[SUP_NAME].[All]},
([DimCommonName].[CENTER_COMMON_NAME].[CENTER_COMMON_NAME].ALLMEMBERS *
[DimDate].[FSCL_YQM].[Month NM].ALLMEMBERS *
[DimEmployee].[AD_NAME].[AD_NAME].ALLMEMBERS *
[DimEmployee].[SUP_NAME].[SUP_NAME].ALLMEMBERS
)
} DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM (
SELECT ( STRTOSET(@DimDateFSCLYR, CONSTRAINED) ) ON COLUMNS
FROM [cb_BGCO EMP CUBE])
WHERE
( IIF( STRTOSET(@DimDateFSCLYR, CONSTRAINED).Count = 1, STRTOSET(@DimDateFSCLYR, CONSTRAINED), [DimDate].[FSCL_YR].currentmember )
)
AND THIS WORKS
SELECT NON EMPTY
{ [Measures].[Some Metric],
}
ON COLUMNS,
NON EMPTY
{
[DimCommonName].[CENTER_LOC_REPORTING].[CENTER_LOC_REPORTING].ALLMEMBERS *
[DimDate].[FSCL_YQM].[Month NM].ALLMEMBERS *
[DimEmployee].[AD_NAME].[AD_NAME].ALLMEMBERS *
[DimEmployee].[SUP_NAME].[SUP_NAME].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION,
MEMBER_UNIQUE_NAME
ON ROWS
FROM ( SELECT ( { [DimDate].[FSCL_YR].[Year No].&[2017] } ) ON COLUMNS
FROM [cb_BGCO EMP CUBE])
WHERE ( [DimDate].[FSCL_YR].[Year No].&[2017] )
Im not sure if i need to pass this to the SSAS forum but heres the issue. Why is SSRS generating the MDX with Dimension.HierarchyName.ALL? The .ALL part is what is failing my code. I can very easily use the fixed MDX to run the report but when i do that, the Aggregate function returns a blank text box.
In order to get the aggregate function to work, i have to switch the query designer to query mode and once i do that, it repopulates my code with the .ALL.
Any help?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply