4 thoughts on “Reporting Services 2012 BI Training

  1. 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 ? ?


    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”)
    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()
    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.

    Asharaf P

    1. 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.

      1. 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.

Leave a Reply