Difference OpenDocument Crystal Reports and WebI

Posted on 14 April 2010 9:53 am.Femke

I experienced some frustations when trying to open 2 different reports (Crystal Reports and WebI), however both were built on same universe and used same objects, from the same URL button in Xcelsius.

The OpenDocument statement for the WebI report was quickly set up, however the Crystal Reports took some time. Even after consulting chapter 3 Crystal Reports in the PDF Viewing Reports and Documents using URLs (for BOXI 3.1) I could not get it to work.

Finally I figured it out by reading several forum topics, in the end the URL was built as follows:

../../../OpenDocument/opendoc/openDocument.jsp?

Identifies Crystal Reports reportH stands for HTML, unfortunately in 3.1 pdf is not supported anymoreforces reports to open in new windowKeeps Xcelsius dashboard “alive”CustomerCode is name of prompt, 0019 is customer code value, note “” to identify stringfoldername in Infoview, don’t forget []identification type, preferred in OTAP streetsforces reports to open in new windowKeeps Xcelsius dashboard “alive”CustomerCode is name of prompt, 0019 is customer code valueYou can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Possible Waterfall charts in Xcelsius

Posted on 1 October 2010 10:44 am.Femke

In Xcelsius a waterfall charts is not default available, however with a little imagination you can create one from a stacked bar chart. Below I have published 4 examples.

Waterfall Example 1A

Excel source

Use the columns for the series (= coloring) and the rows for the labels (= columns in chart) by setting the data range to “Data in Columns”. On the appearance tab set the Marker Gap to 0 and the Marker Overlay to 100. Apply the series coloring accordingly.
Note: remember how Xcelsius builts up the chart; from bottom up to top. If you want your bottom bar color to be white, you have to start your series with the white color data.

Waterfall Example 1B

Excel source

Same example as 1A only the less colors, thus less series used.

Waterfall Example 2A

Excel source

You want to show how your profit and loss accounts are related by showing each individual account and use the colors red and green for the contribution amount (cost = red, revenue = green).
Again use the columns for the series (= coloring) and the rows for the labels (= columns in chart) by setting the data range to “Data in Columns”. On the appearance tab set the Marker Gap to 0 and the Marker Overlay to 100. Apply the series coloring accordingly.
Note: Again the order of the data columns determines the coloring in the chart. The white amount is calculated on the basis of the total of the previous column only in case of a cost account, that cost amount is subtracted from the total.

Waterfall Example 2B

Excel source

This is based on the same principle as the previous chart only it gets more interesting as the total amount gets below zero, thus we have to deal with negative totals with a positive contribution. This can be achieved by creating extra series as the order of the negative positioned bar colors is different than when using positive bars.
Note: The coloring change in the y-axe can be changed by formating in Excel, see this post.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Reset a selector like combo box or radio button

Posted on 7 July 2010 4:18 pm.Femke

1. Bind labels of the selector (eg. B2:B5)
2. Set Insertion type and destination (eg. C2)
3. Set the selected item type on Dynamic on the behaviour tab of the selector
4. Bind the item to a cell (eg F2)

5. Bind source cell of Push button (eg. E2)
6. Bind destination cell of Push button (eg. F2, same as selected item of combo!)
7. Add formula in source cell push =if(F2=”x”,B2,”x”)
8. Add “x” value in destion cell F2

Comes in handy when you want to reset only certain parts of the dashboard and not resetting it to its initial state (as you do with the reset button).

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Request post

Posted on 1 October 2010 4:53 pm.Femke

This post is meant as a request post. If you have a problem which need solving or a question which needs to be answered, just leave a reply and I’ll try to answer it.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Selectors: 6 steps to Multiple Selection

Posted on 20 August 2010 10:50 am.Femke

As I get more and more request for the multiple select file I have written out below the steps to take (thus I will not send the file anymore, you can follow the steps yourself ). Also I updated the source file to use the push button instead of a second source button as this is easier to understand. I created this initially for a Web Intelligence prompt, but of course you can use it for any prompt.

6 Steps to create a multiple select:

1. create spreadsheet

place in cells A5 to A14 your labels (eg. label 1, label 2 etc)in range B5 to B14 you create the parameter labels as they need to be send through (eg. “label 1?, “label 2? etc)place in C5 to C14 a 0 (zero), these are the ‘old selection values’place in D5 to D14 an incremental number (10 labels = 1 to 10)place in D18 a reference to D2 “=D2? (one of the destination cells of the selector), using this formula reference a delay is created in the overall calculation. When instead the direct input cell is used in the cell range E5:E14 no more then 4 items can be selected before re-initialisation. Using this delay formula enables selection of all items.place in E19 the hard coded value 1 (one), this is a source value for the “new value”place in E5 the “new value” formula “=IF($D5=$D$18,$E$19,$C5)” and drag this formula through to E14place in G5 the formula “=IF($E5=1,B5,”")” and drag through to G14. This is the first part of building the WebI prompt with multiple values.place in H5 the formula “=IF($E5=1,C5,”")” and drag through to H14. This is used in the alert of the selector (enables you to see which labels are selected).Place in J5 the formula “=IF(G5=”",”",G5)” and in J6 the formula “=IF(G6=”",IF(J5=”",”",J5),IF(J5=”",G6,J5&”,”&G6))” drag this formula through to J14. This is the second part of building the webi prompt. Cell J14 is the actual prompt send to WebI.place in L5 to L14 a 0 (zero), these are used by the push button to re-initialise to the last selection.place in cell G2 the formula “=”Reset to only “&B2? (label for push button)

2. place a selector, eg.listbox with the following properties:

Labels: A5:A14Insertion type: rowSource data: B5:E14Destination: B2:E2Selected Item: Dynamicenable alertsalert values: H5:H14by valueselect “low values are good”deselect “enable auto colors”, remove 1 color and enter the value 0.5, the range minimum to 0.5 will be with a white background, range 0.5 to maximum will have a red background.

3. place a label which shows the concatenated prompt value

4. place a source data button with the following properties:

insertion type: columnsource data: E5:E14destination: C5:C14

5. place a push button with the following properties:

label: G2source data: L5:L14destination: C5:C14

6. preview your dashboard.

That’s it. Easy is it not?

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

View the original article here

Drill in chart to different Tab

Posted on 5 October 2010 12:02 am.Femke

If you want to give your users the choice of clicking on a chart (e.g. clicking on the chart title) and then going to a specific tab where the KPI is displayed in more detail, you can use the property “Selected Item: Item (by position)” of the Tab-component and several toggle.

You can even return to the overview tab by using the same method (toggle button). However If you want your users to be able to navigate through BOTH the tab buttons and your toggle buttons, you need to set-up the “return” toggle buttons “with non existing tab positions” as source data. This as the Tab component does not have an insert selection option itself. And when clicking on a tab and then clicking in the chart (on toggle button), the position is not changed and thus it does not trigger the “Selected Item: Item (by position)” property.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Open document statement opens only in html viewer Crystal Reports

Posted on 14 April 2010 8:54 am.Femke

In BOXI 3.1 the opendocument statement for Crystal Reports opens the reports only in the html-viewer.

The commands are thus:
&sType=rpt&sOutputFormat=H

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

T-SQL: Previous and Next Modified Date

Normally queries like this are not really solved in T-SQL; rather a front end UI like WebI is capable of navigating through the records.

However if you intend doing it in T-SQL, then here’s how we can retrieve the Next and Previous Records of a given record:

/* Create Sample Table */
DECLARE @TT table
(
ProductID int,
ModifiedDate datetime,
CategoryGroupName varchar(10)
)

/* Create Sample Data */
INSERT INTO @TT VALUES ( 101, ’2010-10-01', ‘AA’)
INSERT INTO @TT VALUES ( 203, ’2010-10-01', ‘AA’);
INSERT INTO @TT VALUES ( 305, ’2010-10-01', ‘AA’);
INSERT INTO @TT VALUES ( 101, ’2010-10-02', ‘BB’);
INSERT INTO @TT VALUES ( 203, ’2010-10-03', ‘BB’);
INSERT INTO @TT VALUES ( 634, ’2010-10-03', ‘BB’);
INSERT INTO @TT VALUES ( 101, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 203, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 305, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 634, ’2010-10-04', ‘CC’);

SELECT
Prod2.ProductID,
Prod2.ModifiedDate,
(SELECT MAX(ModifiedDate)
FROM @TT Prod1
WHERE     Prod1.ModifiedDate <  Prod2.ModifiedDate and Prod1.ProductID=Prod2.ProductID ) as PreviousModifiedDate,
(SELECT MIN(ModifiedDate)
FROM @TT Prod1
WHERE     Prod1.ModifiedDate >  Prod2.ModifiedDate and Prod1.ProductID=Prod2.ProductID) as NextModifiedDate
FROM @TT  Prod2
GROUP BY Prod2.ProductID, Prod2.ModifiedDate
ORDER BY 1,2

Result looks like:

View the original article here

Add on manager is greyed out

Posted on 24 March 2010 1:33 pm.Femke

After installing Windows 7 the add on manager is greyed out in Xcelsius.

This is due to the default User Account Control settings. Change the setting to the lowest level (9) and then import the add ons. After restart of Xcelsius you can change the level back up again (or leave it).

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here

Open document link in new window while dashboard does not re-initialise

Posted on 9 April 2010 4:31 pm.Femke

When you connect an OpenDocument statement to a URL button in Xcelsius and you apply the option New Window in the button properties, a new window is opened to show your WebI report (or Crystal report for that matter). However the report is not actually opened in that window, it is opened in your dashboard window, thus after closing or going back it forces the dashboard to reload again.

Solution:
add the following commands to the opendocument url:
sWindow=New&isApplication=true

the first command forces the link to open in a new window, the second command keeps your dashboard in the state it is in.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

View the original article here