Thursday, May 29, 2008

Reporting service: Dynamic Toggle Group

In my current project i have to use Reporting Service 2005 for generating some reports. It was the first time for me to RS. The report was showing some group on Year >> Month >> Day. The report was drill down one. I had to show the current month's days open i.e when the user open the report which is decending sorted with year,month,day will show the report with current mont's days expanded. All other years and month will remain collapsed.
For that i added two parameter to my report through
Report Layout (Designer)-> Report (Menu) -> Report Parameters(sub menu).
The parameters are showYear of type int and showMonth of type int.
Default value for showYear, i selected "non-queried" and in the textbox wrote =Year(Today)
and same for showMonth with TB value =Month(Today) so that the default value is set to the current month and year.
Now i edit the group showing the month and in the visibility tab i set "Initial Visibility" expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,false,true)", so that if the yeay is equals to current year then it will stay initially visible. And for day i set expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, false,true),false)", so that if the month and year is euals to current then it will be stay initially visible (i kept the "Visibility can be toogled by another report item" section as it was.).
Now when i preview the report i found everything is running fine. By default it is expanding current year and month and if i put some other value in the report changing textbox it is expanding corresponding record. But the "+" and "-" signgs for toogle for those initially expanded is showing opposite i.e. "+" even when initially it is expanded and "-" when clicked to collapse.
Why this is happening? After searching various properies for the report and its item i got the "InitialToogleState" property of the textbox which by default is collapsed. Then i set this property to expression so that it is "Collapsed" and "Expanded" properly to show "+" and "-" sign respectively.
I set the year textbox's (TB which is responsible for toggle in year group row) InitialToogleState property to "=IIF(Parameters!showYear.Value=Fields!Year.Value,true,false)" and for month's one "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, true,false),true)" . Then i saw the result was as desired.
We can hide the Parameter Promt in the report viewer by ShowParameterPrompts="False" in the reportviewer control and send the parameter by querystring as 'showYear=2004&showMonth=5' .
Some points to note:
1) "InitialToogleState" property is boolean. "Collapsed" indicates false and "Expanded" true
2) "Initial Visibility" is set to month and day rows where as "InitialToogleState" is set for year and month TB.

One useful link:

Wednesday, May 21, 2008

XML writing problem.. Encoding Fixing

MemoryStream ms = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(ms, System.Text.ASCIIEncoding.UTF8);
writer.Indentation = 3;
writer.Formatting = Formatting.Indented;
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteAttributeString("xmlns", null, "");//Writing namespace
writer.WriteAttributeString("xmlns", "rd", null, "");//Writing namespace

Or You can use StringBuilder class object like below
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);

Thursday, May 1, 2008

Some Handy SQL Server Query

Get the Information about all the databases available in your SQL Server
EXEC sp_databases
EXEC sp_helpdb
select * from master..sysdatabases
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
Get the Information about all the tables available in your SQL Server Database
exec sp_tables
but with this you have to filter to exclude the table owned by (
Get the Information about all the tables available in your SQL Server Database
sp_columns @tableName
Select * from Information_Schema.Columns where Table_Name=@tableName