Reg: Exporting to CSV Format

Hi,
Is there any facility to restrict the dataset not to display the textbox
more than once when exporting to CSV format,when textbox value displays group
value(text).
Many ThanksPlease ignore this one.
prasad
"VP" wrote:
> Hi,
> Is there any facility to restrict the dataset not to display the textbox
> more than once when exporting to CSV format,when textbox value displays group
> value(text).
> Many Thanks
>
>
>|||Hi,
I want to create a CSV or comma delimited file from four MSSQL databases
running on one server. Can anyone assist me?
"VP" wrote:
> Hi,
> Is there any facility to restrict the dataset not to display the textbox
> more than once when exporting to CSV format,when textbox value displays group
> value(text).
> Many Thanks
>
>
>

Reg Split Funtion

Hi,

Is it possible to split the following value in sql server ?

I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database.
Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution ..

I am using ASP.Net and C# backend is SQL Server 2000.

Thanks and Regards
ArulUse Regular expressions to remove all Non Numeric characters.

Refreshing Parameter values when a higher level Parameter changes

Hi All.

I have a parameter (hidden) that gets its value using an expression base on another parameter.
When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.

When I change the parent parameter again, the value of the child parameter does not seem to change.

How can I make this parameter change automatically when the parent is changed ?

Any help will be appreciated.

Thanks,
Neil

I know a little about this, mainly because i have spent a lot of time doing the reverse - breaking the linkage so that parameters don't update when another changes.

The linkage between parameters is achieved via the ParameterDefinition nodes under the Dataset/Query/rd:MdxQuery/Query/Statement* node. These can be added, but can be hard to get right (it is easy to take them out, hard to put them in). What can be the easiest thing to do is create a new report with just the parent and child (hidden) parameters, by default the designer will make the child one dependant on the parent parameter, and you can then check the generated rdl to see how it is done.

Hope this helps!

*this is based on a report that uses a cube and MDX, i don't have a SQL one to hand that i can give an example from.

|||Thank you!

well, this was a point into the right direction.. I landed up setting the available values to the parent parameter and then whenever the parent parameter changed, so did this child one...

Thanks for the help!!

Refreshing Data

