Passing a uniqueidentifier in the URL link in SSRS?

For some unknown reasons, the uniqueidentifier must be in lowercase for the passing to work in SSRS. So I have had to use LOWER sql function to populate the uniqueidentifiers in lowercase to be used in my dataset.

Read Full Post »

PROPERTIES() MDX – if you have a composite key defined in your dimension and you want to get its key…

For example, a site may have more than one camp and a camp may have more than one room. Hence the hierarchy of this dimension would be Site > Camp > Room.

An example of a Room A01 member in MDX is  [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01]. As you can see in here, 5 is the SiteID and 16 is the CampID. A01 is the Room Name.

To get the SiteID and CampID from the Room member, use the PROPERTIES function.

Example code:

WITH

 MEMBER [Measures].[RoomSiteMemberKey] AS [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01].Properties(‘Key0’)

MEMBER [Measures].[RoomCampMemberKey] AS [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01].Properties(‘Key1’)

SELECT { [Measures].[RoomSiteMemberKey], [Measures].[RoomCampMemberKey] } ON 0

FROM Cube

The above code will return the RoomSiteMemberKey = 5 and RoomCampMemberKey = 16.

Read Full Post »

SSRS Data Driven Subscriptions on SharePoint Integration Mode

They are only available on the Enterprise or Developer edition of the SSRS, unfortunately.

Read Full Post »

How to check if the the multivalued parameter does not contain a specific item in SSRS?

I have a report parameter of Business Unit which is multi-valued (containing MMAS,  MLB, and Operations) and must have the following rules:

1. When the parameter have all items ticked, display it as “MMA and MMAS Combined”

2. When the parameter does not contain “MMAS” but the rest of the other items are ticked, display it as “Australia” on the report.

3. When the parameter only has one item selected and the “MMAS” item is selected, display it as “Asia”

4. When the parameter only has one item selected and the “MLB” is selected, display it as “Logistics”

5. Otherwise, display as selected.

Solution to each of the above rule is below. It took me a while to come to this.. so I am proud of it 😀

1. Check if the number of the items selected in the Business Unit parameter is equal to the total number of the parameter items in the dataset, if it is, display it as “MMA and MMAS Combined”. The syntax to do this is below:

    Parameters!BusinessUnitCount = Count(Fields!BusinessUnit.Value, “Business Unit”), “MMA & MMAS COMBINED”

2. Check if the number of the items selected in the Business Unit parameter is equal to the total number of the parameter items in the dataset minus one and that the item selected is not MMAS, if so, display “Australia”.

    ( Parameters!BusinessUnit.Count = Count(Fields!BusinessUnit.Value, “Business Unit”) – 1 )  AND NOT( Join(Parameters!BusinessUnit.Label, “,”).Contains(“MMAS”) ), “Australia”

3. ( Parameters!BusinessUnit.Count = 1 AND Join(Parameters!BusinessUnit.Label, “,”).Contains(“MMAS”)), UCase(“Asia”)

4. ( Parameters!Level2.Count = 1 AND Join(Parameters!Level2.Label, “,”).Contains(“MLB”)), UCase(“Logistics”)

5. Join(Parameters!BusinessUnit.Label, “,”)

The complete syntax for all of the above rules are below:

Switch(

Parameters!BusinessUnit.Count = Count(Fields!BusinessUnit.Value, “Business Unit”), “MMA & MMAS COMBINED”

,( Parameters!BusinessUnit.Count = Count(Fields!BusinessUnit.Value, “Business Unit”) – 1 )  AND  NOT ( Join(Parameters!BusinessUnit.Label, “,”).Contains(“MMAS”) ), “Australia”

,( Parameters!BusinessUnit.Count = 1 AND Join(Parameters!BusinessUnit.Label, “,”).Contains(“MMAS”)), “Asia”

,( Parameters!BusinessUnit.Count = 1 AND Join(Parameters!BusinessUnit.Label, “,”).Contains(“MLB”)), UCase(“Logistics”)

, True, Join(Parameters!BusinessUnit.Label, “,”)

)

Read Full Post »