Generate Your Dynamic Report Through Reporting Service

I have posted some articles on reporting service. Here is a sample project which will generate report dynamically. It is having an interface where you select the database in a DDL then a table or view from the selected DB and select some columns for details and group of the report. Then it will generate the report RDL and publish it to your report server. If you want to select from multiple tables with joining, you can do that also by giving your custom query.
Here are some setting which you will have to change to run the project like
string serverName = "localhost"; //The server where the report will be published.
string reportVirtualPath = "LocalReportServer"; //The virual directory path of the reportserver
string parentFolder = "QuoteReports"; //The folder where the reports will be published.
Here i am using some AppSettings val in web.config. Change those according your local settings. But in "MasterConStr" the 'Initial Catalog' value should be 'master' because this connection string is used for fetching data about DBs.
Add the web reference of the reporting service to your ptoject. You may have to change the "RSLocal.ReportService2005" AppSettings value.
Here is the link of the project http://w17.easy-share.com/1700907813.html
Here i am using sql express repoting service and my report server and database is in dame machine. If you have the Report server installed in other machine than the Database you may have some problem with the DataSource. For that pls refer to my other post Use stored credential for report created by c# code.

Use PageRequestManager To Trigger Javascript for Asynchronous Postback

In my last post Send Balk Data(Datatable) From Child Window To Parent
i explained how we can send bulk data from child window to parent. But if we are using Asp.Net ajax in client page i.e the postback is caused asynchronously, then we can't use the RegisterStartUpScript to call the javascript function. But instead we have to use the Microsoft's Ajax javascript framework. I did this with the use of PageRequestManager JS Class as follows

var postBackThroughAdd = false;
function addRequestManager()
{
var reqMgr=Sys.WebForms.PageRequestManager.getInstance();
reqMgr.add_beginRequest(
function()
{
//Do something before the call begins
}
)
reqMgr.add_endRequest(
function(sender,args)
{
if (args.get_error() == undefined)//Checks there is no error
{
if(postBackThroughAdd)
{
postBackThroughAdd = false;
addToQuote();
//Function to trigger the parent postback and flag set and close the page
}
}
}
)
}

Variable postBackThroughAdd is a flag variable, which is set "true" when the button for which we want to send the data to parent is clicked. The code is like

<asp:Button ID="btnAddPartNoToCatalog" runat="server" Text="Add" CssClass="formButton"OnCommand="btnAddPartNoToCatalog_Command" OnClientClick="postBackThroughAdd = true;" />

