Best Of

Are the files you need to connect to always the same, just with data being overwritten/added?

If so, then depending on the format of the data in your workbook, using Excel Tables and Range names you can set up a SQL Connection from BOARD to each Excel workbook which you can then read in with a SQL datareader.

The table(s) you have created in the Excel workbook will appear in the RDB list on your datareader.  And through the combination of Tables and Range names they will be dynamic, so as new data is added to the excel table it will be picked up by the connection/datareader and read into BOARD.

Then you would just would need to use either formulas or VBA to ensure the data is being appended to the relevant tables in each Excel workbook.

Another option which I have experimented with mixed results (but never implemented as I've had no need) is to use the Office Add-in and VBA code to essentially 'paste' the values into a BOARD layout with 'save/undo' mode enabled.

Hi Ford Reid, going from your excel sample I'm assuming you want one cube/block to be displayed across multiple columns (the one displaying '1's), 1 for each month, and then the other (displaying the dates) to only show a single column?

If I have this correct it should be easily possible.

First remove 'month' from the by column axes.  This would display one column  for each block.

Go to the 'References' tab on the block you wish to split out by month and select month under 'Detail By'.  This should then detail (ie. display) that block by month, which will put a column for each month.

you set this here.. 

Leaving the other blocks as is (ie. no detail by) will leave them with a single column, and the 'detailed by' block will show multiple columns.

Hope this helps and is what you were after.

Cheers

Hi Mads Meissner, I'm not certain what you're trying to achieve and what file format you need, but have you tried using the 'extract layout to file' action (under the 'Extract' tab) in a procedure?  Not sure if it's suitable but it will let you set up a layout to extract which will be independent of any screen layout.

Otherwise if you need an xlsx file do you have the excel add-in would allow you to use the refresh file option under 'office reports' which again would allow for a layout to be set which is independent of the screen dataview.

Similar to option 2/3 proposed by Helmut Heimann  but you can avoid the requirement for a procedure through use of the 'action value' in the label layout.  This makes it easier to administer and allows it to be done by a power/lite+ user as it doesn't require editing of procedures.

This just requires a text cube containing the relevant screen name(s) (for whatever level you need, by user or related/grouped entity).  Clicking the label will take the user to the screen with the name that is showing in the 'action value' block.

You could then have a single capsule which contains all screens, and different 'home screen' versions for different user types, and make use of this action value in a label to jump the user to their relevant home screen when they click the label.  The only additional thing needed to be maintained is the cube used for the 'action value' block.

Hi Dinesh,

Are the BU and Month also I the txt file data (or can they easily be added)?  If so I'd have a look at setting your ASCII data reader to "file/Path = ALL" to read every file initially... I'm not sure how many there would be and server specs so I'm not sure on how long this will take and if it's appropriate..assuming it is....

You could then either try using the 'Use Current Selection' check box on the ASCII reader (to only read the user selections) or read the files into a staging cube and then dataflow this to your target cube with the screen selections applied (to only populate the current user selections).

You could then use an 'if' action in the procedure to pop up a message if there is no data for the users selection to notify them no data has been read/file doesn't exist.

Cheers

Brendan