Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]

Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]

Cell Data Security question

I have a cube that needs to filter out data for a group of users based on the company they work for.

I thought about using the following MDX expression in the Cell Data section in the Edit Roles window for this particular cube:

Ancestor([Dim OPCO].CurrentMember,[OPCO]) IS [COMPANY NAME]

The issue is that the users have to select there particular OPCO COMPANY Name in which ever tool they are using otherwise they will only see NA as the value for all measures.

I would like to filter the data somehow so they do not need to select the particular company they are working for. I would then setup different role groups based on the users company.

Is there some other way of accomplishing this type of task using a filter or scope command?

Thanks ahead of time

Steve Fibich

Hi Steve,

Once you have your roles in place, how about using member security to only permit the company users to see the member for their company? (And make this the default member for that particular role - I assume this is possible, but have yet to do it myself!).

An alternative is to have separate cubes for each company, which I can appreciate is a considerable amount of overhead.

Regards,

Will.

|||

Will,

This sounds like a good solution, how do you set the default propertie in the role. I tried the the following MDX but I get an error when I try to open the Cube.

Under default Member for that particular dimension

[Dim OPCO].[Opco Desc].MemberValue =[Company Name]

I am pretty new to MDX so if this statement is incorrect please let me know. Also is there a good place to get actual examples of MDX vs. the description provided through most of the MS MSDN help sites.

Thanks,

Steve

|||

Never mind I just found the MDX GUI Tool ....I am a little slow today.

Thanks for your help.

Cell Calculation for cells that already has values

Hi,

I have the table like this

Jan Feb .. Dec

account 50 20 ...

On the cube i would like to make it using calculated cells and that look like this

account 50 70 ...

Same cells has before.

I have tried a few stuff but i cant put values on top of the ones that are already there, so i get the values in the cell and not the values in the calculated cell.

Funny, though, i have put a constant (1) i the 'AS' part of the calculated cell syntax and it adds the cell value with the constant. But if i try to add with the value time.lag(1) SSAS just ignores the value.

Is there a way to do this by calculated cell?

Thank you

Have you considered simply creating a calculated measure using the YTD() or PeriodsToDate() functions? If you did not want the end users to see the underlying measure you could set it's visible property to false.|||

Hello,

The problem with calculated members is that the user would have to change the query when browsing on the cube.

What i would like is that, for the users, browsing on the this account or any other i could use the same measure. And for that reason i need it to be a calculated cell.

Is there a way to solve this?

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

Hello,

This was a good solution, inline with the other solution in the conversation.

I think that i could try altough it will clash a bit with all my other calculated cell. I did not remember to rename the calculated member to the actual measer name.

With all this i assume that the cell calculation doesn't allow this task.

Thank you

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECT NON EMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ON COLUMNS, NON EMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ON COLUMNS FROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ON COLUMNS FROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ON COLUMNS FROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECTNONEMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ONCOLUMNS, NONEMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ONCOLUMNSFROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ONCOLUMNSFROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ONCOLUMNSFROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ONCOLUMNSFROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

catastrophic failure when refreshing a report from a cube

there is a cube that refreshes each night. in the morning there is an excel pivot table report that refreshes from the cube.it has worked fine for years, but now when i try to refresh it gives me the error "catastrophic failure". Recently i have had to archive the time dimensions of the cube, as the business only want the cube to contain 6 months of data, not 4 years. so i deleted all time dimension members older than 6 months, and deleted all fact data relating to the deleted time dimensions. this seems to be the cause of the issue, but i cant see why. can anyone shed light on this?

What type of query did the Excel pivot table execute? Did the data in the pivot table span more than 6 months of data? Did it have a member of the time dimension selected that is now missing? In general, if you will be purging data from a time dimension and cube, you'll likely want to implement some type of calculated member or named set that refers to the "current" or "most recent" set of time dimension members and have things like Excel pivot table reports refer to these things instead of referring directly to members of the dimension (since they might disappear)...

HTH,

Dave Fackler