Here if any error occurs while setting the data in session through server event btnAddPartNoToCatalog_Command, i am simply throughing the error with some userfriendly message. The message will be shown in JS message box and args.get_error() will not be undefined. So the function addToquote() will not be called.
Note: addRequestManager() function should be called on bodyload so that the JS events are registered before they fires actully. With the help of PageRequestManager class you can do some other tasks like showing some message in div after some action has succeeded of failed, show/hide loading... div without using UpdateProgress but same functionality(In the beginRequest handler, set the div's "display" property to "block" and int endRequest hander display to "none").

Send Balk Data(Datatable) From Child Window To Parent

Currently in one of my .aspx page i was selecting some information from a child window which is opened from a parent window. I opened the window like
window.open('PartNoLookup.aspx','PartNo','left=150, top=60,width=800,height=600, toolbar=no, status=1,scrollbars=yes,resizable=no,dependent=yes');
Some PartNos with their information was selected by the child "PartNoLookup.aspx" page. Now i have to send the part nos to the parent page to be displayed. My parent page was NewQuote.aspx.
Now the problem arises if it was a small value then i could do it with
window.opener.document.getelementById('parentCtrlId').value='Some Value'
But as it was a bulk datatable i can't send it like that.
I stored the DataTable in Session["PartNo"] from the child page. And placed a hidden field "hidFlag" (not with runat='server'. this should be a html hiddenfield) in the Parent Page (i.e. NewQuote.aspx). And wrote some javascript with the Page.ClientScript.RegisterStartupScript like
Page.ClientScript.RegisterStartupScript(typeof(string), "", "refreshParentAndClose();", true);
And inside childpage (PartNoLookup.aspx) i added the JS function refreshParentAndClose() as
function refreshParentAndClose()
{
window.opener.document.getelementById('parentCtrlId').value='checksession'
window.opener.document.forms[0].submit();
window.close();
}
So this function will set the flag in the parent a also submit the page.
Here as the page is postback by some unconventional way, only the PageLoad,PreRender and some processing cycle events will fire in the parent. So i have to check for the flag inside tha Load() event. So i added the checking code in Page_LoadCode as
if(Request.Form["hidFlag"]!=null)
{
string flag=Request.Form["hidFlag"];
switch(flag)
{
case "checksession":
//Check the session and show inside the page.
break;
}
}

Note:Put the hidden field as Html hidden, not with runat=server coz, if you set it server variable then change by the javascript will no be visible as it will be restored from the viewstate. y JavaScript we are changing the value not the stored value inside Encripted ViewState. Also if you use server Hidden field the clientid may not be same as server id. So setting it from child page will be problemetic (but not impossible).

Resize and position Javascript popup

In many of my project i came across the situation where i had to open a popup with a particular size and position the popup in the middle of the window. I was sending the height and width parameter in the window.open() funtion parameter. But many times i had to add some extra controls in that page which needs the increase of the popup size. I was problemetic for me to goback to the parent page and find the javascript which is opening the popup and change.
So i decided to control the size from the popup itself. And i used
window.resizeTo(width,height) function for that purpose and called it on body onLoad event.
But what about positioning the popup?
For both the pubpose to solve i wrote a function resizeAndPositionWindow() which takes height and width as parameter and positions the popup in the middle of the window. Below is the function...

function resizeandPositionWindow(width,height)

{

window.resizeTo(width,height);

var scrW = 1024, scrH = 768;

var popW=width,popH=height;

if (document.all document.layers) {

scrW = screen.availWidth;

scrH = screen.availHeight;

}

else

{

try

{

scrW = window.screen.availWidth;

scrH = screen.availHeight;

}

catch(e){}

}

if(document.all)

{

popW=document.body.clientWidth;

}

else

{

if(document.layers)

{

popW=window.innerWidth;

popH=window.innerHeight;

}

else

{

try

{

popW=window.innerWidth;

popH=window.innerHeight;

}

catch(e){alert('Exception');}

}

}

var x,y;

x=(scrW-popW)/2-13;

y=(scrH-popH)/2;

window.moveTo(x,y);

}

By default it is assumed that the screen resolution of the user is 1024X768. Then it determines the actual height.
I used the above funtion to make a function which will take a int parameter as gap between the popup and computer screen and will make the popup which will have that length in top,bottom,left and right.
function resizeAccordingToScreenSize(gap)
{
var scrW = 1024, scrH = 768;
if (document.all document.layers) {
scrW = screen.availWidth;
scrH = screen.availHeight;
}
else
{
try
{
scrW = window.screen.availWidth;
scrH = screen.availHeight;
}
catch(e){}
}
var gapPx=0;
try
{
gapPx=parseInt(gap)*2;
}
catch(e){alert("Wrong value for gap.")}
resizeAndPositionWindow(scrW-gapPx,scrH-gapPx);
}

Use stored credential for report created by c# code

In my current project i have created an interface through which admin can generate the report he wants from any of the table (or combination of tables) from any DB in the server. The interface is generating the xml Report Defination (rdl) and publishing it with the reporting service web service.
Now the problem came with the datasource of the report. When i publish the report it was showing the username and password input box in the report viewer. If i put those it was running beautifulluy. But it was not what i wanted. None of us will bother to give the UID and password everytime. Here is the sample of code i used to generate the report

static int zIndex = 1;
string serverName = "localhost";
string reportVirtualPath = "LocalReportServer";
string parentFolder = "QuoteReports";
string dataSetName = "DSSOP";
string dataSourceName = "DynamicDataSource";
string parTabName = "bodyTable";

void deployReport(string reportName, string reportDefination)// reportDefination is the xml rdl
{
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] byteRDL;
System.Text.UTF8Encoding encoder = new UTF8Encoding();
byteRDL = encoder.GetBytes(reportDefination);
Property[] rsProperty = new Property[10];
//Property property = new Property();
Warning[] warnings;
try
{
warnings = rs.CreateReport(reportName, "/" + parentFolder, true, byteRDL, null);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
tdMessage.InnerHtml = "Exception in publiching report.
" + ex.Message + "";
return;
}
}

