sql dwh problem: based

Showing posts with label based. Show all posts

Thursday, March 29, 2012

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kritisql

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti

Dynamic selecting a table

dynamic row formatting based on dataset field values

HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.

Monday, March 26, 2012

Dynamic query

Hi,
In my stored procedure I have differnet paramters and based on the paramters
different queries needs to be executed against the database.as these queries
share some parts ,I decided to use a dynamic query,so I declared a variable
(DECLARE @.DynamicQuery Varchar(5000)) and based on parameters I append
differnt strings to it (@.DynamicQuery=@.DynamicQuery+ 'string') .The problem
is that this string limits to 750 characters only and it can not hold my
whole query which is more than 750 characters.Why is that so?
Thanks
Here is my sp:
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
WITH RECOMPILE --Throw out the query plan on every execution of this
storedprocedure due to its dynamic nature
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SET @.DynamicQuery = 'SELECT T1.FILEID AS FileID,T1.CURRENT_CGA_REGION_CODE
AS Affiliate_Code,MIN(T1.RECORD_TIMESTAMP) AS
File_ProcessDate,COUNT(T1.FILEID) AS Total_Records,COUNT(CASE
T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID
END) AS NOfRows_with_Biz_Critical_Error,COUNT(CA
SE T1.ERROR_STATUS WHEN 2
THEN T1.FILEID END) AS NOfRows_with_Warning,COUNT(CASE
T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID
END) AS Updated_Different_20,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN
T1.FILEID END) AS Updated_Different_21,COUNT(CASE T1.BIZPROCESSING_STATUS
WHEN 30 THEN T1.FILEID END) AS Matched_NoAction,'
SELECT LEN(@.DynamicQuery )
IF @.ENROLLMENT_TYPR =1 --CURRENT/FUTURE ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,1)
AS Unknown_Active_Members,'
END
ELSE --HISTORY ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,2)
AS Unknown_Active_Members,'
END
SET @.DynamicQuery = @.DynamicQuery +
'T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS
File_Type
FROM IF_C1TRANSFORM T1 LEFT OUTER JOIN IF_FILE T2 ON
T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3 ON
T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID'
IF @.AFFILIATEID <> 1
BEGIN
SET @.DynamicQuery=@.DynamicQuery +
'WHERE T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID'
END
SET @.DynamicQuery=@.DynamicQuery +
'GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME'
SELECT @.DynamicQuery
GO"Ray" <RayAll@.microsft.com> wrote in message
news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In my stored procedure I have differnet paramters and based on the
> paramters
> different queries needs to be executed against the database.as these
> queries
> share some parts ,I decided to use a dynamic query
You should not use a dyniamic query for the purposes of code reuse. Use
dynamic SQL reluctantly and only for good reasons.
Here, this won't hurt a bit...
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SELECT
T1.FILEID AS FileID,
T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
COUNT(T1.FILEID) AS Total_Records,
COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
NOfRows_with_Biz_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
NOfRows_with_Warning,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
Updated_Different_20,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
Updated_Different_21,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
Matched_NoAction,
dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cur
rentAcademicYear,@.CurrentSeesion,case
@.ENROLLMENT_TYPR when 1 then 1 else 2 end)
AS Unknown_Active_Members,
T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
FROM IF_C1TRANSFORM T1
LEFT OUTER JOIN IF_FILE T2
ON T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3
ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
--David|||I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
want to have a where clause ,how dose it work for this purpose?
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u9krc39VFHA.1152@.tk2msftngp13.phx.gbl...
> "Ray" <RayAll@.microsft.com> wrote in message
> news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> You should not use a dyniamic query for the purposes of code reuse. Use
> dynamic SQL reluctantly and only for good reasons.
> Here, this won't hurt a bit...
> CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
> @.AFFILIATEID NUMERIC (8) = 1,
> @.ENROLLMENT_TYPR INT=1 -- 1
> AS
> SET NOCOUNT ON
>
> DECLARE @.CurrentAcademicYear NUMERIC(5)
> DECLARE @.CurrentSeesion NUMERIC(5)
> DECLARE @.DynamicQuery VARCHAR(5000)
>
> SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
> dbo.CURRENT_SESSION
> SELECT
> T1.FILEID AS FileID,
> T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
> MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
> COUNT(T1.FILEID) AS Total_Records,
> COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
> NOfRows_with_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
> NOfRows_with_Biz_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
> NOfRows_with_Warning,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
> Inserted_NewMember,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
> Updated_Different_20,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
> Updated_Different_21,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
> Matched_NoAction,
> dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.C
urrentAcademicYear,@.CurrentSeesion,case
> @.ENROLLMENT_TYPR when 1 then 1 else 2 end)
> AS Unknown_Active_Members,
> T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
> FROM IF_C1TRANSFORM T1
> LEFT OUTER JOIN IF_FILE T2
> ON T1.FILEID=T2.[FILE_ID]
> LEFT OUTER JOIN IF_CONTENT_TYPE T3
> ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
> WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
> GROUP BY
> T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
>
> --David
>
>|||"Ray" <RayAll@.microsft.com> wrote in message
news:%23xjDS%239VFHA.584@.TK2MSFTNGP15.phx.gbl...
>I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
>want to have a where clause ,how dose it work for this purpose?
>
If it adversely affects the execution plan of your query to have the where
clause there, you have a couple of options without resorting to dynamic SQL.
For a query with ony two real variants, I would just code each one as a
static query. That way each one has an appropriate cached plan, and the
whole procedure is much more readable than with dynamic SQL.
David|||>> In my stored procedure I have different paramters and based on the
paramters
different queries needs to be executed against the database.as these
queries share some parts ,I decided to use a dynamic query, <<
Please stop programming and read any basic Software Engineering book.
Look up coupling and cohesion. This is far more fundamental than just
SQL programming. You do not know how to program.
Dynamic SQL is slow, dangerous and an admission that your design is so
weak that a random future user should have more control over it than
you did at design time. I like to name these errors the "Get
BritneySpearsOrSquid" procedures.
Put the core query into a VIEW.|||I so appreciate your help and **thanks for the nice wording** (Excellent way
of helping the others;-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116018663.230642.241190@.g14g2000cwa.googlegroups.com...
> paramters
> different queries needs to be executed against the database.as these
> queries share some parts ,I decided to use a dynamic query, <<
> Please stop programming and read any basic Software Engineering book.
> Look up coupling and cohesion. This is far more fundamental than just
> SQL programming. You do not know how to program.
> Dynamic SQL is slow, dangerous and an admission that your design is so
> weak that a random future user should have more control over it than
> you did at design time. I like to name these errors the "Get
> BritneySpearsOrSquid" procedures.
> Put the core query into a VIEW.
>|||On Fri, 13 May 2005 09:25:55 -0700, Ray wrote:
(an exact copy of a message he also sent 15 minutes earlier)
Hi Ray,
I already replied to your previous message. Please don't multi-post!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dynamic Query

dynamic query

Hello friends,

I want to create a dynamic query based on input of the parameter.

If the user passes nothing then all fields should be displayed else use query based on parameter.

I had view sample of MSDN ,but I got error [BC30203].

Is there another way to it ?Please help.

I use:

= iif(Parameters!SQLQuery.Value<>"",Parameters!SQLQuery.Value,"SELECT somecolumn from sometable") as "select statement"

and provide a query in the SQLQuery-Parameter..

You could also use:

= iif(Parameters!SomeID.Value<>"","SELECT somecolumn from sometable where id=" & Parameters!SQLQuery.Value,"SELECT somecolumn from sometable where id=123")

|||

Thanks For Your Reply

But I m still confusing.

I had used the iif (condition) in the generic query designer but i cannot retrive the fields which I want from the query.

The Query is executing but the data set does not contain any fields.

for eg:

="Select Idnummer,.....

iif(parameter is null,nothing,"AND ART IN ( " & parameter.value & ")")

Please reply sooner.

|||

You can't check your query anymore, thats right. When writing the query as ="select .. " & some_condition .. Hitting the "!"-Button has no effect.. This statement is evaluated at runtime.. So, you have to go to Preview-Mode and check if the result looks right..

If you are missing the Fields!.. for report design, the easiest way is to execute a "normal" sql-statement once (this will add the fields) and then transform your sql-string..

|||

Dynamic query should be avoided wherever possible. It is much harder to do (as you have seen).

I do this, I have a parameter that says All and returns a value of All (it could also return a number if a number field, just make it a value that does not exist in the database.

Do this:

selct * from sometable where (somefield = @.MyParam or @.MyParam = 'All') and ...

|||

I dunno if this is better, but should do the same thing:

The query will be:

select * from TableName
where FieldName LIKE (CASE WHEN @.param IS NULL THEN '%' ELSE @.param END)

|||

Hello Sir,

How can I implement "ALL" in my parameter

The table field does not contain 'ALL" .

If the parameter selected is 'ALL',then query should execute with the parameter contaning 'ALL' the values.Then my problem could be solve if the parameter contains 'ALL'.

please give a sample to implement 'ALL' in my parameter and in query.

Please reply soon.

Thanks

|||

u can use the query in this way,

SELECT AreaName, AreaCode
FROM Area
UNION
SELECT ' All' AS Areaname, '' AS AreaCode
ORDER BY AreaName

This will add 'All' n ur drop down box n using case statement in query u can get the desired results.

regards

Satyendra

Dynamic pivot of resultset

Hi,
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,

> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>

Thursday, March 22, 2012

Dynamic Page Breaks

How can I create dynamic page breaks based on a number of records I only want
to be displayed per page?To create a page break after a specified number of rows:
1. Select the table properties
2. Add a grouping
3. In the Expression field add the following:
=Ceiling(RowNumber(Nothing)/30)
4. Check the â'Page break at endâ' check box.
reeves
"Terry" wrote:
> How can I create dynamic page breaks based on a number of records I only want
> to be displayed per page?|||Thank you so kindly.
By the way, should the new page break group be added to the already existing
groups?
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?|||Terry,
Yes you need to add a group even if groups already exist. Where it is placed
might get a little tricky and need to be played with.
I got this info from a technical article: Report Design: Best Practices and
Guidelines
but it looks like this article got updated to: Report Design Tips and Tricks
(http://msdn2.microsoft.com/en-us/library/bb395166(sql.90).aspx) and there
looks to be another way in the new article to do what your after.
Here is the site for some great articles:
http://msdn2.microsoft.com/en-us/library/aa496080.aspx
The Chart one is VERY good.
Reeves
"Terry" wrote:
> Thank you so kindly.
> By the way, should the new page break group be added to the already existing
> groups?
>
> "Reeves Smith" wrote:
> > To create a page break after a specified number of rows:
> >
> > 1. Select the table properties
> > 2. Add a grouping
> > 3. In the Expression field add the following:
> >
> > =Ceiling(RowNumber(Nothing)/30)
> >
> > 4. Check the â'Page break at endâ' check box.
> >
> > reeves
> >
> > "Terry" wrote:
> >
> > > How can I create dynamic page breaks based on a number of records I only want
> > > to be displayed per page?|||I have tried this on multiple occassions and I always get this error message...
[rsInvalidGroupExpressionScope] A group expression for the table â'table1â'
uses the RowNumber function with a scope parameter that is not valid. When
used in a group expression, the value of the scope parameter of RowNumber
must equal the name of the group directly containing the current group.
Build complete -- 1 errors, 0 warnings
I'm sure there is something else that i am missing...
Any suggestions will be greatly appreciated.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?

Dynamic page break, can we ever do this?

Our reports need to have page breaks based on User's preferences. I
couldn't find a way to achieve this. Can anyone share some ideas?Thanks for your quick response, Teros.
I am sorry I didn't clearly state my problem. What I need is dynamic page
break on groups within a table. Suppose I have 2 groups named 'Location' and
'Category' within an Inventory table. I need enable/disable page break at
the end of each Location or Category based on user settings. I don't see how
this can be done through conditional expression. Is there any workaround?
Thanks.
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:D4705B9E-EA3E-4B76-AF8D-546118BA9F17@.microsoft.com...
> First off, where are the page breaks? After X rows in a report, or after
individual tables, or what? I'd think you could modify the PageBreakAtEnd
or PageBreakAtStart property of various items with a conditional such as
IIf(Parameters!Break.Value = 1, True, False) or something similar, depending
on where you want the page breaks.
> That spark any ideas?
> - T
> "Audrey See" wrote:
> > Our reports need to have page breaks based on User's preferences. I
> > couldn't find a way to achieve this. Can anyone share some ideas?
> >
> >
> >

Wednesday, March 21, 2012

Dynamic object location

Hi, i would like to know if there's a way to set the objects location/size
based on user input parameters. I try to use expressions in location and size
properties but rs says that is a invalid value for the field.
Thanks
Jorge"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge|||I am interested in doing this as well. In particular I want to be able to
compute the height of a rectangle control based on data values.
Bill
"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

sql

Dynamic MDX Query

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

Regards,
Telmo Moreira

|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

Thanks in Advance,

Arun

|||Hi,
is it possible to use a parameter as mentioned below?

select [Measures].[Internet Sales Amount] on 0,
@.ProductCategories on 1
from [Adventure Works]
And the MDX it would actually run against Analysis Services would be:
select [Measures].[Internet Sales Amount] on 0,
{[Product].[Category].&[1],[Product].[Category].&[2]} on 1
from [Adventure Works]

Dynamic lookup with periodical (Yearly) data using JOIN

Hi,

I am generating a dynamic SQL query to perform a lookup based on criterias entered by the user. The quey is generated by a C# application and the target database is Sql Server 2005.

Basically I have 2 tables:

--Cie-- (Over 40k companies, 1 rows per companies, total 40k rows)
(
CieId int not null,
Symbol varchar,
CieName varchar
) Primary key(CieId)

--YearlyData-- (Over 40k companies, 20 years of data per companies, total 800k rows)
(
CieId int not null,
Year int not null,
Revenue currency,
NetIncome currency,
[...over 170 columns]
)Primary key(CieId, Year)

//Sample Case:
The user want to get the list of all the compagnies (CieId) where
YearlyData.NetIncome > 25k for YearlyData.Year 2005
and YearlyData.Revenue > 100k for YearlyData.Year 2005
and YearlyData.Revenue > 95k for YearlyData.Year 2004
and YearlyData.Revenue > 90k for YearlyData.Year 2003
and YearlyData.Revenue of YearlyData.Year 2005 > YearlyData.Revenue of YearlyData.Year 2004

Currently, the Sql query that would be generated looks like this:

SELECT Cie.CieId
FROM Cie
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2005) Y2005 ON Y2005.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2004) Y2004 ON Y2004.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2003) Y2003 ON Y2003.CieId = Cie.CieId
WHERE 1=1
AND Y2005.NetIncome > 25000
AND Y2005.Revenue > 100000
AND Y2004.Revenue > 95000
AND Y2003.Revenue > 90000
AND Y2005.Revenue > Y2004.Revenue

