Actions

Information

4 responses

18 01 2013
Asharaf P

Hi ,
Hope you doing good.
i serched for your mail id but i ddnt found from anywere. I am writing here coz i dont have any other way. Sorry if you mind.
I am new to the SQL reporting. I am strated a report with 9 dtasets from 3 diff databases. I was doing this report from past 2 weeks.
i have a requirement
in dataset A i have like

ProdName Range1 range2 sum from dataset B Sum from Dataset C

Nav 101 501 ? ?

Sql 601 1200 ? ?

code.calculate(range1,range2)

in calculate i want to calcuate the sum of amount from DATASET otherthan tablix(from onotherdataset) in between my range.

Is that Possible?

I tried using with the SQL function but i cannot get the filter value which resides in other dataset. I tried to pass it throug the parameter by setting defualt values but not succeeded.

finally i tried with the custome code. I tried the code below
Function westsubsum() As decimal
Dim conn As New System.Data.SqlClient.SqlConnection (“Data Source=asharaf;Initial Catalog=test;Integrated Security=true”)
conn.Open()
Dim Cmd As New System.Data.SqlClient.SqlCommand
Cmd.Connection = conn
Cmd.CommandText = “select dbo.calculatesumwithdate(1000000,2000000,’12/31/2008′)”
Dim totval As Decimal = Cmd.ExecuteScalar()
conn.Close()
return totval
end function
but i am getting #error in that

or is there any otherway to achieve this?

I have around 9 diff dtasets. I am using Report builder 3.0 to build report.

can u help me to solve this issue? Thanks in advance.

Regards,
Asharaf P

21 01 2013
Paul Turley

It looks like you are taking a very complicated approach to solve this. Reporting Services is not designed, and is usually not the best tool, to combine data from several different datasets in this manner and it is not advisable to open connections and source data through custom code. Requests like these typically suggest that a more holistic approach would simplify the problem. Usually, the best approach is to combine the data in the source query. A query can reference multiple databases on the same server by using a three or four-part name for tables, views and other objects; such as: DatabaseName.SchemaName.TableName (example: InvoiceDB.dbo.Customer and Stock.dbo.Product). Using SQL Server features such as linked servers, you can combine data from different database servers that can then be consumed by multiple reports or applications. This usually requires your database administrator to get involved to carefully plan and implement the solution. Without understanding the entire architecture and history of what brought you to this situation, it would be difficult to give you advise about the optimal solution but it might be to transform data from all these different sources into a staging database, data mart or data warehouse. That depends on a lot of factors outside of report design.

21 01 2013
Asharaf P

Hi sir,
Thank you very much for the valuable reply.
That was my scenario and i was traying to solve it with SSRS.
I will try to modify my report according to what u suggested.

2 01 2013
Binu Mathew

Great. Thanks for the sharing the information on SQL Server training.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 257 other followers

%d bloggers like this: