Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Automatically add columns to a query or report if they exist in an input table or query

I have a crosstab query which increases in columns as a feed table is populated to include data for new periods in the year.

An example of the crosstab output is detailed below.....

Measure 01 02 03 etc (pput eriods in a year)
ABC          1   3    7
DEF           2   9   12

From the crosstab i am running a report but i want to automatically include every period as and when the crosstab adds them, is this possible?

Apologies if its not well explained as i am struggling for the right words.

Thanks
0
SweetingA
Asked:
SweetingA
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
Not easily, in a report.

Search EE on "dynamic crosstab report" for discussions of this concept.
0
 
SweetingAAuthor Commented:
is it easy in a query and then i can just add another step?
0
 
Dale FyeCommented:
If your data is normalized with a Period or [SomeDate] field, which then becomes your column headers in the crosstab query, then you should not have to do anything to create the additional column in your query results.

The challenging part is displaying the output in a report.  The query should export just fine to something like Excel, but you eventually run out of page space with a report, and you then have to decide which columns to include in the report.

You could implement some form of paging in your query, which would prevent more than X# of columns per page, and this would not be too difficult, but exporting to Excel is pretty simple.
0
 
SweetingAAuthor Commented:
Used a solution by GrayL which is very simple as long as you know the eventual names of all columns - simply edit the PIVOT line of the SQL.

PIVOT qry_PDM_Results_CY.Period In ("01","02","03","04","05","06","07","08","09","10","11","12");

Thanks, could have looking for days!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now