I dont feel good about all those JOINs especially since there is up to 20 years of data and the user could use all of them in his query. The request become slower with each new join.

Does anyone see a better way to do this, maybe a PIVOT or something?

Thanks

Math:

I mocked this up establishing 40k cie entries and 800k yearly records. When I ran your query I got the expected execution plan and the expected performance. When the data is not cached it ran in about 15 seconds and with the data cached in ran in zero seconds. I got, of course, 3 table (clustered index) scans as it read in the yearly data. I got the merge join (which I expected).

My view is that (1) this is a report and (2) it doesn't figure to be run often and therefore performance for this is not critical. Now, I did show about 10,000 logical IOs as it scanned the tables. This will roughly amount to a grab of about 80Meg of buffer cache whenever this query runs. For most modern servers used for SQL Server 2005 this should not be a problem. Since (1) this is a report and doesn't figure to run often and (2) it seems to provide very adequate performance for a report I would not take any time to try to upgrade the performance of this; I don't figure you will get a good return on time invested in this report unless you are having some specific problems.

One thing that can be done to provide a marginal performance boost for this particular is to build a "cover index" for the specific columns you are using for this report; however, I don't feel like this is a good investment either. The modest boost received from the cover index does not figure to be a good tradeoff for the cost of the necessary cover index.