Hi All,
I have A Report inwhich Data is Populated from a stored procedure,
When I add a new record in the database that value is not replicated
immediately in the reports.It does replicate after i close the browser and
reopen it.
Cant Understand where Im going wrong'
Can any1 help me out'
Thanks in advance...u can try hitting the refresh button...
other solution could be change the AutoRefresh property
The report doesn't show the new row because is not a "live" report, it needs
to be refreshed to show new data
Hope it helps,
Pablo Diaz
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> Hi All,
> I have A Report inwhich Data is Populated from a stored procedure,
> When I add a new record in the database that value is not replicated
> immediately in the reports.It does replicate after i close the browser and
> reopen it.
> Cant Understand where Im going wrong'
> Can any1 help me out'
> Thanks in advance...|||By default, we do not re-execute the query unless you click the refresh
button or ctrl-refresh in the browser. This is to maintain consistency
between actions like print, pagination, etc.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
>u can try hitting the refresh button...
> other solution could be change the AutoRefresh property
> The report doesn't show the new row because is not a "live" report, it
> needs to be refreshed to show new data
> Hope it helps,
> Pablo Diaz
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
>> Hi All,
>> I have A Report inwhich Data is Populated from a stored procedure,
>> When I add a new record in the database that value is not replicated
>> immediately in the reports.It does replicate after i close the browser
>> and
>> reopen it.
>> Cant Understand where Im going wrong'
>> Can any1 help me out'
>> Thanks in advance...
>|||But even after I clicked the browser refresh button, it still did not work. I
could see from SQL profiler that the stored proc for the report did not get
executed again.
"Brian Welcker [MSFT]" wrote:
> By default, we do not re-execute the query unless you click the refresh
> button or ctrl-refresh in the browser. This is to maintain consistency
> between actions like print, pagination, etc.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> >u can try hitting the refresh button...
> > other solution could be change the AutoRefresh property
> > The report doesn't show the new row because is not a "live" report, it
> > needs to be refreshed to show new data
> >
> > Hope it helps,
> > Pablo Diaz
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> >> Hi All,
> >> I have A Report inwhich Data is Populated from a stored procedure,
> >> When I add a new record in the database that value is not replicated
> >> immediately in the reports.It does replicate after i close the browser
> >> and
> >> reopen it.
> >> Cant Understand where Im going wrong'
> >> Can any1 help me out'
> >>
> >> Thanks in advance...
> >
> >
>
>|||Not the browser refresh button, you want the the refresh button to the right
of export or you have to do a ctrl-F5 (F5 is the browser refresh, Ctrl-F5 is
refresh without caching).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A Fu" <AFu@.discussions.microsoft.com> wrote in message
news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> But even after I clicked the browser refresh button, it still did not
work. I
> could see from SQL profiler that the stored proc for the report did not
get
> executed again.
>
> "Brian Welcker [MSFT]" wrote:
> > By default, we do not re-execute the query unless you click the refresh
> > button or ctrl-refresh in the browser. This is to maintain consistency
> > between actions like print, pagination, etc.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > >u can try hitting the refresh button...
> > > other solution could be change the AutoRefresh property
> > > The report doesn't show the new row because is not a "live" report, it
> > > needs to be refreshed to show new data
> > >
> > > Hope it helps,
> > > Pablo Diaz
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > >> Hi All,
> > >> I have A Report inwhich Data is Populated from a stored
procedure,
> > >> When I add a new record in the database that value is not replicated
> > >> immediately in the reports.It does replicate after i close the
browser
> > >> and
> > >> reopen it.
> > >> Cant Understand where Im going wrong'
> > >> Can any1 help me out'
> > >>
> > >> Thanks in advance...
> > >
> > >
> >
> >
> >|||Unfortunately the application does not use the built-in report viewer at all.
It directly renders the reports in PDF. The solution I'm using right now is
create a dummy report param, the app will pass a random number to this param,
so the URL will be different each time. Not a great solution.
"Bruce L-C [MVP]" wrote:
> Not the browser refresh button, you want the the refresh button to the right
> of export or you have to do a ctrl-F5 (F5 is the browser refresh, Ctrl-F5 is
> refresh without caching).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A Fu" <AFu@.discussions.microsoft.com> wrote in message
> news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> > But even after I clicked the browser refresh button, it still did not
> work. I
> > could see from SQL profiler that the stored proc for the report did not
> get
> > executed again.
> >
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> > > By default, we do not re-execute the query unless you click the refresh
> > > button or ctrl-refresh in the browser. This is to maintain consistency
> > > between actions like print, pagination, etc.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > > >u can try hitting the refresh button...
> > > > other solution could be change the AutoRefresh property
> > > > The report doesn't show the new row because is not a "live" report, it
> > > > needs to be refreshed to show new data
> > > >
> > > > Hope it helps,
> > > > Pablo Diaz
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > > >> Hi All,
> > > >> I have A Report inwhich Data is Populated from a stored
> procedure,
> > > >> When I add a new record in the database that value is not replicated
> > > >> immediately in the reports.It does replicate after i close the
> browser
> > > >> and
> > > >> reopen it.
> > > >> Cant Understand where Im going wrong'
> > > >> Can any1 help me out'
> > > >>
> > > >> Thanks in advance...
> > > >
> > > >
> > >
> > >
> > >
>
>|||Add this to your URL:
rs:ClearSession=true
How things are done really depends on how you are using it (URL Integration,
Web Services, Report Manager).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A Fu" <AFu@.discussions.microsoft.com> wrote in message
news:D702D576-34FC-4760-9952-E18539108432@.microsoft.com...
> Unfortunately the application does not use the built-in report viewer at
all.
> It directly renders the reports in PDF. The solution I'm using right now
is
> create a dummy report param, the app will pass a random number to this
param,
> so the URL will be different each time. Not a great solution.
>
> "Bruce L-C [MVP]" wrote:
> > Not the browser refresh button, you want the the refresh button to the
right
> > of export or you have to do a ctrl-F5 (F5 is the browser refresh,
Ctrl-F5 is
> > refresh without caching).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "A Fu" <AFu@.discussions.microsoft.com> wrote in message
> > news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> > > But even after I clicked the browser refresh button, it still did not
> > work. I
> > > could see from SQL profiler that the stored proc for the report did
not
> > get
> > > executed again.
> > >
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > > > By default, we do not re-execute the query unless you click the
refresh
> > > > button or ctrl-refresh in the browser. This is to maintain
consistency
> > > > between actions like print, pagination, etc.
> > > >
> > > > --
> > > > Brian Welcker
> > > > Group Program Manager
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > > > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > > > >u can try hitting the refresh button...
> > > > > other solution could be change the AutoRefresh property
> > > > > The report doesn't show the new row because is not a "live"
report, it
> > > > > needs to be refreshed to show new data
> > > > >
> > > > > Hope it helps,
> > > > > Pablo Diaz
> > > > >
> > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > > > >> Hi All,
> > > > >> I have A Report inwhich Data is Populated from a stored
> > procedure,
> > > > >> When I add a new record in the database that value is not
replicated
> > > > >> immediately in the reports.It does replicate after i close the
> > browser
> > > > >> and
> > > > >> reopen it.
> > > > >> Cant Understand where Im going wrong'
> > > > >> Can any1 help me out'
> > > > >>
> > > > >> Thanks in advance...
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

