How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to Create Dynamic Report Using Reporting Services

Hi all,
I was wondering is Reporting Services can be used to create dynamic
report as Crystal Report Advanced Edition do? So, we can add new object
based on user requirement through coding.
For example the scenario is (with VB6):
There's 5 checkbox that's reflect 5 database field of a table that will
be choosed by user and appear at the report.
My consideration is, even though Reporting Services can be used at
Windows environment using IE control, the report must be compiled &
built, so if we... we can say... create report runtime is it possible
to do it at Reporting Services?Hi Resant,
YOu have to deploy (publish) the report on the server in order to run it.
You can create an interface that allows the user to create on the fly base on
metadata. But the preview has to come afte a publish.
see this ASP.NET demo, it may has ideas
http://www.rdlcomponents.com/ASPExamples/default.aspx
Thanks
Jerry
"Resant" wrote:
> Hi all,
> I was wondering is Reporting Services can be used to create dynamic
> report as Crystal Report Advanced Edition do? So, we can add new object
> based on user requirement through coding.
> For example the scenario is (with VB6):
> There's 5 checkbox that's reflect 5 database field of a table that will
> be choosed by user and appear at the report.
> My consideration is, even though Reporting Services can be used at
> Windows environment using IE control, the report must be compiled &
> built, so if we... we can say... create report runtime is it possible
> to do it at Reporting Services?
>|||Actually my conception exactly the same as the interface that's shown
by ASP.NET demo, I don't mind if I must learn RDL code, but could you
share how to publish the report on the server automatically (through
coding or the others) ?
I'm interested with RDL Component, how is the component work? Is it
read RDL code and change it into ASP code?
waiting for your reply...|||If you are interested in this I strongly suggest that you look at the new
report controls in the newest beta for Widbey (Visual Studio). There are two
controls: web and winform. They can work with server or work in local mode.
The best thing to understand how RS works is to go read this very good
article here:
http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
RDL stands for Report Definition Language. It is an XML document that
defines the report in a output independent manner. HTML output is just one
of the rendering outputs (RS also does PDF, Excel, etc). The spec is here:
http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Resant" <resant_v@.yahoo.com> wrote in message
news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
> Actually my conception exactly the same as the interface that's shown
> by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> share how to publish the report on the server automatically (through
> coding or the others) ?
> I'm interested with RDL Component, how is the component work? Is it
> read RDL code and change it into ASP code?
> waiting for your reply...
>|||Hi Resant,
The full source code for Publish the report is there.
And the component basically convert (read/write) the XML representation
in objects/collections, in the case the programmer does not want to deal with
the RDL specification and writting raw
XML(http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp).
The ASP.NET interface helps you to select the items to be included in the
report, but its not reading or using any metadata at all.
Hopefully it help you to start.
Thanks
Jerry
--
The first RDL reader/writer of the market.
http://www.rdlcomponents.com
"Resant" wrote:
> Actually my conception exactly the same as the interface that's shown
> by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> share how to publish the report on the server automatically (through
> coding or the others) ?
> I'm interested with RDL Component, how is the component work? Is it
> read RDL code and change it into ASP code?
> waiting for your reply...
>|||Hi Bruce,
Where we can find information abou the web and winform controls?
Thanks
Jerry
"Bruce L-C [MVP]" wrote:
> If you are interested in this I strongly suggest that you look at the new
> report controls in the newest beta for Widbey (Visual Studio). There are two
> controls: web and winform. They can work with server or work in local mode.
> The best thing to understand how RS works is to go read this very good
> article here:
> http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
> RDL stands for Report Definition Language. It is an XML document that
> defines the report in a output independent manner. HTML output is just one
> of the rendering outputs (RS also does PDF, Excel, etc). The spec is here:
> http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Resant" <resant_v@.yahoo.com> wrote in message
> news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
> > Actually my conception exactly the same as the interface that's shown
> > by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> > share how to publish the report on the server automatically (through
> > coding or the others) ?
> >
> > I'm interested with RDL Component, how is the component work? Is it
> > read RDL code and change it into ASP code?
> >
> > waiting for your reply...
> >
>
>|||Check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=8a565186-663e-48aa-b4e2-8bad3056c40f&sloc=en-us
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry" <Jerry@.discussions.microsoft.com> wrote in message
news:1555FEF1-AB9A-4BA1-BA38-B83C5DA7658D@.microsoft.com...
> Hi Bruce,
> Where we can find information abou the web and winform controls?
> Thanks
> Jerry
> "Bruce L-C [MVP]" wrote:
>> If you are interested in this I strongly suggest that you look at the new
>> report controls in the newest beta for Widbey (Visual Studio). There are
>> two
>> controls: web and winform. They can work with server or work in local
>> mode.
>> The best thing to understand how RS works is to go read this very good
>> article here:
>> http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
>> RDL stands for Report Definition Language. It is an XML document that
>> defines the report in a output independent manner. HTML output is just
>> one
>> of the rendering outputs (RS also does PDF, Excel, etc). The spec is
>> here:
>> http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Resant" <resant_v@.yahoo.com> wrote in message
>> news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
>> > Actually my conception exactly the same as the interface that's shown
>> > by ASP.NET demo, I don't mind if I must learn RDL code, but could you
>> > share how to publish the report on the server automatically (through
>> > coding or the others) ?
>> >
>> > I'm interested with RDL Component, how is the component work? Is it
>> > read RDL code and change it into ASP code?
>> >
>> > waiting for your reply...
>> >
>>|||I've answer of my question with :
http://weblogs.sqlteam.com/tarad/archive/2005/01/05/3944.aspx
So by struggling with RDL writing & reading + publish automatically you
can build your dynamic report.sql