Are you having some specific problems? If so, what are they?

Dave

|||

Dave, thank you for the reply.

I dont have any specific problems other than perfomances, I am just trying to see if there is a better way to construct this kind of query (i.e. without 1 join per year). This is not really a report, it is more like a screener, a tools used when looking for companies to invest in. The screener is used frequently so performance is an issue. The data is readonly with only 1 update at the end of each day so we indexed every columns (grouped logicaly).

The query in the OP was only a sample case, the user can really compare:
"columnX of yearX" with "columnX of yearY"
"columnX of yearX" with "columnY of yearY"
"columnX of yearX" with "columnY of yearX"
"columnX of yearX" with a static value (like 250.95)
and more

Do you think the query can be resructured so it perform better (like removing the "join per year")?

Thanks

|||Would partitioning by year be helpful? It seems to be a common filter.|||

You can do below query instead and there is no reason to join with Cie table if you want just CieId values and there is FK relationship between YearlyData & Cie on CieId column.

select y.CieId

from YearlyData as y

where y.Year in (2003, 2004, 2005)

group by y.CieId

having max(case y.Year when 2005 then NetIncome end) > 25000

and max(case y.Year when 2005 then Revenue end) > 100000

and max(case y.Year when 2004 then Reveue end) > 95000

and max(case y.Year when 2003 then Reveue end) > 90000