refresh identity value

hi,
how can I refresh a identity value of a table after a record gets deleted.

for example a table has 100 rows. One of its column name is rowIndex set to identity 1, incremental 1. After I remove the row with rowIndex value 100, if I insert a new record the rowIndex will be 101, but I want its rowIndex value to be 100. Is there a way to update the identity value after a record gets removed?Yes, but that's not the intention of an identity column. An identity column is intended to automaticaly give you unique record ids.

If you want your rowindex to represent something else then add a trigger to get the max(rowindex) and then add 1.sql

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

sql

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug

Referring to report items

Hi,
Is there any way to directly access a value contained in, for example, a
textbox?
If I were to create a textbox ('textbox1') with a default 'value' attribute
of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
and set its own 'value' to equal it?
I thought maybe it would be something like an expression in textbox2 along
the lines of: "=Controls!textbox1.Value"
Of course, I can see problems if controls aren't rendered etc, but I thought
I'd ask.
Thanks,
CraigTry =ReportItems!textbox1.Value
Regards,
Davy Ramirez
Winsight - Paris
http://www.winsight.fr
"CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
> Hi,
> Is there any way to directly access a value contained in, for example, a
> textbox?
> If I were to create a textbox ('textbox1') with a default 'value'
attribute
> of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
> and set its own 'value' to equal it?
> I thought maybe it would be something like an expression in textbox2 along
> the lines of: "=Controls!textbox1.Value"
> Of course, I can see problems if controls aren't rendered etc, but I
thought
> I'd ask.
> Thanks,
> Craig|||Please see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
for more information about ReportItems and Globals in general.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy Ramirez" <davy.ramirez.removethis@.winsight.fr> wrote in message
news:%23gr%23UsssEHA.820@.TK2MSFTNGP12.phx.gbl...
> Try =ReportItems!textbox1.Value
> Regards,
> Davy Ramirez
> Winsight - Paris
> http://www.winsight.fr
> "CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
> news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
>> Hi,
>> Is there any way to directly access a value contained in, for example, a
>> textbox?
>> If I were to create a textbox ('textbox1') with a default 'value'
> attribute
>> of "Hello World!", how would I make 'textbox2' get the value of
>> 'textbox1'
>> and set its own 'value' to equal it?
>> I thought maybe it would be something like an expression in textbox2
>> along
>> the lines of: "=Controls!textbox1.Value"
>> Of course, I can see problems if controls aren't rendered etc, but I
> thought
>> I'd ask.
>> Thanks,
>> Craig
>

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