how to create dynamic data sources in one report

Hi all
I would like to make one report that could connect to all servers on my
network (not all on the same domain, Mixed mode authintacation).
An example of use would be to click a link for each server to view the
result set for sp_who, sp_databases, version information or anything similar.
Does anyone know a source/article that would get me going in the right
direction? What I have found so far... well I am still looking.
much thanksFrom Books Online (RS 2005). This does not work in RS 2000
>>>>>>>>
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:73ED683D-87CE-416D-AB87-8E071E89C32F@.microsoft.com...
> Hi all
> I would like to make one report that could connect to all servers on my
> network (not all on the same domain, Mixed mode authintacation).
> An example of use would be to click a link for each server to view the
> result set for sp_who, sp_databases, version information or anything
> similar.
> Does anyone know a source/article that would get me going in the right
> direction? What I have found so far... well I am still looking.
> much thanks

how to create dynamic columns in a temporary table

Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like

Key Value X1 x X3 x X5 x Y1 y Y2 y

when user select x, the input variable passed to stored proc and the result is shown like

column names
X1 X3 X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

Here the sample script,

Code Snippet

use tempdb

go

Create Table data (

[Key] Varchar(100) ,

[Value] Varchar(100)

);

Insert Into data Values('X1','x');

Insert Into data Values('X3','x');

Insert Into data Values('X5','x');

Insert Into data Values('Y1','y');

Insert Into data Values('Y2','y');

Code Snippet

create table #temp(dummy bit);

Declare @.Script as Varchar(8000);

Declare @.Script_prepare as Varchar(8000);

Set @.Script_prepare = 'Alter table #temp Add [?] varchar(100);'

Set @.Script = ''

Select

@.Script = @.Script + Replace(@.Script_prepare, '?', [Key])

From

data

Where

[Value] = 'X'

Exec (@.Script)

Alter table #temp drop column dummy;

Select * from #temp;

drop table #temp

|||Hi sekaran,
very nice and thanks alot.

but, i don't know that [key]. it's not static.
it is based on the result from a select query.
how to replace that '?' with the key, i need to write some other logic to get that key...

thanks
PRaveen.|||Can you post more information like result query & etc.?