and max(case y.Year when 2005 then Revenue end) > max(case y.Year when 2004 then Reveue end)

This query will provide best performance if there is an index on Year or (Year, CieId). You seem to have primary key on (CieId, Year) which will not help for this particular query resulting in a table/index scan.

|||

Math:

Both ideas help. When I ran Umachandar's query it reduced the logical IOs from about 10000 to about 3200, reduced alpha read time to about 4600 ms and reduced beta read time to around 420 ms. When I then partitioned the data on year logical IO was further reduced from 3200 to about 500, alpha read time from 4600ms to about 3200ms and beta read time from 420ms to about 380 ms.

Dave

|||

Both idea looks good, I will see if this query structure is compatible with all my test case and keep you inform.

Thank you for your help.

Monday, March 19, 2012

dynamic join based on column value?

Hi,

I have three tables.

The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.

I need to do a join to a second table based on the MasterID...

However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.

Make sense?

Anyone got any pointers?

Thanks in advance

James

Something like this might work (untested):

Code Snippet

SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM MasterTable m
JOIN TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
JOIN TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)

|||

I am not sure that I get this; maybe something like this?

--drop table dbo.masterTable
--drop table dbo.TableA
--drop table dbo.tableB
go

create table dbo.masterTable
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)
go
insert into dbo.masterTable
select 1, 11, 21, 'True' union all
select 2, 12, 22, 'False' union all
select 3, 13, 23, 'True'
go