referencing value in subreport in calculated field on parent

I wish to reference the value in a subreport in a calculated field
elsewhere...
this subreport has one txtbox, no grid or any other objects.
is this possible?
what is the syntax...'
i.e. =mysubReport.mytxtbox.value...........
thanksOn Sep 28, 2:51 pm, r...@.mgk.com wrote:
> I wish to reference the value in a subreport in a calculated field
> elsewhere...
> this subreport has one txtbox, no grid or any other objects.
> is this possible?
> what is the syntax...'
> i.e. =mysubReport.mytxtbox.value...........
> thanks
You will need to create a dataset in the main report that accesses the
same data (i.e., query/stored procedure) that is used in the subreport
and add the calculation the same way (basically duplicating the
efforts). Then, in the main report, reference this dataset via an
aggregate expression, for example:
=Max(Fields!SomeFieldName.Value, "NewDataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Sep 29, 10:27 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 28, 2:51 pm, r...@.mgk.com wrote:
> > I wish to reference the value in a subreport in a calculated field
> > elsewhere...
> > this subreport has one txtbox, no grid or any other objects.
> > is this possible?
> > what is the syntax...'
> > i.e. =mysubReport.mytxtbox.value...........
> > thanks
> You will need to create a dataset in the main report that accesses the
> same data (i.e., query/stored procedure) that is used in the subreport
> and add the calculation the same way (basically duplicating the
> efforts). Then, in the main report, reference this dataset via an
> aggregate expression, for example:
> =Max(Fields!SomeFieldName.Value, "NewDataSetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
argh! i was afraid of that...sql

Referencing the temporary Dataset in Lookup control SQL

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
ChestonI'm not clear on what you are trying to do.

You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.

Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?

So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null

Then continue on with the dataset with more lookups/transformations.

Does this help?

Cheston|||Use a derived column transformation to set that column to 0 if it is null.

Referencing the return value of a Subreport

I have a subreport inside my main report and it is working just fine. The subreport is called sbrCaseSummary.

In an expression of another txtbox in the main report I want to reference the value returned from the subreport. How do you reference it? I can't find out how that's done. Help.

I am having the exact same problem. Anybody have any ideas?

BJ

Referencing Textbox Values

Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.

You can try this:

=ReportItems!textbox3.value

or

You should be able to share a value in a hidden textbox using code.

Create a hidden textbox. Add the expression =Code.SetValue(mytextboxcontent) to the textbox.

Add the expression =Code.GetValue() to the matrix.

Create 2 functions

public myval as object

function setValue(value as object) as object

myval = value

return value

end function

function getValue() as object
return myval
end function

|||Thanks. You just helped me solve a bigger problem I've been working on for a week now.

Referencing Text Boxes from other Text Boxes

Is it possible to reference the value of a text box (txtField1) in
another text box's expression?
Example:
txtField1 Expression (txtField1 is in Group1 footer)
=Sum(WidgetsCount)
txtField2 Expression (txtField2 is in Group2 footer)
=Sum(WidgetsCount)
txtResultLbl Expression (txtResultLbl is in Group1 header)
=IIf(txtField1 > txtField2, "Greater", "Less")
I am not sure if the cell needs to be referenced through the properties
of the table in which all the cells reside. (Table1)
Your help is apprectiated,
sturgisYou are making a conceptual mistake. You do not want to reference the text
box. For this sort of thing you need to be referencing the field not the
text box. For instance, if you have a group (for a subtotal let's say) then
you put this in:
= sum(Fields!Fieldname1.Value)
On the table footer you can put:
=Sum(Fields!Fieldname1.Value) or you can put
=Sum(Fields!Fieldname1.Value,"Datasetname")
What the textbox is called matters not at all.
Hope that helps.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128357572.656545.186070@.f14g2000cwb.googlegroups.com...
> Is it possible to reference the value of a text box (txtField1) in
> another text box's expression?
> Example:
> txtField1 Expression (txtField1 is in Group1 footer)
> =Sum(WidgetsCount)
> txtField2 Expression (txtField2 is in Group2 footer)
> =Sum(WidgetsCount)
> txtResultLbl Expression (txtResultLbl is in Group1 header)
> =IIf(txtField1 > txtField2, "Greater", "Less")
> I am not sure if the cell needs to be referenced through the properties
> of the table in which all the cells reside. (Table1)
> Your help is apprectiated,
> sturgis
>|||I don't believe I am making a conceptual mistake since this can easily
be done in Microsoft Access reports.
I am creating a header label that is conditioned upon the totals from
two different groups. You would never expect the group closest to the
detail to have a value less than the next group up but, in some cases,
the group totals could be the same. When they are the same, I don't
want to show the group identifier for the group closest to the detail,
only the identifier for the outer group. This cannot be done by
referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
always equal.
Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Hdr2 - NOT USED
Ftr2 (Visible Condition set to equality of DivisionTotals and
AreaTotals)
Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Microsoft Access allows you to reference the text boxes in footers from
expressions in text boxes within headers. Reporting Services has taken
a step backwards if it cannot also be done.
Thanks,
sturgis|||Access and RS are different. You can access textboxes but for what you are
trying to do you should be creating your formulaes based on the field
values. Everything you want to do is possible. RS can do some things Access
can't and Access can do some things that RS can't. Regardless, even if the
end result is the same that does not mean they do it the same way.
Again, you are missing a concept. In BOL search on the phrase expressions.
There will be 5 titles to select from. I suggest reading all 5. They show
doing what you are interested in doing.
If you do as I suggest it will help you make the leap from Access to RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128360396.740290.263370@.g14g2000cwa.googlegroups.com...
>I don't believe I am making a conceptual mistake since this can easily
> be done in Microsoft Access reports.
> I am creating a header label that is conditioned upon the totals from
> two different groups. You would never expect the group closest to the
> detail to have a value less than the next group up but, in some cases,
> the group totals could be the same. When they are the same, I don't
> want to show the group identifier for the group closest to the detail,
> only the identifier for the outer group. This cannot be done by
> referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
> always equal.
> Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Hdr2 - NOT USED
> Ftr2 (Visible Condition set to equality of DivisionTotals and
> AreaTotals)
> Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Microsoft Access allows you to reference the text boxes in footers from
> expressions in text boxes within headers. Reporting Services has taken
> a step backwards if it cannot also be done.
> Thanks,
> sturgis
>|||The nice part of Access reporting was that you didn't have to replicate
the formula in any expression that used it. You merely referenced the
text box with the initial formula. This is better programming practice.
If the formula changes, it changes in all expressions that reference
it. You don't have to try to remember where all the occurances of your
formula are (albeit, there should not be that many).
I have read the 5 titles to which you referred and found no reference
to what I am doing.
Thanks,
sturgis|||I was able to access the text boxes through the ReportItems collection.
Thanks,
sturgis

referencing subreport value

What is the syntax to reference a subreports returning value?

I need to subtotal values returning from a subreport, but I don't know how to reference it.

Thanks,

Hi,

From your description, it seems that you want to reference a subreport in the parent report, right?

Based on my knowledge, you cannot reference a subreport from the parentreport. I suggest that you should have a DataSet in the parent report which achieves the summing work from the datasource or just create a subreport with the subtotals you need.

Thanks.

|||

I found out a way to this with a subreport. Nevermind.

Neither of your suggestions will work because the data in my subreport is referencing a child table.

I cannot use a subreport to do the summing because I need to attach these values when a group changes.

Any other ideas?

Referencing SQLDataSource In a Sub

I have a SqlDataSource - The select statementselects the DB row with an ID that equals a querystring value... So Iknow I am only selecting one row..

Now I want to create a littleSub that grabs any field(s) of my choice and then I can assign thevalue of it to a textbox on my page or a variable if I need to... Anyidea how I actually reference the fields via the SQLDataSource in asub?

I see you can reference parameters using..

SqlDataSource1.SelectParameters()

I was hoping I could use something similar to get the actual DB fields like the below...

SqlDataSource1.Select("MyDataBaseField").Value = TextBox.Text

Can anyone help please??

Thanks

You need to get the data from the control (gridview,detailsview, etc.) that the sqldatasource is attached to. something like GridView1.Rows[0]...|||

Hmmm I feared that... As I didn't want to use a gridview of details veiw etc...

I'll have a re-think... Thanks

|||

I wrote a blog post about doing what I think you need:

http://peterkellner.net/2006/10/27/smalllistaccessimproved/

just use sqldatasource instead of objectdatasource and the same technique should work.

|||Thanks Peter... I think I was trying to do something that just wasnt possible... I have had a re-think... Thanks for the link though|||

If you don't want to use the data controls, then don't use the sqldatasource.

In your sub, just do your own sqlconnection and command and get your data and assign the values to your controls...

Dim conn as new sqlconnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectString)

dim cmd as new sqlcommand("SELECT field1,field2 FROM MyTable WHEREID=@.ID",conn)

cmd.parameters.add("@.ID",sqldbtype.integer).value=request.querystring("ID")

dim dr as datareader

conn.open

dr=cmd.executereader

dr.read

textbox1.text=dr("field1")

textbox2.text=dr("field2")

dr.close

conn.close

|||

Hi Motley,,,, Thanks for the advice ... I had actually gone down that route on Friday, but had the problem below??

http://forums.asp.net/thread/1468801.aspx

Any ideas? Seems to work fine if I don't use the connection string from my web.config... Even though I use the connection for loads of other pages (SQLDataSources) and its fine? Any ideas? Its driving me nuts

|||Hmmm... Start with the code I posted above?

Referencing sql in IF THEN ELSE Statement

The Background:

From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @.ID)"><SelectParameters><asp:QueryStringParameterName="ID"QueryStringField="id"Type="Int32"/></SelectParameters></asp:SqlDataSource>

The Problem:

What I then need to do is an IF THEN ELSE statement.

IF [Package in SQL1] = 4 Then

Package 4

IF [Package in SQL1] = 3 Then

Package 3

Only how do I reference the sql value in the if statement.

I have tried

<%IF Eval("Package") = 4 Then %> and with Bind - No good

Hi asiddle,

If you want to proceed IF then in SQL statement, I suggest you use CASE...WHEN...

You can first put the SelectCommand into a stored procedure, then use CASE...WHEN... to do the switch.

For more information, please check the following link:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hello Kevin,

Many thanks for your reply. Unfortunately I cant make head nor tails of it. I followed your link to msdn but as usual all its actually done is confused the hell out of me. Is it possible you could drop an example?

Many thanks

|||

Hi asiddle,

I suggest you put the CASE...WHEN into a stored procedure, and take @.ID as parameter.

For example:

SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE [ID] =
CASE
WHEN @.ID>1 Then XXXX
WHEN XXXX Then XXXX

|||

[Package] is an integer field ?

SELECT [ID], [CompName], 'Package ' + convert(varchar(10), [Package]) as [Package_Desc]

FROM [CDetails] WHERE ([ID] = @.ID

Referencing ReportItems in Subreport from Main Report

Is this at all possible in SQL 2000 reporting services?
I want to reference a textbox value from the sub report into the main
report.
Thanks
GeorgeOn Dec 6, 6:18 pm, "_george" <none@.nojne@.none> wrote:
> Is this at all possible in SQL 2000 reporting services?
> I want to reference a textbox value from the sub report into the main
> report.
> Thanks
> George
The best way to accommodate this is to create the same dataset that is
used in the subreport's textbox control (where applicable) in the main
report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant