SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Show SQL Query with dynamic columns to RS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

A-bsal
Starting Member

9 Posts

Posted - 12/20/2012 :  09:24:53  Show Profile  Reply with Quote
Hi all

I have the SQL query results with Pivot Table in the first column I have the name of a product, and the following columns, filtering dates for the product, which the filter days (start date - end date) I have more columns or less columns of the query result, each product showing me, how many have been sold.

For example:

name 1-12 2-12 3-12 ...

prod1 5 10 15 ...



As I can show this table as it is in a table or matrix within the report?

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/20/2012 :  11:24:25  Show Profile  Reply with Quote
you've to use matrix with name field as column group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  02:38:34  Show Profile  Reply with Quote
but the problem is that not as the columns are called
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  02:40:58  Show Profile  Reply with Quote
sorry didnt get that. can you explain wit an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 12/21/2012 02:41:25
Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  02:59:18  Show Profile  Reply with Quote
for example, has made ??a query where the filter is from December 1 to December 3 and the result of the query is:
columnName: name - 1Dec - 2Dec - 3Dec
resultQuery: porod1 - 2 - 3 - 5

and another query where the filter is the 5th of December to 7 December and the result of the query is:
columnName: name - 5Dec - 6Dec - 7Dec
resultQuery: porod1 - 4 - 8 - 9
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  03:45:22  Show Profile  Reply with Quote
ok..so whats the issue? you just need to add dates field as column group in matrix

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  03:53:45  Show Profile  Reply with Quote
That I have done, but as I have to configure Reporting Services to display the results correctly?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  04:10:26  Show Profile  Reply with Quote
can you be more specific. what configuration are you taking about? if possible, post a screenshot and explain your issue. cant make out much from your explanation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  04:40:01  Show Profile  Reply with Quote
this is result my query:

and this I would like the result to look like this, but with the values ??of the query:
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  04:47:06  Show Profile  Reply with Quote
oh...you already pivoted it? if you want dynamic columns to appear as values in header easiest thing is to bring them as rows and then do the pivotting in report using matrix.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  04:54:58  Show Profile  Reply with Quote
and how I can make the pivot on the same report? Because the dates if I can get.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  04:58:48  Show Profile  Reply with Quote
your query/dataset will bring one row per date as per parameters passed and in report matrix will pivot them onto columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  05:10:56  Show Profile  Reply with Quote
and how to do the pivot in the matrix?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  05:15:43  Show Profile  Reply with Quote
you dont have to do any pivot. Just include field containing dates inside column group for matrix and apply some aggregates over data field (min,max,sum,avg etc) and you'll get data pivoted automatically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  06:29:03  Show Profile  Reply with Quote
first of all I have to say thanks for all the help you are giving me qye.

Do not quite understand, as shown in the image, which Deveria putting data in cell Data "Datos" for the result then show me results that are in the query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  07:55:10  Show Profile  Reply with Quote
whats the field you're trying to show as data? that what you need to place under datos

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 12/21/2012 :  08:07:10  Show Profile  Reply with Quote
the numbers that appear in the SQL query
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2012 :  09:23:26  Show Profile  Reply with Quote
yep

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000