Crystal Xcelsius – Filtered Rows From A Filter Component

This is really obvious with hindsight but it took me ages before “the penny dropped”.
Most of the time I find myself using the “Filtered Rows” option on my selectors.
The main selector that I want to use is the “Filter”, but strangely this does not have the option of filtered rows.
What I have resorted to doing in the past is to use multiple selectors:
A date drop down list box filters the rows from “Raw Data” sheet to the “Monthly” sheet.
A product drop down list box filters the rows from the “Monthly” sheet to the “MonthlyProducts” sheet.
A customer drop down list box filters the rows from the “MonthlyProducts” sheet to the “MonthlyProductsCustomers” sheet which now lists all of the sales made for that combination.
The filter component could do that, but this component assumes that there will only be a single row in the output. As soon as you have multiple rows in your result set then you can’t use the filter component.
Ahhh, but you can with a little lateral thinking; that’s what Xcelsius is all about.

In my “RawData” sheet I have the columns Date, Product, Customer and then some other raw data columns. To the right of the Customer column I insert a new column and call it “Key”, this contains a formula to concatenante the three previous columns together

=a2 & “-” & b2 & “-” & c2

I then build a filter component that looks at A2:D500 and tell it that I want three filters.
I tell it that the destination cell is on a separate sheet “Controls!A1″, I call this cell the “SelectedKey”
This gives me a component that allows me to select the unique combination of the three keys, Date, Product and Customer and record the selected value, but how do I then show the multiple rows that are associated with this SelectedKey?
Use a ComboBox component:
The labels come from the key column RawData!D2:D500
The Insertion Type is Filtered Rows.
The Source Data is RawData!D2:Z500
The Destination is a new sheet FilteredRows!D2:Z500
On the Behaviour tab set the selected item to reference Controls!A1 which stores the selected key.
When the filter component selection changes, the SelectedKey is updated, this changes the selected value in the Combo Box which filters the rows.
We don’t want the users to see the Combo Box so my preference is to create a panel container and put all of my “hidden” or “debugging” components in it. The panel container can then be hidden using the Dynamic Visibility setting.

In my example I wanted to filter multiple datasets using the same criteria so I simply added multiple Combo Box components, each with their own source and destination ranges but each referring to the same selected key field.
This was by far the easiest way to implement this.

I know that I should be using QAAWS or Live office and doing this all interactively, but for this customer they wanted to manipulate a static dataset.

About 

    Find more about me on:
  • facebook
  • flickr
  • googleplus
  • linkedin
  • skype
  • twitter
  • youtube

4 thoughts on “Crystal Xcelsius – Filtered Rows From A Filter Component

  1. youl lost me at the setting of the behaviour tab

    what should go in the display staus and in the display status key ?

  2. The behaviour tab contains an entry for “selected item”. This allows you to over ride the default behaviour of showing the first item in the list.

    You can if you want simply select a default value.

    You can alternatively tell it, that the selected item is the one referenced by an excel cell. The cell you point it too will be the “output” of the previous filter.

    This way your first filter produces an output, this output then causes the second combo box to change value, triggering it to further filter some rows.

    Clearer?

  3. Ross,
    I like your logic. By doing this you are effectively creating an AND statement thus filtering the data. Have you figured out a way, with similar lateral thinking to emulate an AND/OR statement. So if there were many criteria and the user didn’t care about certain inputs they could indicate that and the result would reflect. Again that should be done probably with QAAWS like you say, but perhaps it could be here as well.

    Thanks,James

  4. Thank you very much!! I was looking for a way of getting multiple filtered rows from a pie, and your article gives me the idea. I did the same but using a pie chart instead of filter.

    Regards,
    Liliana

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>