Wednesday, March 28, 2012
How to uutofit text when rendered in Excel
We are facing following issue in SQL Server Reporting Service 2000.
We are generating report in excel format using reporting service. We are
unable to format the column width and height based on the content. We tried
couple of option like CanDraw and CanShrink, but it did not work.
We would like know if this is feasible to achieve or is it limitation in
Reporting ServiceFYI: I am having similar difficulties. The report looks find in the Report
Manager, but when it's saved to Excel, text wrapping is lost.
Karen
"Omnibuzz" wrote:
> Hi
> We are facing following issue in SQL Server Reporting Service 2000.
> We are generating report in excel format using reporting service. We are
> unable to format the column width and height based on the content. We tried
> couple of option like CanDraw and CanShrink, but it did not work.
> We would like know if this is feasible to achieve or is it limitation in
> Reporting Service
>sql
Friday, March 23, 2012
How to use sql express in offline mode for report generation ?
Hi , I have been using ms sql 2000 database for generating reports up till now . but now as per a new requirement , i have to obtain data for generating the report from sql express & not ms sql 2000 , and that too in offline mode . i do not know how the structure of data is there in sql express . 1)do we have data stored in tables? or do we use xml ? 2)do we write similar queries as we write in ms sql ?? 3) and lastly what is this offline stuff ? Can any one please throw some light on comparison between sql 2000 and sql express in these three points. Thanks in advance
>I do not know how the structure of data is there in sql express . 1)dowe have data stored in tables? or do we use xml ?
Data is stored in tables as in SQL2000 or SQL2005 - the limitation is on the size of the database. You can also use XML when in
2)do we write similarqueries as we write in ms sql ?
Just the same!
3) and lastly what is this offlinestuff ?
Offline refers to getting data from a ource other than SQL Server (which includes SQL 2005 Express)
Wednesday, March 21, 2012
How to use sql express in offline mode for report generation ?
Hi , I have been using ms sql 2000 database for generating reports up till now . but now as per a new requirement , i have to obtain data for generating the report from sql express & not ms sql 2000 , and that too in offline mode . i do not know how the structure of data is there in sql express . 1)do we have data stored in tables? or do we use xml ? 2)do we write similar queries as we write in ms sql ?? 3) and lastly what is this offline stuff ? Can any one please throw some light on comparison between sql 2000 and sql express in these three points. Thanks in advance
>I do not know how the structure of data is there in sql express . 1)dowe have data stored in tables? or do we use xml ?
Data is stored in tables as in SQL2000 or SQL2005 - the limitation is on the size of the database. You can also use XML when in
2)do we write similarqueries as we write in ms sql ?
Just the same!
3) and lastly what is this offlinestuff ?
Offline refers to getting data from a ource other than SQL Server (which includes SQL 2005 Express)
How to use sql express in offline mode for generating a report !!
Hi ,
I have been using ms sql 2000 database for generating reports up till now . but now as per a new requirement , i have to obtain data for generating the report from sql express & not ms sql 2000 , and that too in offline mode . i do not know how the structure of data is there in sql express .
1)do we have data stored in tables? or do we use xml ?
2)do we write similar queries as we write in ms sql ?
3) and lastly what is this offline stuff ?
Can any one please throw some light on comparison between sql 2000 and sql express in these three points.
Thanks in advance
SQL Express is the exact same database engine as SQL Server. SQL Express has maximum database size limits (4 GB), and some other functionalities are not included, but generally, you use it the same. Same tables, VIEWs, queries, security, etc.
As to the 'off-line'. I 'm not sure what you mean, but I suspect the requirement means to not allow data changes while the report is being generated. An 'Off-line' database is generally not available -it is off-line. (Go figure...) You could restrict usage by changing the database to 'Read-Only' or to 'Single User' mode. More information is needed to give a definitive response to that part.
Click here for a more detailed contrast between the versions.
|||In addition to Arnie, you don′t need a database for generating reports in SQL Server Reporting Services. the reports can also be based on XML files or any other providers shipped with SQL Server. For SQL Server Express, you can only use local SQL Server data (stored in tables)
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Wednesday, March 7, 2012
How to use data to generate a report
Hi,
I have one problem in my project. Generating the report plz help me.
I have one table called Emp, which consists of fields, empid,tdate,attn,reason.
Emp
-
empid | tdate | attn | reason
2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad
2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null
I want to generate a report as given below based on the date submitted by the user.
Frm Date: 6/3/2006 To Date: 6/4/2006
empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1
Please write the query and reply back to me. ASAP.
Thanx in advance.
This is typically called crosstab reports. Here is how you will do it SQL 2000.
Select EmpId,
Sum(Case When Attn = 'Present' Then 1 ELSE 0 END) As "Present",
Sum(Case When Attn = 'Tour' Then 1 ELSE 0 END) As "Tour",
Sum(Case When Attn = 'Absent' Then 1 ELSE 0 END) As "Absent",
FROM Emp
WHERE tDate >= '3 Jun 2006' AND tDate <= '4 Jun 2006'
GROUP BY EmpId
ORDER BY EmpId
The key is the SUM(CASE..END) section. The CASE statement checks if the record's Attn field contains the given value. If it does then 1 is added to the sum, otherwise 0 is added. So for your data...
2281 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/3/2006 Tour 0 for present, 1 for tour and 0 for absent
2283 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2281 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2283 6/4/2006 Tour 0 for present, 1 for tour and 0 for absent
So, when you start aggregating the data on emp id, you will get the data you need. However, this type of solution is suitable for known columns, if you dynamic columns then you will have to check for other alternatives.
SQL 2005 has new PIVOT predicates that you can explore in books online. It simplifies the SQL a lot.
|||
Hi Badri Narayanan,
I'm glad that you have answered my question and suggested the PIVOT predicates method to me. I will learn PIVOT predicates and try to solve my problem in easy way. If you have any suggestions plz forward to me.
Thanx for response.
Take care, Bye.
Regards
Ram