Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>