DAX based SSRS reports with parameter based upon another parameter

There could be a situation where we need to have a parameter based on the choice made in some other parameter in the SSRS report.

It can be implemented using standard queries as well as Data source based on business logic.

Step 1: Create a query “DemoQuery” in DAX. Choose data source as EmplTable. Change the property DYNAMIC on DataSources->EmplTable->Fields node to No. This will enable us to select the fields that are required.

Step 2 : Create another query “DemoQueryRelated” with HRPPartyPositionTableRelationship table  as  the data source .Also add a range on the field reference.

Step 3 : Create a VS 2008 MSDAX Reporting Project. In the solution Explorer open the Report1.moxl that is the model view of the report .Create two Datasets based on the two queries that has been created in DAX. Choose the query and corresponding fields from a list populated with Physical queries from AX by clicking on the ellipses button of “QUERY” property in the property sheet of new dataset. After this process it will be like this :

Step 4: As soon as the datasets are created  it can be observed that a parameter has automatically been added in the parameter list.

Step 5: Create two parameters EmplId & Position and assign them values.

Step 6: Now we have two parameters “EmplId & Position”. Parameter “Position” is dependent on “EmplId”. In order to achieve it the value selected in parameter “EmplId” is passed to the auto created parameter “PositionDS_Reference”. This way a range is being applied on the Query “DemoQueryRelated” in AX. See below.

This way the value chosen in the parameter EmplId is passed to the parameter “PositionDS_Reference”. Also make the Property VISIBLE as Hidden.

Step 7: After getting all these steps done we can add an auto design by dragging the PositionDS dataset onto Design node. After browsing the design we can see parameter list similar to below.

The values available in Position parameter list varies based on the value chosen in Parameter Employee. It can be verified by looking at the respective list. This demo is done with Single-value parameters. The same works with parameters of type Multi-value.

I will talk about the same example with multi-value parameter in a business logic environment.

Njoy……….. J

Dynamics AX 2009 default cubes configuration issues & resolution

Issue-1. OLE DB error: OLE DB or ODBC error: Login failed for domain/machinename 28000; Cannot open database requested by the login. The login failed.; 42000.

Solution -- Give the user access on the OLTP database. In this case user is the machine hosting OLAP DB.

Issue-2. SQL execution error. The multi-part-identifier "dbo.CUSTTABLE.CUSTABLE.CUSTGROUP"  could not be bound.

Solution -- After removing invalid columns from Named-Queries when it is executed it may give this error. After monitoring the issue you will find statements like this "dbo.CUSTTABLE.CUSTGROUP.CUSTGROUP AS CUSTGROUP_CUSTGROUP". This is a known issue with Dynamics AX 2009 with SSAS 2008. 

Step1: Install the sql server 2005 native client.

Step2: Open properties page of SSAS Database dataset and change the PROVIDER to "SQLNCLI.1" from the existing "SQLNCLI10.1" value.

Step3: Make changes in the Named Queries. Make further changes as per required and Save, Build & Deploy them.

Step4: Change back the PROVEDER to SQLNCLI10.1as in step2. 

     For more details Click here.

Issue-3. Cannot call method on nvarchar.

Solution -- This error comes while executing the named query.  In complex queries it is difficult to parse the entire query and try to find where is the exact fault. Better is to check the table. It is possible that a FIELD in the table is disabled due to configuration key. I compared the table fields with those available when browsing the table. Fields that are disabled will not show up while browsing table. You can also find it by looking at the property sheet and the config keys.

Issue-2. Revisited.

Solution -- There are chances that the issue 2 comes back to haunt even after changing the Property for Provider. The solution is to correct the Identifiers in the query manually and change like CUSTGROUP.CUSTGROUP instead of CUSTABLE.CUSTGROUP.CUSTGROUP .

Hope it helps ......

Develop a simple AX 2009 SSRS Report with a Multi Value parameter

Well this is my first blog article and I hope it will help add value to the online knowledge-base of AX !!!!! 

So here it goes  ..  

Like Single value parameter report in AX 2009 SSRS , multi value parameter reports can be developed. There is a property named Multi-value. If the value is set to true then the report becomes multi valued.

Note: Once the parameter is set to “multi value” it can’t be made nullable or blank simultaneously.

So the real problem arises when user ask for an option to choose data in a multi value parameter and at the same time can leave it as blank. Out of the box SSRS 2008 does not provide this option. The workaround is to pass values as default value. So the user will not provide value explicitly but the system will automatically consider all the available data.

Let’s start with a dummy report development:( keeping in mind that creation of a new report project is known)

1. Create a new report.

2. Now add a dataset with data source type as Query. (See image below) Data source type can be Business Logic also.

3. Create a new parameter like in the image below. Thus we have created a new parameter with “Values” based on AccountNum column from the dataset (ForMultiValueParmDS).

4. Set the multi value property as True.

5. Provide a default value as shown below to the same parameter “AccountNum”.

6. After all these steps build and save the report to AOD.

7. Lets Preview the design. (Hope you have added the dataset in the auto design else just drag the dataset created to the design node from which you want to display data.)

8. The way you create filter is a bit different for multi value parameters. See below

9. Now lets preview the report and see how does it come.

10. Values in the parameter Account Num is being displayed as a list of values with check boxes with it. Also see that it has got (select all) option. It is coming like this because default value property fro the parameter is set. When the report is deployed and viewed in EP the parameters are rendered as Drop-down consisting of a list of values and each of them prefixed with a check box. Also the “Select All” option is available there.

Now the user has flexibility if he/she wants to explicitly provide values or not. It is similar to what we have in MS Office Excel Macros.

Please provide your feedback on this article.