September 9, 2011 at 8:58 am
Hi
In the report i am creating i need help to achieve the following
1.Allowing the user to select multiple values from a drop down menu in SSRS. For example, the user can select more than one items A101 and A102, etc. The problem I that having is that if I select both of them no data values will appear in the table. However, if I was to select let’s say A101 data would appear.
2.I want to write a script/code in my SP that will look at the current month and only should 12 months (or whatever month I specify) of data including the current month. For example, it’s now September 2011 I would expect to see data from Nov, Dec, January 12, Feb12, March, April, May, June, July, Aug, Sep12
Can you help me to achieve the above??
I've enclosed a copy of the database schema and the SSRS report. Currently using SQL Server 2008 R2 and SQL Server Business Intelligence Development.
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 9, 2011 at 9:41 am
#1 Under the covers, SSRS uses sp_executesql @Items = 'A101,A102'
If you use a query (not a sp). The code WHERE tbl.Item IN (@Items) will be converted correctly.
However if you use a sp the code will be treated as IN ('A101,A102')... this is a single value.
I preffer to use sps myself so what I do is to create a temp table (not table variable). Then I use my favorite splitter to insert the list of values in that table.
The table as a single column PK clustered (better stats which helps greatly making a great plan).
#2 Many ways to go about this. You could do dateadd(yy, -1, @date) to get the last year.
I personnally use a calendar table for this and the query would look something like this :
--To get the first day of the month 12 months ago
SELECT dt FROM dbo.Calendar WHERE Y = YEAR(@date) - 1 AND M = MONTH(@date) AND D = 1
That gets you the start date, then you can use between with that date and the parameter to get all days in between (then you can filters out the holidays, week-ends, etc).
Link to my calendar table : http://www.sqlservercentral.com/Forums/Attachment8839.aspx
September 12, 2011 at 8:16 am
Ninja's_RGR'us (9/9/2011)
#1 Under the covers, SSRS uses sp_executesql @Items = 'A101,A102'If you use a query (not a sp). The code WHERE tbl.Item IN (@Items) will be converted correctly.
However if you use a sp the code will be treated as IN ('A101,A102')... this is a single value.
I preffer to use sps myself so what I do is to create a temp table (not table variable). Then I use my favorite splitter to insert the list of values in that table.
The table as a single column PK clustered (better stats which helps greatly making a great plan).
#2 Many ways to go about this. You could do dateadd(yy, -1, @date) to get the last year.
I personnally use a calendar table for this and the query would look something like this :
--To get the first day of the month 12 months ago
SELECT dt FROM dbo.Calendar WHERE Y = YEAR(@date) - 1 AND M = MONTH(@date) AND D = 1
That gets you the start date, then you can use between with that date and the parameter to get all days in between (then you can filters out the holidays, week-ends, etc).
Link to my calendar table : http://www.sqlservercentral.com/Forums/Attachment8839.aspx
Many thanks for your reply Ninja's_RGR'us
#1 you suggested that i should use a temp table to insert the values in. From my understanding the problem with this is SSRS does not read temp tables only table variables.
--This type of code would not be recognized in SSRS
select column1, column2
into #temptable
from table
select column1
from #temptable
Do you know of another way or do i have the wrong interpretation??
2# I'm in the process of testing this out. Its more likely I'll have some few questions.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 12, 2011 at 8:25 am
Hi Ninja's_RGR'us
I received the following error message when trying to run your calender script.
Msg 1921, Level 16, State 1, Line 1
Invalid filegroup 'Data Filegroup 1' specified.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Calendar'.
Processed 100 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 200 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 300 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 400 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 500 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 600 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 700 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 800 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 900 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1000 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1100 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1200 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1300 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1400 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1500 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1600 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1700 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1800 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 1900 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2000 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2100 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2200 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2300 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2400 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2500 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2600 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2700 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2800 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 2900 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3000 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3100 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3200 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3300 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3400 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3500 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3600 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3700 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3800 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 3900 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Calendar'.
Processed 4000 total records
Did you experience the same error message? Can you tell me how to fix it
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 12, 2011 at 8:26 am
#1 Is half correct. The query will work but the refresh columns will fail. What you need to do to go around that is to run the query first, then hit refresh columns (this goes around the SET FMT_ONLY ON command. Since it tells the server to NOT RUN any code, then the tables don't exists and you have an error).
#2 Fire away if you need to!
September 12, 2011 at 8:29 am
In the create table script, comment out the ON 'Data Filegroup 1].
That will let the table be created on your default file group.
After that the inserts will work.
September 12, 2011 at 9:35 am
Ninja's_RGR'us (9/12/2011)
#1 Is half correct. The query will work but the refresh columns will fail. What you need to do to go around that is to run the query first, then hit refresh columns (this goes around the SET FMT_ONLY ON command. Since it tells the server to NOT RUN any code, then the tables don't exists and you have an error).
For the point above can you explain to me step by step how to do this cause i cant seem to get it to work.
What i tried to do
1. Create the following temp table in SP and execute to save changes
select Item
into #itemtest
from dbo.January
select *
from #itemtest
SSRS still does not recognize the newly created table
Within SSRS
2. Report Data tab> Datasets > right click add data set> in Dataset Properties
placed the following code in the query section
select Item
into #itemtest
from dbo.January
select *
from #itemtest
Still no luck
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 12, 2011 at 9:40 am
Does it work in SSMS?
Because if it works there you'll be able to populate the dataset and see the results. That I'm 100% sure.
Might be permissions issues if you are not using the same account in both environements.
September 14, 2011 at 2:32 am
It works in SSMS in the sp but when i try to refer to it in BI visual studio (SSRS) i usually get an error message that it doesnt exist. Any ideas why? Am i doing something wrong?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 14, 2011 at 4:57 am
Jnrstevej (9/14/2011)
It works in SSMS in the sp but when i try to refer to it in BI visual studio (SSRS) i usually get an error message that it doesnt exist. Any ideas why? Am i doing something wrong?
Wrong server
Wrong DB
Wrong Owner
Maybe different logins so missing permissions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply