DAO Error Code:External table is not in the Expected format

 I found this Error while connecting to Excel file in Crystal Report 2008 .

Some times if the Excel File is accepted at very beginning you may play around the database fields in crystal report. But later if you modify the Excel file you might get this Error "External table is not in the expected format".

 

Solution:

This Problem arises when Crystal Report doesn't recognise higher versions of Excel such as 2010

So It is Advised to save the Excel file in lower version i.e Microsoft Excel(97-2003).

Which is accepted by Crystal Report.

Error:

It's that Bussiness Objects only supports that version (97,5.0/95) of Excel.

Solution:

Reasons to Choose Xcelsius

What amazing dashboards you can create with Xcelsius 2008
with its rich UI elements and data connectivity


let’s check some reasons to choose Xcelsius as your dashboarding tool.

•  It’s powerful.
• By using cutting-edge technology, Xcelsius provides the best visualization effect and user experience. With several kinds of data connectivity, such as Web services and XML data, you can connect to almost any kind of data source.
•  It’s easy to use.Xcelsius offers a wide range of UI components such as pie charts, candlestick charts, accordion menu, and maps, and you need simply drag and drop the components to create a professional dashboard.
•  It uses almost everyone’s daily tool, Microsoft Excel, as its direct data source and provides a built-in Excel. In this way, you can easily bind the UI components to a single cell or a range of cells in the Excel spreadsheet.
•  It’s extensible and growing.Xcelsius brings with it a wide range of UI components and data connectivity methods, but sometimes you may encounter a scenario where you need something new. Xcelsius is extensible in that it provides a software development kit (SDK), which you can use to create your custom UI components and data connectivity. 
• Moreover, some companies are working on developing a new component, and there are many active forums about how to use Xcelsius. 

Also, SAP keeps releasing new features to Xcelsius.

Use Existing Report Template with New Data

Running Crystal Report for updated excel data updated with new data!!

So if you want to Run the Report with Existing Template with the Updated Excel or the data Connection

Ref. check Data Connections.

You might notice there are not exact calculations or the exact format or the font.

You may want to check the Data of the field  -> Go To Field Explorer right click on the Data field and browse data.

So might delete fields and map the same again . so the new data gets in.

Ma be some fields MAY contain the previous data , even though you made all your connections correct.

Crystal Reports Display Only one Record Per page

Working on Crystal Report is cool!!
Every  thing i write on my blog is actual issues/problems or discovers worked out in real time scenarios.

So

My requirement was to Display 1 record on one page or to Suppressing the blank page .

1. Open Crystal  Reports -> Blank Report-> Get the data either from BW or excel or any other source.
2. Drag and drop the required fields onto the page to view the Report.
3. To view 1 record per page : Go to Section Expert from the menu or right click on the required section.
4. Section Expert in the Group Footer. select the check box for suppress blank pages & New Page After. 
5. Beside them we have X+2 formula tab. 

           PageNumber=TotalPageCount

           save and close.

           Now Run the report..

           Every page is displayed with Record No and Record Details.

Access Crystal Report Using Excel Data

Using Crystal Report with Excel Data !!!

1. Name the Excel Sheet and Set the Headers with the Field Names highlighted , Bold Letters .
2. Make sure you format field i.e Currency, date, Text, General, etc.
3. Now open Crystal Reports , It opens up Database expert or you can click Database expert on the menu bar.
   
4. In the DataSources , open a Create a New Connection ->  select Access/Excel (DAO).
5. You can see a new window to input excel file. Database Name is your file path and Database Type is Excel 8.0 or other (version try it to be latest versions  to eliminate bugs) & click finish
6. Here on the right side of the window you can see Path Name and the Excel Sheet Name.
7. Note: If you dont find your Excel Sheet , you may want to check right click on the file name in the database expert -> select option and make sure Table name or owner like is empty. As you are creating a new report .
8. once you see you table in the field browser, then you have table data which is ready to use.
9. And Imp is to check the Database -> Log on or off server. -> create a new connection and add in file .

Vertical or Horizontal Axis Labels, Number Format

Well ,
Everyone Likes to see data in common terminology, not dragging eyes to count how many series of Zeros in that Number .

Here is an Example of the Year to Date Totals for Budget and Actual.

You Could Just fix this :

1. Inset a Column Chart or Any Other Chart you wish to use.
2. Go to Behavior Tab-> Scale 
3. Now you can find Fixed Label Scale-> click on the Check box   

By Checking this Field you can instantly see the result on you Dashboard..

Extract date from timestamp in crystal report 2008

 Using Crystal Report 2008 :

Found a way to extract Date from field which contains Timestamp.

for ex: userevent table there is a table.field which contains timestamp. And i want to convert into a date
while eliminating time .

you can see here;

for example timestamp field contains 20130128145651 this date and time . now i wanted to convert into
01/28/2013 format.

you can try using this formula:


Go to formula -> create a new formula -> type in the below formula

date(tonumber("20" & mid({table.field},3,2)),tonumber(mid({table.field},5,2)),tonumber(mid({table.field},7,2)))

check for errors by clicking x+2 -> save and close.

place the formula in the details section and  preview.


or you check this formula too..


//stringvar dt;
//
//dt:=(totext(Year(currentdate),'0000')&totext(Month(currentdate),"00")&totext(day(currentdate),'00'));
//
//tonumber(dt);

First one worked for me in this case and second one is also similar