Solved

SQL Server Report Builder

Posted on 2013-05-30
28
1,016 Views
Last Modified: 2013-07-18
I would like to create a report in report builder that would allow a users to select any table within a database then allow them to select a date range which after selection will provide all fields within that table and date range. The query (a) below gets me the list of tables. I would like then a vairable that takes the selected table name and passes to a query simliar to (b)

(a)
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

(b)
SELECT *
  FROM (the variable)
WHERE (Date BETWEEN (@StartDate) AND (@EndDate))

Is this possible and if so how can I wedge this into SQL server report builder?
0
Comment
Question by:SimonJohnG
  • 12
  • 12
28 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39209691
Use query (a) as the source of the "available values" for your first parameter, and then offer a pair of parameters to enter dates. On pressing View Report the report should reload and (b) will have parameters to provide data to see.

If you put (b) in a stored procedure then you can have it return an acceptable blank row when the report is called first, because it'll get just default values from the parameters.

hth

Mike
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39209961
SSRS doesn't deal very well with datasets that don't always return the same columns. So you're going to need a couple of tricks to get this to work.

Have a read through the following article: Building Reports With Dynamic Datasets

It will help you to understand how SSRS handles this.

I would probably go for a stored procedure that returns generalized field names: f001, f002, f003, ...  In the report you can then use the Fields!f001.IsMissing logic in the Hidden property to show/hide columns.

That stored procedure would need to use dynamic SQL to get this to work - your "FROM (the variable)" part won't work otherwise.

Here's an article to help you get started with that: Generate dynamic SQL statements in SQL Server

And this is also a very interesting article but you'll need a good strong coffee before you start reading it: The Curse and Blessings of Dynamic SQL
0
 

Author Comment

by:SimonJohnG
ID: 39210039
Thanks guys, unfortunately my experience is limited so not sure if I can get a positive result from this. I will read through the articles but likely to prove to much for my limited experience

Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39210124
In that case it won't be very straightforward. Don't hesitate to post questions if you get stuck with something. If you put them in the SSRS topic like you did with this one, I'll be seeing them and I'm sure several others as well.

In the meantime: good luck getting that implemented!
0
 

Author Comment

by:SimonJohnG
ID: 39210588
I created dataset1 with code (a) above and parameter to return a a drop down list. Then created stored procedure (c) below and assigned to dataset2. I dont want to worry about different fields at the moment so will take only the date field from any table selected (more to see if I can do it than anything else) Do I need to now create a third dataset to provide the date field, I'm just not sure what the query would contain?

I know I can get the table name, some how pass that to the stored procedure...how do I then get the date fieled to appear? From another dataset or am I just completely confused! :)

(c)
CREATE PROCEDURE uspSimon
@TableName nvarchar(100)
AS
BEGIN
DECLARE @execquery AS NVARCHAR(MAX)
SET @execquery = N'SELECT * FROM ' + QUOTENAME(@TableName)
EXECUTE sp_executesql @execquery
END


THE ABOVE IS NOW WORKING!!

Unfortunately I've now hit the formatting of the report itself, I see what you mean regarding different field from different tables affecting the report. This coulds be the killer!! Any ideas on a better way of preseting this outside of Report Builder if neccessary?

Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39215442
Hi Simon,

Your report should have a parameter based on first dataset, let's call it TableParam.  This way the user can select a table name.

The CREATE PROCEDURE statement should not be put in a dataset but just executed on your database.  Your second dataset can then use it:

EXEC uspSimon @TableParam

Ensure to have a look at the Parameters page of the dataset to see if the query parameter is linked to the report parameter.

Hope this helps you to get a bit further.

Valentino.
0
 

Author Comment

by:SimonJohnG
ID: 39215498
Hi Valentino,

Thank you for your help so far. All is working but now I have the issue of formatting the report. My problem being that all the selected tables have different field headings. If I format for one table it will be different for another. Would be useful if it was able to place all field headings dynamically without any fixed format.

Regards
Simon
0
 

Author Comment

by:SimonJohnG
ID: 39215579
Is it possible to put a WHERE on this, I tried putting but I just get invalid invalid syntax?

DECLARE @execquery AS NVARCHAR(MAX)
DECLARE @tablename AS NVARCHAR(128)

SET @tablename = 'House1_Card1'
SET @execquery = N'SELECT * FROM ' + QUOTENAME(@tablename)

EXECUTE sp_executesql @execquery


Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39215822
To make the header dynamic your best option is to use an additional dataset that contains the header labels for each possible table, fields would something like:

TableName, ColumnNumber, ColumnLabel

And values like:

'Table A', 1, 'Header 1'
'Table A', 2, 'Header 2'
'Table B', 1, 'Different Header 1'

This dataset can be filtered on that table parameter as well.  And you could then use the Lookup function to retrieve the appropriate value.
See following article for info on that function: Looking Up Data On Different Sources
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39215830
Is it possible to put a WHERE on this, I tried putting but I just get invalid invalid syntax?

DECLARE @execquery AS NVARCHAR(MAX)
DECLARE @tablename AS NVARCHAR(128)

SET @tablename = 'House1_Card1'
SET @execquery = N'SELECT * FROM ' + QUOTENAME(@tablename)

EXECUTE sp_executesql @execquery

Something like this:

SET @execquery = N'SELECT * FROM ' + QUOTENAME(@tablename) + N' WHERE SomeField = ''SomeString'''

(to hardcode a string you need to double the quote if it's inside another string)
0
 

Author Comment

by:SimonJohnG
ID: 39216633
When I create the stored procedure below and execute it I get the error below, I thought I had already declared it?

---------creation script
CREATE PROCEDURE uspSimon
@TableName nvarchar(100),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN
DECLARE @execquery AS NVARCHAR(MAX)
SET @execquery = N'SELECT * FROM ' + QUOTENAME(@TableName)+ N' WHERE (Date BETWEEN (@StartDate) AND (@EndDate))'
EXECUTE sp_executesql @execquery
END

----My execution script
EXEC uspSimon @TableName = 'House1_Card1', @StartDate = '2013-06-01', @EndDate = '2013-06-01'

------Error
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@StartDate".

regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39218263
Try this:

CREATE PROCEDURE uspSimon
@TableName nvarchar(100),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN
DECLARE @execquery AS NVARCHAR(MAX)
SET @execquery = N'SELECT * FROM ' + QUOTENAME(@TableName)
	+ N' WHERE ([Date] BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + '''))'
EXECUTE sp_executesql @execquery
END

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:SimonJohnG
ID: 39218979
Hi there,

I tried the script above and got 'Msg 241, Level 16, State 1, Procedure uspSimon, Line 8
Conversion failed when converting date and/or time from character string'

Do I need to add some kind of conversion or am I entering dates into the execution script incorrectly?

Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39219141
Ow right, overlooked that issue.  The following is then a better approach:

CREATE PROCEDURE uspSimon
@TableName nvarchar(100),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN
DECLARE @execquery AS NVARCHAR(MAX)
declare @Params nvarchar(1000);
set @Params = '@StartDate datetime, @EndDate datetime';
SET @execquery = N'SELECT * FROM' + QUOTENAME(@TableName)
	+ N' WHERE ([Date] BETWEEN @StartDate AND @EndDate)';
EXECUTE sp_executesql @execquery, @Params, @StartDate, @EndDate;
END

Open in new window

Ref. sp_executesql (Transact-SQL)
0
 

Author Comment

by:SimonJohnG
ID: 39221903
Hi again,

All seems to be working even the script to hide colums in the report using (a) in column visibility. My problem is that there may be up to 1600 field names in the report design view and I have to assign the customised script to all. Am I doing this the correct way or am I making to much work for myself. Will this number have an impact in performance?

Is there a better way, even outwith the use of report builder? At the end of the day I am providing an easy method to do (b) but for selectable tables. If they were competant enough they could just use management studio and make less work for me. Is there an alternative tool I can provide that will only allow processing of pre-defined scripts in a 'user' based way?


(a)
=IIF(IsNothing(sum(Fields!Wind_Direction.Value,"DataSet2")),true,false)


