Tag Archives: filter

In list import in report

Hello there!

Today let’s talk about a simple but good tip: Import a list to filter a report!

Let’s suppose that you have to filter 100 elements in a report and you have those elements in a txt, csv or .xls file. To import those values you just have to:

1- Open report filter and on qualify on field choose the specific form attribute that you want to filter;

pic

2- In Operator choose In List;

pic

3- Click on Import… button and select your input file. The elements should appears like this:

pic

 

That’s it! Now you can import a list that contains all elements to filter a report.

Hope it helps!

 

God bless you!

Difference between filter and slice in selectors

Hello there!

Today let’s talk about the difference between Filter and Slice when filtering in documents.

You can see the option to in Selector tab in Properties of the selector.

pic1

If you check Apply selection as filter all targets are going to be filtered using Filter, if you uncheck, all targets will use Slice.

You can also click on “Manual Targets” button and choose for each target, which kind of filter use.

pic2

 

Let’s keep it simple. If you select Slice, the aggregation is made when opening the document. If you select Filter, the aggregation is made when you change the filter. So…

When using Slice:

1. You have everything preprocessed and you won’t have delays when changing an element in a selector;

2. Your document runs slower than using Filter, because it creates all aggregations when the document is been processed. It stores all the content within the document, so it doesn’t need to create new aggregations when changing the selector element.

3. The aggregation is made using all the attributes of the source dataset instead of the attributes included in the grid/graph. So, if you have Region, Year and Cost in source dataset and in your grid you only have Region and Cost, MicroStrategy will aggregate it using Region, Year and Cost.

Here is an example:

Using Slice:

pic1

As you can see, I have Year and Cost in Grid and Region in the selector. MicroStrategy will not summarize all Regions by Year, it will repeat the years for every Region selected, because as I told before, the aggregation uses Region and Year. Think that you have an invisible Region in the grid template.

Using Filter:

pic1

Here MicroStrategy will aggregate the Cost metric only using Year and filter using Region. So, you will see a sum of all Regions by Year.

 

4. It’s good for caching and exporting. Because all data is included in your document. So, if you export a document in Flash, you will be able to change the selector without receiving an error message.

When using Filter:

1. Your document runs much faster because it only aggregates the data using the selected elements in the selectors. But, every time that you change a single element in a selector, MicroStrategy has to aggregate the data at runtime, so it can delay much more time to change element than using slice, that is already aggregated and runs instantly*;

2.  The size of your exported document is lighter than using slice, but it only stores data of the selected elements of the selectors;

3. It aggregates using the attributes of your grid/graph. So, if you have Region, Year and Cost in the source dataset and create a grid with Year and Cost, it will summarize Cost only by Year, as the image above shows;

4. It’s not so good for caching and exporting.  If you export to a Flash file, you won’t be able to change the selectors elements;

 

That’s it.

Hope it heps.

 

God bless you!

Adding filter in a Derived Metric

Hello there!

Today let’s talk about an excellent trick with derived metrics.

There is a way to insert a filter in your derived metric!

Let me show you an example:

1. Create a filter with Year = 2010 and save your filter as “2010“:

pic2

pic2

2. Create a blank report with Year and the metric Cost:

pic1

As you can see, I am fetching all years from database and displaying the Cost for each year.

2. Create a new derived metric:

pic2

3. Use a Sum formula, for example, like this:

Sum(Cost) {~ }

pic2

4. Insert <[name_of_the_filter]; @x; -> after the dimensionality of the metric. Where @x is the embedding method number:

In my case, I’m going to write: <[2010]; @2; -> 

pic2

In x you can use:

1 – Merge into new;

2– Merge report filter into metric;

3– Merge metric condition into report;

When you edit your metric and select Condition there is an Advanced… button where you can see those options.

You can also use + instead of the :

<[2010]; @3; +> – That means that you will “Remove related report filter elements”

<[2010]; @3;> – That means that you will not “Remove related report filter elements”

 

5. Now, edit your “New metric” and use the Fact Cost instead of the Metric Cost in the formula:

Was: pic2

Now: pic2

6. Save your metric;

7. Re-run your report:

pic2

As you can see, we have filtered only 2010 to the “New Metric“! So, you don’t need to create a lot of metrics with different filters to use in just 1 report, you can create a local filtered metric and reduce the amount of metrics created in your project…

Hope it helps!

God bless you!