Excel Filtering Limit - only 32,000 values are allowed in the filter

Problem statement:

Sometimes we have very large dimensions in the Cubes and when we create Excel Reports using those dimensions in the Filter etc. We face the excel filter limit restriction, only 32,000 vales are allowed in the filter.

But still, we want to create the report and the report should work properly for the end-users.

Solution:

We can achieve this by creating the Groups where we can group the dimension values and create hierarchy using this Group description and dimension values.

Say suppose we have a dimension Employee. In the Employee Dimension, we have around 150,000 records. So then we create a Report using the Employee in the filter. The Excel Report will through an error when we select the values for the employee as the number of counts is more than 32000 for the Employee dimension.

So for this, we can create h new column in the Employee dimension which is Employee Group and bifurcate the values into these groups.

As the number of employees is 150,000.
So, we can create a group of 500 employees based on the Employee ID and there will be 150,000/500=300 groups created.

This group will be then used to create the hierarchy in the SSAS cubes.
By keeping the excel limitation (32,000) in the mind to group the values.

Employee Group-->Employee

So, now in the excel report, we can use the employee group hierarchy in the filter.
By selecting the particular group we can choose the employee which we want to select.

This will solve our issue of 32,000 values for the filter issue in Excel.

Comments

SSRS Reports - Space/Gap issue between the objects in SSRS Reports

In Reports where some objects are set to be shown/hide based on some expression/filter condition etc. There Could be some white space issue between the objects on the report( i.e. when few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report). Following are few ways using which we can handle this kind of issue: 1. Remove the Gap between the objects in the report layout. 2. Or put a text box and make the border the same as the background color and apply the same show/hide action on it. 3. If you are using a table/matrix then instead of putting the space between objects, just add one static row above the column header for the table/matrix and make the border color the same background color. below is one approach: The report before any space handling: ----------------------------------------------------------------------------- Report Preview when all the o...

How to do Error Handling for Transaction using Try Catch Block

Issue : Some time we get requirements where we have to use transaction to do some DML operations to make sure the unit of work should be done completely. But there could be error while doing this. So, how we can cope up with errors to achieving this? Solution: We can use Transaction in the Try Catch block to make sure that the transaction either successful or rollback if there is any error. Below is an example for this: BEGIN TRY --Start the Try Block.. BEGIN TRANSACTION -- Start the transaction.. UPDATE [dbo] . [Table] SET salary = salary * 1.1 WHERE MID = 6 --DML operations to be done in the transaction COMMIT TRAN -- Transaction Success! END TRY --End of the Try Block BEGIN CATCH --Start the Catch Block IF @@TRANCOUNT > 0 --check the error count for the errors ROLLBACK TRAN --RollBack in case of Error --to raise the error message when there is an issue. Declare the variables and se the values for them DECLARE @Er...

SSRS Reports - When few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report

JK - BI Blog

Search This Blog