How to create computed columns(dynamic) in matrix

Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
Any help is greatly appreciated.
Thanks,
VenkatSeems like this is not possible with reporting services!!...
venkat.oar@.gmail.com wrote:
> Hello,
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
> the report should look something like this (M1, M2 are model_id's)
> Div/Sec M1 M2 Variance % Difference
> ----
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
> and my query is :
> select div_sec, model_ID, model_time from table1
> Any help is greatly appreciated.
> Thanks,
> Venkat|||Yes when you select datasets from view menu and right click on the datasets
window you can see a add option. click and you can add calculated fields.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Seems like this is not possible with reporting services!!...
> venkat.oar@.gmail.com wrote:
> > Hello,
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> > and my query is :
> >
> > select div_sec, model_ID, model_time from table1
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> > Venkat
>|||Sorry.. i think u got me wrong here. I am able to add calculated fields
but the data what i have is part of 2 rows.
here's a brief description for you.
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
** *** READ BELOW QUERY..
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Amarnath wrote:
> Yes when you select datasets from view menu and right click on the datase=ts
> window you can see a add option. click and you can add calculated fields.
> Amarnath
> "venkat.oar@.gmail.com" wrote:
> > Seems like this is not possible with reporting services!!...
> >
> > venkat.oar@.gmail.com wrote:
> > > Hello,
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ---=--
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > > and my query is :
> > >
> > > select div_sec, model_ID, model_time from table1
> > >
> > > Any help is greatly appreciated.
> > > > > > Thanks,
> > > Venkat
> > > >|||Is there a way in this scenario to use the ReportItems!<textbox>.Value
syntax? Or, does it fall apart because it's a matrix?
venkat.oar@.gmail.com wrote:
> Sorry.. i think u got me wrong here. I am able to add calculated fields
> but the data what i have is part of 2 rows.
> here's a brief description for you.
>
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
>
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
>
> the report should look something like this (M1, M2 are model_id's)
>
> Div/Sec M1 M2 Variance % Difference
> ----­--
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
>
> and my query is :
> ** *** READ BELOW QUERY..
> select div_sec, model_ID, model_time from table1
> the output of my query is below( seperated by commas)
> div_sec, model_ID, model_time
> O3580,M1,0.7
> O3580,M2,1.47
> 8040 ,M1,1.33
> 8040 ,M2,1.33
> 8110 ,M1,9.98
> 8110 ,M2,14.47
> 11210 ,M1,5.44
> 11210 ,M2,6.36
> Any help is greatly appreciated.
>
> Thanks,
> Venkat
> Amarnath wrote:
>>Yes when you select datasets from view menu and right click on the datasets
>>window you can see a add option. click and you can add calculated fields.
>>Amarnath
>>"venkat.oar@.gmail.com" wrote:
>>
>>Seems like this is not possible with reporting services!!...
>>venkat.oar@.gmail.com wrote:
>>Hello,
>>I am using matrix control for which the rows, columns are all dynamic(
>>based on the group i specify). I am able to get the Subtotal for both
>>the rows and colums, which is Great!!..
>>my problem is i need to add more columns(some formula columns) which
>>might have "% difference", "varience" etc..
>>the report should look something like this (M1, M2 are model_id's)
>>Div/Sec M1 M2 Variance % Difference
>>----
>>O3580 0.71 1.47 -0.76 -107.04
>>8040 1.33 1.33 0 0
>>8110 9.98 14.47 -4.49 -44.99
>>11210 5.44 6.36 -0.92 -16.91
>>and my query is :
>>select div_sec, model_ID, model_time from table1
>>Any help is greatly appreciated.
>>Thanks,
>>Venkat
>>
>|||Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----=--=AD--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the data=sets
> >>window you can see a add option. click and you can add calculated field=s=2E
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>---=--
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >|||Hi try getting all the required data from query, here is the query for that.
see if it works for you.. I think you can put this in table object in SSRS as
well.
select div_sec,
sum((case (model_ID) when 'M1' then (model_time) end)) M1,
sum((case (model_ID) when 'M2' then (model_time) end)) M2,
sum((case (model_ID) when 'M1' then (model_time) end)) - sum((case
(model_ID) when 'M2' then (model_time) end)) Variance
from #temp
group by div_sec
order by 1
You can replace #temp with your table and you can add more columns as well.
This is basically converting all vertical data to horizontal ie pivoting.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Mike,
> I can't even go that far to use ReportItems!<txtbox>.value, the reason
> is when i add column to the matrix, the columns appear for each column
> it is created dynamically( in my case the columns appear for each
> model_ID).
> Still no clue how to do this or is it anyway possible?.
>
> Michael Cervantes wrote:
> > Is there a way in this scenario to use the ReportItems!<textbox>.Value
> > syntax? Or, does it fall apart because it's a matrix?
> >
> >
> >
> > venkat.oar@.gmail.com wrote:
> >
> > > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > > but the data what i have is part of 2 rows.
> > > here's a brief description for you.
> > >
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ----­--
> > >
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > >
> > > and my query is :
> > >
> > > ** *** READ BELOW QUERY..
> > > select div_sec, model_ID, model_time from table1
> > > the output of my query is below( seperated by commas)
> > > div_sec, model_ID, model_time
> > > O3580,M1,0.7
> > > O3580,M2,1.47
> > > 8040 ,M1,1.33
> > > 8040 ,M2,1.33
> > > 8110 ,M1,9.98
> > > 8110 ,M2,14.47
> > > 11210 ,M1,5.44
> > > 11210 ,M2,6.36
> > >
> > > Any help is greatly appreciated.
> > >
> > >
> > > Thanks,
> > > Venkat
> > >
> > > Amarnath wrote:
> > >
> > >>Yes when you select datasets from view menu and right click on the datasets
> > >>window you can see a add option. click and you can add calculated fields.
> > >>
> > >>Amarnath
> > >>
> > >>"venkat.oar@.gmail.com" wrote:
> > >>
> > >>
> > >>Seems like this is not possible with reporting services!!...
> > >>
> > >>venkat.oar@.gmail.com wrote:
> > >>
> > >>Hello,
> > >>
> > >>I am using matrix control for which the rows, columns are all dynamic(
> > >>based on the group i specify). I am able to get the Subtotal for both
> > >>the rows and colums, which is Great!!..
> > >>
> > >>my problem is i need to add more columns(some formula columns) which
> > >>might have "% difference", "varience" etc..
> > >>
> > >>the report should look something like this (M1, M2 are model_id's)
> > >>
> > >>Div/Sec M1 M2 Variance % Difference
> > >>----
> > >>O3580 0.71 1.47 -0.76 -107.04
> > >>8040 1.33 1.33 0 0
> > >>8110 9.98 14.47 -4.49 -44.99
> > >>11210 5.44 6.36 -0.92 -16.91
> > >>
> > >>and my query is :
> > >>
> > >>select div_sec, model_ID, model_time from table1
> > >>
> > >>Any help is greatly appreciated.
> > >>
> > >>Thanks,
> > >>Venkat
> > >>
> > >>
> > >
>|||Hi Venkat, my problem (as posted on 5th July, "Calculations in Matrix
report") is almost identical, so I'll let you know if a solution/workaround
can be found.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152882808.590984.275540@.i42g2000cwa.googlegroups.com...
Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----­--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the
> >>datasets
> >>window you can see a add option. click and you can add calculated
> >>fields.
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>----
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >

how to create a dynamic view?

Is there a way to create a dynamic view, that will read the current month and select records in table for the last 12 months?

thanks

Something like this should work for you: This will alway provide the data for the previous twelve months up to the moment of execution.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
DateCol datetime,
)
GO

CREATE VIEW MyView
AS
SELECT
RowID,
DateCol
FROM MyTable
WHERE DateCol >= ( dateadd( month, -12, getdate() ) )
GO

SET NOCOUNT ON

INSERT INTO MyTable VALUES ( '02/15/2006' )
INSERT INTO MyTable VALUES ( '03/01/2006' )
INSERT INTO MyTable VALUES ( '04/01/2006' )
INSERT INTO MyTable VALUES ( '03/15/2006' )
INSERT INTO MyTable VALUES ( '02/28/2006' )
INSERT INTO MyTable VALUES ( '02/15/2007' )
INSERT INTO MyTable VALUES ( '03/01/2007' )
INSERT INTO MyTable VALUES ( '04/01/2007' )
INSERT INTO MyTable VALUES ( '03/15/2007' )
INSERT INTO MyTable VALUES ( '02/28/2007' )

SELECT *
FROM MyView
ORDER BY DateCol DESC

DROP TABLE MyTable
DROP VIEW MyView

|||You could do something along the lines of the following:

CREATE VIEW YourLastTwelve
AS
SELECT *
FROM YourTable
WHERE YourDateField < CONVERT(varchar(25),
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate())), 101)
AND YourDateField >= CONVERT(varchar(25), DATEADD(yy, -1 ,
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate()))), 101)