I was using report-specific datasource with authentication. My sample datasource section of the RDL is below
<datasource name="Personal">
<?xml:namespace prefix = rd /><rd:datasourceid>1a234378-11f1-4dc0-bc74-91df6d7d94f7</rd:datasourceid>
<connectionproperties>
<dataprovider>SQL</dataprovider>
<connectstring>Data Source=SODC42\SQLEXPRESS;Initial Catalog=SOP;UID=sa;password=123456;</connectstring>
</connectionproperties>
</datasource>

Though i was putting the connection string with UID and password, still it was showing the textboxes for username and password.
After a lot of google i found that reporting service uses two connection strings 1) one for connection to the report server 2) another for connection the report server to the databse (In mycase the both of report server and DB server is same express version in localhost). The connection string supplied in the RDL is for the first purpose and the second connection information is stored with the DataSource information in the ReportServer DB.
Then i open the report with the reportmanager (http://localhost/reports) and edit the report. In the editor i found the datasources link where i saw the "Credentials supplied by the user running the report" radio is selected. I canged the selection to "Credentials stored securely in the report server" and gave the desired credentials. Then i saw the report is not showinf the text boxes.
Now it was clear that the problem is with the DataSource not with the RDL. Then i went with the idea of using SharedDataSource and attach it with the report. While deploying the report i was checking if the shared datasource is already existing. If not then create it. Here is the code for creating the shared datasource.

DataSourceDefinition def = new DataSourceDefinition();
def.CredentialRetrieval = CredentialRetrievalEnum.Store;
def.ConnectString = ReportConnection.GetConnection("SOP").ConnectionString;
def.Enabled = true;
def.EnabledSpecified = true;
def.Extension = "SQL";
def.ImpersonateUser = false;
def.ImpersonateUserSpecified = true;
def.WindowsCredentials = false;
def.UserName = "sa";
def.Password = "123456";
rs.CreateDataSource(dataSourceName, dataSourcePath, true, def, null);
rs.SetDataSourceContents(dataSourcePath + "/" + dataSourceName, def);

Note:Before creating you have to check whether it already exists of not. And also the folder structure.
The rdl for datasource :
<DataSources>
<DataSource Name="DynamicDataSource">
<DataSourceReference>/Data Sources/DynamicDataSource</DataSourceReference>
</DataSource>
</DataSources>
Now the report is running fine.
But still there was something hitting in my mind. Why i shall use SharedDataSource? I shall use report specific datasource. But how?
After some days lots of RnD s i found it. It is totally my assumtion. I have no idea how far it is true. But it works.
The report specific datasources you have to keep in a folder "Data Sources" in the same directory as the report and then only the report can use it. So i created the datasource in the report folder's "Data Sources" folder. And so far it is running.

Download Sample Project
For information about the project refer to my other post Generate Your Dynamic Report Through Reporting Service

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:http://msdn.microsoft.com/en-us/library/aa337391.aspx

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.WriteStartElement("Report");
writer.WriteAttributeString("xmlns", null, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition");//Writing namespace
writer.WriteAttributeString("xmlns", "rd", null, "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");//Writing namespace


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