(b)
SELECT * FROM [LabView].[dbo].[House1_WeatherStation]

Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39221979
Ouch, 1600... So the database contains a table with 1600 columns?

No idea how this will run, if at all, never came across that issue before...

"If they were competant enough they could just use management studio and make less work for me. Is there an alternative tool I can provide that will only allow processing of pre-defined scripts in a 'user' based way?"

If you give the users read-only access to the DB then they'd be able to use SSMS without messing up the DB. Or you could create views for them to use.

Just wondering: in what context would a non-technical person need to query the tables with a "select *" without any WHERE clause?  I assume these are what they call  "power users"?

In that case perhaps you could consider giving them Report Builder (optional: and access to the report server), then they can create their own reports.
0
 

Author Comment

by:SimonJohnG
ID: 39222248
No, the 1600 would be the sum of all fields over 30 possible tables. I've never used views so worth taking a look at that, can date searches be achieved in views?

There is a WHERE clause based on two given dates, I didnt enter this above but we discussed in the SP.

The users are researchers who dont want to learn SQL or develop reports. The reports they generate will be exported.....believe it or not into Excel as a CSV. If I were able to create a system whereby they seelcted card type and dates then is went directly into Excel as a CSV all the  better. They dont need to view the results in a report.

Regards
Simon
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39222492
"I've never used views so worth taking a look at that, can date searches be achieved in views?"

A view can be queried just like you'd query a table so: yes.

"There is a WHERE clause based on two given dates, I didnt enter this above but we discussed in the SP."

True indeed, sorry for the confusion :)

" If I were able to create a system whereby they seelcted card type and dates then is went directly into Excel as a CSV all the  better."

Hmm interesting.  How about connecting directly to the database from Excel, wouldn't that be more interesting for them?  See Connect to (import) SQL Server data
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39228602
Would you mind explaining why you gave this a B-rate?

Ref. What grade should I award?

A should be the default grade awarded unless the answer is deficient. An A grade means the solution provided is thorough and informative or is a link to information that answered the question. Any links that are posted will be accompanied by a summary of what can be found there and how it helps solve the problem.
 
B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
 
C should only be given for an incomplete solution that does not fully address or answer the question. A C grade should be awarded only after the asker has replied to all expert comments, provided all requested information, tried all suggested solutions, given the experts ample time to reply, and received clarification about the answer given. The asker must justify giving a C grade and give the experts an opportunity to improve it.  If the answer provided is incomplete or unsatisfactory enough to get a C grade, the asker should consider deleting the question entirely.
0
 

Author Comment

by:SimonJohnG
ID: 39228642
Sorry, should have been an A, is it possible to change it?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39228666
Aha, cool! :)  You should be able to click the "request attention" link and describe the change. Then a moderator will pick it up.

Thanks! (and have a nice weekend)
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39228787
@Simon: I requested attention so you don't have to do it anymore :)
0
 

Author Comment

by:SimonJohnG
ID: 39228800
OK, thank you again for your help.

Regards
Simon
0
 

Author Comment

by:SimonJohnG
ID: 39243807
Not sure why this was re-opened but as it is I would like to ask an additional question. I am using the stored procedure below (A) How can I add (B) to this so the user is requested to select tablename and the start and end date? It worked fine separately in SRSS but would like to combine for use outwith report manager

(A)
CREATE PROCEDURE uspSimon
@TableName nvarchar(100),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN
DECLARE @execquery AS NVARCHAR(MAX)
declare @Params nvarchar(1000);
set @Params = '@StartDate datetime, @EndDate datetime';
SET @execquery = N'SELECT * FROM' + QUOTENAME(@TableName)
      + N' WHERE ([Date] BETWEEN @StartDate AND @EndDate)';
EXECUTE sp_executesql @execquery, @Params, @StartDate, @EndDate;
END

(B)
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
0
 

Author Comment

by:SimonJohnG
ID: 39259487
No I didnt want it re-opened as  after I posted I wanted it changed the expert posted "@Simon: I requested attention so you don't have to do it anymore :)"

Regards
Simon
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now