-Sue

how to create a dynamic Select statements in my C# project

hi, can someone teach me how to create a dynamic Select statements in my C#
project.
i have this sets of select statements that will return the table name and
fields...and that table name and fields are the one im going to use to
create my dynamic select statements...how can i do this'
thanksProbably the best answer is Don't! In a well-designed business process
application Dynamic SQL is rarely necessary and not often a good idea.
Dynamic SQL is bad news for many reasons to do with performance, security an
d
maintainability. The best practice in a tiered architecture is usually to
build data access code in stored procedures and views and then use
parameterized procedures as your application's entry point to the database.
If you are building a general purpose database admin tool then you might
want to consider putting dynamic code in your application, in which case you
would probably want to use the .Net SqlCommand class. Otherwise you may want
to come back with a bit more information about your requirements so that
someone can advise you better.
David Portas
SQL Server MVP
--|||If your question is about generating dynamic SQL statements using
Transact-SQL, see http://www.sommarskog.se/dynamic_sql.html for a
discussion.
To generate dynamic SQL in application code, simply build a string
containing the needed SQL statement and execute using one of the
System.Data.SqlClient objects. Use command parameters as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"cEciLlE" <cecille.regidor@.gmail.com> wrote in message
news:eHeK54rLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> hi, can someone teach me how to create a dynamic Select statements in my
> C# project.
> i have this sets of select statements that will return the table name and
> fields...and that table name and fields are the one im going to use to
> create my dynamic select statements...how can i do this'
>
> thanks
>