create table dbo.TableA
( MasterID integer,
Col2 integer
)
go

insert into tableA
select 1, 101 union all
select 2, 102 union all
select 3, 103
go

create table dbo.TableB
( MasterID integer,
Col2 integer
)
go

insert into tableB
select 1, 201 union all
select 2, 202 union all
select 3, 203
go

SELECT
m.MasterId,
m.Col1 as m_col1,
m.Col2 as m_col2,
b.Col2 as [subfield?]
FROM MasterTable m
JOIN
( select masterID,
'True' as isSubField,
Col2
from TableA
union all
select masterId,
'False' as isSubField,
Col2
from TableB
) b
on m.MasterId = b.masterId
and m.isSubfield = b.isSubfield

/*
MasterId m_col1 m_col2 subfield?
-- -- -- --
1 11 21 101
2 12 22 202
3 13 23 103
*/

|||

Hi,

thanks for that - I haven't tested it yet, but the snytax is working.. so just need to get a few test records in now..

|||

Hi,

I have this as my query - but don't get any results:

SELECT m.MaterialID, m.MasterID, a.ProductName, b.MaterialName, m.IsSubMaterial
FROM redrose1.Materials_Listings m INNER JOIN
dbo.Products a ON m.MasterID = a.ProductID AND (m.IsSubMaterial = 'No' OR
m.IsSubMaterial IS NULL) INNER JOIN
redrose1.Materials b ON m.MasterID = b.MaterialID AND m.IsSubMaterial = 'Yes'
WHERE (m.MaterialID = @.MaterialID)

any ideas?

|||

Both JOINs should be LEFT JOIN.

Code Snippet

SET NOCOUNT ON

DECLARE @.MasterTable table
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)

INSERT INTO @.MasterTable VALUES ( 1, 11, 21, 'True' )
INSERT INTO @.MasterTable VALUES ( 2, 12, 22, 'False' )
INSERT INTO @.MasterTable VALUES ( 3, 13, 23, 'True' )

DECLARE @.TableA table
( MasterID integer,
Col2 integer
)

INSERT INTO @.TableA VALUES ( 1, 101 )
INSERT INTO @.TableA VALUES ( 2, 102 )
INSERT INTO @.TableA VALUES ( 3, 103 )

DECLARE @.TableB table
( MasterID integer,
Col2 integer
)

INSERT INTO @.TableB VALUES ( 1, 201 )
INSERT INTO @.TableB VALUES ( 2, 202 )
INSERT INTO @.TableB VALUES ( 3, 203 )

SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM @.MasterTable m
LEFT JOIN @.TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
LEFT JOIN @.TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)

Col1 Col2 Col2 Col2
-- -- -- --
11 21 101 NULL
12 22 NULL 202
13 23 103 NULL

|||excellent - thanks!!

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articl...b9cc0d4526e1913

Dynamic Index Drop and Create

Dynamic Images in report body

I need to insert a small graphic into a report's cell based on the underlying data. I can get the image into the cells, but have not been successful in having it change depending on the data. I was planning on using the IIF() function to select one of two images. How do I add the link to the images? I haven't been able to get the correct syntax so far.

Any help?

Thanks

Will

If i understand you good you need to display image to acknowlage the states of each elemnet in this report

the best way is to implement dynamic image in the reporting serveice which will depend on a value of one filed from you data source

for example if value is 1 then display image1 else if 1 then display image 2 and so on for a complete example and steps read this article

http://blog.toddtaylor.com/PermaLink,guid,b1517702-ad4b-48e1-a6e3-b736e8a982cb.aspx

|||

That's exactly what I am looking for. I will try this out this morning.

My heart-felt appreciation to you.

Regards,

Will

|||

You welcome Smile ,

Could you please mark the answer which helps you

Thanks,

|||

OK, I finally got it working. Thanks again.

Will

Thursday, March 29, 2012

Monday, March 26, 2012

Thursday, March 22, 2012

Wednesday, March 21, 2012

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Monday, March 19, 2012

Blog Archive

About Me