Report Search Tool
I received an interesting request from our Power Users this week. They only have Web access (no Desktop) and wanted to have a way to locate reports based on their components. For example, when someone requests a report containing Region and Cost, currently they can only hope to either know where it is or search for a match in the Name / Description. Using Desktop, they would simply search for Dependents on one of those objects and be on their way, but as stated, they only have Web. So, we came up with a simple little report that queries the metadata directly and prompts on objects, providing them an easy to use “Report Search” interface. Here’s how we did it.
It’s important to note where this data lives. Enterprise Manager isn’t a good place, because as it has a Warehouse model, the relationship between Reports and their components are driven through a Fact Reporting table. That means that it stores what the relationships were at the time a report ran and not necessarily what they are today, not to mention a report that hasn’t been run in some time frame wouldn’t show up either. The true source for this is the metadata itself, and fortunately this particular query is pretty easy.
Step 1 – Create a Metadata DSN
If you don’t already have one, create a DSN that points to your Metadata. It’s perfectly fine to query against the metadata, it won’t hurt anything and we aren’t doing any updates. This style of reporting is perfectly acceptable by MicroStrategy standards.
Step 2 – Create a Logical View
I chose to create a Logical View for this report. The more traditional option is probably a Freeform SQL Report, but I wanted to have more flexible prompting options than that would allow. Alternatively, you could just bring the tables in and map them directly, but as you’ll see by the query, it would be complex to setup and include table aliases; a bit more messy than I’d be willing to invest for a single report.
select d.PROJECT_ID,p.OBJECT_NAME as Project_Name, r.object_id as Report_ID, r.OBJECT_NAME as Report_Name, o.object_type, o.Object_Desc, o.Object_Image, s.object_id as Depn_ID, s.OBJECT_NAME as Depn_Name
from dssmdobjdepn d
join DSSMDOBJINFO r
on d.object_id = r.OBJECT_ID and d.PROJECT_ID = r.PROJECT_ID
join DSSMDOBJINFO s
on d.DEPN_OBJID = s.OBJECT_ID and d.PROJECT_ID = s.PROJECT_ID
join DSSMDOBJINFO p
on p.OBJECT_ID = d.PROJECT_ID
join tbl_Object_Type o
on o.Object_Type = s.OBJECT_TYPE
where o.Object_Type in(1, 10, 12, 4, 47) -- Filter/Custom Group, Prompt, Attribute, Metric, Consolidation
and d.OBJECT_TYPE = 3 --Report
and s.SUBTYPE not in(1026) --Function
and d.project_id = '0114E056-CFF8-402E-B0DA-95D880289705' --Development Project
The key table is DSSMDOBJDEPN which contains the list of dependencies between all objects in the system. In this example, the OBJECT_ID field in that table will end up representing our Report, and the DEPNOBJ_ID will represent all of the components of that report (Attributes, Filters, Metrics, etc). The table DSSMDOBJINFO contains all of the descriptive information for an Object, such as it’s Name. We’ll join to that table 3 times to get the display name of the Report, Dependents Objects and the Projects.
The table tbl_Object_Type is one I’ve created myself. The metadata doesn’t keep a lookup table for Object Types (this is normally just handled by the UI), so we have to maintain our own in order to pull back the descriptions. Here’s the DDL for the table I created in SQL Server:
CREATE TABLE [dbo].[tbl_Object_Type](
[Object_Desc] [varchar](50) NOT NULL,
[Object_Type] [varchar](50) NOT NULL,
[Object_Desc_Long] [varchar](500) NULL,
[Object_Image] [varchar](500) NULL
) ON [PRIMARY]
I’ll leave generating the insert statements up to you. You can get the full list of objects and their names / descriptions from this tech note.
The Object_Image field is some nice sugar for your report. What you can do is paste a URL to an image that represents that object type, for example, http://yourwebserver/microstrategy/images/metric.gif. You can browse your WebASPximages folder for icons for other object types and fill in the full URLs as desired.
I also have added a few filters in the WHERE clause. First, I only want my Report Search to return specific types of objects because some types (like Attribute Forms) aren’t relevant to the user’s needs. Second, it’s important to limit the main object we’re looking at to Reports. The DSSMDOJBDEPN table contains definitions for everything, so we need to lock down only to reports. Third, some objects share an Object Type and are only distinguishable by a SubType (fun fact: Filter and Custom Group share an Object Type). In this case, the Function type bleeds into our report via the Metric Object Type, so we want to exclude that specific Sub Type so we don’t see “Product” and “Quotient” in the results. Finally, I’ve also chosen (per my specific requirements), to only consider my Development project. In my environment, I have just one main project and a development project, and for my user’s purposes, the Development project contains all the information they need to search. You could just as easily remove this filter and prompt on the Project.
Step 3 – Creating Your Objects
Now we’re into normal MicroStrategy stuff. Create your attributes against this table.
IMPORTANT: The Parent / Child relationships are crucial to get correct results.
I created 4 attributes:
- Object Type
- Dependent Object
Set Project as a parent of Report with Many to Many as their relationship.
Set Report as a parent of Dependent Object with Many to Many as their relationship.
Set the Object Type as a parent of Dependent Object with One to Many as their relationship.
I chose to put all of the objects together in a single “Dependent Object” field instead of breaking them out as “Metric, Attribute, etc” because I thought it would be easier to build and a lot more usable. It’s easier for a user to use a single prompt instead of using multiple prompts and having to worry about whether something is a Filter or a Conditional Metric or a Prompt, etc.
The objects are all standard and pretty self explanatory to setup, except for the 2nd Attribute Form of the Object Type Attribute which I call “Object Image”. For this form, set it to of type HTML Tag and use the following code for it’s Form Expression:
ApplySimple("'<img src=' + #0 + '> ' + #1", [Object_Image], [Object_Desc])
This will generate the HTML code to display the image and show the Object Name. You can see the output at the end.
Step 4 – Create Your Report
Since we want the user’s to be able to search for reports that contain specific component objects, we’ll need to prompt for those components. But we don’t want to just show a list of reports that contain that object, we want to show all of the objects on that report. This will give them a better picture of the report matches so they can choose the appropriate one.
To do this, we’ll first create a report that we’ll use as a Report as Filter. On this report, we place Project and Report (since that’s the primary key of a Report) and a prompt for Dependent Object. We can then drag this report into the Report Filter of our Report Search. When the user now answers the Dependent Object prompt, Report Search will return all objects for Reports that were matched. Confused? Sorry, here’s a picture: