Home > Default > SSRS Report date parameter values using SSAS Cube as datasource

SSRS Report date parameter values using SSAS Cube as datasource

October 11Hits:2
Advertisement
Hey Guys, 
i m building an SSRS report using SSAS cube as the data source. The report contains shared data-sets which provide the required data. below is the set up.
Sql server version : Sql server 2008R2
Report builder 3.0 
I have, Report A, powered by Dataset D, running on Cube C. The dataset has 3 parameters out of which one of them is a date parameter- P1. I have it set up such that on the report there is  another parameter - P2 which contains the type as Date ( to
ensure the user is exposed to a date picker), the input from this, P2 is then manipulated to required format before fed into the dataset parameter P1 (hidden). I have two questions regarding this parameter.
Currently, when the user pick's a date (P2) which has no values, the report errors out as it doesn't find that corresponding member on the cube. Can it be made such that if the member doesn't exists, a simple error message like "date not found"
pops up as opposed to report failing?
Secondly, can i manipulate the date picker (P2) exposed to the user such that the unavailable dates are grey'ed out on the date parameter (P2)? 
Please let me know if there is any more info required on each of the questions.
Thanks
Srikanth

Answers

Hello Katherine,
below are the errors which pop up with the 'Constrained' function in place and without. The MDX query is what follows.
Without: 
An Error has occurred during report processing. (rsProcessingAborted). The Execution Failed for the shared data set “Dataset1”.(rsDataSetExecutionError). Query Execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand). The dimension ‘[10 Sep 2014]’
was not found in the cube when the string, [10 Sep 2014], was parsed.
With Constrained flag:
An Error has occurred during report processing. (rsProcessingAborted). The Execution Failed for the shared data set “Dataset1”.(rsDataSetExecutionError). Query Execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand). Query(1,1476) The restrictions
imposed by the CONSTRAINED flag in the STRTOSET function were violated.
SELECT
NON EMPTY
[Measures].[A]
,[Measures].[B]
,[Measures].[C]
,[Measures].[D]
,[Measures].[E]
} ON COLUMNS
,NON EMPTY
[DimA].[LevelA].[LevelA].ALLMEMBERS*
[DimB].[LevelB].[LevelB].ALLMEMBERS*
[Date].[Date].[Date].ALLMEMBERS
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM
SELECT
StrToSet (@FilterA ,CONSTRAINED) ON COLUMNS
FROM
SELECT
StrToSet(@Date, CONSTRAINED) ON COLUMNS
FROM [Cube1]
WHERE
[DimC].[Level1].&[Member1]
,[DimC].[Level2].&[Member1]
,[DimC].[Level3].&[Member1]
,[DimC].[Level4].&[Member1]
Thanks
Srikanth

Read other 3 answers

Tags:

Related Articles

  • SSRS Report date parameter values using SSAS Cube as datasourceOctober 11

    Hey Guys,  i m building an SSRS report using SSAS cube as the data source. The report contains shared data-sets which provide the required data. below is the set up. Sql server version : Sql server 2008R2 Report builder 3.0  I have, Report A, powered

  • Create SSRS report using DMV for querying SSAS cube.October 11

    I am trying to create a SSRS Report to find the Cube/Dimension Status (when was Cube/Dimension last processed and is Failed/Success), for example I have below DMV query for the same. SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES When

  • SSRS Dynamics Date ParameterOctober 11

    Hi, I have an SSRS report with Dates Parameter. it is compsed of FROMDATE and TODATE. The default value of FROMDATE is the current date and TODATE i added plus 1. This reports will get the transaction for Shift 1 and Shift 2. the shift1 start at 6:00

  • SSRS Report Data Caching Problem in Internet ExplorerOctober 11

    HI All, We have setup reporting server in native mode and have deployed SSRS reports on reporting server and mapped it to sharepoint page with report viewer web part. The changes on the data is not getting reflected on the report unless I close the b

  • Crystal Reports Date Parameter Default Value OPtion EmptyNovember 30

    Is there a way to set a default date value for a Date Parameter? It is shown empty, it only allows to set a fixed date, i.e. 10/10/2013. But it doesn´t allow me to use a formula (which has a CurrentDate inside), so I can set, for example, today's dat

  • Crystal Reports Date Parameter ProblemOctober 11

    Hello Experts, I have this query in crystal, I need it to be suming the Quantity and Totals but because of the date optional its not doing that, How  I put the date parameter and remove it from her so that I can achieve my results. SELECT T0.[DocDate

  • Error trying to create a Power View report against a Multi Dimensional SSAS cubeOctober 11

    Hi all, We have installed the Power View For Multidimensional Models CTP, released last November 27 on our Analysis Server instances.  I am now trying to create a Power View report in SharePoint that is connected to a Multi-Dimensional cube. I have f

  • SSRS report connecting SSAS 2014 cube not returning measure valueOctober 11

    Hi all Recently we migrated SSAS from 2008r2 to 2014 in Dev environment. when we are testing  SSRS reports which are connecting to that cube is having issue. the measure value is returning as [Measures].[LSM Count] instead of numeric value. when we c

  • SSRS Expression Current month in a Fiscal calendar dimension from a SSAS cubeOctober 11

    How to create an expression for a default SSRS parameter to a SSAS cube when using i Fiscal Year calendar - and where I want to show current Calendar month and year (for example February 2014)  This is an example of my Time dimension for February 201

  • Ssrs 2008 r2 date parameter not working all the timeOctober 11

    In an SSRS 2008 r2 report, I am not getting some records selected when I run the report in the ssrs 2008 r2 report. However when I run the sql in ssis manager, the records are selected. Thus I am trying to determine why some of the records are not se

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.694 s.